Difference between revisions of "Dynamic SQL"
From dbawiki
| Line 1: | Line 1: | ||
* [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]] | * [[http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm Using Dynamic SQL]] | ||
* [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:227413938857 asktom discussion]] | * [[http://asktom.oracle.com/pls/asktom/f?p=100:11:0::NO::P11_QUESTION_ID:227413938857 asktom discussion]] | ||
| + | ===Nice example from a question on AskTom=== | ||
| + | Ref: [https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9536889800346305468 - asktom.oracle.com] | ||
| + | <pre> | ||
| + | set serverout on size 999999 | ||
| + | set verify off | ||
| + | declare | ||
| + | p_query varchar2(32767) := q'{&1}'; | ||
| + | |||
| + | l_theCursor integer default dbms_sql.open_cursor; | ||
| + | l_columnValue varchar2(4000); | ||
| + | l_status integer; | ||
| + | l_descTbl dbms_sql.desc_tab; | ||
| + | l_colCnt number; | ||
| + | n number := 0; | ||
| + | procedure p(msg varchar2) is | ||
| + | l varchar2(4000) := msg; | ||
| + | begin | ||
| + | while length(l) > 0 loop | ||
| + | dbms_output.put_line(substr(l,1,80)); | ||
| + | l := substr(l,81); | ||
| + | end loop; | ||
| + | end; | ||
| + | begin | ||
| + | execute immediate | ||
| + | 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' '; | ||
| + | |||
| + | dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); | ||
| + | dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); | ||
| + | |||
| + | for i in 1 .. l_colCnt loop | ||
| + | dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000); | ||
| + | end loop; | ||
| + | |||
| + | l_status := dbms_sql.execute(l_theCursor); | ||
| + | |||
| + | while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop | ||
| + | for i in 1 .. l_colCnt loop | ||
| + | dbms_sql.column_value( l_theCursor, i, l_columnValue ); | ||
| + | p( rpad( l_descTbl(i).col_name, 30 ) | ||
| + | || ': ' || | ||
| + | l_columnValue ); | ||
| + | end loop; | ||
| + | dbms_output.put_line( '-----------------' ); | ||
| + | n := n + 1; | ||
| + | end loop; | ||
| + | if n = 0 then | ||
| + | dbms_output.put_line( chr(10)||'No data found '||chr(10) ); | ||
| + | end if; | ||
| + | end; | ||
| + | / | ||
| + | |||
| + | set verify on | ||
| + | |||
| + | SQL> @pt "select * from scott.emp where rownum = 1" | ||
| + | EMPNO : 7369 | ||
| + | ENAME : SMITH | ||
| + | JOB : CLERK | ||
| + | MGR : 7902 | ||
| + | HIREDATE : 17-dec-1980 00:00:00 | ||
| + | SAL : 800 | ||
| + | COMM : | ||
| + | DEPTNO : 20 | ||
| + | ----------------- | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | |||
| + | SQL> @pt "select * from scott.dept where rownum = 1" | ||
| + | DEPTNO : 10 | ||
| + | DNAME : ACCOUNTING | ||
| + | LOC : NEW YORK | ||
| + | ----------------- | ||
| + | |||
| + | PL/SQL procedure successfully completed. | ||
| + | |||
| + | SQL> | ||
| + | </pre> | ||
Revision as of 13:19, 9 August 2018
Nice example from a question on AskTom
Ref: - asktom.oracle.com
set serverout on size 999999
set verify off
declare
p_query varchar2(32767) := q'{&1}';
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
n number := 0;
procedure p(msg varchar2) is
l varchar2(4000) := msg;
begin
while length(l) > 0 loop
dbms_output.put_line(substr(l,1,80));
l := substr(l,81);
end loop;
end;
begin
execute immediate
'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
for i in 1 .. l_colCnt loop
dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
end loop;
l_status := dbms_sql.execute(l_theCursor);
while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
p( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
n := n + 1;
end loop;
if n = 0 then
dbms_output.put_line( chr(10)||'No data found '||chr(10) );
end if;
end;
/
set verify on
SQL> @pt "select * from scott.emp where rownum = 1"
EMPNO : 7369
ENAME : SMITH
JOB : CLERK
MGR : 7902
HIREDATE : 17-dec-1980 00:00:00
SAL : 800
COMM :
DEPTNO : 20
-----------------
PL/SQL procedure successfully completed.
SQL> @pt "select * from scott.dept where rownum = 1"
DEPTNO : 10
DNAME : ACCOUNTING
LOC : NEW YORK
-----------------
PL/SQL procedure successfully completed.
SQL>
