• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1804
  • Last Modified:

an autosys or unix shell script to execute an oralce stored procedure

I have an oracle server which has a package TEST_FILES and one of the procedures  is

createOracleFile
(
p_Error_Number             OUT ERROR_MESSAGES.ERROR_NUMBER%TYPE
,p_Error_Category             OUT ERROR_MESSAGES.ERROR_CATEGORY%TYPE
,p_Error_Type                   OUT ERROR_MESSAGES.ERROR_TYPE%TYPE
,p_Error_Message             OUT ERROR_MESSAGES.ERROR_MESSAGE%TYPE
,p_Error_Resolution       OUT ERROR_MESSAGES.ERROR_RESOLUTION%TYPE
,c_rs out c_Cursor
)

how can I run shell script or schedule this in autosys every day at 3:30 and read the p_Error_Number that this stored procedure outputs. Check this error number.

0
tech_question
Asked:
tech_question
1 Solution
 
biraCommented:
1 - create a script with this contents:

sqlplus oracleuser/password@database <<end
spool /tmp/result.txt

  here you place the oracle plsql block

/
spool off
end

2 -    chmod 777 yourscript

3 -    ./yourscript

             ( the output will be at /tmp/result.txt )

4 - place in crontab, in order to run everday at 3:30 :
    crontab -e

 30 03 * * * /yourdir/yourscript
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now