Print_Table utility
May 09, 2001
Reviewer: Logo Palanisamy from San Francisco, Bay Area
Very very useful.
Most useful
July 09, 2001
Reviewer: Ashish from Tysons Corner, VA
very very useful, life made so easy
August 06, 2001
Reviewer: A reader
Reader
January 01, 2002
Reviewer: Reader from USA
Tom,
When using print_table for the query that returns about
18 rows, I got the overflow:
BEGIN print_table('select * from v$sqlarea where upper(sql_text) like ''%EMP%'' '); END;
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
ORA-06512: at "SYSTEM.PRINT_TABLE", line 42
ORA-06512: at line 1
Can this script be improved to capture the output with a
utl_file call ;)
Thanks
Followup:
how would that "improve" it?? If it used UTL_FILE, you would actually never "see" it.
change this:
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
to
dbms_output.put_line
(substr( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue, 1, 255 ) );
and realize that if the column is >225 characters, it'll be truncated. I suppose you could write a
fancier word wrap routine to display really long stuff -- and if you do, post it back here (so the
answer is yes, the script can be improved)
Extemely Useful
January 01, 2002
Reviewer: Reader from USA
Tom,
Thanks for this enhancement
I am just learning PL/SQL kudos and caveats from your
web site. Very practical and extremely helpful
Even so, Ill attempt to write word wrap routine, if I do Ill share it
in this site.
best way in 9.2?
September 27, 2002
Reviewer: George from Chester Springs, PA USA
Tom,
I need to write a procedure that will execute an arbitrary query that will return 1 to 40 columns -
the caller gets to write the whole query and I only get to execute and return data, or fail it.
I'm pretty sure I can solve my problem by using dbms_sql.parse to find the number of columns in the
requested query, then writing a dynamic query that lists just those variables that I actually
intend to populate.
I just can't help thinking there should be an easier way...
can you think of one?
here's the first pass I wrote, "hoping" the database might just ignore the extra output
variables... no luck of course ;-)
create type string_list_t as table of varchar2(32767);
/
create procedure exec_dyn_fetch
( p_stmt in varchar2,
p_out1 out string_list_t,
p_out2 out string_list_t,
p_out3 out string_list_t,
p_out4 out string_list_t
)
is
begin
execute immediate p_stmt
bulk collect into
p_out1, p_out2, p_out3, p_out4;
end;
and then an anonymous block to test:
declare
v_var1 string_list_t;
v_var2 string_list_t;
v_var3 string_list_t;
v_var4 string_list_t;
begin
exec_dyn_fetch( 'select table_name from user_tables',
v_var1, v_var2, v_var3, v_var4 );
end;
/
*
ERROR at line 1:
ORA-01007: variable not in select list
ORA-06512: at "GLIBLICK.EXEC_DYN_FETCH", line 10
ORA-06512: at line 7
thanks in advance, you're always a big help :-)
George
Followup:
When you do not know the number of input (bind) or output (defines) columns - you will not be using
native dynamic sql.
You'll use DBMS_SQL from start to finish.
Just like I did in print_table.
sorry
September 27, 2002
Reviewer: George from Chester Springs, PA USA
about my previous posting here... I didn't plan to put it here, but rather
http://asktom.oracle.com/pls/ask/f?p=4950:8:879134::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:3522716
17074,%7Bdynamic%7D%20and%20%7Boutput%7D
I tried hard to find an archived answer first, then got confused with all the windows I had open.
It's been a long day :-(
Followup:
Thats OK -- the print_table example is perfect to go with the question.
[OFFTOPIC] Tom, Print_table is most usefull but..
February 07, 2003
Reviewer: Martin from The Netherlands
Just curious,
Why did you use the 'When others' exception in your procedure whereas you kinda say this is bad
practice here http://asktom.oracle.com/pls/ask/f?p=4950:8:2036387::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:115506
6278457,%7Bwhen%7D%20and%20%7Bothers%7D
Personally I never use When others, I think it stinks.
(Sorry for being offtopic)
Followup:
Well, what I said in that link is:
A when others is almost always a BUG unless it is immediately followed by a
RAISE.
And fortunately I coded:
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/
I just wanted to put the default date format back -- so many things can get screwed up if it isn't
what it should be.
Of course, it would be even better if i queried the value of nls_date_format out and used that --
but for my purposes -- dd-mon-rr is just what I use.
print_table wrap routine
February 07, 2003
Reviewer: Andrew from Troy, MI USA
I took up your challenge (???) to improve the print_table proc so that it wraps before hitting the
255 char limit of dbms_output.put_line. Here it is... (In case it matters to anyone, I did this
in 9.2)
- - - - -
CREATE OR REPLACE
PROCEDURE print_table( p_query IN VARCHAR2 )
AUTHID CURRENT_USER
IS
/*
these two constants manage output formatting for strings.
adjust them as you wish.
*/
c_text_seg_len CONSTANT NUMBER := 65; -- do not exceed 224
c_cont_char CONSTANT VARCHAR2(30) := '.'; -- for continuation
Csr_Da_Cursor INTEGER DEFAULT dbms_sql.open_cursor;
v_Column_Value VARCHAR2(4000);
v_Column_Title VARCHAR(30);
v_Status INTEGER;
v_Table_Desc dbms_sql.desc_tab;
v_Column_Count NUMBER;
v_nls_date_restore VARCHAR2(1000);
BEGIN
/*
this section prepares for resetting then nls_date_format.
adjust it to meet your needs.
*/
-- SELECT 'ALTER SESSION SET nls_date_format = ''' || value ||''''
-- INTO v_nls_date_restore
-- FROM v$parameter
-- WHERE name = 'nls_date_format';
SELECT 'ALTER SESSION SET nls_date_format = ''DD-MON-RR'''
INTO v_nls_date_restore
FROM dual;
EXECUTE IMMEDIATE 'ALTER SESSION SET nls_date_format = ''dd Mon yyyy hh24:mi:ss'' ';
dbms_sql.parse( Csr_Da_Cursor,
p_query,
dbms_sql.native );
dbms_sql.describe_columns ( Csr_Da_Cursor,
v_Column_Count,
v_Table_Desc );
FOR i IN 1 .. v_Column_Count
LOOP
dbms_sql.define_column ( Csr_Da_Cursor,
i,
v_Column_Value,
4000 );
END LOOP;
v_Status := dbms_sql.execute( Csr_Da_Cursor );
WHILE ( dbms_sql.fetch_rows( Csr_Da_Cursor ) > 0 )
LOOP
FOR i IN 1 .. v_Column_Count
LOOP
dbms_sql.column_value ( Csr_Da_Cursor,
i,
v_Column_Value );
IF v_Table_Desc(i).col_type = 1
THEN
IF LENGTH(v_Column_Value) > 0
THEN
BEGIN
v_Column_Title := RPAD( v_Table_Desc(i).col_name, 30 );
FOR j in 1 .. CEIL(LENGTH(v_Column_Value)/c_text_seg_len)
LOOP
dbms_output.put_line(v_Column_Title || ': ' ||
SUBSTR(v_Column_Value, (((j-1)*c_text_seg_len)+1), c_text_seg_len));
v_Column_Title := RPAD( c_cont_char, 30 );
END LOOP;
END;
ELSE
dbms_output.put_line ( RPAD( v_Table_Desc(i).col_name, 30 ) || ': ' || v_Column_Value );
END IF;
ELSIF v_Table_Desc(i).col_type IN ( 8, 23, 24, 112, 113, 114, 115)
THEN
dbms_output.put_line ( RPAD( v_Table_Desc(i).col_name, 30 ) || ': ** UNPRINTABLE **' );
ELSE
dbms_output.put_line ( RPAD( v_Table_Desc(i).col_name, 30 ) || ': ' || v_Column_Value );
END IF;
END LOOP;
dbms_output.put_line( '-----------------' );
END LOOP;
EXECUTE IMMEDIATE v_nls_date_restore;
EXCEPTION
WHEN others THEN
EXECUTE IMMEDIATE v_nls_date_restore;
RAISE;
END;
/
show errors
Very Nice
August 08, 2003
Reviewer: Sikandar Hayat from Pindi Bhattian - Pakistan
I was getting the following error,
ORA-20000: ORU-10028: line length overflow, limit of 255 bytes per line
and I used
SET SERVEROUT ON SIZE 1000000
it worked for me.
BLOBS?
December 18, 2003
Reviewer: Vinnie from Orlando
Tom,
How can I modify the print_table procedure
to process or skip blob data?
Followup:
here is my "coolest" printtbl so far, look for the bolded code and add other types as you like (113
is "blob")
create or replace
procedure print_table
( p_query in varchar2,
p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
-- this utility is designed to be installed ONCE in a database and used
-- by all. Also, it is nice to have roles enabled so that queries by
-- DBA's that use a role to gain access to the DBA_* views still work
-- that is the purpose of AUTHID CURRENT_USER
AUTHID CURRENT_USER
is
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;
l_cs varchar2(255);
l_date_fmt varchar2(255);
-- small inline procedure to restore the sessions state
-- we may have modified the cursor sharing and nls date format
-- session variables, this just restores them
procedure restore
is
begin
if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
then
execute immediate
'alter session set cursor_sharing=exact';
end if;
if ( p_date_fmt is not null )
then
execute immediate
'alter session set nls_date_format=''' || l_date_fmt || '''';
end if;
dbms_sql.close_cursor(l_theCursor);
end restore;
begin
-- I like to see the dates print out with times, by default, the
-- format mask I use includes that. In order to be "friendly"
-- we save the date current sessions date format and then use
-- the one with the date and time. Passing in NULL will cause
-- this routine just to use the current date format
if ( p_date_fmt is not null )
then
select sys_context( 'userenv', 'nls_date_format' )
into l_date_fmt
from dual;
execute immediate
'alter session set nls_date_format=''' || p_date_fmt || '''';
end if;
-- to be bind variable friendly on this ad-hoc queries, we
-- look to see if cursor sharing is already set to FORCE or
-- similar, if not, set it so when we parse -- literals
-- are replaced with binds
if ( dbms_utility.get_parameter_value
( 'cursor_sharing', l_status, l_cs ) = 1 )
then
if ( upper(l_cs) not in ('FORCE','SIMILAR'))
then
execute immediate
'alter session set cursor_sharing=force';
end if;
end if;
-- parse and describe the query sent to us. we need
-- to know the number of columns and their names.
dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );
-- define all columns to be cast to varchar2's, we
-- are just printing them out
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end if;
end loop;
-- execute the query, so we can fetch
l_status := dbms_sql.execute(l_theCursor);
-- loop and print out each column on a separate line
-- bear in mind that dbms_output only prints 255 characters/line
-- so we'll only see the first 200 characters by my design...
while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
loop
for i in 1 .. l_colCnt loop
if ( l_descTbl(i).col_type not in ( 113 ) )
then
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
substr( l_columnValue, 1, 200 ) );
end if;
end loop;
dbms_output.put_line( '-----------------' );
end loop;
-- now, restore the session state, no matter what
restore;
exception
when others then
restore;
raise;
end;
/
print table
February 13, 2004
Reviewer: prakash from India
Print_table a nice feature
April 13, 2004
Reviewer: Shashidhar Chandrappa from Cincinnati, OH USA
It's a nice feature. It saves me so much of time in formating my output all the time.
8.1.7 installation non-initialization
May 03, 2004
Reviewer: OLALEKAN AJIROBA from nigeria
Dear Sir/Madam,
I am trying to install Oracle8i Release3 ver 8.1.7 on Windows 2000 Server with Service Pack 3
running compaq Proliant Server.
After inserting the CD for Oracle I choose Install/Deinstall Products from the initial menu.
After some time the Initial Menu closes and no other screen comes up.
In fact nothing at all happens, not even an error shows.
Following is my System Configuration:
Intel(R) Xeo
n(TM)CPU 2.4GHz
AT/AT COMPATIBLE
2,096,660KBRAM
3x36GB SCSI Hard Drives
RAID 5 Configuration
Extra Slot for another Processor
I hope that you have heard of this problem before and can send me a list of possible solutions.
Followup:
please contact support, sounds like the well known "p4" issue with the java sdk on that platform.
May 03, 2004
Reviewer: A reader
Hey Tom,
Is there a way to contact support if I am installing Personal Oracle? I'm assuming not, but I
wanted to check. Thanks.
Followup:
if you have licensed support -- absolutely.
Warning when using print_table
June 29, 2004
Reviewer: Richard Manton from Australia
A warning should be issued when using the print_table script.
If you have any uncommitted changes prior to running the print_table script they get committed by
the script as it uses ddl.
Followup:
alter session doesn't commit.
so, what DDL do you see that commits?
ops$tkyte@ORA9IR2> drop table t;
Table dropped.
ops$tkyte@ORA9IR2> create table t ( x int );
Table created.
ops$tkyte@ORA9IR2> insert into t values ( 1 );
1 row created.
ops$tkyte@ORA9IR2> exec print_table( 'select * from t' );
X : 1
-----------------
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> rollback;
Rollback complete.
ops$tkyte@ORA9IR2> select * from t;
no rows selected
ops$tkyte@ORA9IR2>
it does not commit..
Very Nice!
June 30, 2004
Reviewer: Bjorn Hedlin from Sweden
print_table proc
July 20, 2004
Reviewer: Murali from India
Very useful utility comes handy.
Thanks
Murali
Tried to find out the error line length overflow
August 11, 2004
Reviewer: A reader from India
DBA
November 14, 2004
Reviewer: Vinod.G from BAHRAIN
This has been very useful to manupulate the staged data.
procedure print_table
November 24, 2004
Reviewer: Jim Cox from USA
Could not get any output; Must be missing something;
Using Oracle 9i
SQL> exec print_table ('select * from v$database');
PL/SQL procedure successfully completed.
Followup:
SQL> set serveroutput on size 1000000
put that in your login.sql, it is a pre-req for dbms_output.put_line to be displayed in sqlplus.
procedure print_table
November 24, 2004
Reviewer: Jim Cox from USA
Sorry....
my error on last comment
forgot to set serveroutput on
Print_table Tool
December 16, 2004
Reviewer: Rex Bugcalao from Philippines
This is very helpful!!
Im new to PL/SQL.
Im very Impressed!! :)
Update for 9iR2?
December 21, 2004
Reviewer: A reader
Is there an update for your print_table with 9iR2 that doesnt use dbms_output? Using object types,
pipelined functions and all that good stuff?
Thanks
Followup:
dbms_output seems like a good fit to me?
"ain't broke" "don't fix"
comes to mind.
print_table('select * from emp')
seems easier than
select * from table( print_table( 'select * from emp' ) );
as well -- but it would be really easy to make it pipelined yourself -- very little change is
actually needed.
Pipelined
December 24, 2004
Reviewer: A reader
"but it would be really easy to make it pipelined yourself -- very little change is actually
needed"
Well, the pipelined table function needs to return a user-defined type. The query is dynamic, can
contain any number of columns, so how can I set up a TYPE for the function?
Followup:
there are precisely two columns in print_table output!
ops$tkyte@ORA9IUTF> create or replace type printTblScalar as object
2 ( cname varchar2(30),
3 cvalue varchar2(4000)
4 )
5 /
Type created.
ops$tkyte@ORA9IUTF> show errors
No errors.
ops$tkyte@ORA9IUTF>
ops$tkyte@ORA9IUTF> create or replace type printTblTable as table of printTblScalar
2 /
Type created.
ops$tkyte@ORA9IUTF> create or replace function print_table_pipelined
2 ( p_query in varchar2,
3 p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
4 return printTblTable
5 authid current_user
6 PIPELINED
7 is
8 pragma autonomous_transaction;
9 l_theCursor integer default dbms_sql.open_cursor;
10 l_columnValue varchar2(4000);
11 l_status integer;
12 l_descTbl dbms_sql.desc_tab2;
13 l_colCnt number;
14 l_cs varchar2(255);
15 l_date_fmt varchar2(255);
16
17 -- small inline procedure to restore the sessions state
18 -- we may have modified the cursor sharing and nls date format
19 -- session variables, this just restores them
20 procedure restore
21 is
22 begin
23 if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
24 then
25 execute immediate
26 'alter session set cursor_sharing=exact';
27 end if;
28 if ( p_date_fmt is not null )
29 then
30 execute immediate
31 'alter session set nls_date_format=''' || l_date_fmt || '''';
32 end if;
33 dbms_sql.close_cursor(l_theCursor);
34 end restore;
35 begin
36 -- I like to see the dates print out with times, by default, the
37 -- format mask I use includes that. In order to be "friendly"
38 -- we save the date current sessions date format and then use
39 -- the one with the date and time. Passing in NULL will cause
40 -- this routine just to use the current date format
41 if ( p_date_fmt is not null )
42 then
43 select sys_context( 'userenv', 'nls_date_format' )
44 into l_date_fmt
45 from dual;
46
47 execute immediate
48 'alter session set nls_date_format=''' || p_date_fmt || '''';
49 end if;
50
51 -- to be bind variable friendly on this ad-hoc queries, we
52 -- look to see if cursor sharing is already set to FORCE or
53 -- similar, if not, set it so when we parse -- literals
54 -- are replaced with binds
55 if ( dbms_utility.get_parameter_value
56 ( 'cursor_sharing', l_status, l_cs ) = 1 )
57 then
58 if ( upper(l_cs) not in ('FORCE','SIMILAR'))
59 then
60 execute immediate
61 'alter session set cursor_sharing=force';
62 end if;
63 end if;
64
65 -- parse and describe the query sent to us. we need
66 -- to know the number of columns and their names.
67 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
68 dbms_sql.describe_columns2
69 ( l_theCursor, l_colCnt, l_descTbl );
70
71 -- define all columns to be cast to varchar2's, we
72 -- are just printing them out
73 for i in 1 .. l_colCnt loop
74 if ( l_descTbl(i).col_type not in ( 113 ) )
75 then
76 dbms_sql.define_column
77 (l_theCursor, i, l_columnValue, 4000);
78 end if;
79 end loop;
80
81 -- execute the query, so we can fetch
82 l_status := dbms_sql.execute(l_theCursor);
83
84 -- loop and print out each column on a separate line
85 -- bear in mind that dbms_output only prints 255 characters/line
86 -- so we'll only see the first 200 characters by my design...
87 while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
88 loop
89 for i in 1 .. l_colCnt loop
90 if ( l_descTbl(i).col_type not in ( 113 ) )
91 then
92 dbms_sql.column_value
93 ( l_theCursor, i, l_columnValue );
94 pipe row( printTblScalar( l_descTbl(i).col_name,
95 substr(l_columnValue,1,4000) ) );
96 end if;
97 end loop;
98 pipe row( printTblScalar( rpad('-',10,'-'), null ) );
99 end loop;
100
101 -- now, restore the session state, no matter what
102 restore;
103 return;
104 exception
105 when others then
106 restore;
107 raise;
108 end;
109 /
Function created.
ops$tkyte@ORA9IUTF> column cname format a10
ops$tkyte@ORA9IUTF> column cvalue format a40
ops$tkyte@ORA9IUTF> select *
2 from table( print_table_pipelined('select * from scott.emp where rownum = 1' ));
CNAME CVALUE
---------- ----------------------------------------
EMPNO 7369
ENAME SMITH
JOB CLERK
MGR 7902
HIREDATE 17-dec-1980 00:00:00
SAL 800
COMM
DEPTNO 20
----------
9 rows selected.
ops$tkyte@ORA9IUTF> select *
2 from table( print_table_pipelined('select * from scott.dept' ));
CNAME CVALUE
---------- ----------------------------------------
DEPTNO 10
DNAME ACCOUNTING
LOC NEW YORK
----------
DEPTNO 20
DNAME RESEARCH
LOC DALLAS
----------
DEPTNO 30
DNAME SALES
LOC CHICAGO
----------
DEPTNO 40
DNAME OPERATIONS
LOC BOSTON
----------
16 rows selected.
it does remove the 1,000,000 buffer limit of dbms_output and increased the column value being
displayed from a max of 200 characters to 4000
Cursor?
December 26, 2004
Reviewer: A reader
Oops, yes, I missed the fact that print_table returns only 2 columns, thats the whole point! Thanks
for the sample code
Another possible enhancement is to change it to accept a cursor instead of a varchar2
p_query in sys_refcursor,
This would make it easier to pass in any arbitrary SQL without worrying about doubling up the
quotes and stuff?
select * from table(print_table_pipelined(cursor(select * from emp where name='SMITH')))
Thanks
Followup:
and I'll let that (impossible) task up to you to complete :)
think about why dbms_sql is needed, mandatory, necessary here.....
print_table in Release 9.2.0.5.0
July 18, 2005
Reviewer: Warren Tolentino from Albany, NY USA
I tried the print_table procedure but did not get the result when executing the procedure at the
SQL*Plus command line. There was no error when the procedure was created.
Followup:
SQL> set serveroutput on size 1000000
did you have serveroutput enabled?
July 18, 2005
Reviewer: Warren Tolentino
Now it works I just had to issue the command Set ServerOutput On.
print_table_pipelined bug(?)
September 26, 2005
Reviewer: Paul from Clearwater, Fl
Sorry if this is too off-topic. I wonder what I'm missing here. print_table_pipelined seems to
be sentimental - it can't forget about job 844 (there's the off topic part) even though it was
removed ( dbms_job.remove) some time ago and no longer appears in dba_jobs.
Sequence of events: submit job 844, run the print_table_pipelined query seen below, remove job
844, run the the two queries shown below repeatedly in a script.
SQL> select * from table ( print_table_pipelined ( 'select * from dba_jobs d where what like
'||''''||'bcf_stats_util.gather%'||''''))
2 /
CNAME CVALUE
-------------------------------- ----------------------------------------------
JOB 844
LOG_USER CBOADMIN
PRIV_USER CBOADMIN
SCHEMA_USER CBOADMIN
LAST_DATE
LAST_SEC
THIS_DATE
THIS_SEC
NEXT_DATE 26-sep-2005 17:00:00
NEXT_SEC 17:00:00
TOTAL_TIME 0
BROKEN N
INTERVAL null
FAILURES
WHAT bcf_stats_util.gather_bcf_stats_job;
NLS_ENV NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA
' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA'
NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='d
d/mon/yy hh24:mi' NLS_DATE_LANGUAGE='AMERICAN'
NLS_SORT='BINARY'
MISC_ENV 0102000000000000
INSTANCE 0
----------
19 rows selected.
SQL>
SQL> select job, what, broken, failures, log_user, next_date
2 from dba_jobs where what like 'bcf_stats_util.gather%'
3 /
JOB WHAT B FAILURES LOG_USER
NEXT_DATE
---------- ------------------------------------- - ---------- ------------------------------
--------------------
846 bcf_stats_util.gather_bcf_stats_job; N CBOADMIN
26/sep/05 17:00
Interestingly if I reconnect ( connect x/y@z ) print_table_pipelined is no longer sentimental - it
has forgotten completely about job 844 and it shows me job 846.
Db versions are 9.2.0.4 and 10gr2. In 10gr2 we're plan on using the automatic statistics gathering
approach but we're working on our own version as a fallback/adjunct/handler-of-exceptional cases.
I suppose it's possible that I'm using print_table_pipelined outside it's intended context, but
since this is interesting behavior and it might be a bug, I figure I should mention it.
Thanks for all the help and advice over the years, and, looking at some of my old posts, thanks for
being patient.
Followup:
are you using authid current_user?
can you help reproduce - all of the steps necessary?
authid current user
September 27, 2005
Reviewer: Paul from Fla
I am using authid current user. It's true that print_table_pipelined doesn't show me rows from
some tables that I can see from sqlplus, I suppose because the only way I can see them is via
role-based privileges which are disabled in print_table_pipelined. That's not the behavior that
caused me to post earlier.
However print_table_pipelined is not cooperating with me in that it won't reproduce the
sentimentality I described earlier. That's actually a good thing because I want to use it in one
of my applications if that's legal. If I ever see that strange behavior again I will try to set up
a test case and post it here.
Thanks.
Followup:
roles are not disabled with authid current_user.
test case
November 07, 2005
Reviewer: Paul from fla
Here's a test case where print_table_pipelined doesn't show committed changes.
SQL> create or replace type cboadmin.printTblScalar as object
2 ( cname varchar2(30),
3 cvalue varchar2(4000)
4 )
5 /
Type created.
SQL> create or replace type cboadmin.printTblTable as table of printTblScalar
2 /
Type created.
SQL> create or replace function cboadmin.print_table_pipelined
2 ( p_query in varchar2,
3 p_date_fmt in varchar2 default 'dd-mon-yyyy hh24:mi:ss' )
4 return printTblTable
5 authid current_user
6 PIPELINED
7 is
8 pragma autonomous_transaction;
9 l_theCursor integer default dbms_sql.open_cursor;
10 l_columnValue varchar2(4000);
11 l_status integer;
12 l_descTbl dbms_sql.desc_tab2;
13 l_colCnt number;
14 l_cs varchar2(255);
15 l_date_fmt varchar2(255);
16
17 -- small inline procedure to restore the sessions state
18 -- we may have modified the cursor sharing and nls date format
19 -- session variables, this just restores them
20 procedure restore
21 is
22 begin
23 if ( upper(l_cs) not in ( 'FORCE','SIMILAR' ))
24 then
25 execute immediate
26 'alter session set cursor_sharing=exact';
27 end if;
28 if ( p_date_fmt is not null )
29 then
30 execute immediate
31 'alter session set nls_date_format=''' || l_date_fmt || '''';
32 end if;
33 dbms_sql.close_cursor(l_theCursor);
34 end restore;
35 begin
36 -- I like to see the dates print out with times, by default, the
37 -- format mask I use includes that. In order to be "friendly"
38 -- we save the date current sessions date format and then use
39 -- the one with the date and time. Passing in NULL will cause
40 -- this routine just to use the current date format
41 if ( p_date_fmt is not null )
42 then
43 select sys_context( 'userenv', 'nls_date_format' )
44 into l_date_fmt
45 from dual;
46
47 execute immediate
48 'alter session set nls_date_format=''' || p_date_fmt || '''';
49 end if;
50
51 -- to be bind variable friendly on this ad-hoc queries, we
52 -- look to see if cursor sharing is already set to FORCE or
53 -- similar, if not, set it so when we parse -- literals
54 -- are replaced with binds
55 if ( dbms_utility.get_parameter_value
56 ( 'cursor_sharing', l_status, l_cs ) = 1 )
57 then
58 if ( upper(l_cs) not in ('FORCE','SIMILAR'))
59 then
60 execute immediate
61 'alter session set cursor_sharing=force';
62 end if;
63 end if;
64
65 -- parse and describe the query sent to us. we need
66 -- to know the number of columns and their names.
67 dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
68 dbms_sql.describe_columns2
69 ( l_theCursor, l_colCnt, l_descTbl );
70
71 -- define all columns to be cast to varchar2's, we
72 -- are just printing them out
73 for i in 1 .. l_colCnt loop
74 if ( l_descTbl(i).col_type not in ( 113 ) )
75 then
76 dbms_sql.define_column
77 (l_theCursor, i, l_columnValue, 4000);
78 end if;
79 end loop;
80
81 -- execute the query, so we can fetch
82 l_status := dbms_sql.execute(l_theCursor);
83
84 -- loop and print out each column on a separate line
85 -- bear in mind that dbms_output only prints 255 characters/line
86 -- so we'll only see the first 200 characters by my design...
87 while ( dbms_sql.fetch_rows(l_theCursor) > 0 )
88 loop
89 for i in 1 .. l_colCnt loop
90 if ( l_descTbl(i).col_type not in ( 113 ) )
91 then
92 dbms_sql.column_value
93 ( l_theCursor, i, l_columnValue );
94 pipe row( printTblScalar( l_descTbl(i).col_name,
95 substr(l_columnValue,1,4000) ) );
96 end if;
97 end loop;
98 pipe row( printTblScalar( rpad('-',10,'-'), null ) );
99 end loop;
100
101 -- now, restore the session state, no matter what
102 restore;
103 return;
104 exception
105 when others then
106 restore;
107 raise;
108 end;
109 /
Function created.
SQL>
SQL> select application_name, monday_open from bcf_operational_window
2 /
APPLICATION_NAME MONDAY_OPEN
-------------------- ------------
gather_bcf_stats 03:00
SQL> select * from table( cboadmin.print_table_pipelined('select application_name, monday_open
from bcf_operational_window') );
CNAME CVALUE
------------------------------ ----------------------------------------
APPLICATION_NAME gather_bcf_stats
MONDAY_OPEN 03:00
----------
SQL> update bcf_operational_window set
2 monday_open = '04:00';
1 row updated.
SQL> select application_name, monday_open from bcf_operational_window;
APPLICATION_NAME MONDAY_OPEN
-------------------- ------------
gather_bcf_stats 04:00
SQL> select * from table( cboadmin.print_table_pipelined('select application_name, monday_open
from bcf_operational_window') );
CNAME CVALUE
------------------------------ ------------------------------
APPLICATION_NAME gather_bcf_stats
MONDAY_OPEN 03:00
----------
SQL> commit;
Commit complete.
SQL>
SQL> select * from table( cboadmin.print_table_pipelined('select application_name, monday_open
from bcf_operational_window') );
CNAME CVALUE
------------------------------ ----------------------------------------
APPLICATION_NAME gather_bcf_stats
MONDAY_OPEN 04:00
----------
It's still a useful function that I will use in the proper context.
Followup:
ahh - i see it now.
pragma autonomous transaction;
that'll do it. needed for the "ddl" (setting the nls_date_format is considered such...)
we could "fix" that by isolating that code into some other routine that is an a-trans and removing
the pragma from this one.
Now I see, too...
November 07, 2005
Reviewer: Paul from fla
Thank you for clarifying that. As time permits I will do as you suggest, put the autonomous
transaction in another function, and it will be even more useful.
fixed
November 10, 2005
Reviewer: Paul from Fla
I moved the autonomous transaction and the various alter session commands needed to set and restore
nls_date_format and cursor_sharing into a separate session_state function as you suggested. The
results were as you predicted, print_table_pipelined is able to see the uncommitted changes. It is
now even more useful than it was before.
It's interesting that autonomous transaction works that way - the function containing the
autonomous transaction seems isolated from any uncommitted changes in any other transactions, which
I guess is what ATs are all about.
It's an interesting wrinkle. I suppose it follows that an autonomous transaction that does "insert
into .. select ... from ..." would not be able to retrieve the changes from an uncommitted
transaction.
I found another strange behavior wrinkle in print_table_pipelined. I hesitate to post it since it
is so far off topic from the original post. On the plus side if it's ok to pursue it, then
print_table_pipelined will be improved a bit more. A brief description -
Unless I insert this command (which I can't remember all of at the moment)
pipe row ( ... sys_context ..nls_date_format);
at the start of print_table_pipelined, nls_date_format doesn't get restored correctly upon exit. I
suspect but haven't determined yet that just getting nls_date_format from sys_context would
suffice. If further testing doesn't indicate it's a bug of my own I will post a simple test case
here - unless you object to hijacking of the thread.
Followup:
i'd need to see an example
Using describe_columns2
October 12, 2006
Reviewer: Venkataramesh K from Hyderabad, AP, India
hi Thomas,
I am using the describe_columns procedure for query processing. [ Which is working fine ]
Now the problem is the column is execeeding 30 chars. So i got a message to use describe_columns2
when i use describe_columns2 i am getting the following message.
SQL> @ESS_PACKAGE.sql
Package created.
No errors.
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY OWA_SYLK:
LINE/COL ERROR
-------- -----------------------------------------------------------------
175/5 PL/SQL: Statement ignored
175/5 PLS-00306: wrong number or types of arguments in call to
'DESCRIBE_COLUMNS2'
i am using the same variable and using the same syntax as i used for describe_columns.
waiting for your reply
Followup:
i am waiting for YOUR EXAMPLE
Examples
October 12, 2006
Reviewer: Greg from Reston, VA
Tom,
what is up with you? You continue to demand examples! Does this mean that your crystal ball is
STILL in the shop? :-) I'm amazed at your patience with those who don't provide details ...
Very Handy
December 11, 2006
Reviewer: Laks from INDIA
Tom,
This is one of my favourite and most handly one for seeing data in a nice and easy way.
Asktom rocks.!!
|
Deep link provided by Citebite