Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Line break in sqlplus output through ksh script

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Line break in sqlplus output through ksh script
# 1  
Old 04-11-2017
Line break in sqlplus output through ksh script
Hi,

I am new to shell script programming. I have written a ksh script to run the sql File placed in server directory and spool the output in destination directory.
Below Command:
Code:
$ORACLE_HOME/bin/sqlplus -s $ora_uid @$sqlfile_loc$testquery.sql  > $opfiledirectory

It is generating the output but complete data is in Single Line.
in the .sql file we have below values turned on and off
Code:
set feedback off
set echo off
set verify off
set pagesize 0
set heading off
set trim on
set wrap on
set trimspool on
set linesize 9000

current Output Data format:
Code:
Item_id|organization_id|ItemName|Item_id|organization_id|ItemName|Item_id|organization_id|ItemName|Item_id|organization_id|ItemName|

Can someone please help how to get output data with each row in each line.
Expected format:
Code:
Item_id|organization_id|ItemName|
Item_id|organization_id|ItemName|
Item_id|organization_id|ItemName|
Item_id|organization_id|ItemName|

Moderator's Comments:
Mod Comment Please use CODE tags when displaying sample input, sample output, and code segments (as required by forum rules).
Last edited by Don Cragun; 04-11-2017 at 02:04 AM.. Reason: Add CODE and ICODE tags.
# 2  
Old 04-11-2017
can we see your sql please?

also how are you viewing the output?

vi? emacs? dumping it to a text file and opening it in notepad?
Last edited by os2mac; 04-11-2017 at 02:30 AM..
# 3  
Old 04-11-2017
Below is the SQL:
Code:
SELECT &&1 || '|' || to_char(sysdate, 'YYYYMMDD') || 'T' ||
        to_char(sysdate, 'HH24MM') || '00' || '|' || item.segment1 || '|' || 'IM' || '|' ||
        substr(item.description, 1, 50) || '|' || item.primary_UOM_Code || '|' || case
          when item.unit_weight > 0 then
           item.unit_weight
          when item.unit_weight <= 0 then
           9999.9999
          else
           9999.999
        end || '|' || NVL(item.weight_uom_code, 'KG') || '|' || 0 || '|' ||
        'USD' || '|' || 'N' || '|' || '' || '|' || '' || '|' || 'E' || '|' || '' || '|' || '' || '|' ||
        replace(glo_buyer.agent_name, '''', '') || '|' || '' || '|' || case
          when length(hts.tariff_code) < 10 THEN
           '1111111111'
          when hts.tariff_code Is Null THEN
           '1111111111'
          else
           hts.tariff_code
        END || '|' || 'M' || '|' || 'XX' || '|' || 'MULTIPLE' || '|' || '' || '|' || case
          when cst.item_cost > 0 then
           cst.item_cost
          when lacst.item_cost > 0 then
           lacst.item_cost
        --when pendingcst.item_cost > 0 then pendingcst.item_cost
          else
           0
        end || '|' ||
       --cst.item_cost||'|'||
        0 || '|' || 'USD' || '|' || 0 || '|' || 0 || '|' || '' || '|' || 0 || '|' || 0 || '|' || '' || '|' || '' || '|' || '' || '|' || 0 || '|' || '' || '|' || 0 || '|' || '' || '|' ||
        glo_buyer.agent_name || '|' || '' || '|' || ''
  FROM apps.mtl_system_items_b item

(more conditions)

we are generating output in .txt format and viewing output in notepad.
Last edited by Don Cragun; 04-11-2017 at 03:28 AM.. Reason: Add CODE and ICODE tags, again.
# 4  
Old 04-11-2017
Try a here document like this:
I am assuming the things with a $ are shell variables.
Which you should write like this so somebody later on can figure it out:
Code:
# like this: ${variable goes here}
# example:
${ora_uid}

Example here document starts with -EOF, end with EOF:
Code:
$ORACLE_HOME/bin/sqlplus -s ${ora_uid} <<-EOF 
[set commands go here]
spool ${opfiledirectory}
@${sqlfile_loc}${testquery.sql}
spool off
EOF

This User Gave Thanks to jim mcnamara For This Post:
Sumit Arora
# 5  
Old 04-11-2017
On a slightly different note:
Quote:
Originally Posted by Sumit Arora
...
Code:
 ...
case
    when item.unit_weight > 0 then
        item.unit_weight
    when item.unit_weight <= 0 then
        9999.9999
    else
        9999.999
end || '|' ||
...

...
when will the "else" branch be executed? Smilie
This User Gave Thanks to durden_tyler For This Post:
saps19
# 6  
Old 04-11-2017
Quote:
Originally Posted by Sumit Arora
we are generating output in .txt format and viewing output in notepad.
I suspected as much. You have an OS problem not a code problem. Windows and Unix use different end of line characters. You'll need to convert the file in Unix to a windows format before moving it to windows for viewing.

in your Unix box from a shell window do
Code:
man unix2dos

This will change the end of line characters to windows versions and allow you to see the line breaks.
This User Gave Thanks to os2mac For This Post:
Sumit Arora
# 7  
Old 04-11-2017
Thanks everyone for prompt response.
Found that it was issue while copying the file from unix to local system and opening in Notepad. converting the file into text mode while copying solved the issue.
In server files were ok.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Oracle/SQLPlus help - ksh Script calling .sql file not 'pausing' at ACCEPT, can't figure out why

Hi, I am trying to write a script that calls an Oracle SQL file who in turns call another SQL file. This same SQL file has to be run against the same database but using different username and password at each loop. The first SQL file is basically a connection test and it is supposed to sort... (2 Replies)
Discussion started by: newbie_01
2 Replies

2. UNIX for Beginners Questions & Answers

awk script for pattern match and line break

Hi, I have input which reads like 9089.00 ----- kl jkjjljk lkkk; (909099) 9097.00 ----- HGJJHHJ jcxkjlkjvhvlk jhdkjksdfkhfskd 898.00 ----- HHHH I am trying to do something like this - As soon as I found pattern match "XYZ.00-----" it will insert a line break to the input and will go to... (3 Replies)
Discussion started by: Indra2011
3 Replies

3. Shell Programming and Scripting

Output result with break line

Hello, I am coding a script to check mysql databases using bash script, I would like to check if the status of a table is not 'OK', will return the table name and do some more actions: check.log table1 OK table2 Some error here table3 ... (5 Replies)
Discussion started by: Abu Rayane
5 Replies

4. Shell Programming and Scripting

Shell Script (ksh) - SQLPlus query filter using a string variable

Using ksh, I am using SQLPlus to execute a query with a filter using a string variable. REPO_DB=DEV1 FOLDER_NM='U_nmalencia' FOLDER_CHECK=$(sqlplus -s /nolog <<EOF CONNECT user/pswd_select@${REPO_DB} set echo off heading off feedback off select subj_name from subject where... (5 Replies)
Discussion started by: nkm0brm
5 Replies

5. Solaris

sqlplus output from ksh.

Hi All, I have the below simple script. It runs just fine by itself when I manually invoke it. But once I put it in the crontab with entry: * * * * * /users/myuser/test.ksh >> /users/myuser/log/test.txt" It does NOT print the returned value ($REMAIN) from the DB!? The result in the... (3 Replies)
Discussion started by: steve701
3 Replies

6. Shell Programming and Scripting

KSH script to run other ksh scripts and output it to a file and/or email

Hi I am new to this Scripting process and would like to know How can i write a ksh script that will call other ksh scripts and write the output to a file and/or email. For example ------- Script ABC ------- a.ksh b.ksh c.ksh I need to call all three scripts execute them and... (2 Replies)
Discussion started by: pacifican
2 Replies

7. UNIX for Advanced & Expert Users

sqlPlus output with new line character

Hi, I have a script which calls a sqlplus command and i'm saving the output in a variable. Z=`sqlplus -s $TC_ORACLE_USER/$TC_ORACLE_PASSWORD@$TC_CONNECT_STRING <<eof set echo off set head off set serveroutput on; set feedback off; select description from period where trunc(sysdate)... (2 Replies)
Discussion started by: decci_7
2 Replies

8. Shell Programming and Scripting

How to pass variable to SQLPLUS in a ksh script?

Hi, I am writing a ksh script which will use sqlplus to run a sql and pass 2 variables as the SQL request. In the ksh script, I have 2 variables which are $min_snap and $max_snap holding 2 different numbers. Inside the same script, I am using SQLPLUS to run an Oracle SQL script,... (6 Replies)
Discussion started by: rwunwla
6 Replies

9. Shell Programming and Scripting

Need help with ksh script that uses sqlplus, called from PHP

I have a ksh script that connects to sqlplus and dumps the query results into a file. The script works file when I run it from the command line, however, when I call it from PHP using system(), exec(), or shell_exec() commands, the script doesn't seem to run the query. It will create the text file... (7 Replies)
Discussion started by: j2owilson
7 Replies

10. Shell Programming and Scripting

ksh script: 'exit' being treated as 'break 2'

hi, in a korn shell script, has anyone ever seen an 'exit' being treated as a 'break 2'? I have a script which has 3 nested loops. Within the inner most loop, i'm trying to exit the script on a fault condition. instead of exiting, it's acting as a 'break 2' and then continuing on with the... (4 Replies)
Discussion started by: gsatch
4 Replies
Login or Register to Ask a Question