Passing Multiple Values To PL/SQL Input

Question

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;
/

As it stands, it looks like if you call this function multiple times in series, it will drop and recreate temp_rpa. Surely you need to do something else between creations of this table? (Or perhaps there is no reason to create the table at all? Your function is declared as returning NUMBER but there is no RETURN statement.) — Dave Costa
– Dave Costa, Commented Jul 1, 2020 at 12:46
Usually it is a bad design to drop and create a table dynamically in a PL/SQL procedure. Better use a GLOBAL TEMPORARY TABLE. — Wernfried Domscheit
– Wernfried Domscheit, Commented Jul 1, 2020 at 12:57
@DaveCosta I am working with RPA, I can extract results inbetween each run and combine on my side. Apologies removed there (simplified the real package) — Eoin2211
– Eoin2211, Commented Jul 1, 2020 at 12:57
If these parameters are used only in IN clauses then you can use quoted strings like ` q'['111','222','333','444']' `. Short demo. — Ponder Stibbons
– Ponder Stibbons, Commented Jul 1, 2020 at 13:01
@PonderStibbons do you want to post this as an answer? Looking good and you gave a better explanation from the start — Eoin2211
– Eoin2211, Commented Jul 2, 2020 at 9:18

Popeye · Accepted Answer · 2020-07-01 12:59:35Z

1

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;

answered Jul 1, 2020 at 12:59

Popeye

36k4 gold badges12 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eoin2211 Over a year ago

Can you modify 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;

Giliam · Accepted Answer · 2020-07-01 12:59:02Z

0

What you could do is using an associative array as input type. Instead of varchar2, use dbms_sql.varchar2a as date type for the 2nd and 3rd arguments.

But if the arguments are related to each other, let's say

p_hospital_id '111' belongs to procedure code '1234'
p_hospital_id '222' belongs to procedure code '2345'
etc.

I think you would want to create a custom record type, create a table type of the record type and use that as an parameter.

Your arguments become p_hospital_ids in dbms_sql.varchar2a in both the package specification and the package body.

In you code, you would have to loop over it and instead of dropping the table and recreate it each time, you drop it once at the start and add data within the loop;

truncate table;  --alternative drop and create

for i in 1 .. p_hospital_ids.count loop
  insert into temp_rpa
  select <columns>
  from   claim_item ci
  ......
  and    ci.hospital_id = p_hospital_ids[i]
end loop

edited Jul 1, 2020 at 12:59

answered Jul 1, 2020 at 12:38

Giliam

6403 silver badges16 bronze badges

1 Comment

Eoin2211 Over a year ago

I tried changing the data type in both the inputs and in the body but getting [1]: ORA-24344: success with compilation error

VIX · Accepted Answer · 2020-07-01 13:00:39Z

You may want to refer to the below example which is taken from Oracle Website. Hope it helps.

CREATE OR REPLACE TYPE nt_type IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE print_nt (nt nt_type) AUTHID DEFINER IS
  i  NUMBER;
BEGIN
  i := nt.FIRST;
 
  IF i IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('nt is empty');
  ELSE
    WHILE i IS NOT NULL LOOP
      DBMS_OUTPUT.PUT('nt.(' || i || ') = ');
      DBMS_OUTPUT.PUT_LINE(NVL(TO_CHAR(nt(i)), 'NULL'));
      i := nt.NEXT(i);
    END LOOP;
  END IF;
 
  DBMS_OUTPUT.PUT_LINE('---');
END print_nt;
/
DECLARE
  nt nt_type := nt_type();  -- nested table variable initialized to empty
BEGIN
  print_nt(nt);
  nt := nt_type(90, 9, 29, 58);
  print_nt(nt);
END;
/

Wernfried Domscheit · Accepted Answer · 2020-07-01 13:11:30Z

You don't need dynamic SQL at all.

CREATE OR REPLACE TYPE NUMBER_TABLE_TYPE AS TABLE OF NUMBER;
CREATE OR REPLACE TYPE VARCHAR_TABLE_TYPE AS TABLE OF VARCHAR2(1000);
    
function mis_run_claim_assessment_script (
   p_input_dt in varchar2,
   p_hospital_id in NUMBER_TABLE_TYPE, -- why on earth do you put numbers as strings?
   p_procedure_code in VARCHAR_TABLE_TYPE 
)  RETURN ??? AS

   INSERT INTO temp_rpa (...)
   SELECT ...
   FROM ...
   WHERE ch.input_dt like p_input_dt
      AND ci.hospital_id MEMBER OF p_hospital_id 
      AND procedure_code MEMBER OF p_procedure_code ;

   RETURN ???

END;

 ln_ret := mis_run_claim_assessment_script(
    '%2020%', 
    NUMBER_TABLE_TYPE(111, 222, 333, 444), 
    VARCHAR_TABLE_TYPE('not','clear','in','your','question')
 );

Collectives™ on Stack Overflow

Passing Multiple Values To PL/SQL Input

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related