Here is something, which I wrote to an application recently using Dynamic SQL - Check if this help you.
rajesh@ORA11G> $type delim_data.sql
declare
l_cursor int;
l_sql long := replace('&1','"','''');
l_col_cnt int;
l_desc_tab dbms_sql.desc_tab;
l_value varchar2(1000);
l_status int;
l_result varchar2(32767);
l_delimiter varchar2(1) := ',';
begin
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor,l_sql,dbms_sql.native);
dbms_sql.describe_columns(l_cursor,l_col_cnt,l_desc_tab);
for i in 1..l_col_cnt
loop
dbms_sql.define_column(l_cursor,i,l_value,1000);
end loop;
l_status := dbms_sql.execute(l_cursor);
while ( dbms_sql.fetch_rows(l_cursor)>0)
loop
l_result := null;
for i in 1..l_col_cnt
loop
dbms_sql.column_value(l_cursor,i,l_value);
l_result := l_result ||l_value ||l_delimiter;
end loop;
l_result := trim(l_delimiter from l_result);
dbms_output.put_line(l_result);
end loop;
dbms_sql.close_cursor(l_cursor);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end;
/
rajesh@ORA11G>
rajesh@ORA11G> set verify off
rajesh@ORA11G>
rajesh@ORA11G> @delim_data 'select * from dept'
10,ACCOUNTING,NEW YORK
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON
PL/SQL procedure successfully completed.
rajesh@ORA11G>
rajesh@ORA11G> @delim_data 'select * from emp where job="CLERK"'
7369,SMITH,CLERK,7902,17-DEC-1980 12:00:00 AM,800,,20
7876,ADAMS,CLERK,7788,23-MAY-1987 12:00:00 AM,1100,,20
7900,JAMES,CLERK,7698,03-DEC-1981 12:00:00 AM,950,,30
7934,MILLER,CLERK,7782,23-JAN-1982 12:00:00 AM,1300,,10
PL/SQL procedure successfully completed.
rajesh@ORA11G>
rajesh@ORA11G>
rajesh@ORA11G> @delim_data 'select empno,ename,to_char(hiredate,"yyyymmddhhmiss") as hire_date from emp where job="CLERK"'
7369,SMITH,19801217120000
7876,ADAMS,19870523120000
7900,JAMES,19811203120000
7934,MILLER,19820123120000
PL/SQL procedure successfully completed.
rajesh@ORA11G>