Call stored procedure that's in a package in oracle
I'm trying to call a stored proc that is inside of a package. I can't seem to find much documentation around this anywhere. I have seen a few somewhat complicated groovy scripts but it seems like this should be available as a feature in SoapUI seeing as how they put a checkbox on the jdbc steps for doing this.
The most straight forward example I have found is
{call package_name('param1', 'param2', 'param3')}
When I use this I get the error
- 2018-04-19 12:59:31.654 - Error getting response; java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'GET_BEAM_SERVICE' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored
So I pulled the declare out of sqldeveloper
DECLARE
SATELLITE_NAME_IN VARCHAR2(32);
ADDRESS_LINE1_IN VARCHAR2(80);
ADDRESS_LINE2_IN VARCHAR2(80);
CITY_IN VARCHAR2(80);
STATE_CODE_IN VARCHAR2(2);
ZIP_CODE_IN VARCHAR2(5);
ZIP_4_IN VARCHAR2(4);
SALES_CHANNEL_NAME_IN VARCHAR2(1024);
BEAM_SVC_ADDR_REF_OUT APP.beam_service_refcur;
BEGIN
SATELLITE_NAME_IN := 'Sat-1';
ADDRESS_LINE1_IN := '512 HIGHWAY 26';
ADDRESS_LINE2_IN := '';
CITY_IN := 'OKOLONA';
STATE_CODE_IN := 'AR';
ZIP_CODE_IN := '71962';
ZIP_4_IN := '9799';
SALES_CHANNEL_NAME_IN := 'DIRECT';
APP.GET_BEAM_SERVICE(
SATELLITE_NAME_IN => SATELLITE_NAME_IN,
ADDRESS_LINE1_IN => ADDRESS_LINE1_IN,
ADDRESS_LINE2_IN => ADDRESS_LINE2_IN,
CITY_IN => CITY_IN,
STATE_CODE_IN => STATE_CODE_IN,
ZIP_CODE_IN => ZIP_CODE_IN,
ZIP_4_IN => ZIP_4_IN,
SALES_CHANNEL_NAME_IN => SALES_CHANNEL_NAME_IN,
BEAM_SVC_ADDR_REF_OUT => BEAM_SVC_ADDR_REF_OUT
);
/* Legacy output:
DBMS_OUTPUT.PUT_LINE('BEAM_SVC_ADDR_REF_OUT = ' || BEAM_SVC_ADDR_REF_OUT);
*/
:BEAM_SVC_ADDR_REF_OUT := BEAM_SVC_ADDR_REF_OUT; --<-- Cursor
--rollback;
END;
but when I run that I get the error
- 2018-04-19 13:17:10.562 - Error getting response; java.sql.SQLException: Missing IN or OUT parameter at index:: 1
Hi jkrier,
Unfortunately, it's a known issue in ReadyAPI - currently, the stored procedures with output parameters cannot be executed. You can use the following workarounds:
- un-check the Stored procedure check-box and write a query according to SQL syntax;
- use a groovy script to call a stored procedure.
