| Print value in table columns dynamically [message #685531] |
Sun, 30 January 2022 12:02  |
 |
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;
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  |
 |
Michel Cadot
Messages: 68421
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 #685534 is a reply to message #685533] |
Sun, 30 January 2022 14:42 |
|
Michel Cadot
Messages: 68421
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.
[Updated on: Sun, 30 January 2022 14:45] Report message to a moderator |
|
|
|
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
