topic generate flat files in Operating System - HP-UX

generate flat files

Gyankr — Wed, 19 Dec 2007 13:03:12 GMT

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

Regards,
Gyan

Re: generate flat files

James R. Ferguson — Wed, 19 Dec 2007 13:07:54 GMT

Hi Gyan:

This is remarkably like your previous query:

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

Regards!

...JRF...

Re: generate flat files

Gyankr — Wed, 19 Dec 2007 13:11:45 GMT

Hi James,

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

Regards,
Gyan

Re: generate flat files

Hasan Atasoy — Wed, 19 Dec 2007 13:20:20 GMT

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

Re: generate flat files

Gyankr — Wed, 19 Dec 2007 15:19:26 GMT

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)

Re: generate flat files

Yogeeraj_1 — Wed, 19 Dec 2007 17:58:19 GMT

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

Re: generate flat files

Volker Borowski — Wed, 19 Dec 2007 18:38:41 GMT

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

Re: generate flat files

Gyankr — Sat, 29 Dec 2007 17:19:16 GMT

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

Regards,
Gyan

Re: generate flat files

Gyankr — Mon, 31 Dec 2007 11:51:37 GMT

Problem resolved