Hi I am trying to use Plsql Associative arrays in oracle procedure as in parameter.
how to pass values to array item as in parameter in plsql procdure.
in envelope i want to pass param_list values
for
emp_no
ename
hiredate,
salary)
using soap ui.
any ideas please advise.
thanks.
my eg code.
create or replace PACKAGE test_interface
AS
type param_list is TABLE OF VARCHAR2(4000) INDEX BY pls_integer;
procedure show_report(Template_Name IN VARCHAR2,absolute_path in VARCHAR2,Report_Path IN VARCHAR2,v_param_list param_list);
end test_interface;
create or replace package body test_interface
procedure show_report(Template_Name IN VARCHAR2,absolute_path in VARCHAR2,Report_Path IN VARCHAR2,v_param_list param_list)
IS
l_envelope CLOB;
l_response_msg CLOB;
l_blob BLOB;
l_clob CLOB;
l_xml XMLTYPE;
BEGIN
l_envelope := '<?xml version="1.0" encoding="UTF-8"?><soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:v2="http://xmlns.oracle.com/oxp/service/v2">
<soapenv:Header/>
<soapenv:Body>
<v2:runReport>
<v2:reportRequest>
<v2:attributeFormat>excel</v2:attributeFormat>
<v2:attributeLocale>en-US</v2:attributeLocale>
<v2:attributeTemplate>Template_Name</v2:attributeTemplate>
<!-- <v2:attributeTemplate>WPACorpDataExtractTemplate.xls</v2:attributeTemplate>-->
<v2:parameterNameValues>
<v2:listOfParamNameValues>
<!--Zero or more repetitions:-->
<v2:item>
<v2:name>P_LANGUAGE</v2:name>
<v2:values>
<v2:item>1</v2:item>
</v2:values>
</v2:item>
<v2:item>
<v2:name>P_EMP_ID</v2:name>
<v2:values>
<v2:item>101</v2:item>
</v2:values>
</v2:item>
<v2:item>
<v2:name>P_ENAME</v2:name>
<v2:values>
<v2:item>'TESTEMP'</v2:item>
</v2:values>
</v2:item>
<v2:item>
<v2:name>P_HIREDATE</v2:name>
<v2:values>
<v2:item>2014-2015</v2:item>
</v2:values>
</v2:item>
<v2:item>
<v2:name>P_SAL</v2:name>
<v2:values>
<v2:item>1500</v2:item>
</v2:values>
</v2:item>
</v2:listOfParamNameValues>
</v2:parameterNameValues>
<v2:reportAbsolutePath>absolute_path</v2:reportAbsolutePath>
<v2:sizeOfDataChunkDownload>-1</v2:sizeOfDataChunkDownload>
</v2:reportRequest>
<v2:userID>SCOTT</v2:userID>
<v2:password>TIGER</v2:password>
</v2:runReport>
</soapenv:Body>
</soapenv:Envelope>' ;
l_xml := apex_web_service.make_request(p_url =>'https://bidev.gnb.ca/xmlpserver/services/v2/ReportService',--'http://swa78webld01:9704/xmlpserver/services/v2/ReportService', --
p_action => '""' ,
p_envelope => l_envelope);
l_response_msg := APEX_WEB_SERVICE.PARSE_XML_CLOB (
p_xml => l_xml,
p_xpath => '//runReportResponse/runReportReturn/reportBytes/text()',
p_ns=>'xmlns="http://xmlns.oracle.com/oxp/service/v2"' );
blobdownload (Report_Path, l_blob);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE ||' ' ||SQLERRM);
END SHOW_REPORT;