unix help again regarding sqlplus

i'm reading lines in a file and then for each line, it has to connect to sqlplus and query ...

i've attached the code...i have 2 issues...

is there a more efficient way to do this wkithout having to connect to sqlplus for each line?  I mean is there a way to open one sqlplus session, read the files line by line and process and after parsing the file, close the connection.

The other problem i have is for some lines it throws an error saying "Error initializing sqlplus - Internal error" and it moves on to the next line. It might be related to the fact that i'm opening a new connection to sqlplus for each session

Can someone please advice?
while read line; do 
substrstart=$(echo $line|cut -c 1-2)
SERVICENO=$(echo $line|cut -c 3-12)
if (["$substrstart" = "25"]
echo "\
              set pagesize 0
              select psdreqt_recordtype,psdreqt_psd,adt.asdt_recordtype from psdrequest_transactions,(select asdt_recordtype,asdt_gainingpsd from asdresponse_transactions where asdt_cli='$SERVICENO' and asdt_uid=(select max(asdt_uid) from asdresponse_transactions where asdt_cli='$SERVICENO')) adt where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50') and psdreqt_uid=(select max(psdreqt_uid) from psdrequest_transactions where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50'));"|
       sqlplus -s test/test@tst01 | read PSDTRECORDTYPE PSDTGAININGPSD ASDTRECORDTYPE
              if [ "$ASDTRECORDTYPE" != "25" ] && ([ "$PSDTGAININGPSD" = "007" ] || [ "$PSDTGAININGPSD" = "003" ]); then
    echo $line >> $IN007003
   fi
else
echo $line >> $IN007002
fi
done

Open in new window

trance12Asked:
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.

nociSoftware EngineerCommented:
The sql statement is adjusted to contain 'limit 1' (the original only reads the first line)
first all input is handled like before but the SQL is written to an .sql file
the sql file it then processed by sqlplus, the output by the then following while loop.
THe sql statement should be on one line.. ;-)
---8<---
X=$$
echo "set pagesize 0" >/tmp/scratch-1.$X.tmp
while read line; do
substrstart=$(echo $line|cut -c 1-2)
if (["$substrstart" = "25"]
   SERVICENO=$(echo $line|cut -c 3-12)
   echo "select '$line' as x from
#Now my sql is not good.... so you need to print the above line only
#when
# adt recordtype  != 25 and psdtgainingpsd == 007 or == 003
#while read PSDTRECORDTYPE PSDTGAININGPSD ASDTRECORDTYPE ; do
#   if [ "$ASDTRECORDTYPE" != "25" ] && ([ "$PSDTGAININGPSD" = "007" ] || #[ "$PSDTGAININGPSD" = "003" ]); then
#you might also need to output the below statement to 1 line max.
# and subselect on the valid PSDTGN, ASDTRT...
(psdtrt, psdreqt_psd as PSDTGN ,adt.asdt_recordtype as ASDTRT from psdrequest_transactions,(select asdt_recordtype,asdt_gainingpsd from asdresponse_transactions where asdt_cli='$SERVICENO' and asdt_uid=(select max(asdt_uid) from asdresponse_transactions where asdt_cli='$SERVICENO')) adt where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50') and psdreqt_uid=(select max(psdreqt_uid) from psdrequest_transactions where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50')) limit 1) having PSDTGN in('003','007) and ASTRT!='25' " >>scratch-1.$X.tmp
else
   echo $line >>$IN007002
fi
done
sqlplus -s test/test@tst01 < /tmp/scratch-1.$X.tmp >$IN007003
rm -f /tmp/scratch-1.$X.tmp
0
trance12Author Commented:
Thanks..

i'm not sure what "psdtrt" is below on the first line? There is no table by that name

echo "select '$line' as x from (psdtrt

0
nociSoftware EngineerCommented:
You are right but they can be named from after the from?...
Along this line:
---8<---
use mysql
select 'xxxxxxxxxxx'  from (select count(*) as a from db where update_priv='Y') as b where a = 1 ;
---8<---

if you change the update_priv to user='someuser'

which is inside once then it will show xxxxxxxxxx otherwise it will be empty..
As said before i'm not a SQL wizard. but I can do simple queries.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

trance12Author Commented:
The line

else
echo $line >>$IN007002

how will that work?...if the sql query doesnt meet the criteria, will it write the line to IN007002?
0
trance12Author Commented:
sorry..i cut and pasted the code with a slight mistake above..

Notice that the fi comes below the
echo $line >> $IN007002 and not above...

will this still work?
while read line; do 
substrstart=$(echo $line|cut -c 1-2)
SERVICENO=$(echo $line|cut -c 3-12)
if (["$substrstart" = "25"]
echo "\
              set pagesize 0
              select psdreqt_recordtype,psdreqt_psd,adt.asdt_recordtype from psdrequest_transactions,(select asdt_recordtype,asdt_gainingpsd from asdresponse_transactions where asdt_cli='$SERVICENO' and asdt_uid=(select max(asdt_uid) from asdresponse_transactions where asdt_cli='$SERVICENO')) adt where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50') and psdreqt_uid=(select max(psdreqt_uid) from psdrequest_transactions where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50'));"|
       sqlplus -s test/test@tst01 | read PSDTRECORDTYPE PSDTGAININGPSD ASDTRECORDTYPE
              if [ "$ASDTRECORDTYPE" != "25" ] && ([ "$PSDTGAININGPSD" = "007" ] || [ "$PSDTGAININGPSD" = "003" ]); then
    echo $line >> $IN007003
else
echo $line >> $IN007002
fi
fi
done

Open in new window

0
nociSoftware EngineerCommented:
No the code wouldn't work but you can run two queries one resulting in all 007002 answer and one in all 007003 answers?
two queries is still a lot more efficient then runing it more than two times....

So the ECHO $line >>$IN007002 needs it's own equivalent query..
and now your original script makes more sense...
Please try to keer indenting to the right levels, it would have made the cut/paste mistake more visible.,..

while                    ; do
   if ....          ; then
      if .... ; then
         ...
      else
         ...
      fi
   fi
done

0
trance12Author Commented:
But the ECHO $line >>$IN007002 does not have a sql query...

The way i want it to work is if the sql query for $IN007003 returns no results from  the Database for a file record, then it should write the record from the file to $IN007002

so echo "select ....." >> scratch-1.$X.tmp

if no result from above, then

ECHO $line >>$IN007002

How would i write that?
0
nociSoftware EngineerCommented:
so copy the sql line  of the 003 variant and adjust the query to only yield the non 003 records.
(by changing a == '003' to a != '003' probably, because that would have been the difference too.
---8<---  (extract....)
if (["$substrstart" = "25"]
   SERVICENO=$(echo $line|cut -c 3-12)
   echo "select '$line' as x from
#Now my sql is not good.... so you need to print the above line only
#when
# adt recordtype  != 25 and psdtgainingpsd == 007 or == 003
#while read PSDTRECORDTYPE PSDTGAININGPSD ASDTRECORDTYPE ; do
#   if [ "$ASDTRECORDTYPE" != "25" ] && ([ "$PSDTGAININGPSD" = "007" ] || #[ "$PSDTGAININGPSD" = "003" ]); then
#you might also need to output the below statement to 1 line max.
# and subselect on the valid PSDTGN, ASDTRT...
(psdtrt, psdreqt_psd as PSDTGN ,adt.asdt_recordtype as ASDTRT from psdrequest_transactions,(select asdt_recordtype,asdt_gainingpsd from asdresponse_transactions where asdt_cli='$SERVICENO' and asdt_uid=(select max(asdt_uid) from asdresponse_transactions where asdt_cli='$SERVICENO')) adt where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50') and psdreqt_uid=(select max(psdreqt_uid) from psdrequest_transactions where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50')) limit 1) having PSDTGN in('003','007) and ASTRT!='25' " >>scratch-1.$X.tmp
else
   echo $line >>$IN007002
fi
done
sqlplus -s test/test@tst01 < /tmp/scratch-1.$X.tmp >$IN007003
rm -f /tmp/scratch-1.$X.tmp
---8<---

would become:

---8<---
if (["$substrstart" = "25"]
   SERVICENO=$(echo $line|cut -c 3-12)
   echo "select '$line' as x from
#Now my sql is not good.... so you need to print the above line only
#when
# adt recordtype  != 25 and psdtgainingpsd == 007 or == 003
#while read PSDTRECORDTYPE PSDTGAININGPSD ASDTRECORDTYPE ; do
#   if [ "$ASDTRECORDTYPE" != "25" ] && ([ "$PSDTGAININGPSD" = "007" ] || #[ "$PSDTGAININGPSD" = "003" ]); then
#you might also need to output the below statement to 1 line max.
# and subselect on the valid PSDTGN, ASDTRT...
(psdtrt, psdreqt_psd as PSDTGN ,adt.asdt_recordtype as ASDTRT from psdrequest_transactions,(select asdt_recordtype,asdt_gainingpsd from asdresponse_transactions where asdt_cli='$SERVICENO' and asdt_uid=(select max(asdt_uid) from asdresponse_transactions where asdt_cli='$SERVICENO')) adt where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50') and psdreqt_uid=(select max(psdreqt_uid) from psdrequest_transactions where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50')) limit 1) having PSDTGN in('003','007) and ASTRT!='25' " >>scratch-1.$X.tmp
   echo "select '$line' as x from
#Now my sql is not good.... so you need to print the above line only
#when
# adt recordtype  != 25 and psdtgainingpsd == 007 or == 003
#while read PSDTRECORDTYPE PSDTGAININGPSD ASDTRECORDTYPE ; do
#   if [ "$ASDTRECORDTYPE" != "25" ] && ([ "$PSDTGAININGPSD" = "007" ] || #[ "$PSDTGAININGPSD" = "003" ]); then
#you might also need to output the below statement to 1 line max.
# and subselect on the valid PSDTGN, ASDTRT...
(psdtrt, psdreqt_psd as PSDTGN ,adt.asdt_recordtype as ASDTRT from psdrequest_transactions,(select asdt_recordtype,asdt_gainingpsd from asdresponse_transactions where asdt_cli='$SERVICENO' and asdt_uid=(select max(asdt_uid) from asdresponse_transactions where asdt_cli='$SERVICENO')) adt where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50') and psdreqt_uid=(select max(psdreqt_uid) from psdrequest_transactions where psdreqt_cli='$SERVICENO' and psdreqt_recordtype in('10','50')) limit 1) having not (PSDTGN in('003','007) and ASTRT!='25') " >>scratch-2.$X.tmp
fi
done
sqlplus -s test/test@tst01 < /tmp/scratch-1.$X.tmp >$IN007003
sqlplus -s test/test@tst01 < /tmp/scratch-2.$X.tmp >$IN007002
rm -f /tmp/scratch-[12].$X.tmp
---8<---
0

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
trance12Author Commented:
The second query will return no results though, so there might be records in the file that dont satisfy any sql query.

Those are the ones that need to go to IN007002.

In the code snippet above by you, if a record doesnt fall into either of the queries, it will be skipped. I need to account for that record to and write it to the IN007002 file.

I hope i'm making sense.
0
nociSoftware EngineerCommented:
if you have 2 sets of data one satisfying the condition: X == 1 and the opposite  you should be able to test for the opposite by
not(X==1) ....
that's what i did in the above queries...

select ....... having X
and
select ....... having not(X)

X=   "PSDTGN in('003','007) and ASTRT!='25'"

if you omit the having stuff.... what data do you get...
(You could also go the other way...) select a whole lot of records using one sql query (not having  a 'having')
to scratch-3.tmp
and then
egrep  "^[^ \t]+[ \t]00[73][ \t]25[ \t]" scratch-3.tmp  >$IN007003
egrep  -v "^[^ \t]+[ \t]00[73][ \t]25[ \t]" scratch-3.tmp  >$IN007002

assuming your IFS is still a space or tab and your data starts with nonblank field...
0
trance12Author Commented:
Thanks..that works except for one issue which i'm not sure ...the lines from the original file get split up when it writes to the new file..

eg: the line below:

1502955762420030022010032502NENVY JEWELLERY PTY LTD                    28      MALLETT                 ST            CAMPERDOWN               NTW1050

When this writes to scratch-1.$X.tmp, it creates the sql fine

select '1502955762420030022010032502NENVY JEWELLERY PTY LTD                    28      MALLETT                 ST            CAMPERDOWN               NSW2050' from table name etc

However when i run the process

sqlplus -s test/test@tst01 < a >$IN007003, it breaks up the record..as below (ALLETT starts on a new line)

1502951640180030022010032302NENVY JEWELLERY PTY LTD                    28      M
ALLETT                 ST            CAMPERDOWN               NSW2050


and for some records, it throws the belowerror in the IN007003 file..
Enter value for associates: SP2-0232: Input too long. Must be less than 241 characters

Any ideas?

0
trance12Author Commented:
Thanks..that works except for one issue which i'm not sure ...the lines from the original file get split up when it writes to the new file..

eg: the line below:

1502955762420030022010032502NENVY JEWELLERY PTY LTD                    28      MALLETT                 ST            CAMPERDOWN               NTW1050

When this writes to scratch-1.$X.tmp, it creates the sql fine

select '1502955762420030022010032502NENVY JEWELLERY PTY LTD                    28      MALLETT                 ST            CAMPERDOWN               NSW2050' from table name etc

However when i run the process

sqlplus -s test/test@tst01 < a >$IN007003, it breaks up the record..as below (ALLETT starts on a new line)

1502951640180030022010032302NENVY JEWELLERY PTY LTD                    28      M
ALLETT                 ST            CAMPERDOWN               NSW2050


and for some records, it throws the belowerror in the IN007003 file..
Enter value for associates: SP2-0232: Input too long. Must be less than 241 characters

Any ideas?

0
trance12Author Commented:
i think the reason it's throwing

Enter value for associates: SP2-0232: Input too long. Must be less than 241 characters

is because the record has "& ASSOCIATES" and it must be treating the ampersand as a value input?
0
trance12Author Commented:
ok..i've fixed the issue with the & sign...

The only issue i have left is it splits the record into 2 lines...when writing to IN007003 file...

Funnily enough it seems to be happening at character 80 for all lines

0
nociSoftware EngineerCommented:
Small thing i saw, the 'set pagesize 0' is missing from scratch-2.
adding a 'set linesize 512' to both should prevent wrapping until 512 characters or more are printed.

You could run sed over the file to replace all & by \& i guess.

You could also try to break up the query in multiple lines ending in a \

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
Unix OS

From novice to tech pro — start learning today.