How to invoke PL/SQL with parameters 1

[weberm]

Here's my situation. I am attempting to create a shell script that will process every test file in a UNIX directory through a PL/SQL call but am having trouble b/c I have to do it by invoking a separate shell script.

for testfile in TestFile*.txt; do
    echo "Testing load file $testfile"
#   $HOME/bin/qs "exec RCPI.CLOSEOUT_PKG.load($testfile)"
    $HOME/bin/qs "@$HOME/sql/load_RF.sql"
done

The one I commented out works fine, except that it produces no output because the PL/SQL session is set up to not display DBMS_OUTPUT statements (I have tried but failed in the past to get that changed), so I have to call a separate file "load_RF.sql".

SET SERVEROUTPUT ON
EXEC CLOSEOUT_PKG.load($testfile)
EXIT

However, PL/SQL doesn't know what to do with $testfile. How can I pass the value of testfile into the PL/SQL script? I really don't want to hard-code the stored proc parameters in the PL/SQL script!

 

[Annihilannic]

Does qs accept scripts on standard input? If so, try supplying the script using a "here" document, for example:

for testfile in TestFile*.txt; do
    echo "Testing load file $testfile"
    $HOME/bin/qs << HERE
SET SERVEROUTPUT ON
EXEC CLOSEOUT_PKG.load($testfile)
EXIT
HERE
done

That way the value of the $testfile variable should be substituted as you expect.

Annihilannic.

 

[weberm]

Does qs accept scripts on standard input?

Correct.

echo "$*" |sqlplus -s scott/tiger

If so, try supplying the script using a "here" document

Ah, so I should pipe in the commands! I'm a little confused about "here", though. Is it the name of a file being piped in or the delimiters to an inline command?

 

[chgwhat]

I have not tried this but does this work?

for testfile in TestFile*.txt; do
    echo "Testing load file $testfile"
#   $HOME/bin/qs "exec RCPI.CLOSEOUT_PKG.load($testfile)"
    $HOME/bin/qs "@$HOME/sql/load_RF.sql" $testfile
done

And then use a parameters

SET SERVEROUTPUT ON
EXEC CLOSEOUT_PKG.load(&1)
EXIT

Tony ... aka chgwhat

When in doubt,,, Power out...

 

[weberm]

I have not tried this but does this work?

Almost! I had to put quotes around the parameters because the stored proc uses a string, but that did the trick.

SET SERVEROUTPUT ON
EXEC CLOSEOUT_PKG.load('&1')
EXIT

Thank you so much!

 

[Annihilannic]

A side topic, but... is qs and alias for sqlplus or something? I can't find it on any of our systems...

Annihilannic.

 

[weberm]

It's the script file that I use to invoke sqlplus.