SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
1 row created.
SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
1 row created.
SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
1 row created.
SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
1 row created.
SQL>
SQL> create or replace procedure print_table( p_query in varchar2 ) AUTHID CURRENT_USER is
2 l_theCursor integer default dbms_sql.open_cursor;
3 l_columnValue varchar2(4000);
4 l_status integer;
5 l_descTbl dbms_sql.desc_tab;
6 l_colCnt number;
7 begin
8 dbms_sql.parse(l_theCursor,p_query,dbms_sql.native);
9
10 dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl);
11
12 for i in 1 .. l_colCnt loop
13 dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000);
14 end loop;
15
16 l_status := dbms_sql.execute(l_theCursor);
17
18 while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
19 for i in 1 .. l_colCnt loop
20
21 dbms_sql.column_value( l_theCursor, i, l_columnValue );
22
23 dbms_output.put_line( rpad( l_descTbl(i).col_name, 30 )|| ': ' ||l_columnValue );
24 end loop;
25 end loop;
26 exception
27 when others then dbms_sql.close_cursor( l_theCursor ); RAISE;
28 end;
29 /
Procedure created.
SQL>
SQL> exec print_table('select * from dept')
DEPTNO : 10
DNAME : ACCOUNTING
LOC : NEW YORK
DEPTNO : 20
DNAME : RESEARCH
LOC : DALLAS
DEPTNO : 30
DNAME : SALES
LOC : CHICAGO
DEPTNO : 40
DNAME : OPERATIONS
LOC : BOSTON
DEPTNO : 10
DNAME : ACCOUNTING
LOC : NEW YORK
DEPTNO : 20
DNAME : RESEARCH
LOC : DALLAS
DEPTNO : 30
DNAME : SALES
LOC : CHICAGO
DEPTNO : 40
DNAME : OPERATIONS
LOC : BOSTON
PL/SQL procedure successfully completed.
SQL>
SQL> drop table dept;
Table dropped.