Example: Oracle Stored Procedure

The following is an example of an Oracle stored procedure body. This is the format that would be required for the Open event of a Virtual Fields activity assuming that the key field is called NUMBER1 and the data fields are called NUMBER2, TEXT1, and TEXT2. In this context, the key field is the input parameter, and the result set is expected to include the data fields and the key field. To accommodate the fact that one of the output parameter names is the same as a key value in the select statement, the parameter keys should be copied to local variables to avoid scoping problems in the procedure.

(NUMBER2 out tablename.number2%TYPE,
TEXT1 out tablename.text1%TYPE,
TEXT2 out tablename.text2%TYPE,
NUMBER1 in out tablename.number1%TYPE)
IS
BEGIN
  DECLARE number1_request tablename.number1%TYPE := number1;
  BEGIN
  SELECT t.NUMBER2, t.TEXT1, t.TEXT2, t.NUMBER1
    INTO number2, text1, text2, number1
    FROM tablename t
   WHERE t.NUMBER1 = number1_request;
  END;
END;