KNOWLEDGE BASE
Log In    |    Knowledge Base    |    4D Home
Tech Tip: Using OUT Parameters with 4D for Oracle v6.5
PRODUCT: 4D | VERSION: | PLATFORM:
Published On: June 4, 1999
Your current browser may not allow you to download the asset. We recommend Mozilla Firefox or Google Chrome.
Log In

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)
AS
nKey NUMBER;
BEGIN
IF (inp_primary_key IS NULL) OR (inp_primary_key = 0) THEN
SELECT my_sequence.nextval INTO nKey FROM dual;
ELSE
nKey := inp_primary_key;
END IF;
INSERT INTO my_table VALUES (
nKey,
inp_name_last,
inp_name_first,
inp_name_middle,
inp_date_of_birth);
out_primary_key := nKey;
END;
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.:
iPrimaryKey:=0
sName_Last:="Doe"
sName_First:='John"
sName_Middle:="Guido"
dDate_Birth:=!01/01/1969!
$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.