PL/SQL Parameters   «Prev  Next»

Oracle PL/SQL Table

Matching parameter modes using PL/SQL

  1. IN, Your procedure updates records based on an incoming value of a record key.
  2. OUT, Your procedure looks at data and then sends a single value to the calling program.
  3. IN OUT, Your procedure receives a value, modifies it, and sends it back to the calling program.
  4. No parameter, Your procedure processes some information based on the current date, which it gets from the database.

Specifying Parameter Modes for Bind Variables in Dynamic SQL Strings

With the USING clause, the mode defaults to IN, so you do not need to specify a parameter mode for input bind arguments. With the RETURNING INTO clause, the mode is OUT, so you cannot specify a parameter mode for output bind arguments. You must specify the parameter mode in more complicated cases, such as this one where you call a procedure from a dynamic PL/SQL block: 

CREATE PROCEDURE create_dept (
deptid IN OUT NUMBER,
dname IN VARCHAR2,
mgrid IN NUMBER,

locid IN NUMBER) AS
BEGIN
SELECT departments_seq.NEXTVAL INTO deptid FROM dual;
INSERT INTO departments VALUES (deptid, dname, mgrid, locid);
END;
/

To call the procedure from a dynamic PL/SQL block, you must specify the IN OUT mode for the bind argument associated with formal parameter deptid, as shown in Example 5-5.

Example 5-5: Using IN OUT Bind Arguments to Specify Substitutions

DECLARE
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/