Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions
Thanks for the question, Mohammed.
Asked: January 12, 2018 - 7:45 pm UTC
Last updated: October 25, 2019 - 5:50 am UTC
Version: 12
Viewed 10K+ times! This question is
SQL> declare 2 p_query varchar2(32767) := 'select * from 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 execute immediate 20 'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' '; 21 22 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native ); 23 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl ); 24 25 for i in 1 .. l_colCnt loop 26 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000); 27 end loop; 28 29 l_status := dbms_sql.execute(l_theCursor); 30 31 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop 32 for i in 1 .. l_colCnt loop 33 dbms_sql.column_value( l_theCursor, i, l_columnValue ); 34 p( rpad( l_descTbl(i).col_name, 30 ) 35 || ': ' || 36 l_columnValue ); 37 end loop; 38 dbms_output.put_line( '-----------------' ); 39 n := n + 1; 40 end loop; 41 if n = 0 then 42 dbms_output.put_line( chr(10)||'No data found '||chr(10) ); 43 end if; 44 end; 45 / EMPNO : 7369 ENAME : SMITH JOB : CLERK MGR : 7902 HIREDATE : 17-dec-1980 00:00:00 SAL : 800 COMM : DEPTNO : 20 ----------------- EMPNO : 7499 ENAME : ALLEN JOB : SALESMAN MGR : 7698 HIREDATE : 20-feb-1981 00:00:00 SAL : 1600 COMM : 300 DEPTNO : 30 ----------------- EMPNO : 7521 ENAME : WARD JOB : SALESMAN MGR : 7698 HIREDATE : 22-feb-1981 00:00:00 SAL : 1250 COMM : 500 DEPTNO : 30 ----------------- EMPNO : 7566 ENAME : JONES JOB : MANAGER MGR : 7839 HIREDATE : 02-apr-1981 00:00:00 SAL : 2975 COMM : DEPTNO : 20 ----------------- EMPNO : 7654 ENAME : MARTIN JOB : SALESMAN MGR : 7698 HIREDATE : 28-sep-1981 00:00:00 SAL : 1250 COMM : 1400 DEPTNO : 30 ----------------- EMPNO : 7698 ENAME : BLAKE JOB : MANAGER MGR : 7839 HIREDATE : 01-may-1981 00:00:00 SAL : 2850 COMM : DEPTNO : 30 ----------------- 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 ----------------- EMPNO : 7839 ENAME : KING JOB : PRESIDENT MGR : HIREDATE : 17-nov-1981 00:00:00 SAL : 5000 COMM : DEPTNO : 10 ----------------- EMPNO : 7844 ENAME : TURNER JOB : SALESMAN MGR : 7698 HIREDATE : 08-sep-1981 00:00:00 SAL : 1500 COMM : DEPTNO : 30 ----------------- EMPNO : 7876 ENAME : ADAMS JOB : CLERK MGR : 7788 HIREDATE : 12-jan-1983 00:00:00 SAL : 1100 COMM : DEPTNO : 20 ----------------- EMPNO : 7900 ENAME : JAMES JOB : CLERK MGR : 7698 HIREDATE : 03-dec-1981 00:00:00 SAL : 950 COMM : DEPTNO : 30 ----------------- EMPNO : 7902 ENAME : FORD JOB : ANALYST MGR : 7566 HIREDATE : 03-dec-1981 00:00:00 SAL : 3000 COMM : DEPTNO : 20 ----------------- EMPNO : 7934 ENAME : MILLER JOB : CLERK MGR : 7782 HIREDATE : 23-jan-1982 00:00:00 SAL : 1300 COMM : DEPTNO : 10 -----------------
A reader, October 21, 2019 - 10:45 pm UTC
Check out more PL/SQL tutorials on our LiveSQL tool.
PL/SQL reference manual from the Oracle documentation library