Parameter Modes:
You do not require to specify a parameter mode for the input bind arguments (those used, for illustration, in the WHERE clause) as the mode defaults to IN. Though, you should specify the OUT mode for the output bind arguments used in the RETURNING clause of an INSERT, DELETE, or UPDATE statement. The illustration is as shown below:
DECLARE
sql_stmt VARCHAR2(100);
old_loc VARCHAR2(15);
BEGIN
sql_stmt :=
'DELETE FROM dept WHERE deptno = 20 RETURNING loc INTO :x';
EXECUTE IMMEDIATE sql_stmt USING OUT old_loc;
...
END;
Similarly, when appropriate, you should specify the OUT or IN OUT mode for the bind arguments passed as parameters. For illustration, assume that you want to call the stand-alone procedure as shown below:
CREATE PROCEDURE create_dept (
deptno IN OUT NUMBER,
dname IN VARCHAR2,
loc IN VARCHAR2) AS
BEGIN
deptno := deptno_seq.NEXTVAL;
INSERT INTO dept VALUES (deptno, dname, loc);
END;
To call a procedure from the dynamic PL/SQL block, you should specify the IN OUT mode for the bind argument related with the formal parameter deptno, as shown:
DECLARE
plsql_block VARCHAR2(200);
new_deptno NUMBER(2);
new_dname VARCHAR2(15) := 'ADVERTISING';
new_loc VARCHAR2(15) := 'NEW YORK';
BEGIN
plsql_block := 'BEGIN create_dept(:a, :b, :c); END;';
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptno, new_dname, new_loc;
IF new_deptno > 90 THEN ...
END;