Thomas Kyte 编写的print_table()过程的源代码

 

/* Formatted on 2007/10/29 12:40 (Formatter Plus v4.8.8) */

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;

/