4D for Oracle v6.5 contains a new command called
There are many situations where you will want to retrieve OUT parameter data from a Stored Procedure. Frequently, for example, you will want to see a Result Code from an operation, e.g., Success or Failure. A less obvious use for the OUT parameter is for retrieving a Primary Key for a newly inserted row.
Many corporate Oracle implementations will require that common INSERT, UPDATE, and DELETE operations be performed by a Stored Procedure. Before the row is inserted, it is likely that you will not know the Primary Key value. You will frequently want to know what Primary Key value was assigned to the row after the Stored Procedure has inserted the data. This problem can be solved by using the OUT parameter.
Examine the following PL/SQL code:
CREATE OR REPLACE PROCEDURE my_data_insert_prc (
inp_primary_key IN NUMBER,
inp_name_last IN VARCHAR2,
inp_name_first IN VARCHAR2,
inp_name_middle IN VARCHAR2,
inp_date_of_birth IN DATE,
out_primary_key OUT NUMBER)
IF (inp_primary_key IS NULL) OR (inp_primary_key = 0) THEN
SELECT my_sequence.nextval INTO nKey FROM dual;
nKey := inp_primary_key;
INSERT INTO my_table VALUES (
out_primary_key := nKey;
This Stored Procedure accepts the Primary Key as its first parameter, in the event that it was obtained somehow ahead of time. The procedure is built such that if the supplied Primary Key value for the new row is NULL or equal to 0, the next Primary Key value is obtained from the "my_sequence" sequence. Finally, at the very end of the Stored Procedure, whatever Primary Key value was used for the row is assigned to the out_primary_key value.
If you have used OD Execute object to execute the Stored Procedure, e.g.:
$iResult:=OD Execute object (<
The 4D variable "iAssignedKeyValue" will have the Primary Key value for the newly inserted row. This is useful if, for example, the user is adding multiple rows (as if in a loop), and wishes to see a list screen displaying the newly entered data at the end of the process.