PLSQL Help: Execute Immediate with Parameters

Example 1 : Running Execute immediate with an OUT parameter.

I wanted to output a count of tables to see if there is a data difference between Live/Test. I would have created a SQL script but it is hard to share with other developers so I found creating a procedure handy.

Create or replace procedure update_row_count as
l_count number;
l_sql_error_message varchar2(100) :=NULL;
SQL_STMT varchar2(200) :=NULL;
TYPE table_name_type IS VARRAY(7) OF VARCHAR2(30);
table_names table_name_type
:= table_name_type
(‘EMP’,
‘DEPT’,
‘SALARY’);
BEGIN
FOR i IN Table_Names.FIRST..Table_Names.LAST
LOOP
SQL_STMT := ‘SELECT COUNT(*) FROM SCOTT.’||table_names(i);
EXECUTE IMMEDIATE sql_stmt INTO l_count;
l_sql_error_message := SUBSTR(SQLERRM, 1, 100);
DBMS_OUTPUT.PUT_LINE(‘TABLE : ‘||upper(table_names(i)) || ‘ ‘|| l_count);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Others Error Message : ‘||l_sql_error_message);
END;
set serverout on size 10000;
/
exec update_row_count;

For an IN and OUT parameters you would do

EXECUTE IMMEDIATE ‘BEGIN SCOTT.’|| Table_Names(i)||'(:1,:2,:3);END;’

USING parameter1,OUT parameter2,OUT parameter3;

Example 2 : Running Execute immediate with number of IN parameters. In an example below I am sending IN parameters Emp_id , Dept_id, Dept_name,location and retrieving Salary & Emp_rec

Create or replace procedure update_row_count as
sql_stmt VARCHAR2(200);
plsql_block VARCHAR2(500);
emp_id NUMBER(4) := 7566;
salary NUMBER(7,2);
dept_id NUMBER(2) := 50;
dept_name VARCHAR2(14) := ‘PERSONNEL’;
location VARCHAR2(13) := ‘DALLAS’;
emp_rec emp%ROWTYPE;
BEGIN
EXECUTE IMMEDIATE ‘CREATE TABLE bonus (id NUMBER, amt NUMBER)’;
sql_stmt := ‘INSERT INTO dept VALUES (:1, :2, :3)’;
EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
sql_stmt := ‘SELECT * FROM emp WHERE empno = :id’;
EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
plsql_block := ‘BEGIN emp_pkg.raise_salary(:id, :amt); END;’;
EXECUTE IMMEDIATE plsql_block USING 7788, 500;
sql_stmt := ‘UPDATE emp SET sal = 2000 WHERE empno = :1
RETURNING sal INTO :2’;
EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
EXECUTE IMMEDIATE ‘DELETE FROM dept WHERE deptno = :num’
USING dept_id;
EXECUTE IMMEDIATE ‘ALTER SESSION SET SQL_TRACE TRUE’;
END;