| shell script error - passing parameters to PL/SQL (merged) [message #302446] |
Mon, 25 February 2008 12:43  |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
Hi,
I am using Ksh script to read file name of a particualr pattern.. say P*.txt from a directory
I want to use the the filename to be sent as parameter to a concurrent program which is in turn called by a procedure which uses FND_REQUEST.SUBMIT_REQUEST.
Please help,
|
|
|
|
|
|
| Re: Passing parameter to PL/SQL procedure [message #302450 is a reply to message #302448] |
Mon, 25 February 2008 13:36  |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
Hi,
Thanks for the link, but none of them had to use a file name as parameter.
I just wanted to know, what should be done to relate the Unix script and the pl/sql script and exchange values between the two.
scenario is like this.
ksh script is needed to read filenames of a particular pattern from a directory.
Once a file name is found, control must be given to a pl/sql procedure.This procedure uses FND_REQUEST.SUBMIT_REQUEST to call an already existing concurrent program(This concurrent program uses the filename as parameter, given by the Unix script).
Once the concurrent program ends, the request status and id has to be sent back to the Unix shell script. Depending on the status, the Unix script archives the file to another directory.
So can you please assist me with the code? both pl/sql and Unix.
Thanks in advance
|
|
|
|
|
|
| shell script error [message #303488 is a reply to message #302446] |
Fri, 29 February 2008 09:39 |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
I am running the below shell script and getting some errors
| Quote: | #!/bin/ksh
echo "=================================================="
echo "Beginning program " `date "+%m/%d/%y %H:%M:%S"` "\n"
echo "================================================="
datadir="/path/data";
archdir="/path/archive";
cd $datadir
ls -lrt A*.txt | awk '{print $9}' | while read fname
do
echo The file to be passed to importing program is $datadir/$fname
RETVAL='sqlplus -s apps/apps4you@GAMDEV << EOF
set serveroutput on
declare
fname := $fname;
datadir := $datadir
request_id number(30);
status varchar2(30);
phase varchar2(30);
begin
XX_Call_Import_Prgm(fname,datadir,request_id,status,phase);
dbms_output.put_line ('Values_are '|| request_id ||' '|| status ||' '|| phase);
end;
/
exit;
EOF'
STATUS= echo $RETVAL | grep Values_are | awk '{print $2}'
PHASE= echo $RETVAL | grep Values_are | awk '{print $3}'
echo status=$STATUS
echo phase=$PHASE
if [[$PHASE='Completed' -a $STATUS='Normal']]; then
echo Import Successful
mv $fname $archdir
else
echo Import Not Successful
fi
done
echo "=================================================="
echo "Ending program " `date "+%m/%d/%y %H:%M:%S"` "\n"
echo "=================================================="
|
The errors are as follows;
| Quote: | || request_id ||: not found
status=
phase=
[[=Completed: not found
|
The pl/sql procedure which this shell script calls, is executing fine even though it takes long..What could be the reason behind these errors?
Thanks in advance
[Updated on: Fri, 29 February 2008 10:03] Report message to a moderator |
|
|
|
| Re: shell script error [message #303493 is a reply to message #303488] |
Fri, 29 February 2008 10:16 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
You still mix up different quotes, like you did last week.
` (backticks) are something entirely different and mean something entirely different than than ' (single quotes).
|
|
|
|
|
|
| Re: shell script error [message #303503 is a reply to message #303493] |
Fri, 29 February 2008 10:48 |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
Thank you Thomas,
Now the error "request_id not found" is fixed.
But I still get the output as
status=
phase=
[=Completed: not found
Can you please give me the corrections needed in the script?
Thank you so much for your advice.
[Updated on: Fri, 29 February 2008 10:51] Report message to a moderator |
|
|
|
| Re: shell script error [message #303511 is a reply to message #303503] |
Fri, 29 February 2008 11:15 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
You don't have anything in the $PHASE variable it seems, so the IF is syntactically wrong.
Possibly because of some errors that happen during the SQL execution somewhere that and don't show up anywhere, because the error messages wind up in the RETVAL variable somewhere.
And even if you get the script to run, the moment anything database related goes wrong during the execution there is no way of knowing if and what went wrong.
The whole shell-script / back-tick / grep strategy to execute procedures this way simply gets impossible to maintain at some point.
You should stop digging deeper into the hole and switch so something that makes SQL execution easier to maintain and debug, perl for example.
|
|
|
|
| Re: shell script error [message #303541 is a reply to message #303511] |
Fri, 29 February 2008 13:46 |
shree_z
Messages: 75
Registered: February 2008
|
Member |
|
|
Yes Thomas, I agree with you. But I have another approach. I am not sure if I am right.
Now I had created a procedure which calls a concurrent program. I am calling this procedure from the shell script discussed above.
Instead of creating a procedure I can create a sql script which does the function of this procedure and then call this sql script from the shell script.
then I would like to know how to call sql script from shell script.
I might need to register the shell program as .prog to get the job done.
Could you please help me in this regard?
|
|
|
|
| Re: shell script error [message #303560 is a reply to message #303541] |
Fri, 29 February 2008 14:46 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
|
I could help you if you had a specific question about a specific problem, but I don't have time to fix every single syntax error you encounter one by one.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
