system
April 19, 2018, 7:21pm
1
Originally posted by jkrier on April 19, 2018
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
system
April 20, 2018, 9:28pm
2
Originally posted by jkrier on April 20, 2018
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.
system
January 15, 2019, 9:26am
3
Originally posted by Nastya_Khovrina on January 15, 2019
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.
system
January 16, 2019, 6:15pm
4
Originally posted by TNeuschwanger on January 16, 2019
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/[email protected] :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...';