Running a SQL script via Unix (AIX) shell - Importing and validating

Dear experts,
I have a sql scripts that I need to execute inside a ksh on Unix AIX as part of a automation project. but I'm keep getting the following errors. Any advice?
>:$ onhold.sh
SP2-0735: unknown SET option beginning "q.hold_fla..."
SP2-0734: unknown command beginning "where q.DE..." - rest of line ignored.
SP2-0734: unknown command beginning "and DESCRI..." - rest of line ignored.
SP2-0734: unknown command beginning "and q.REQU..." - rest of line ignored.
SP2-0734: unknown command beginning "and q.phas..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "and q.stat..." - rest of line ignored.
SP2-0734: unknown command beginning "and q.HOLD..." - rest of line ignored.

Commit complete.

SQL Script ran without error

Here is my SQL script file named onhold.sql:
 
update fnd_concurrent_requests q
 
set q.hold_flag = 'Y'
 
where q.DESCRIPTION Like '%Part%'
 
and DESCRIPTION NOT Like '%Part 1%'
 
and q.REQUESTED_BY = '2400'
 
and q.phase_code = 'P'
 
and q.status_code in ('Q','I')
 
and q.HOLD_FLAG = 'N';
commit;
 
--------------------
Here is my shell script file named onhold.sh:
#!/bin/ksh
 
mailadmin='myname@gmail.com'
SUBJECT="Your scripts was successfull"
 
sqlplus -s apps/password@testdb  <onhold.sql
 
EXIT_STATUS=$?
 
if [ ${EXIT_STATUS} -ne 0 ]; then
   echo "Error in SQL script"
 
   exit 1
else
   echo "SQL Script ran without error"
mailx -s "$SUBJECT" "$mailadmin"
exit
fi
 
----
Thanks in advance,
kmsadr

Open in new window

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

hernst42Commented:
migth be the -s option from sqlplus (couldn't find the meaning of that option). Shouldn't it be -S ?
sqlplus -S apps/password@testdb  <onhold.sql
0
dfkeCommented:
try using shell indirection inside your script..

=========
sqlplus connectstring << EOD
SQL statement1
SQL statement2
.
.
SQL statementn
EOD
=========


0
kmsadrAuthor Commented:
hernst42,
the '-s' flag suppresses the standard Oracle banner.( it's short for Silence). you can use either -s of -S.
Thanks,
kmasdr
0
kmsadrAuthor Commented:
dfke,
I've done that before with same result but I did it one more time as follow:

sqlplus -s apps/passoword@testdb  << EOF

update fnd_concurrent_requests q

set q.hold_flag = 'Y'

where q.DESCRIPTION Like '%Part%'

and DESCRIPTION NOT Like '%Part 1%'

and q.REQUESTED_BY = '2400'

and q.phase_code = 'P'

and q.status_code in ('Q','I')

and q.HOLD_FLAG = 'N';
commit;
EOF

AND THE OUTPUT:

>:$ onholdsql.sh
SP2-0735: unknown SET option beginning "q.hold_fla..."
SP2-0734: unknown command beginning "where q.DE..." - rest of line ignored.
SP2-0734: unknown command beginning "and DESCRI..." - rest of line ignored.
SP2-0734: unknown command beginning "and q.REQU..." - rest of line ignored.
SP2-0734: unknown command beginning "and q.phas..." - rest of line ignored.
SP2-0044: For a list of known commands enter HELP
and to leave enter EXIT.
SP2-0734: unknown command beginning "and q.stat..." - rest of line ignored.
SP2-0734: unknown command beginning "and q.HOLD..." - rest of line ignored.

Commit complete.
----------------------------
Thanks,
kmsadr




0
kmsadrAuthor Commented:
I was able to fix the issue. it has to with the way my sql was written with spaces in between them.
Thanks for all you comments. I'm closing this question.
kmsadr
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
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.