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;
- /
Link URL: http://www.csjrc.com/default.asp?id=91
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/89703/viewspace-217908/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/89703/viewspace-217908/