SQL output vertically aligned?

Shell Programming and Scripting
1

I used the SQL query (taken from other threads here) to get the expected values to be written into a file.

myQuery=`sqlplus -s cr_appsrvr/appsrvr@qwi << EndofFile
set heading off;
set tab off;
set wrap off;
set pages 0;
set feedback off;

SELECT CLEARINGHOUSE_TRACE_NUM, INSURED_ID FROM CR_APPSRVR.APRC_OUTBOUND_CLAIM C
WHERE C.PAYER_ID='MCAREGHI' AND C.INSURED_ID='060406043A';
exit;
EndofFile`

echo $myQuery>sql_out
echo "Query Output -  $myQuery"

This makes the file sql_out to look like:
NEIS0MDL-00022 060406043A NEIS2FTE-00111 060406043A NEIS2FTE-00112 060406043A NEIS2FTE-00113 060406043A NEIS2FTE-00114 060406043A NEIS2FTE-00115 060406043A NEIS2FTE-00116 060406043A 060406043A NEIUPVOV-00039 060406043A NEIUPVOV-00040 060406043A 10 rows selected.

But I want the output file to look like:

NEIS0MDL-00022 060406043A
NEIS2FTE-00111 060406043A
NEIS2FTE-00112 060406043A
NEIS2FTE-00113 060406043A
NEIS2FTE-00114 060406043A
NEIS2FTE-00115 060406043A
NEIS2FTE-00116 060406043A
<blank value> 060406043A
NEIUPVOV-00039 060406043A
NEIUPVOV-00040 060406043A

Kindly help me to make the output file like the above.

Regards,
Swami

2

one way:

sqlplus -s cr_appsrvr/appsrvr@qwi << EndofFile > myfile
set heading off;
set tab off;
set wrap off;
set pages 0;
set feedback off;

SELECT CLEARINGHOUSE_TRACE_NUM, INSURED_ID FROM CR_APPSRVR.APRC_OUTBOUND_CLAIM C
WHERE C.PAYER_ID='MCAREGHI' AND C.INSURED_ID='060406043A';
exit;
EndofFile

cat myfile

The problem is you are putting multiple lines of output into a variable - the shell ignores the newlines.

somevar=$(sqlplus -s cr_appsrvr/appsrvr@qwi << EndofFile
set heading off;
set tab off;
set wrap off;
set pages 0;
set feedback off;

SELECT CLEARINGHOUSE_TRACE_NUM, INSURED_ID || '|'
FROM CR_APPSRVR.APRC_OUTBOUND_CLAIM C
WHERE C.PAYER_ID='MCAREGHI' AND C.INSURED_ID='060406043A';
exit;
EndofFile )

echo "$somevar" | tr -s '|' '\n'

This lets you keep the variable, but you have to decode the | characters into newline characters

you should just use the Oracle spool command really.

3

Thanks for your reply. I appreciate for spending time on this.

I tried both the options you gave, first works fine and values are listed in the file as i expected. Regarding the second, it reported the following error:

This will be written to a file
WHERE C.PAYER_ID='MCAREGHI' AND C.INSURED_ID='060406043A'
                                             *
ERROR at line 3:
ORA-00904: "060406043A": invalid identifier

Actually my requirement is to verify these values from DB with another flat file. For using these values I think I can have a special character in between the two values while writing them into a file.

I'm not sure which of the above two would work for me good. Help me to choose.

4

That ORA- error message does not appear to be related to jim's scripts. Post your actual script here.

tyler_durden

5

I think i got it right now. I was helped by the user "sanjay.login"
Thanks everybody.