Pass a query statement to a stored procedure

  
SQL>
SQL> CREATE TABLE EMP
  2  (EMPNO NUMBER(4) NOT NULL,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(7, 2),
  8   COMM NUMBER(7, 2),
  9   DEPTNO NUMBER(2)
 10  );

Table created.

SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH',  'CLERK',     7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7499, 'ALLEN',  'SALESMAN',  7698,TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);

1 row created.

SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);

1 row created.

SQL> INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);

1 row created.

SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

1 row created.

SQL>
SQL> create or replace
  2      procedure print_table( p_query in varchar2 )
  3      AUTHID CURRENT_USER is
  4        l_theCursor     integer default dbms_sql.open_cursor;
  5        l_columnValue   varchar2(4000);
  6        l_status        integer;
  7        l_descTbl       dbms_sql.desc_tab;
  8        l_colCnt        number;
  9      begin
 10       dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
 11       dbms_sql.describe_columns
 12         ( l_theCursor, l_colCnt, l_descTbl );
 13
 14       for i in 1 .. l_colCnt loop
 15         dbms_sql.define_column
 16           (l_theCursor, i, l_columnValue, 4000);
 17       end loop;
 18
 19       l_status := dbms_sql.execute(l_theCursor);
 20
 21       while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 22         for i in 1 .. l_colCnt loop
 23           dbms_sql.column_value
 24             ( l_theCursor, i, l_columnValue );
 25           dbms_output.put_line
 26             ( rpad( l_descTbl(i).col_name, 30 )
 27             || ': ' ||
 28             substr( l_columnValue, 1, 200 ) );
 29         end loop;
 30       end loop;
 31
 32  end print_table;
 33  /

Procedure created.

SQL>
SQL> exec print_table( 'select * from emp where empno like ''778%''' );
EMPNO                         : 7782
ENAME                         : CLARK
JOB                           : MANAGER
MGR                           : 7839
HIREDATE                      : 09-JUN-1981 00:00:00
SAL                           : 2450
COMM                          :
DEPTNO                        : 10
EMPNO                         : 7788
ENAME                         : SCOTT
JOB                           : ANALYST
MGR                           : 7566
HIREDATE                      : 09-DEC-1982 00:00:00
SAL                           : 3000
COMM                          :
DEPTNO                        : 20

PL/SQL procedure successfully completed.

SQL>
SQL> drop table emp;

Table dropped.

SQL> --

Related examples in the same category

1.	First DBMS_SQL Example
2.	Use dbms_sql.open_cursor create a cursor
3.	Use dbms_sql.bind_variable, dbms_sql.execute to insert value to a table
4.	Use dbms_sql.bind_array to bind array to a cursor
5.	Use dbms_sql.parse to bind a select statement to a cursor
6.	Call dbms_sql.describe_columns2 to get info for a column
7.	Non-Query DML and DDL Statements
8.	Use DBMS_SQL with the RETURNING clause.
9.	DBMS_SQL.NATIVE;
10.	DBMS_SQL.PARSE and DBMS_SQL.EXECUTE
11.	Dynamic sql statement with variable binding
12.	Dynamic select statement and row fetch
13.	Execute PL/SQL block
14.	DBMS_SQL package: dynamic SQL
15.	Pass where clause to a stored procedure
16.	Executing Queries and use DBMS_SQL.COLUMN_VALUE to map value
17.	Executing PL/SQL Blocks and use BIND_VARIABLE to bind variable
18.	drop the supplied table using dynamic SQL.
19.	DBMS_SQL.BIND_VARIABLE_CHAR
20.	illustrate the importance of setting out_value_size.
21.	Calling a function which uses dynamic SQL from within an SQL statement.
22.	Illustrates the interaction of roles and dynamic SQL.
23.	illustrates a DDL statement which is built dynamically from the procedure parameters.
24.	Use dbms_sql to process query, cursor and value
25.	Use dbms_sql.bind_variable to bind variable
26.	dbms_sql.varchar2_table type variable

Pass a query statement to a stored procedure : dbms_sql « System Packages « Oracle PL / SQL

Related examples in the same category