nirvanastorage
asked on
print table row contents to scree
I'm new to PL/SQL and I'm trying to print the contents of a specific table row using a PL/SQL procedure. The tricky part is that I want to make the procedure generic enough so that it can print the contents of ANY table. There shouldn't be any hard coding of column types.
Here is some pseudo code (NOT WORKING!!!) of what it is that I'm trying to do:
Unfortunately you cannot SELECT INTO a SYS.AnyData variable, which I though was the purpose of AnyData - that it can take ANY data of ANY data type. The print_any (data IN SYS.AnyData) procedure already exists and works. But I really need help with the print_row procedure. It seems such a simple task so there must be something I'm missing...
Tino
Here is some pseudo code (NOT WORKING!!!) of what it is that I'm trying to do:
CREATE OR REPLACE PROCEDURE print_row(table_name IN VARCHAR2(50), table_id IN number) IS
currdata SYS.AnyData;
BEGIN
FOR i < columnCount LOOP
SELECT columnName INTO currdata FROM table_name WHERE table_id = table_id;
print_any(currdata);
END LOOP;
END print_row;
/
Unfortunately you cannot SELECT INTO a SYS.AnyData variable, which I though was the purpose of AnyData - that it can take ANY data of ANY data type. The print_any (data IN SYS.AnyData) procedure already exists and works. But I really need help with the print_row procedure. It seems such a simple task so there must be something I'm missing...
Tino
ASKER
I know the dbms_output.put_line but how can it be used to print an entire table row with all its columns to the screen - without hardcoding all the column names?
How do you want the row data displayed? CSV, fixed length, ???
How do you plan on handling date columns?
You can probably pull this off with an XML trick similar to:
https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?#25864822
I would just like a little more info before I work on the test code.
How do you plan on handling date columns?
You can probably pull this off with an XML trick similar to:
https://www.experts-exchange.com/questions/24914739/In-Oracle-how-can-I-concatenate-multiple-results-returned-from-select-statement-into-a-comma-separated-string.html?#25864822
I would just like a little more info before I work on the test code.
ASKER
The format can be CSV.
In the tables I'm concerned about there are no "date" types but only "timestamp with time zone" types. However, the output format is not important - as long as it prints something I can change the format setting the nls_timestamp_tz_format session variable.
The other column types I'm encountering are mostly varchar2 and number.
In the tables I'm concerned about there are no "date" types but only "timestamp with time zone" types. However, the output format is not important - as long as it prints something I can change the format setting the nls_timestamp_tz_format session variable.
The other column types I'm encountering are mostly varchar2 and number.
I apologize for not getting to this until now. Got tied up.
What version of Oracle does this need to run on?
I'm sure I can come up with something but need to know what version it needs to be compatible with.
What version of Oracle does this need to run on?
I'm sure I can come up with something but need to know what version it needs to be compatible with.
ASKER
11g R2
Hopefully this can wait a while or another Expert will show up later.
The bad news is I don't have access to 11g right now and what I want to do doesn't work with my 10g XE database I have at home.
If you don't have a solution by tomorrow, I'll work on one for you when I get back to my development database.
The bad news is I don't have access to 11g right now and what I want to do doesn't work with my 10g XE database I have at home.
If you don't have a solution by tomorrow, I'll work on one for you when I get back to my development database.
ASKER
10g XE is fine; It's very unlikely that the PL/SQL syntax would have changed in 11g
Sorry but, I want to use XML functions not available in XE. They use Java.
I might be able to kludge something together in XE but it would be very ugly.
I might be able to kludge something together in XE but it would be very ugly.
ASKER
Ugly is OK. This is mainly for debugging purposes - not production.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
compiliation returns error: identifier 'CLOBARRAY' must be declared
I ran that entire script as-is using sqlplus.
Did you see/run:
drop type clobArray;
create type clobArray as table of clob;
/
Did you see/run:
drop type clobArray;
create type clobArray as table of clob;
/
ASKER
this seems to work when I run the
select * from table(print_row('TAB1'));
from sqlplus. But what if I needed to run the print_row from another PL/SQL procedure? How do I call it from there an print the output to the screen?
select * from table(print_row('TAB1'));
from sqlplus. But what if I needed to run the print_row from another PL/SQL procedure? How do I call it from there an print the output to the screen?
We're no back to dbms_outut.put_line. The difference now is the function now outputs a single clob for each row.
In pl/sql something like (untested, typed in on mobile):
Begin
For I in ( select * from table(print_row('TAB1')) loop
Dbms_output.put_line(I.col umn_value) ;
End loop;
End;
/
In pl/sql something like (untested, typed in on mobile):
Begin
For I in ( select * from table(print_row('TAB1')) loop
Dbms_output.put_line(I.col
End loop;
End;
/
You might also tweak the function or remove it all together to fit your needs.
It's possible it doesn't need to be a pipelined function. If not, you don't need the clobarray type.
It's possible it doesn't need to be a pipelined function. If not, you don't need the clobarray type.
ASKER
that works; great thank you!
ASKER
very speedy response
Glad to help!
dbms_output is buffered and flush when the process is complete.
in sqlplus you need to make sure to: set serveroutput on