HI,

I'm trying to get this right, please can you help. In my unix korn shell script, I call an oracle stored proc within a package and I specify 3 parameters, 2 of which are IN OUT parameters (i.e. I expect the stored proc to change them and return them back to me).

Does the unix code look ok and I using the bind variable in the right way syntactically???

Here's the code:

In Unix:

ERROR\_NO_OUT=0
ERROR\_TEXT_NO="N/A"

sqlplus -s $\{DB\_UNAME\}/$\{DB\_UPSWD\}@$\{DB_NAME\} << END\_OF_SQL
    exec psit\_ba\_upload\_pkg.apply\_jobs\('18/09/2002',:$ERROR\_NO_OUT, :$ERROR\_TEXT_NO\)

END_OF_SQL

echo "ERROR\_NO\_OUT=$ERROR\_NO_OUT\\n"
echo "ERROR\_TEXT\_NO=$ERROR\_TEXT_NO\\n"

In Oracle:

CREATE OR REPLACE PACKAGE chee_psit_ba_upload_pkg AS
PROCEDURE apply_jobs(p_business_date IN VARCHAR,
error_no_out IN OUT INT,
error_text_out IN OUT VARCHAR2);
END chee_psit_ba_upload_pkg;
/

CREATE OR REPLACE PACKAGE BODY chee_psit_ba_upload_pkg AS
PROCEDURE apply_jobs(p_business_date IN VARCHAR,
error_no_out IN OUT INT,
error_text_out IN OUT VARCHAR2)
IS
BEGIN
...
EXCEPTION
WHEN OTHERS THEN
error_no_out := 1;
error_text_out := 'Error retrieving data for the business units. ' || TO_CHAR(SQLCODE) || ' : ' || SQLERRM;
ROLLBACK;
RAISE;
END apply_jobs;

END psit_ba_upload_pkg;
/

In sqlplus, if you run 'exec psit_ba_upload_pkg.apply_jobs('18/09/2002',0, N/A) ' does it return any errors? Try typing 'show err' after the exec completes in sqlplus and see if it tells you anything. I think the problem may be the lack of ' around your variables as you call the exec.

You need to define the bind variables to SQLPLUS and set a unix variable to contain the returned variables, then parse them in unix using something like awk or cut
In Unix:

ERROR_NO_OUT=0
ERROR_TEXT_NO="N/A"

l_string_returned = `sqlplus -s ${DB_UNAME}/${DB_UPSWD}@${DB_NAME} << END_OF_SQL
-- define them
var error_no_out number := $ERROR_NO_OUT;
var error_test_no varchar2 := $ERROR_TEXT_NO;
exec psit_ba_upload_pkg.apply_jobs('18/09/2002',:ERROR_NO_OUT, :ERROR_TEXT_NO);
print :error_no_out,:error_text_out;
END_OF_SQL`

Echo $l_string_returned

-- parse em out here to unix varaiables using awk or cut.

#echo "ERROR_NO_OUT=$ERROR_NO_OUT\n"
#echo "ERROR_TEXT_NO=$ERROR_TEXT_NO\n"

I have a requirement to write a shell script that calls stored proc.
The stored proc should run for different regions(Asia, Europe) at the same time.

Please suggest best ways to achieve this.

Thanks in advance.

If this is Oracle, then this can be done with SQLPLUS.

Look at cron for scheduling.

What you are doing with time zones is up to you.

Thanks for the quick reply.

Yes. I am using SQLPLUS.

Could you please elaborate on cron scheduling..

Many thanks in advance.

Search this site for the word "crontab" and you will find many examples.

To make it run for different regions, I am planning to run as below instead of cron scheduling
(Wrapper script)-

nohup <xyz.ksh> ASIA 1>/dev/null &
nohup <xyz.ksh> EU 1>/dev/null &

Where xyz.ksh calls stored proc.