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.