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

Home
Oracle PL / SQL
1.Aggregate Functions
2.Analytical Functions
3.Char Functions
4.Constraints
5.Conversion Functions
6.Cursor
7.Data Type
8.Date Timezone
9.Hierarchical Query
10.Index
11.Insert Delete Update
12.Large Objects
13.Numeric Math Functions
14.Object Oriented Database
15.PL SQL
16.Regular Expressions
17.Report Column Page
18.Result Set
19.Select Query
20.Sequence
21.SQL Plus
22.Stored Procedure Function
23.Subquery
24.System Packages
25.System Tables Views
26.Table
27.Table Joins
28.Trigger
29.User Previliege
30.View
31.XML
Oracle PL / SQL » System Packages » dbms_sql 
Pass a query statement to a stored procedure
  
SQL>
SQL> CREATE TABLE EMP
  2  (EMPNO NUMBER(4NOT NULL,
  3   ENAME VARCHAR2(10),
  4   JOB VARCHAR2(9),
  5   MGR NUMBER(4),
  6   HIREDATE DATE,
  7   SAL NUMBER(72),
  8   COMM NUMBER(72),
  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);

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

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

row created.

SQL>
SQL> create or replace
  2      procedure print_tablep_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 .. 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_theCursorloop
 22         for i in .. l_colCnt loop
 23           dbms_sql.column_value
 24             l_theCursor, i, l_columnValue );
 25           dbms_output.put_line
 26             rpadl_descTbl(i).col_name, 30 )
 27             || ': ' ||
 28             substrl_columnValue, 1200 ) );
 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
java2s.com  | Contact Us | Privacy Policy
Copyright 2009 - 12 Demo Source and Support. All rights reserved.
All other trademarks are property of their respective owners.