How to select the one column
per row in display?
Introduction : This pl/sql block displayes in the following
fashion
column1 - value1
column2 - value2
column3 - value3
Any number of tables can be there
Usage - Save the qurey as disprec.sql
In the sqlprompr
@disprec
Enter your
statement
Limitations - Maximum output can be 1000000
Entire sql should be typed without pressing Enter
If any invalid column or table is present does not say which table or column
If more throws exception ORU-100027
declare
q_cursor
integer;
column_val varchar2(256);
tretval
integer;
col_cnt
integer;
desc_t
dbms_sql.desc_tab;
error_position integer;
fileid
utl_file.file_type;
filedir
varchar2(250);
filename
varchar2(250);
t_query
varchar2(4000);
tstr
varchar2(200);
statement_error exception;
pragma exception_init(statement_error,-921);
begin
t_query := ltrim(rtrim('&qry'));
dbms_output.enable(1000000);
if substr(t_query,length(t_query),1) in (';','/')
then
t_query := substr(t_query,1,length(t_query)-1);
end if;
q_cursor := dbms_sql.open_cursor;
dbms_sql.parse(q_cursor,t_query,dbms_sql.v7);
error_position := dbms_sql.last_error_position;
dbms_sql.describe_columns(q_cursor,col_cnt,desc_t);
for i in 1..col_cnt loop
dbms_sql.define_column(q_cursor,i,column_val,255);
end loop;
tretval := dbms_sql.execute(q_cursor);
loop
tretval := dbms_sql.fetch_rows(q_cursor);
if tretval = 0 then
exit;
end if;
for i in 1..col_cnt loop
dbms_sql.column_value(q_cursor,
i,column_val);
dbms_output.put_line(desc_t(i).col_name
|| ' - '||column_val);
end loop;
dbms_output.put_line('<-----Record
End ----------->');
end loop;
exception when others then
error_position := dbms_sql.last_error_position;
raise_application_error(-20001,sqlcode||' '||sqlerrm||'
Error at '||error_position);
end;
/
Have a Oracle Question
Do
you have an Oracle Question?
Oracle Books
Oracle Certification,
Database Administration, SQL, Application, Programming Reference Books
Oracle Application
Oracle
Application Hints and Tips
Oracle Home
Oracle
Database, SQL, Application, Programming Tips
All the site contents are Copyright © www.erpgreat.com
and the content authors. All rights reserved.
All product names are trademarks of their respective
companies.
The site www.erpgreat.com is not affiliated with or endorsed
by any company listed at this site.
Every effort is made to ensure the content integrity.
Information used on this site is at your own risk.
The content on this site may not be reproduced
or redistributed without the express written permission of
www.erpgreat.com or the content authors.
|