[펌] print_table 프로시져 만들기 ^^

출처 : http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1035431863958

 

For print_table, thats easy.  Here is the Oracle8i specific one.  It uses
authid_current user so you can install it ONCE per database and many people can
use it (with roles and all intact):

SCOTT> create or replace procedure 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=''dd-mon-yyyy 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);

    dbms_output.put_line( '---------------------------------------------------' );

    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
      execute immediate
          'alter session set nls_date_format=''dd-MON-rr'' ';
      raise;
end;
/


The above, you would run as:

SCOTT> set serveroutput on
SCOTT> exec print_table( 'select * from emp where ename = ''SCOTT'' ' );

 

(note the doubling of the quotes for character string constants!!)

 

---------------------------------------------------
EMPNO                      : 7788
ENAME                      : SCOTT
JOB                           : ANALYST
MGR                          : 7566
HIREDATE                  : 19-apr-1987 00:00:00
SAL                           : 3000
COMM                       :
DEPTNO                     : 20
---------------------------------------------------

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

SCOTT> grant execute on print_table to public;

 

SCOTT> conn system/manager

SYSTEM> create public synonym print_table for scott.print_table;
SYSTEM> exec print_table('select * from v$session_wait where sid=6');

 

---------------------------------------------------
SID                           : 6
SEQ#                        : 14
EVENT                      : rdbms ipc message
P1TEXT                     : timeout
P1                            : 180000
P1RAW                      : 0002BF20
P2TEXT                     :
P2                            : 0
P2RAW                      : 00
P3TEXT                     :
P3                             : 0
P3RAW                      : 00
WAIT_TIME                 : 0
SECONDS_IN_WAIT     : 1650
STATE                       : WAITING
---------------------------------------------------

 

PL/SQL 처리가 정상적으로 완료되었습니다.

 

========================================================================================

In Oracle8.0 and before, I use this sqlplus script instead:

declare
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;

    procedure execute_immediate( p_sql in varchar2 )
    is
    BEGIN
        dbms_sql.parse(l_theCursor,p_sql,dbms_sql.native);
        l_status := dbms_sql.execute(l_theCursor);
    END;
begin
    execute_immediate( 'alter session set nls_date_format=
                        ''dd-mon-yyyy hh24:mi:ss'' ');
    dbms_sql.parse(  l_theCursor,
                     replace( '&1', '"', ''''),
                     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);
    dbms_output.put_line( '-----------------' );

    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-yy'' ');
exception
    when others then
        execute_immediate( 'alter session set
                         nls_date_format=''dd-MON-yy'' ');
        raise;
end;
/

You would run this as:

SCOTT> @printtbl 'select * from emp where ename = "SCOTT" '

 

Note my use of " instead of '' here.  Important for the script to work as is.