New line for each row in sqlplus

Hi,
    I need to write a sqlplus script inside a shell script which creates a flat file out of an oracle table for me. Below is the script:

export exedir=/home/mallaj
export go_directory=/logs/etl/go
export logfile=/home/mallaj/logs/pre_fct_cat_upc_log.logfile
echo "" >$logfile
. $exedir/db.config
sql=`sqlplus -s <<END
$admin_user/$admin_password@$db_name
set pagesize 0 feedback off verify off heading off echo off timing off
set serveroutput on
set linesize 100
select
PRODKEY || '|' ||
LEVELKEY || '|' ||
GAP
FROM SALES_LEVEL;
set trimspool on
/
exit;
END`
echo $sql>>$logfile

What sqlplus command should i be using so that each row is extracted on a separate line when the output is spooled?
mallelaajayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Guy Hengel [angelIII / a3]Billing EngineerCommented:
normally sqlplus spoool does this?

also, you might need to put this line:
set trimspool on
before the select?!
0
JPrzybyszewskiCommented:
Hi

I am not sure if I understood your question correctly, but in general each row in SQLPLUS is displayed in separate line. Sometimes the row gets divided into separate lines due to the display settings for the columns that are displayed in the query. Assuming that the whole length of 1 row in your query is at most 100 characters I would modify the query as following and add one more SQLPLUS configuration modifier.

set pagesize 0 feedback off verify off heading off echo off timing off
set serveroutput on
set linesize 100

col display_col for a100

select
PRODKEY || '|' ||
LEVELKEY || '|' ||
GAP as display_col
FROM SALES_LEVEL;
set trimspool on

Please note that I have difined the name for the column - display_col and then I have defined in SQPLUS level the length of the column before the select. This should solve your problem.

Regards
Jarek
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mallelaajayAuthor Commented:
I don't understand why, but my code is not doing that. Actually i'm not spooling it, but the sqlplus output goes to a unix variable and then appending it into a file. May be that's the reason, i don't see each row on a new line.
0
OP_ZaharinCommented:
- as the earlier expert suggest use CHR(10) or with CHR(13) for a new line:

select
PRODKEY || '|' ||
LEVELKEY || '|' ||
GAP || CHR(10)
FROM SALES_LEVEL;

- or


select
PRODKEY || '|' ||
LEVELKEY || '|' ||
GAP || CHR(10) || CHR(13)
FROM SALES_LEVEL;
0
mallelaajayAuthor Commented:
Thanks for all the help guys.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.