• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 5308
  • Last Modified:

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?
0
mallelaajay
Asked:
mallelaajay
4 Solutions
 
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
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now