Home » Infrastructure » Unix » shell script error - passing parameters to PL/SQL (merged)
shell script error - passing parameters to PL/SQL (merged) [message #302446] Mon, 25 February 2008 12:43 Go to next message
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: Pasing parameter to PL/SQL procedure [message #302448 is a reply to message #302446] Mon, 25 February 2008 12:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Just using your title in search field:
http://www.orafaq.com/forum/?SQ=d41792e9db90769a27f0bcf81d0d4d1a&t=search&srch=Passing+parameter+to+PL%2FSQL+procedure+&b tn_submit=Search&field=all&forum_limiter=&search_logic=AND&sort_order=DESC&author=

Regards
Michel
Re: Passing parameter to PL/SQL procedure [message #302450 is a reply to message #302448] Mon, 25 February 2008 13:36 Go to previous messageGo to next message
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
Re: Passing parameter to PL/SQL procedure [message #302451 is a reply to message #302450] Mon, 25 February 2008 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is what many topics does here and I'm surprised you don't find the answer in these ones.
The principle of passing a parameter from a ksh script to a PL/SQL procedure that returns a value to the ksh script is usual and there even if this parameter is not a file name.

Regards
Michel
shell script error [message #303488 is a reply to message #302446] Fri, 29 February 2008 09:39 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #303502 is a reply to message #303488] Fri, 29 February 2008 10:47 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Use [code] tags and not [quote] tags to post code.
And continue the same script in the same topic.

Regards
Michel

[Updated on: Fri, 29 February 2008 10:47]

Report message to a moderator

Re: shell script error [message #303503 is a reply to message #303493] Fri, 29 February 2008 10:48 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous message
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.
Previous Topic: Accepting out parameters from a pl/sql procedure
Next Topic: Looping problem
Goto Forum:
  


Current Time: Thu Dec 12 04:05:23 CST 2024