使用:
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;
/