Visit Citebite Deep link provided by Citebite
Close this shade
Source:  http://asktom.oracle.com/pls/asktom/f?p=100:11:2264752026530068::::P11_QUESTION_ID:1035431863958



Home>Question Details



ted -- Thanks for the question regarding "re: prior posting on index", version 8.1.6

Submitted on 13-Nov-2000 17:45 Eastern US time
Last updated 12-Oct-2006 8:33

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

Reviews    
5 stars Print_Table utility   May 09, 2001
Reviewer: Logo Palanisamy from San Francisco, Bay Area
Very very useful. 


5 stars Most useful   July 09, 2001
Reviewer: Ashish from Tysons Corner, VA


5 stars very very useful, life made so easy   August 06, 2001
Reviewer: A reader 


5 stars 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)

 

5 stars 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, I’ll attempt to write word wrap routine, if I do I’ll share it
in this site.

 


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

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

5 stars [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. 

4 stars 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
 


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


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

3 stars print table   February 13, 2004
Reviewer: prakash from India


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


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

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

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

5 stars Very Nice!   June 30, 2004
Reviewer: Bjorn Hedlin from Sweden


3 stars print_table proc   July 20, 2004
Reviewer: Murali from India
Very useful utility comes handy.

Thanks
Murali 


4 stars Tried to find out the error line length overflow   August 11, 2004
Reviewer: A reader from India


4 stars DBA   November 14, 2004
Reviewer: Vinod.G from BAHRAIN
This has been very useful to manupulate the staged data. 


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

5 stars procedure print_table   November 24, 2004
Reviewer: Jim Cox from USA
Sorry....
my error on last comment 
forgot to set serveroutput on 


4 stars Print_table Tool   December 16, 2004
Reviewer: Rex Bugcalao from Philippines
This is very helpful!!
Im new to PL/SQL.
Im very Impressed!! :) 


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

5 stars 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

 

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

2 stars 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?

 

5 stars   July 18, 2005
Reviewer: Warren Tolentino 
Now it works I just had to issue the command Set ServerOutput On. 


4 stars 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? 

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

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

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

 


4 stars 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 

5 stars 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 

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


5 stars 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.!! 



Write a Review
 


About Oracle | Legal Notices and Terms of Use | Privacy Statement