Passing Multiple Values To PL/SQL Input

I have an Oracle package which contains a function. This function has 3 inputs. I need to pass multiple values to each input. I could automate a process which runs it multiple times with each combination of variables but I would like to only make one call to the database.

Simplified Code

declare
ln_ret number;

begin
ln_ret := dbo.pkg_rpa.mis_run_script (
    '%2020%', 
    '111','222','333','444', 
    '1234','2345','6192','1204'
    );
dbms_output.put_line('ln_ret=' || t.t (ln_ret));

end;




CREATE OR REPLACE 
package     dbo.pkg_rpa IS

function mis_run_script (
p_input_dt in varchar2,
p_hospital_id in varchar2,
p_procedure_code in varchar2) RETURN number;   

end PKG_RPA;
/

CREATE OR REPLACE 
PACKAGE BODY     dbo.pkg_rpa IS

function mis_run_claim_assessment_script (
p_input_dt in varchar2,
p_hospital_id in varchar2,
p_procedure_code in varchar2
)  



Begin

for i in (select table_name from user_tables where lower(table_name) = 'temp_rpa') loop
      execute immediate 'drop table temp_rpa';
  end loop;       
  execute immediate      ' create table temp_rpa as   select distinct ci.claim_id, count(ci.receipt_id) as count_receipts, 
   sum(ci.billed_amount) as total_billed_amount, count(*) as claim_items
   from claim_item ci left join claim_header ch on ch.claim_id = ci.claim_id 
   left join cd_hos ho on ho.hospital_id = ci.hospital_id 
   left join claim_type_header cl on cl.claim_id = ci.claim_id 
   where cl.claim_status is null and ch.deleted_flag is null 
   and ch.input_dt like p_input_dt
   and ci.hospital_id in (p_hospital_id) 
   and (ci.claim_id, NVL(ci.claim_item_id,0)) in (select claim_id, NVL(claim_item_id,0) from cd_roc_claim_item 
   where procedure_code in (p_procedure_code)) 
   and (ci.claim_id, NVL(ci.claim_item_id,0)) not in (select claim_id, NVL(claim_item_id,0) from cd_roc_claim_item 
   where procedure_code not in (p_procedure_code)) 
   group by ci.claim_id 
   having sum(case when ci.service_type_id is null then 1 end) = 1)';
   
End;

end mis_run_script;

end PKG_RPA;
/

Answer

Pass it with quoted string (Q'<delimeter><your_actual_string><delimeter>') as follows:

begin
ln_ret := dbo.pkg_rpa.mis_run_script (
    '%2020%', 
    Q'#'111','222','333','444'#', 
    Q'#'1234','2345','6192','1204'#'
    );
dbms_output.put_line('ln_ret=' || t.t (ln_ret));

end;

Advertisement

Answer