PRINT_TABLE

使用：

SQL> exec print_table('select * from t1 where rownum <= 3');
OWNER                         : SYS
OBJECT_NAME                   : ICOL$
OBJECT_ID                     : 20
STATUS                        : VALID
CREATED                       : 13-8月 -2009 23:00:54
-----------------
OWNER                         : SYS
OBJECT_NAME                   : I_USER1
OBJECT_ID                     : 46
STATUS                        : VALID
CREATED                       : 13-8月 -2009 23:00:54
-----------------
OWNER                         : SYS
OBJECT_NAME                   : CON$
OBJECT_ID                     : 28
STATUS                        : VALID
CREATED                       : 13-8月 -2009 23:00:54
-----------------

PL/SQL 过程已成功完成。

脚本：

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