REM
/*
 * 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