How to Run a Database Stored Function in a Job

book

Article ID: 40904

calendar_today

Updated On:

Products

CA Workload Automation AE - Business Agents (AutoSys) CA Workload Automation AE - Scheduler (AutoSys) CA Workload Automation Agent

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

Release: ATSYHA99000-11.3.6-Workload Automation AE-High Availability Option
Component: