How to call a Oracle stored procedure in uc4

[KumarRahul605332]

Hi All,

I have to execute a Oracle stored procedure from uc4 v9A. For this I first installed  a Oracle agent in Uc4 QA and Prod. I am able to make a connection to the db and also able to execute the command "Select * from Dual". I also got the prompt that SQL_EXECUTE_JOB will only work with SQL db and not with Oracel. So  I tried the below command to execute the stored procedure but keep getting the error Invalid sql statement. Please help.
Execute procedure_name

Thanks,
Kumar

[Pete Wirfs]

Here is what we use.  The dbms_output statement assures that large outputs from the Oracle procedure will not be lost.

sql_set_statement_terminator term="@";
begin
 dbms_output.enable(NULL);
 &SP_NAME#(&PARAMETER_LIST#);
end;
 @

[Nicole Boeger]

In Version 12 there is an extra command "set statement terminator". Add this first and enter the @-character as terminator.

Then add a user defined SQL statement like:

begin
 dbms_output.enable(NULL);
 &SP_NAME#(&PARAMETER_LIST#);
end;
@

[KumarRahul605332]

Pete Wirfs said:

Here is what we use.  The dbms_output statement assures that large outputs from the Oracle procedure will not be lost.

sql_set_statement_terminator term="@";
begin
 dbms_output.enable(NULL);
 &SP_NAME#(&PARAMETER_LIST#);
end;
 @

So i copied the exact script but i still got the same error of invalid SQL statement. I am not using a variable to call the stored procedure, neither am I specifying any parameters. I simply write sp_name(); I even tried sp_name; but it gave the same error.

[Pete Wirfs]

We are V9.SP5, and here is an example "Agent Log" from one of our executions of the above script.  I don't know why it isn't working for you.

2015-10-31 23:41:03 - U2000005 Job 'GLM0110.PK73334.GL.DIS' with RunID '2842401' started.

2015-10-31 23:41:03 - U2012001 Successfully established connection to 'ssssssss:0001/PRODDB' with user 'xxxxxxxx'.

2015-10-31 23:41:03            Oracle

2015-10-31 23:41:03            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

2015-10-31 23:41:03            With the Partitioning, OLAP, Data Mining and Real Application Testing options

2015-10-31 23:41:03 - U2012014 JDBC Driver version: 11.2.0.1.0

2015-10-31 23:41:03            ALTER SESSION SET NLS_DATE_FORMAT = 'dd-MON-YYYY'

2015-10-31 23:41:03 - U2012003 0 row(s) affected

2015-10-31 23:41:03            begin

2015-10-31 23:41:03               dbms_output.enable(NULL);

2015-10-31 23:41:03               pk73334_General_Ledger.sp73334_GL_DIS_GL110('10/31/2015');

2015-10-31 23:41:03            end;

2015-10-31 23:42:35 - U2004026 Job script ended normally.

[KumarRahul605332]

Pete Wirfs said:

We are V9.SP5, and here is an example "Agent Log" from one of our executions of the above script.  I don't know why it isn't working for you.

2015-10-31 23:41:03 - U2000005 Job 'GLM0110.PK73334.GL.DIS' with RunID '2842401' started.

2015-10-31 23:41:03 - U2012001 Successfully established connection to 'ssssssss:0001/PRODDB' with user 'xxxxxxxx'.

2015-10-31 23:41:03            Oracle

2015-10-31 23:41:03            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

2015-10-31 23:41:03            With the Partitioning, OLAP, Data Mining and Real Application Testing options

2015-10-31 23:41:03 - U2012014 JDBC Driver version: 11.2.0.1.0

2015-10-31 23:41:03            ALTER SESSION SET NLS_DATE_FORMAT = 'dd-MON-YYYY'

2015-10-31 23:41:03 - U2012003 0 row(s) affected

2015-10-31 23:41:03            begin

2015-10-31 23:41:03               dbms_output.enable(NULL);

2015-10-31 23:41:03               pk73334_General_Ledger.sp73334_GL_DIS_GL110('10/31/2015');

2015-10-31 23:41:03            end;

2015-10-31 23:42:35 - U2004026 Job script ended normally.

Here is what I am getting. Maybe I am going wrong with the arguments as I am not specifying any in my script.

2015-11-30 13:09:58 - U2000005 Job 'ZWD_V2P_PPM_CREATE_PR_FILE' with RunID '12578704' started.

2015-11-30 13:09:58 - U2012001 Successfully established connection to 'a829dae0.am.csplc.org:1525/MITGD' with user 'ppmdev'.

2015-11-30 13:09:58            Oracle

2015-11-30 13:09:58            Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

2015-11-30 13:09:58            With the Partitioning, OLAP, Data Mining and Real Application Testing options

2015-11-30 13:09:58 - U2012014 JDBC Driver version: 11.2.0.1.0

2015-11-30 13:09:58            sql_set_statement_terminator term="@"

2015-11-30 13:09:58            begin

2015-11-30 13:09:58             dbms_output.enable(NULL);

2015-11-30 13:09:58             Dpsg_FrameworkPOSendPRDet_UC4;

2015-11-30 13:09:58            end;

2015-11-30 13:09:58            @

2015-11-30 13:09:58            ORA-00900: invalid SQL statement

2015-11-30 13:09:58 - U2004025 Job-script execution was aborted.

2015-11-30 13:09:58 - U2012006 SQL Rollback executed

[Pete Wirfs]

Your sql_set_statement_terminator statement should not be displayed here.  That line of code should end with a semi-colon(;) so it won't get confused with the next statement.  Is that semi-colon missing?

[KumarRahul605332]

Pete Wirfs said:

Your sql_set_statement_terminator statement should not be displayed here.  That line of code should end with a semi-colon(;) so it won't get confused with the next statement.  Is that semi-colon missing?

Thanks Pete, it worked !! I used the below code and it worked and I was so delighted.

begin

 dbms_output.enable(NULL);

 Dpsg_FrameworkPOSendPRDet_UC4();

end;