Forum Discussion

jkrier's avatar
jkrier
Regular Contributor
7 years ago

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
  • Nastya_Khovrina's avatar
    Nastya_Khovrina
    6 years ago

    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.

     

  • jkrier's avatar
    jkrier
    Regular Contributor
    7 years ago

    So I was able to get a bit farther. It looks like I wasn't getting all the way to the proc and had to change the call to

     

    {call SCHEMA_NAME.PACKAGE_NAME.PROCEDURE_NAME('param1', 'param2', 'param3')}

    I am still getting an error

     

    • 2018-04-20 15:23:45.000 - Error getting response; java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00801: internal error [22503] ORA-06550: line 1, column 7: PL/SQL: Statement ignored

     

    If I try this in sqldeveloper it returns

     

    Error starting at line : 1 in command -
{call SIEBAS_OWNER.SIEBAS_APP.GET_BEAM_SERVICE(1)('Anik-F2-SB2', '5120 HIGHWAY 26 WEST', '', 'OKOLONA', 'AR', '71962', '9799', 'WB DIRECT')}
Error report -
Unknown Command

    So I'm guessing this is not the correct command.

    • Nastya_Khovrina's avatar
      Nastya_Khovrina
      SmartBear Alumni (Retired)
      6 years ago

      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.

       

      • TNeuschwanger's avatar
        TNeuschwanger
        Champion Level 1
        6 years ago

        hello jkrier...  Thanks to Nastya, this topic was surfaced to near the top of the list so i noticed it.  I don't know if the following groovy code might help or not, but i have successfully called an oracle package stored procedure through groovy script step...

         

        Regards,

        Todd

         

         

        //   jdbc:oracle:thin:rwmb/PASS_VALUE@yourserver.business.com:1577:w043
//  RWM.RWMB_APP_PKG.GET_BUS_SUBJECT_AREA_ALL_SP(?, <%=AplycnSysId%>, '<%=RetroDate%>')

import java.sql.*;
import groovy.sql.Sql;
import oracle.jdbc.driver.OracleTypes;
import groovy.xml.MarkupBuilder;

def protocol = 'jdbc:oracle:thin:';
def props = new Properties();
props.put('user', 'someuser');
props.put('password', 'somepassword');
def connectStr = "${protocol}@yourserver.business.com:1577:w043";
log.info "connectStr=" + connectStr;
def sqlOracle = Sql.newInstance(connectStr, props);
   
def sysId = 9018;
def retroDt = 'CURR';

 //PROCEDURE GET_BUS_SUBJECT_AREA_ALL_SP  ( p_handle_inout  IN OUT RetData, p_aplycn_sys_id_in  IN Number, p_retro_date_in  IN Varchar2 );

def outList = [];
def eachVal = '';
MarkupBuilder xml;
xml = new MarkupBuilder(new PrintWriter(new File('c:\\temp\\markupbuilder' + testRunner.testCase.name + '.xml')));

//sqlOracle.call("begin RWM.RWMB_APP_PKG.GET_BUS_SUBJECT_AREA_ALL_SP(${Sql.resultSet OracleTypes.CURSOR}, ${sysId},${retroDt}); end;") {rset ->
sqlOracle.call("begin RWM.RWMB_APP_PKG.GET_BUS_SUBJECT_AREA_ALL_SP(${Sql.resultSet(-10)}, ${sysId},${retroDt}); end;") {rset ->
   ResultSetMetaData rsmd = rset.getMetaData();
   rsmd.eachWithIndex { rs, indx ->
      log.info '====================' + rsmd.getColumnName(indx + 1);
   };
//   rset.eachRow(){
//     log.info it.getAt(1) + '|1|' + it.getAt(2) + '|2|' + it.getAt(3) + '|3|' + it.getAt(4) + '|4|' + it.getAt(5) + '|5|' + it.getAt(6) + '|6|' + it.getAt(7) + '|7|' + it.getAt(8);
////   log.info it.getString("BUS_AREA_NAME");
//   };
//   rset.eachRow(){
//     log.info '-------------------' + it.getAt(1) + '|1|' + it.getAt(2) + '|2|' + it.getAt(3) + '|3|' + it.getAt(4) + '|4|' + it.getAt(5) + '|5|' + it.getAt(6) + '|6|' + it.getAt(7) + '|7|' + it.getAt(8);
//   };   
   xml.ROWSET {
      rset.eachRow(){ row ->
         ROW {
            BUS_AREA_ID(row.BUS_AREA_ID);
            BUS_AREA_NAME(row.BUS_AREA_NAME);
            PARTY_ID(row.PARTY_ID);
            DATA_OBJECT_ID(row.DATA_OBJECT_ID);
            DATA_OBJECT_NAME(row.DATA_OBJECT_NAME);
            DATA_OBJECT_BUS_NAME(row.DATA_OBJECT_BUS_NAME);
            PERS_FST_NAME(row.PERS_FST_NAME);
            PERS_MID_NAME(row.PERS_MID_NAME);
            PERS_LST_NAME(row.PERS_LST_NAME);
         };
      };
   };
};

log.info '';
log.info 'Test Step "' + testRunner.runContext.currentStep.name + '" done...';