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





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.










31.26.DBMS_SQL
31.26.1.dbms_sql.number_table
31.26.2.Close a cursor
31.26.3.Assign date with DBMS_SQL package
31.26.4.Create Pl/SQL block dynamically and then execute it by calling 'DBMS_SQL.EXECUTE'
31.26.5.DBMS_SQL.PARSE
31.26.6.An example of using DBMS_SQL.OPEN_CURSOR
31.26.7.Package for running dynamic sql based on DBMS_SQL package
31.26.8.Wrap dbms_sql package
31.26.9.Dump Column long with DBMS_SQL.DEFINE_COLUMN_LONG
31.26.10.DBMS_SQL.BIND_VARIABLE and DBMS_SQL.EXECUTE
31.26.11.DBMS_SQL.EXECUTE an update statement
31.26.12.Use DBMS_SQL to update a table and get the number of rows updated
31.26.13.DBMS_SQL.VARCHAR2_TABLE and DBMS_SQL.NUMBER_TABLE
31.26.14.Use dynamic SQL to check the business logic
31.26.15.Use DBMS_SQL package to parse math expression
31.26.16.Use a character string of arithmetic logic, selecting against the DUAL table to return a number value.
31.26.17.DBMS_SQL.LAST_ERROR_POSITION
31.26.18.Dump query with dbms_sql
31.26.19.Use dbms_sql.describe_columns
31.26.20.Print table with dynamic query