Thanks for the question, ted.

Asked: November 13, 2000 - 5:45 pm UTC

Last updated: June 21, 2012 - 7:55 am UTC

Version: 8.1.6

Viewed 10K+ times! This question is

You Asked

Thomas, clarification related to null in indexed fields:

1. I read from several book and got the impression if higher order (leftest field) is null an index entry will not be generated. Because null value is not equal to any thing. Is this a misconception ?
2. If null is the leftest of a compound index, where would it be located in a binary tree ?

3. can you send me your print_table procedure ?
thnx in advance as always
Ted Chyn
===========================================

Only totally NULL records are not indexed.. If any component of
the index is not null for a given row -- there is an index entry
made.

This can be seen this way:

ops$tkyte@ORA8I.WORLD> create table test ( a int,b int);

Table created.

ops$tkyte@ORA8I.WORLD> create index test_a_b on test(a,b);

Index created.

ops$tkyte@ORA8I.WORLD> insert into test values(1,2);

1 row created.

ops$tkyte@ORA8I.WORLD> insert into test values(1,null);

1 row created.

ops$tkyte@ORA8I.WORLD> insert into test values(null,2);

1 row created.

ops$tkyte@ORA8I.WORLD> insert into test values(null,null);

1 row created.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> analyze index test_a_b validate
structure;

Index analyzed.

ops$tkyte@ORA8I.WORLD>
ops$tkyte@ORA8I.WORLD> exec print_table( 'select * from
index_stats' );
HEIGHT : 1
BLOCKS : 64
NAME : TEST_A_B
PARTITION_NAME :
LF_ROWS : 3 <<<======
LF_BLKS : 1
LF_ROWS_LEN : 47
LF_BLK_LEN : 7996
BR_ROWS : 0
BR_BLKS : 0
BR_ROWS_LEN : 0
BR_BLK_LEN : 0
DEL_LF_ROWS : 0
DEL_LF_ROWS_LEN : 0
DISTINCT_KEYS : 3
MOST_REPEATED_KEY : 1
BTREE_SPACE : 7996
USED_SPACE : 47
PCT_USED : 1
ROWS_PER_KEY : 1
BLKS_GETS_PER_ACCESS : 2
PRE_ROWS : 0
PRE_ROWS_LEN : 0

and Tom said...

Well, the example you cut and pasted in shows that index entries are made for indexes with leading nulls. One of the rows I added was (null,2). Only the totally null row (null,null) is missing in the index.

As for the sort order, nulls are considered "greater" for sorting:

ops$tkyte@ORA8I.WORLD> set null null
ops$tkyte@ORA8I.WORLD> select 1 a, 2 b from dual
2 union all
3 select to_number(null) a, 2 b from dual
4 union all
5 select 1 a, to_number(null) b from dual
6 union all
7 select to_number(null) a, to_number(null) b from dual
8 order by 1, 2
9 /

A B
---------- ----------
1 2
1 null
null 2
null null

For print_table, thats easy. Here is the Oracle8i specific one. It uses authid_current user so you can install it ONCE per database and many people can use it (with roles and all intact):

create or replace procedure print_table( p_query in varchar2 )
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;
begin
execute immediate
'alter session set
nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
dbms_sql.define_column
(l_theCursor, i, l_columnValue, 4000);
end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value
( l_theCursor, i, l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name, 30 )
|| ': ' ||
l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
exception
when others then
execute immediate
'alter session set nls_date_format=''dd-MON-rr'' ';
raise;
end;
/

The above, you would run as:

SQL> exec print_table( 'select * from T where a = ''X'' ' );

(note the doubling of the quotes for character string constants!!)

In Oracle8.0 and before, I use this sqlplus script instead:

declare
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_descTbl dbms_sql.desc_tab;
l_colCnt number;

procedure execute_immediate( p_sql in varchar2 )
is
BEGIN
dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
l_status := dbms_sql.execute(l_theCursor);
END;
begin
execute_immediate( 'alter session set nls_date_format=
''dd-mon-yyyy hh24:mi:ss'' ');
dbms_sql.parse( l_theCursor,
replace( '&1', '"', ''''),
dbms_sql.native );

dbms_sql.describe_columns( l_theCursor,
l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
dbms_sql.define_column( l_theCursor, i,
l_columnValue, 4000 );

end loop;

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
dbms_output.put_line
( rpad( l_descTbl(i).col_name,
30 ) || ': ' || l_columnValue );
end loop;
dbms_output.put_line( '-----------------' );
end loop;
execute_immediate( 'alter session set nls_date_format=
''dd-MON-yy'' ');
exception
when others then
execute_immediate( 'alter session set
nls_date_format=''dd-MON-yy'' ');
raise;
end;
/

You would run this as:

SQL> @printtbl 'select * from T where a = "X" '

Note my use of " instead of '' here. Important for the script to work as is.

Rating

(47 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Print_Table utility

Logo Palanisamy, May 09, 2001 - 8:04 pm UTC

Very very useful.

Most useful

Ashish, July 09, 2001 - 11:08 am UTC

very very useful, life made so easy

A reader, August 06, 2001 - 10:17 am UTC

Reader

Reader, January 01, 2002 - 2:41 pm UTC

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

January 01, 2002 - 8:28 pm UTC

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

Reader, January 01, 2002 - 11:10 pm UTC

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?

George, September 27, 2002 - 3:27 pm UTC

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

September 27, 2002 - 3:50 pm UTC

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

George, September 27, 2002 - 3:31 pm UTC

about my previous posting here... I didn't plan to put it here, but rather

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:352271617074,%7Bdynamic%7D%20and%20%7Boutput%7D <code>

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 :-(

September 27, 2002 - 3:54 pm UTC

Thats OK -- the print_table example is perfect to go with the question.

[OFFTOPIC] Tom, Print_table is most usefull but..

Martin, February 07, 2003 - 5:42 am UTC

Just curious,

Why did you use the 'When others' exception in your procedure whereas you kinda say this is bad practice here </code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1155066278457,%7Bwhen%7D%20and%20%7Bothers%7D <code>

Personally I never use When others, I think it stinks.
(Sorry for being offtopic)

February 07, 2003 - 8:20 am UTC

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

Andrew, February 07, 2003 - 5:09 pm UTC

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

Sikandar Hayat, August 08, 2003 - 1:38 am UTC

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?

Vinnie, December 18, 2003 - 3:06 pm UTC

Tom,

How can I modify the print_table procedure
to process or skip blob data?

December 18, 2003 - 4:07 pm UTC

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

prakash, February 13, 2004 - 1:47 am UTC

Print_table a nice feature

Shashidhar Chandrappa, April 13, 2004 - 11:23 am UTC

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

OLALEKAN AJIROBA, May 03, 2004 - 5:50 am UTC

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.

May 03, 2004 - 7:33 am UTC

please contact support, sounds like the well known "p4" issue with the java sdk on that platform.

A reader, May 03, 2004 - 10:02 am UTC

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.

May 03, 2004 - 3:27 pm UTC

if you have licensed support -- absolutely.

Warning when using print_table

Richard Manton, June 29, 2004 - 10:59 pm UTC

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.

June 29, 2004 - 11:54 pm UTC

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!

Bjorn Hedlin, June 30, 2004 - 8:22 am UTC

print_table proc

Murali, July 20, 2004 - 9:13 am UTC

Very useful utility comes handy.

Thanks
Murali

Tried to find out the error line length overflow

A reader, August 11, 2004 - 9:36 am UTC

DBA

Vinod.G, November 14, 2004 - 4:34 am UTC

This has been very useful to manupulate the staged data.

procedure print_table

Jim Cox, November 24, 2004 - 12:10 pm UTC

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.

November 24, 2004 - 12:26 pm UTC

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

Jim Cox, November 24, 2004 - 12:17 pm UTC

Sorry....
my error on last comment
forgot to set serveroutput on

Print_table Tool

Rex Bugcalao, December 16, 2004 - 5:45 am UTC

This is very helpful!!
Im new to PL/SQL.
Im very Impressed!! :)

Update for 9iR2?

A reader, December 21, 2004 - 9:17 am UTC

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

December 21, 2004 - 9:21 am UTC

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

A reader, December 24, 2004 - 10:48 am UTC

"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?

December 24, 2004 - 1:44 pm UTC

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.



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

Cursor?

A reader, December 26, 2004 - 11:06 pm UTC

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

December 27, 2004 - 9:54 am UTC

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

Warren Tolentino, July 18, 2005 - 8:46 am UTC

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.

July 18, 2005 - 8:57 am UTC

SQL> set serveroutput on size 1000000

did you have serveroutput enabled?

Warren Tolentino, July 18, 2005 - 8:51 am UTC

Now it works I just had to issue the command Set ServerOutput On.

print_table_pipelined bug(?)

Paul, September 26, 2005 - 3:21 pm UTC

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.

September 27, 2005 - 9:47 am UTC

are you using authid current_user?

can you help reproduce - all of the steps necessary?

authid current user

Paul, September 27, 2005 - 11:32 pm UTC

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.

September 28, 2005 - 9:21 am UTC

roles are not disabled with authid current_user.

test case

Paul, November 07, 2005 - 10:45 am UTC

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.

November 07, 2005 - 8:29 pm UTC

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...

Paul, November 07, 2005 - 10:42 pm UTC

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

Paul, November 10, 2005 - 12:59 am UTC

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.

November 11, 2005 - 11:35 am UTC

i'd need to see an example

Using describe_columns2

Venkataramesh K, October 12, 2006 - 8:11 am UTC

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

October 12, 2006 - 8:33 am UTC

i am waiting for YOUR EXAMPLE

Examples

Greg, October 12, 2006 - 6:17 pm UTC

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

Laks, December 11, 2006 - 1:39 am UTC

Tom,
This is one of my favourite and most handly one for seeing data in a nice and easy way.

Asktom rocks.!!

Error

Vikas, March 07, 2007 - 2:15 am UTC

BEGIN print_table('select * from v$session_longops where sid=26'); 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

March 07, 2007 - 10:22 am UTC

did you read the error message?

it is obvious isn't it?

You have exceeded the dbms_output line limit present in 9i and before of 255 characters.

either

a) rewrite print table to substr this stuff if you want
b) select substr() directly in the query you pass to print table.

Follow up example (for someone who didn't!)

Anthony Greenwood, March 22, 2007 - 1:50 pm UTC

Hi Tom,

One of the most recent posts (even though back in 2006) encountered a describe_columns issue and the "solution" was to use describe_columns2 but the person who had the problem never posted an example - I have one and wondered how to handle it? Thanks in advance for your help!

SQL> @crDOCTABAextractV2
SQL> alter session set nls_date_format = 'YYYY-MM-DD';

Session altered.

SQL>
SQL> SET LINESIZE 150;
SQL> SET SERVEROUTPUT on SIZE 1000000 FORMAT WRAPPED;
SQL>
SQL> declare
  2     l_rows    number;
  3     begin
  4         l_rows := unloader.run
  5                ( p_query      =>
  6  'select
  7        F_DOCNUMBER,
  8        F_DOCCLASSNUMBER,
  9        TO_CHAR(TO_DATE(''19700101'',''YYYY-MM-DD'')+F_ENTRYDATE),
 10        F_PAGES,
 11        A31,
 12        A32,
 13        A33,
 14        A36,
 15        A37,
 16        A50
 17  from
 18        F_SW.DOCTABA
 19  where F_DOCCLASSNUMBER in
 20        (3,7,9,11,12,13,14,17,18,19,20,21,22,23,24,25,26,27)',
 21                        p_town       => 'F_SW',
 22                        p_tname      => 'DOCTABA',
 23                        p_mode       => 'replace',
 24                        p_dbdir      => 'UNLOADER',
 25                        p_filename   => 'DOCTABAUNLOAD',
 26                        p_separator  => '|',
 27                        p_enclosure  => '',
 28                        p_terminator => '\n',
 29                        p_ctl        => 'NO',
 30                        p_header     => 'NO' );
 31          dbms_output.put_line( to_char(l_rows) || ' rows extracted to ascii file' );
 32     end;
 33
 34  /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: dbms_sql.describe_columns overflow, col_name_len=53. Use describe_columns2
ORA-06512: at "SYS.UNLOADER", line 596
ORA-06512: at line 4


SQL>

When I tried replacing describe_columns with describe_columns2 in your unloader procedure I got the following:

Warning: Package Body created with compilation errors.

SQL> --
SQL> SET ARRAYSIZE 1;
SQL> SHOW ERRORS;
Errors for PACKAGE BODY UNLOADER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
307/7 PLS-00306: wrong number or types of arguments in call to
'DESCRIBE_COLUMNS2'

307/7 PL/SQL: Statement ignored
483/7 PLS-00306: wrong number or types of arguments in call to
'DESCRIBE_COLUMNS2'

483/7 PL/SQL: Statement ignored

March 22, 2007 - 2:57 pm UTC

it needs a wider type - that is the purpose of the describe columns 2 routine, to let you have a wider column name.......

procedure describe_columns2(c in integer, col_cnt out integer,
desc_t out desc_tab2);

use the desc_tab2 type.

Follow up example - describe_columns2

Anthony Greenwood, March 22, 2007 - 4:01 pm UTC

Thank you so much Tom for your swift and perfect response - I had been puzzling over this for quite some time and you have saved me a lot of time and effort. I'm in awe of your vast array of knowledge and really appreciate being able to tap into you as a resource!
Best regards,
Anthony

Use refcursor in 100g?

Richard Smith, April 15, 2010 - 4:04 pm UTC

>>Followup   December 27, 2004 - 9am Central time zone:
>>and I'll let that (impossible) task up to you to complete :)
>>think about why dbms_sql is needed, mandatory, necessary here.....

Now in 11g there's dbms_sql.to_cursor_number so that should be what was missing when you commented back in '04 that it was imposible... but now in 11g passing a refcursor should work, correct? (I'd test it but have not had a chance to set up an 11g virtualbox yet.)

April 16, 2010 - 9:15 am UTC

yes, in 11g there is a dbms_sql api to turn a ref cursor into a dbms_sql cursor and vice versa...

Tejas Patel, June 19, 2011 - 1:54 am UTC

simply excellent script, by geniunely genious Tom.

Tejas Patel
Reliance DBA

a variant on the print_table idea

gpoz, January 25, 2012 - 4:50 pm UTC

Wonderful material, I grabbed it and we use it (with approp. credit to asktom as source) all the time. but I've looked at this code, and I think it could be improved by offering the column names in alpha order ... I looked into the dbms_sql package, but didn't see any capability like this. I also won't have a problem being given a link to any Oracle doc I might have overlooked, always glad to learn.

thank you

January 26, 2012 - 10:30 am UTC

you'd have to do it procedurally, we don't have any 'magic' to sort column names in any manner, we only return them in the order you ask for them.

and select * returns them in the order they were created.

print_table follow-up

gpoz, January 27, 2012 - 2:13 pm UTC

thank you, I can even appreciate when I don't get the answer I hoped, because if this site doesn't have a ready answer, I can stop spinning my wheels and look for another approach.

As it happens, the Oracle forums had a similar question, which had its basis in sorting an array, and I was able to tweak a code sample from there to produce a clean (and fairly short) solution within the print_table routine.

The solution was a simple copying of the final data to an array indexed on varchar2 (whose mechanism for doing the sorting seems to be like an IOT), then outputting the content of the 2nd array instead of the original unsorted substance. I would be happy to provide my updates to the original code if desired.

January 31, 2012 - 5:03 pm UTC

you may do that if you wish, however, I've never once had any need to see things in "alphabetical order" myself :)

print_table another question

chrisd, June 15, 2012 - 8:42 am UTC

...we have column name and value, but what about data type? Is there any way to get it?

June 15, 2012 - 1:11 pm UTC

sure there is

we got it right here:

    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );

It is in l_descTbl

column types oracle 10g

krzyslol, June 20, 2012 - 9:32 am UTC

...we have column name and value, but what about data type? Is there any way to get it (before 11g?)

June 21, 2012 - 7:55 am UTC

dbms_sql.describe_columns
( l_theCursor, l_colCnt, l_descTbl );

you have it - datatype - in all releases, you've always had it.

http://docs.oracle.com/cd/E11882_01/appdev.112/e23448/d_sql.htm#CHDCIIBF

col_type is in there. easiest way to decode it is to use the text of all_tab_cols:

       decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
                       2, decode(c.scale, null,
                                 decode(c.precision#, null, 'NUMBER', 'FLOAT'),
                                 'NUMBER'),
                       8, 'LONG',
                       9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
                       12, 'DATE',
                       23, 'RAW', 24, 'LONG RAW',
                       58, nvl2(ac.synobj#, (select o.name from obj$ o
                                where o.obj#=ac.synobj#), ot.name),
                       69, 'ROWID',
                       96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
                       100, 'BINARY_FLOAT',
                       101, 'BINARY_DOUBLE',
                       105, 'MLSLABEL',
                       106, 'MLSLABEL',
                       111, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
                       113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
                       121, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       122, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       123, nvl2(ac.synobj#, (select o.name from obj$ o
                                 where o.obj#=ac.synobj#), ot.name),
                       178, 'TIME(' ||c.scale|| ')',
                       179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       180, 'TIMESTAMP(' ||c.scale|| ')',
                       181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
                       231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
                       182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
                       183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
                             c.scale || ')',
                       208, 'UROWID',
                       'UNDEFINED'),
       decode(c.type#, 111, 'REF'),

Differences between dbms_sql and all_tab_cols

Oliver, June 18, 2013 - 6:44 pm UTC

I stumbled upon a small problem using dbms_sql.describe_columns3 with some types where desc_rec.col_type would not match your excerpt from all_tab_columns. Seeing some constants defined in dbms_sql I wrote a function that works for me.

...
FUNCTION get_type(
    p_rec sys.dbms_sql.desc_rec3)
RETURN VARCHAR2
IS
    l_type VARCHAR2(255);
BEGIN
    -- siehe all_tab_cols und dbms_sql.open_cursor
    SELECT  decode(p_rec.col_type, 
                1, decode(p_rec.col_charsetform, 2, 'NVARCHAR2', 'VARCHAR2')||'('||p_rec.col_max_len||')',
                2, decode(p_rec.col_scale, null, decode(p_rec.col_precision, null, 'NUMBER', 'FLOAT'), 'NUMBER'),
                8, 'LONG',
                11, 'ROWID',
                12, 'DATE',
                23, 'RAW', 
                24, 'LONG RAW',
                96, decode(p_rec.col_charsetform, 2, 'NCHAR', 'CHAR')||'('||p_rec.col_max_len||')',
                100, 'BINARY_FLOAT',
                101, 'BINARY_DOUBLE',
                106, 'MLSLABEL',
                109, p_rec.col_schema_name||'.'||p_rec.col_type_name, --User_Defined_Type
                111, p_rec.col_schema_name||'.'||p_rec.col_type_name, --Ref_Type
                112, decode(p_rec.col_charsetform, 2, 'NCLOB', 'CLOB'),
                113, 'BLOB', 
                114, 'BFILE', 
                180, 'TIMESTAMP('||p_rec.col_scale||')',
                181, 'TIMESTAMP('||p_rec.col_scale||')'||' WITH TIME ZONE',
                182, 'INTERVAL YEAR('||p_rec.col_precision||') TO MONTH',
                183, 'INTERVAL DAY('||p_rec.col_precision||') TO SECOND('||p_rec.col_scale||')',
                208, 'UROWID',
                231, 'TIMESTAMP('||p_rec.col_scale||')'||' WITH LOCAL TIME ZONE',
                'UNDEFINED('||p_rec.col_type||')')
        INTO l_type
        FROM dual;
    RETURN l_type;
END get_type;
...

MOTO-1

kailde, August 22, 2015 - 9:06 pm UTC

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

re: prior posting on index

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

Print_Table utility

Most useful

very very useful, life made so easy

Reader

Extemely Useful

best way in 9.2?

sorry

[OFFTOPIC] Tom, Print_table is most usefull but..

print_table wrap routine

Very Nice

BLOBS?

print table

Print_table a nice feature

8.1.7 installation non-initialization

Warning when using print_table

Very Nice!

print_table proc

Tried to find out the error line length overflow

DBA

procedure print_table

procedure print_table

Print_table Tool

Update for 9iR2?

Pipelined

Cursor?

print_table in Release 9.2.0.5.0

print_table_pipelined bug(?)

authid current user

test case

Now I see, too...

fixed

Using describe_columns2

Examples

Very Handy

Error

Follow up example (for someone who didn't!)

Follow up example - describe_columns2

Use refcursor in 100g?

a variant on the print_table idea

print_table follow-up

print_table another question

column types oracle 10g

Differences between dbms_sql and all_tab_cols

MOTO-1

More to Explore

PL/SQL demos

PL/SQL docs