To use this as a utility script for a dynamically provided SQL query, replace "select * from scott.emp" with "&1", and save the script as (say) "printrows.sql". Then the script can be run from SQLcl, SQL*Plus or SQL Developer with a simple: @printrows "select * from scott.emp"
declare
p_query varchar2(32767) := q'{select * from scott.emp}';
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;
Statement processed.
EMPNO : 7839
ENAME : KING
JOB : PRESIDENT
MGR :
HIREDATE : 17-NOV-1981 00:00:00
SAL : 5000
COMM :
DEPTNO : 10
-----------------
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 : 7566
ENAME : JONES
JOB : MANAGER
MGR : 7839
HIREDATE : 02-APR-1981 00:00:00
SAL : 2975
COMM :
DEPTNO : 20
-----------------
EMPNO : 7788
ENAME : SCOTT
JOB : ANALYST
MGR : 7566
HIREDATE : 19-APR-1987 00:00:00
SAL : 3000
COMM :
DEPTNO : 20
-----------------
EMPNO : 7902
ENAME : FORD
JOB : ANALYST
MGR : 7566
HIREDATE : 03-DEC-1981 00:00:00
SAL : 3000
COMM :
DEPTNO : 20
-----------------
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 : 7654
ENAME : MARTIN
JOB : SALESMAN
MGR : 7698
HIREDATE : 28-SEP-1981 00:00:00
SAL : 1250
COMM : 1400
DEPTNO : 30
-----------------
EMPNO : 7844
ENAME : TURNER
JOB : SALESMAN
MGR : 7698
HIREDATE : 08-SEP-1981 00:00:00
SAL : 1500
COMM : 0
DEPTNO : 30
-----------------
EMPNO : 7876
ENAME : ADAMS
JOB : CLERK
MGR : 7788
HIREDATE : 23-MAY-1987 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 : 7934
ENAME : MILLER
JOB : CLERK
MGR : 7782
HIREDATE : 23-JAN-1982 00:00:00
SAL : 1300
COMM :
DEPTNO : 10
-----------------