SQL script to print record -wise
data in a SQL *PLUS enviroment from a table
In development environments you have a requirement of
seeing information record by record when you query from a table in a
SQL enviroment. To achieve that do the following
1. Run the format_query.sql script
2. Put the table_name in CAPS -e.g. EMP
3. you must use two single quotes instead of one in where
clause,e.g. where emp_name = ''KING''
Limitation --- Can query only a single table for record
output.
set echo off
set heading off
set feedback off
set echo off
set verify off
set linesize 131
set pagesize 10000
column junk noprint
column selectwhat format a131
prompt Enter table name :
set termout off
select '&&table_name' from dual;
set termout on
prompt
prompt Enter where/order by clause (include keywords,
use ''
instead of '):
set termout off
select '&&where_clause' from dual;
spool interim.sql
prompt set linesize 79
select 'column '||column_name||' format a79 trunc' selectwhat
from all_tab_columns
where table_name = upper('&table_name')
and data_type != 'LONG'
/
select -1 junk,'SELECT'
from dual
union
select 100 ,
''''||rpad(column_name,30,' ')
||':'||''''||'||'||''' '''||'||'||
column_name||' '||column_name||','
from all_tab_columns
where table_name = upper('&table_name')
and data_type != 'LONG'
union
select 200, 'rpad(''='',79,''='')' from dual
union
select 201, 'FROM '||'&table_name' from dual
union
select 202,'&&where_clause'||';' from dual
order by 1,2
/
spool off
undefine table_name
undefine where_clause
set termout on
set array 1
set feedback on
spool easy.rpt
start interim
spool off
set heading on
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.
|