Solved

print table row contents to scree

Posted on 2011-02-23
19
919 Views
Last Modified: 2012-05-11
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:

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

Open in new window


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
0
Comment
Question by:nirvanastorage
  • 10
  • 9
19 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34964870
Just to the screen: dbms_output.put_line(columnname);

dbms_output is buffered and flush when the process is complete.

in sqlplus you need to make sure to: set serveroutput on
0
 

Author Comment

by:nirvanastorage
ID: 34964950
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34964999
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:
http://www.experts-exchange.com/Database/Oracle/Q_24914739.html?#25864822

I would just like a little more info before I work on the test code.
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:nirvanastorage
ID: 34965036
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34965818
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.
0
 

Author Comment

by:nirvanastorage
ID: 34965820
11g R2
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34965838
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.
0
 

Author Comment

by:nirvanastorage
ID: 34965856
10g XE is fine; It's very unlikely that the PL/SQL syntax would have changed in 11g
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34965869
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.
0
 

Author Comment

by:nirvanastorage
ID: 34965884
Ugly is OK. This is mainly for debugging purposes - not production.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 34966249
Try this.  Since it isn't production and obviously doesn't have to be as efficient as it could be nor does it appear you need a defined output, I came up with this.

Who knows, it might be quicker what what I was planning?
drop table tab1 purge;
create table tab1(col1 char(1), col2 char(1));
insert into tab1 values('a','b');
insert into tab1 values('c','d');

drop table tab2 purge;
create table tab2(col1 char(1));
insert into tab2 values('a');

drop table tab3 purge;
create table tab3(col1 char(1), col2 char(1), col3 timestamp);
insert into tab3 values('a','b',systimestamp);
commit;

drop type clobArray;
create type clobArray as table of clob;
/

create or replace function print_row(inTab in varchar2)
  return clobArray
  PIPELINED
  as
	mySQL varchar2(32000);
	myCur sys_refcursor;
	myCLOB clob;
  begin

	mySQL := 'select value(q).getclobval() bob from ' || 
		'( ' || 
		'select extract(node,''/*'') myColNodes ' || 
		'from ( ' || 
			'SELECT VALUE(p) node ' || 
			'FROM (select xmltype(dbms_xmlgen.getxml(''select * from ' || inTab || ''')) myXML from dual), ' || 
			'TABLE(XMLSEQUENCE(EXTRACT(myXML, ''/ROWSET/*''))) p ' || 
		') ' || 
		'), TABLE(XMLSEQUENCE(EXTRACT(myColNodes,''/*''))) q ';


	open myCur for mySql;
	loop
    		fetch myCur into myCLOB;
    		exit when myCur%notfound;
         	pipe row(myCLOB);
     	end loop;
	close myCur;
     return;
  end;
/

show errors


select * from table(print_row('TAB1'));
select * from table(print_row('TAB2'));
select * from table(print_row('TAB3'));

Open in new window

0
 

Author Comment

by:nirvanastorage
ID: 34966301
compiliation returns error: identifier 'CLOBARRAY' must be declared
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966318
I ran that entire script as-is using sqlplus.

Did you see/run:
drop type clobArray;
create type clobArray as table of clob;
/

0
 

Author Comment

by:nirvanastorage
ID: 34966400
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966436
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.column_value);
End loop;
End;
/
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966446
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.
0
 

Author Comment

by:nirvanastorage
ID: 34966551
that works; great thank you!
0
 

Author Closing Comment

by:nirvanastorage
ID: 34966553
very speedy response
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34966557
Glad to help!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 8 50
Gettg error - Please help Msg 252, Level 16, State 1, Line 1 3 29
SQL NULL vs Blank 26 36
sql server insert 12 30
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question