Thanks for the question, José Laurindo.
Asked: May 07, 2012 - 3:23 pm UTC
Last updated: May 10, 2012 - 3:35 am UTC
Version: 10.2.0 / 11.2.0
Viewed 1000+ times
You Asked
In some 10gr2 and 11gr2 Customer´s databases I need, inside sqlplus , print some data vertically, much like your PRINT_TABLE function, but my user is "read-only" , I´m not allowed to create nothing (ie, no stored PL/SQLs, no tables, nothing permanent) inside this databases, what invalidate the option for your PRINT_TABLE function ...
Would you have some suggestion to mimic PRINT_TABLE functionality but without using any permanent db object, maybe inside a sqlplus script, or something like that ?
Best regards,
J. Laurindo Chiappa
Oracle DBA
and Tom said...
I use
set verify off
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;
procedure p ( p_str in varchar2 )
is
l_str long := p_str;
begin
loop
exit when l_str is null;
dbms_output.put_line( substr( l_str, 1, 250 ) );
l_str := substr( l_str, 251 );
end loop;
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);
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 );
p( 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;
/
set verify on
that script in a pinch. Use double quotes to quote literals:
ops$tkyte%ORA11GR2> @printtbl 'select * from scott.emp where ename = "KING"'
EMPNO : "7839"
ENAME : "KING"
JOB : "PRESIDENT"
MGR : ""
HIREDATE : "17-nov-1981 00:00:00"
SAL : "5000"
COMM : ""
DEPTNO : "10"
-----------------
PL/SQL procedure successfully completed.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment