Simpler Navigation for Servers and Operating Systems
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
cancel
Showing results for 
Search instead for 
Did you mean: 

generate flat files

SOLVED
Go to solution
Gyankr
Frequent Advisor

generate flat files

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

Regards,
Gyan
8 REPLIES
James R. Ferguson
Acclaimed Contributor

Re: generate flat files

Hi Gyan:

This is remarkably like your previous query:

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

Regards!

...JRF...
Gyankr
Frequent Advisor

Re: generate flat files


Hi James,

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

Regards,
Gyan
Hasan Atasoy
Honored Contributor

Re: generate flat files

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
Frequent Advisor

Re: generate flat files

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
Honored Contributor

Re: generate flat files

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Volker Borowski
Honored Contributor
Solution

Re: generate flat files

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
Frequent Advisor

Re: generate flat files

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

Regards,
Gyan
Gyankr
Frequent Advisor

Re: generate flat files

Problem resolved