Home » SQL & PL/SQL » SQL & PL/SQL » Print value in table columns dynamically
Print value in table columns dynamically [message #685531] Sun, 30 January 2022 12:02 Go to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Hi to all,

Please, can anyone help me?
I would like print, for each record, column value from a table dynamically using cursors table and column.
For example I would like somethings as follow:


declare 

rCursor      sys_refcursor;
rCursorCol   sys_refcursor;
rTableRec    departments%rowtype;
rColRec      all_tab_columns%rowtype;
table_name   varchar2(10) := 'MY_TABLE'
begin

    open rCursor for 'select *
                        from '||table_name;
                 loop
                    fetch rCursor into rTableRec;
                    exit when rCursor%notfound;

                 open rCursorCol for 'select *
                                       from all_tab_columns
                                      where table_name = '||table_name;
                             loop
                                fetch rCursorCol into rColRec;
                                exit when rCursorCol%notfound;

                                 dbms_ouput.put_line('Column Value is: '||rTableRec.rColRec.column_name);

                             end loop;
                 close rCursor;                              
                 end loop;
   close rCursor;
end;
Clearly, the statement "dbms_ouput.put_line(' Column Value is: '||rTableRec.rColRec.column_name);", in the code above, is wrong and raise an error.
But I would like something like that.
Is there a way to print value in table columns dynamically?

Thanks a lot.

[Updated on: Sun, 30 January 2022 13:14]

Report message to a moderator

Re: Print value in table columns dynamically [message #685532 is a reply to message #685531] Sun, 30 January 2022 13:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Have a look at T.Kyte's print_table function.

SQL> exec print_table('select * from emp where rownum<=3');
EMPNO                         : 7369
ENAME                         : SMITH
JOB                           : CLERK
MGR                           : 7902
HIREDATE                      : 17/12/1980 00:00:00
SAL                           : 800
COMM                          :
DEPTNO                        : 20
-----------------
EMPNO                         : 7499
ENAME                         : ALLEN
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 20/02/1981 00:00:00
SAL                           : 1600
COMM                          : 300
DEPTNO                        : 30
-----------------
EMPNO                         : 7521
ENAME                         : WARD
JOB                           : SALESMAN
MGR                           : 7698
HIREDATE                      : 22/02/1981 00:00:00
SAL                           : 1250
COMM                          : 500
DEPTNO                        : 30
-----------------

PL/SQL procedure successfully completed.

[Updated on: Sun, 30 January 2022 13:22]

Report message to a moderator

Re: Print value in table columns dynamically [message #685533 is a reply to message #685532] Sun, 30 January 2022 13:37 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Hi Michel,

to tell you the truth, exactly, I have to not print the values from a table. That is only an example for to see what I want do to.
In particularly I want get a values from a column table dynamically without know the table name.
The table name is a variable.

[Updated on: Sun, 30 January 2022 13:38]

Report message to a moderator

Re: Print value in table columns dynamically [message #685534 is a reply to message #685533] Sun, 30 January 2022 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
In particularly I want get a values from a column table dynamically without know the table name.
The table name is a variable.

As with print_table, you can execute it for any query.
SQL> exec print_table('select sysdate from dual')
SYSDATE                       : 30/01/2022 21:39:27
-----------------

PL/SQL procedure successfully completed.

SQL> exec print_table('select * from dept order by dname')
DEPTNO                        : 10
DNAME                         : ACCOUNTING
LOC                           : NEW YORK
-----------------
DEPTNO                        : 40
DNAME                         : OPERATIONS
LOC                           : BOSTON
-----------------
DEPTNO                        : 20
DNAME                         : RESEARCH
LOC                           : DALLAS
-----------------
DEPTNO                        : 30
DNAME                         : SALES
LOC                           : CHICAGO
-----------------

PL/SQL procedure successfully completed.
If you want to do something other than print the values then replace "dbms_output.put_line" in the code by what you want.


[Updated on: Sun, 30 January 2022 14:45]

Report message to a moderator

Re: Print value in table columns dynamically [message #685538 is a reply to message #685534] Wed, 02 February 2022 05:25 Go to previous messageGo to next message
dancko
Messages: 108
Registered: June 2013
Location: italy
Senior Member
Many thanks for the suggestion Michel!
The print_table procedure has been very useful for me.
I have modified and adapted it for my purpose.
Re: Print value in table columns dynamically [message #685542 is a reply to message #685538] Wed, 02 February 2022 13:10 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Glad it helps you, thanks for your feedback.

Previous Topic: ORA-04068: existing state of packages has been discarded
Next Topic: Oracle case INSERT statement
Goto Forum:
  


Current Time: Fri Mar 29 08:36:48 CDT 2024