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

Return to : Oracle Database, SQL, Application, Programming Tips