Print table with dynamic query : DBMS_SQL « System Packages « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. System Packages
3. DBMS_SQL

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.