Skip to Main Content
  • Questions
  • Dynamic query to print out any table

Breadcrumb

May 4th

Question and Answer

Connor McDonald

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 1000+ times

You Asked

Hi Tom
How i can use procedure have a parameter type of query 'any query'
and print the data looks like comma separated ?
please help ..

and Connor said...

Here is a routine to print the columns and rows out down the page. It is trivial to change this to print out with commas instead

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
-----------------



Obviously most tools (sqlplus, sqlcl) already have this functionality built in, so it might be easier just to use those.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, October 21, 2019 - 10:45 pm UTC

Cant we use
execute immediate 'create table t as select * from q_query'
and then print columns(from all_tab_columns)/values(from table t), then drop it ? Only problem is this procedure cannot be run in parallel !!!
Connor McDonald
October 25, 2019 - 5:50 am UTC

You could, but that seems very high resource, ie, storing all of the data just to report on it.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library