?
Solved

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

Posted on 2008-02-12
5
Medium Priority
?
4,003 Views
Last Modified: 2013-11-17
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

0
Comment
Question by:kmsadr
  • 3
5 Comments
 
LVL 48

Expert Comment

by:hernst42
ID: 20882534
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
 
LVL 11

Expert Comment

by:dfke
ID: 20882553
try using shell indirection inside your script..

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


0
 

Author Comment

by:kmsadr
ID: 20883608
hernst42,
the '-s' flag suppresses the standard Oracle banner.( it's short for Silence). you can use either -s of -S.
Thanks,
kmasdr
0
 

Author Comment

by:kmsadr
ID: 20886078
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
 

Accepted Solution

by:
kmsadr earned 0 total points
ID: 20892677
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

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Utilizing an array to gracefully append to a list of EmailAddresses
For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

589 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question