generate flat files

[Gyankr]

I would like to know how to generate flat files (pipe delimited)using plsql.

Regards,
Gyan

[James R. Ferguson]

Hi Gyan:

This is remarkably like your previous query:

http://forums12.itrc.hp.com/service/forums/questionanswer.do?threadId=1184948

Regards!

...JRF...

[Gyankr]

Hi James,

As requirements change.... the forum changes :)

Regards,
Gyan

[Hasan Atasoy]

hi gyan ;

write an sql file,


set pagesize 0
set linesize 10000
set feedback off
spool file.lst
select col1 || '|' || col2 || '|' || col3


and execute it
sqlplus user/pass@TNS @sqlfile..


Hasan

[Gyankr]

Hi Hasan,

At the same time can i pass a shell variable to that sql file ,perhaps the below thread can be linked to this one. http://forums12.itrc.hp.com/service/forums/questionanswer.do?threadId=1185452

Regards,
Gyan
bottomline: To be done on the db side(using plsql)

[Yogeeraj_1]

hi Gyan,

Sorry i don't have a database to play with it right now but basically, you just have to use the following:

Set serveroutput on
Spool flatfile.txt
declare

begin

dbms_output.put_line(...)

end;
/


hope this helps!
kind regards
yogeeraj

[Volker Borowski · Accepted solution]

OK,

first approach should always utilize a favorite search engine of your choice :-)

A google with
"oracle flat file stored procedure"
on germanys google gives this hit on page one item 4 or 5:

http://forums.oracle.com/forums/thread.jspa?threadID=233148

I'll guess the solution there is not working first hand, but you should be able to work through oracle documentation and get a solution with this as a sample.

The solution there uses UTL_FILE.-functions
which have a good intro here
http://www.oracle.com/technology/sample_code/tech/pl_sql/htdocs/x/Utl_File/start.htm

Good luck
Volker

[Gyankr]

Thanks everybody and yes Volker i agree a combination of search engine and itrc forums can solve most of the problems :)

Regards,
Gyan

[Gyankr]

Problem resolved