Thanks for the question, sandeep.
Asked: February 21, 2017 - 6:47 am UTC
Last updated: February 24, 2017 - 4:10 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom,
I have a requirement wherein a sql saved in a table column (clob type) is executed in plsql . The results will then be used for further processing .
SQLs to be executed will be selected based on some criteria.
eg : Assume the table name is queries and i have 2 records in it each with different sqls
sl. query_txt
1 select name , dob, address from person a , place b where a.address_id = b.id
2 select address,st,zip where person a , shipping_address b where a.address_id = b.address_id
I want a single procedure to execute either query1 or query2 based on some criteria and capture the results for further processing . Can this be done using DBMS_SQL package ? .
and Connor said...
Here's an example of how I use DBMS_SQL to output any query "down the page", but it shows the principles of using DBMS_SQL to access an unknown number of columns and rows.
SQL> set serverout on size 999999
SQL> declare
2 p_query varchar2(32767) := 'select * from scott.emp';
3
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 n number := 0;
10 procedure p(msg varchar2) is
11 l varchar2(4000) := msg;
12 begin
13 while length(l) > 0 loop
14 dbms_output.put_line(substr(l,1,80));
15 l := substr(l,81);
16 end loop;
17 end;
18 begin
19 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
20 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
21
22 for i in 1 .. l_colCnt loop
23 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
24 end loop;
25
26 l_status := dbms_sql.execute(l_theCursor);
27
28 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
29 for i in 1 .. l_colCnt loop
30 dbms_sql.column_value( l_theCursor, i, l_columnValue );
31 p( rpad( l_descTbl(i).col_name, 30 )
32 || ': ' ||
33 l_columnValue );
34 end loop;
35 dbms_output.put_line( '-----------------' );
36 n := n + 1;
37 end loop;
38 if n = 0 then
39 dbms_output.put_line( chr(10)||'No data found '||chr(10) );
40 end if;
41 end;
42 /
EMPNO : 7369
ENAME : SMITH
JOB : CLERK
MGR : 7902
HIREDATE : 17-DEC-80
SAL : 800
COMM :
DEPTNO : 20
-----------------
EMPNO : 7499
ENAME : ALLEN
JOB : SALESMAN
MGR : 7698
HIREDATE : 20-FEB-81
SAL : 1600
COMM : 300
DEPTNO : 30
-----------------
EMPNO : 7521
ENAME : WARD
JOB : SALESMAN
MGR : 7698
HIREDATE : 22-FEB-81
SAL : 1250
COMM : 500
DEPTNO : 30
-----------------
EMPNO : 7566
ENAME : JONES
JOB : MANAGER
MGR : 7839
HIREDATE : 02-APR-81
SAL : 2975
COMM :
DEPTNO : 20
-----------------
EMPNO : 7654
ENAME : MARTIN
JOB : SALESMAN
MGR : 7698
HIREDATE : 28-SEP-81
SAL : 1250
COMM : 1400
DEPTNO : 30
-----------------
EMPNO : 7698
ENAME : BLAKE
JOB : MANAGER
MGR : 7839
HIREDATE : 01-MAY-81
SAL : 2850
COMM :
DEPTNO : 30