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 thi...
  • 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

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...';