I used the SQL query (taken from other threads here) to get the expected values to be written into a file.
Code:
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.
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.
Code:
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.
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:
Code:
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.
Hi,
Anyone can help on how to re-aligned the data into one raw;
Input Text File:
TESTA Carbon A China 06/28/2016 Active Vol. 8210, No. 2048,
New Era 02/25/2010
... (1 Reply)
Hi All,
I want to run multiple sql queries and store the data in variable but i want to use sql command only once. Is there a way without running sql command twice and storing.Please advise.
Eg :
Select 'Query 1 output' from dual;
Select 'Query 2 output' from dual;
I want to... (3 Replies)
Heyas
I havent found a thread to introduce, so i combine it with the issue i have.
EDIT /* Removed Problem because solved */
My first contact with Linux was back in 1995 with slackware, beeing a gamer back then, i (sadly) didnt bother to dig deeper.
I finaly joined the Linux community in... (0 Replies)
Hi All,
I have around 900 Select Sql's which I would like to run in an awk script and print the output of those sql's in an txt file.
Can you anyone pls let me know how do I do it and execute the awk script? Thanks. (4 Replies)
I do have a big file in the following format
>A1
ATGCGG
>A2
TCATGC
>A3
-TGCTG
The number of characters will be same under each subheader and only possible characters are A,T,G,C and -
I want to count the number of A's, T's,G's, C's & -'s vertically for all the positions so that I... (5 Replies)
Hi,
Im writing a script to run a bit of sql(via sqlplus) that pulls back some data and spools it to a file, I want the spool file to only display the data, with no sql command at the top and no reports at the bottom ie(# of records recieved).
I am currently doing it via a grep command but... (1 Reply)
Hi All
I'm running a shell script and the output is something like:
Col1 Col2 Col3
aaaa aaaaaaa aaaaa
bbbbb bbbbb bbbbbb
ccc cccccc ccccccc
But I require the output to printed as given below:
Col1 Col2 Col3
aaaa ... (4 Replies)
Hi
Need ur help for the below question.
I have two files File-1 & File-2.
File-1(This is a fixed file i.e. the content of this file is not going to change over a period of time)
------
a
b
c
d
e
File-2 (This is a file which changes daily but the record count remains the same)... (1 Reply)
Hi,
The following error message occured when I was trying to reboot my SUN machine:
Memory address not aligned
Its a Sun 280 R , Ultra SPARC III
What should I do.
Varma (3 Replies)
When I issue a "df" command the columns of the output are not aligned.
QMN012:.../oracle> df -k
Filesystem 1024-blocks Free %Used Iused %Iused Mounted on
/dev/hd4 524288 439592 17% 5423 6% /
/dev/M121A_HOME 3080192 2343764 24% 7263 2%... (1 Reply)
