[20150107]关于print_table.txt

-- 有时候显示的列太多，使用sqlplus显示不是很方便，一般会把它竖着输出。
-- 比较常用的方法就是使用tom的脚本：

1.方法1：
http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1035431863958
CREATE OR REPLACE procedure SYSTEM.print_table( p_query in varchar2 )
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;
begin
    execute immediate
    'alter session set
        nls_date_format=''yyyy-mm-dd hh24:mi:ss'' ';

    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        dbms_sql.define_column
        (l_theCursor, i, l_columnValue, 4000);
    end loop;

l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        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 )
              || ': ' ||
              l_columnValue );
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
-- execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
exception
    when others then
      NULL;
-- execute immediate 'alter session set nls_date_format=''dd-MON-rr'' ';
      raise;
end;
/

--我仅仅改动日期显示格式，这样比较符合国人的习惯。

--这个方法的缺点是要在数据库中建立存储过程。而是仅仅在数据库open状态才能使用。

2.方法2：
$ cat print_table.sql
--http://orasql.org/2013/04/02/sqlplus-tips-2/
-- show output
set termout on
-- but without echo
set echo off
-- without newpage on start:
set embedded on
-- scrolling control
set pause on
-- two lines between rows:
set newpage 2
-- text for prompt after each page:
set pause "Press Enter to view next row..."
-- new page on new "row_num"
break on row_num skip page

-- main query:
select *
from
   xmltable( 'for $a at $i in /ROWSET/ROW
                 ,$r in $a/*
                   return element ROW{
                                     element ROW_NUM{$i}
                                    ,element COL_NAME{$r/name()}
                                    ,element COL_VALUE{$r/text()}
                                    }'
             passing xmltype(cursor( &1 ))
             columns
                row_num   int
--               ,col_name varchar2(30)
               ,col_name varchar2(16)
               ,col_value varchar2(100)
      );
-- disabling pause and breaks:
set pause off
clear breaks

--或者
cat print_table2.sql
-- http://orasql.org/scripts/print_table2.sql

set termout off
-- set arraysize for minimal value, so sqlplus did not fetch extra rows:
set arraysize 2
-- but without echo
set echo off
-- without newpage on start:
set embedded on
-- scrolling control
set pause on
-- two lines between rows:
set newpage 2
-- disabling xml rewriting:
alter session set events '19027 trace name context forever, level 0x1';
-- text for prompt after each page:
set pause "Press Enter to view next row..."
-- new page on new "row_num"
break on row_num skip page
-- show output
set termout on

-- main query:
select row_num
      ,t2.*
from
   (select rownum row_num
         , column_value x
    from table(xmlsequence(cursor( &1 )))
   ) t1
,xmltable( 'for $r in /ROW/*
              return element ROW{
                                  element COL_NAME{$r/name()}
                                 ,element COL_VALUE{$r/text()}
                                 }'
             passing t1.x
             columns
                col_num for ordinality
               ,col_name varchar2(30)
               ,col_value varchar2(100)
      )(+) t2;
-- disabling pause and breaks:
set pause off
clear breaks

--这种方法优点是不需要建立存储过程，但是缺点一样仅仅在数据库open状态才能使用。

3.如果有一种能在mount状态就能使用就ok了。

--正好前一阵子要解决字符集转换导入与导出的问题，看了http://www.anysql.net/相关内容：
--可以从他的站点下载http://www.anysql.net/download，Oracle文本导出工具。

$ cat ptsql
#/bin/bash -x
# echo $1
#sqluldr2 user=sys query="$1" text=form control=/dev/null NULL=NULL
sqluldr2 user=sys query="$1" text=form control=/dev/null NULL=NULL field=0x0a0x7c- record=0x0a0x0a rowpre=*-

--里面的参数自己看看文档，还是很好理解的，这个软件参数也太复杂与混乱了。$1也可以使用sql语句。

--注意 $ 符号要转义一下，不然会当作shell变量。
--结合grep可以更好的获得输出显示，甚至包括column 过滤。

-- 再做一个例子来说明：
--比如查询V$SQL_SHARED_CURSOR查看不能共享光标的原因是一个考眼力的事情，使用sqluldr2变得简单了。

select * from dept;
alter session set optimizer_index_cost_adj=200;
select * from dept;

--这样由于参数发生了变化，oracle会生成新的子光标。

SCOTT@test> @share 3154rqzb8xudy
SQL_TEXT                       = select * from dept
SQL_ID                         = 3154rqzb8xudy
ADDRESS                        = 00000000BE88BCB8
CHILD_ADDRESS                  = 00000000AF798388
CHILD_NUMBER                   = 0
--------------------------------------------------
SQL_TEXT                       = select * from dept
SQL_ID                         = 3154rqzb8xudy
ADDRESS                        = 00000000BE88BCB8
CHILD_ADDRESS                  = 00000000BE5A9390
CHILD_NUMBER                   = 1
OPTIMIZER_MISMATCH             = Y
--------------------------------------------------

PL/SQL procedure successfully completed.

--使用sqluldr2也可以实现。

$ ptsql "select * from v\$sql_shared_cursor where sql_id='3154rqzb8xudy'" | grep -v ": N$" -a
*-SQL_ID                        : 3154rqzb8xudy
|-ADDRESS                       : 緢几
|-CHILD_ADDRESS                 : 痽儓
|-CHILD_NUMBER                  : 0
|-REASON                        : 03Optimizer mismatch(12)2x216 100 200

--一样可以实现。
--说明：$前要转义\. grep 的过滤要加入参数-a ,等于--binary-files=text。可以发现ADDRESS，CHILD_ADDRESS 出现乱码，这个估计
--是程序的bug，把它当作字符类型来转换了。我前面的脚本没有reason，看来要补充上去。

--总结：
--具体情况具体分析，总有一个适合你。
--实际上在mount状态，你也可以使用图形工具(像toad之类)，这样就方便许多。

ITPub博客

[20150107]关于print_table.txt

最新文章