Forum Discussion

jkrier's avatar
jkrier
Regular Contributor
8 years ago
Solved

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