/* * Print_Table.Sql (c) Tim Onions 2007 * Please check this script works as expected on a test system before using on a production database. * The author can accept no liability or responsibility for any ill-effect it may have. * You are free to use this script on one condition - use it to improve the performance of your database! */ SET ECHO off FEEDBACK off VER off PROMPT Usage example: START PRINT_TABLE "SELECT * FROM NO_READ_CODE_SHIPPER WHERE nrs_shipper=''$D'' ORDER BY nrs_code" DECLARE p_query varchar2(4000):='&1'; p_date_fmt varchar2(128) default 'dd-fmMonth-yyyy hh24:mi:ss'; P_NULLAs VARCHAR2(32):='
'; 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); L_RowCount PLS_INTEGER:=0; PROCEDURE PR_restoreNLS (P_date_fmt VARCHAR2 ,L_date_fmt VARCHAR2 ) IS BEGIN /* * If a specific date/time format was used put the session back to the date format it had before we started. */ IF P_date_fmt IS NOT NULL AND P_date_fmt!=L_date_fmt THEN EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || L_date_fmt || ''''; END IF;--P_date_fmt IS NOT NULL AND P_date_fmt!=L_date_fmt END PR_restoreNLS; BEGIN /* * If a specific date/time format is requested then set the session to use it provided it is not already using the same mask. */ IF p_date_fmt IS NOT NULL then L_date_fmt:=sys_context( 'userenv', 'nls_date_format' ); IF P_date_fmt!=l_date_fmt THEN EXECUTE IMMEDIATE 'alter session set nls_date_format=''' || p_date_fmt || ''''; END IF; --P_date_fmt!=l_date_fmt THEN END IF;--P_date_fmt IS NOT NULL -- 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 varchar2s. We are just printing them out. for i in 1 .. l_colCnt loop dbms_sql.define_column(l_theCursor, i, l_columnValue, 4000); 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. -- dbms_output prints only 255 characters/line so only show the first 200 characters. DBMS_OUTPUT.PUT_LINE(SUBSTR('Results for query "'||P_Query||';" are...',1,255)); WHILE dbms_sql.fetch_rows(l_theCursor)>0 LOOP L_RowCount:=L_RowCount+1; DBMS_OUTPUT.PUT_LINE('Row #'||L_RowCount||':'); 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 )|| ': ' ||substr(NVL(l_columnValue,P_NULLAs), 1, 200 ) ); end loop; dbms_output.put_line( '-----------------' ); end loop; DBMS_OUTPUT.PUT_LINE(L_RowCount||' rows selected.'); DBMS_OUTPUT.PUT_LINE(SUBSTR('Results for query "'||P_Query||';" are...',1,255)); dbms_sql.close_cursor(l_theCursor); PR_restoreNLS(P_date_fmt,L_date_fmt); EXCEPTION WHEN OTHERS THEN dbms_sql.close_cursor(l_theCursor); PR_restoreNLS(P_date_fmt,L_date_fmt); RAISE; END; / SET DOC off /* SUMMARY - Display results of a query in one column (aka Tom Kyte but without creating a procedure) * * DETAIL - askTom has an excellent utility tool that allows the results of a query to be displayed in "table format" * as opposed to the standard rows of columns across one line format that SQL*Plus offers. The results from this can * be much easier to read, especially for tables/queries with lots of columns to be displayed and also ensures that the * column names (field names) can be fuly read and not truncated to the SQL*Plus COLUMN size. * * There is no doubt that this is yet another most useful addition to the Oracle developer/DBA's toolbox and once more we bow down to * the great stuff that askTom produces. However (there had to be a but coming), in order to use Tom's "print_table" procedure you need to * be able to create a procedure - what if you do not have priviledge to do so, or their are some role grant permissions making it tricky to * run the procedure? It does happen - particularly if you are a consultant travelling from database to database with little or no privileges. * * This script takes all the good things from Tom's print_table procedure (i.e. shamelessly copies it, but always recognising that it is their brilliant code not ours) and does just about the same thing from an anonymous PL/SQL block. In that * way nothing has to be created on the "client's" database - you just run a script. It also simplifies a few things. As it is purely an ad-hoc tool * no change is made to the cursor_sharing setting. Also, the date format is only changed if the current session setting is not the same as the requested * format (and likewise there is no need to siwtch back afterwards either). The number of rows is reported (just like a standard SQL statement in SQL*Plus when * FEEDBACK is SET to ON and the query used is echoed back (useful if it scrolled off the top of the sreen!). NULL values are reported with a text string of * . * * Because this is SQL*Plus and the SQL needs to be passed in as a command line variable it suffers from two limitations over using a procedure: * * 1-The maximum length of the SQL statement is 255 characters (if you really need more then split the SQL into 255 byte segments and hack the script * to accept multiple parameters and then concatenate them back together in the DBMS_SQL statement). * * 2-quoted literals are a pain to handle - basically the rule is to always enclose the SQL statement in double quotes and to double up all single quotes * that you need in the statement (see sample usage below). * * 3-The version given here is written for Oracle 9i where DBMS_OUTPUT is still limited to 255 characters (so some of the output can be truncated). Oracle10g * limits this restriction so if you are using this on 10g remove the SUSBTR(...,1,255) clauses wherever they occur. * * Sample code might look like: SYS@RACDB> start print_table "select * from user_objects where rownum<32 AND status=''VALID''" Results for query "select * from user_objects where rownum<32 AND status='VALID';" are... Row #1: OBJECT_NAME : /1000e8d1_LinkedHashMapValueIt SUBOBJECT_NAME : OBJECT_ID : 20518 DATA_OBJECT_ID : OBJECT_TYPE : JAVA CLASS CREATED : 24-June-2005 17:40:20 LAST_DDL_TIME : 24-June-2005 17:40:20 TIMESTAMP : 2005-06-24:17:40:20 STATUS : VALID TEMPORARY : N GENERATED : N SECONDARY : N ----------------- Row #2: OBJECT_NAME : /1005bd30_LnkdConstant SUBOBJECT_NAME : OBJECT_ID : 13674 DATA_OBJECT_ID : OBJECT_TYPE : JAVA CLASS CREATED : 24-June-2005 17:40:20 LAST_DDL_TIME : 24-June-2005 17:40:20 TIMESTAMP : 2005-06-24:17:40:20 STATUS : VALID TEMPORARY : N GENERATED : N SECONDARY : N ----------------- 2 rows selected. Results for query "select * from user_objects where rownum<32 AND status='VALID';" are... SYS@RACDB> spool off */ REM