How to Run a Database Stored Function in a Job
Article ID: 40904
Updated On:
Products
Issue/Introduction
Problem:
Using DBPROC job to run a Database Stored Function failed with the error message like the following:
java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'GETAREA' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored
Resolution:
You can define a Database Stored Procedure (DBPROC) job to invoke a procedure stored in a database. To run a stored function, you can define a SQL job and use the select statement to invoke the stored function as follows:
sql_command: "select stored_function(parameters) from dual" (Oracle)
or
sql_command: "select stored_function(parameters)" (SQL Server, Sybase)
Here is an example. When job testStoredFunction starts, the stored function pkg_test1.getArea(4) will be called by the SQL statement defined for the job.
Job definition:
/* ----------------- testStoredFunction ----------------- */
insert_job: testStoredFunction job_type: SQL
machine: localhost
owner: [email protected]
permission:
date_conditions: 0
alarm_if_fail: 1
destination_file: "/test/cases/testStoredFunction.out"
sql_command: "select pkg_test1.getArea(4) from dual"
# sendevent -E STARTJOB -J testStoredFunction
# autorep -J testStoredFunction -d
Job Name Last Start Last End ST Run/Ntry Pri/Xit
________________________________________________________________ ____________________ ____________________ __ ________ _______
testStoredFunction 04/13/2016 18:09:23 04/13/2016 18:09:23 SU 890080/1 0
Status/[Event] Time Ntry ES ProcessTime Machine
-------------- --------------------- -- -- --------------------- ----------------------------------------
STARTING 04/13/2016 18:09:23 1 PD 04/13/2016 18:09:23 localhost
RUNNING 04/13/2016 18:09:23 1 PD 04/13/2016 18:09:24 localhost
<Executing at WA_AGENT_64>
SUCCESS 04/13/2016 18:09:23 1 PD 04/13/2016 18:09:24 localhost
<PKG_TEST1.GETAREA(4)=50.24>
SQL> select pkg_test1.getArea(4) from dual;
PKG_TEST1.GETAREA(4)
--------------------
50.24
In the CA Reference Guide, it states: If you use an Oracle or SQL Server database, you can also define a Database Stored Procedure job to run a stored function.
This is not true as the CA WA Agent for Database does not support Database Stored Function and there is no job attribute in a DBPROC job for the return value from a stored function. You can see from the example below, the attempt to run a stored function in a DBPROC job fails
/* ----------------- GetArea ----------------- */
insert_job: GetArea job_type: DBPROC
machine: localhost
owner: [email protected]
permission:
date_conditions: 0
alarm_if_fail: 1
sp_name: scott.pkg_test1.getArea
sp_arg: argtype=IN,datatype=NUMERIC,ignore=1,name=i_rad,value="4"
# sendevent -E STARTJOB -J GetArea
# autorep -J GetArea -d
Job Name Last Start Last End ST Run/Ntry Pri/Xit
________________________________________________________________ ____________________ ____________________ __ ________ _______
GetArea 04/13/2016 17:31:00 04/13/2016 17:31:00 FA 890000/1 6550
Status/[Event] Time Ntry ES ProcessTime Machine
-------------- --------------------- -- -- --------------------- ----------------------------------------
STARTING 04/13/2016 17:31:00 1 PD 04/13/2016 17:31:00 localhost
RUNNING 04/13/2016 17:31:00 1 PD 04/13/2016 17:31:01 localhost
<Executing at WA_AGENT_64>
FAILURE 04/13/2016 17:31:00 1 PD 04/13/2016 17:31:01
<java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'GETAREA' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored>
[*** ALARM ***]
JOBFAILURE 04/13/2016 17:31:01 1 PD 04/13/2016 17:31:01 localhost
Environment
Component:
Feedback
