Oracle procedure input is comma delimited, not returning any values

Question

the procedure Im working has an input variable that is comma delimited. As of right now when I go to run a test script, I dont get any values back. Here is what I have so far.

procedure get_patient(
p_statusmnemonic_in    in membermedicalreconcilationhdr.reconciliationstatusmnemonic%type,
p_return_cur_out       out sys_refcursor,
p_err_code_out         out number,
p_err_mesg_out         out varchar2)
  is
  begin
open p_return_cur_out for
  select h.primarymemberplanid,
         h.assigneduserid,
         h.accountorgid,
         h.reconciliationstatusmnemonic,
         h.estimatedenddt,
         h.actualenddt,
         h.inserteddt,
         h.insertedby,
         h.updateddt,
         h.updatedby
  from membermedicalreconcilationhdr h
  where h.reconciliationstatusmnemonic in (p_statusmnemonic_in);
p_err_code_out := 0;
  exception
when others then
  p_err_code_out := -1;
  p_err_mesg_out := 'error in get_patient=> ' || sqlerrm;
  end get_patient;

Here is the test script:

set serveroutput on
declare
  type tempcursor is ref cursor;
  v_cur_result tempcursor;
  errcode number;
  errmesg varchar2(1000);
  p_primarymemberplanid_in    membermedicalreconcilationhdr.primarymemberplanid%type;
  p_assigneduserid_in         membermedicalreconcilationhdr.assigneduserid%type;
  p_accountorgid_in           membermedicalreconcilationhdr.accountorgid%type;
  p_reconstatusmnemonic_in        membermedicalreconcilationhdr.reconciliationstatusmnemonic%type;
  p_estimatedenddt_in         membermedicalreconcilationhdr.estimatedenddt%type;
  p_actualenddt_in            membermedicalreconcilationhdr.actualenddt%type;
  p_inserteddate_in           membermedicalreconcilationhdr.inserteddt%type;
  p_insertedby_in             membermedicalreconcilationhdr.insertedby%type;
  p_updateddate_in           membermedicalreconcilationhdr.updateddt%type;
  p_updatedby_in           membermedicalreconcilationhdr.updatedby%type;

begin
  get_patient      
('COMPLETE,SUSPENDED_PRIOR_TO_COMPARE',v_cur_result, errcode, errmesg);
--('COMPLETE',v_cur_result, errcode, errmesg);


   loop
fetch v_cur_result into p_primarymemberplanid_in,p_assigneduserid_in,p_accountorgid_in,p_reconstatusmnemonic_in,
                        p_estimatedenddt_in,p_actualenddt_in,p_inserteddate_in,p_insertedby_in,
                        p_updateddate_in,p_updatedby_in;

  dbms_output.put_line(' planid '||p_primarymemberplanid_in||' userid '||p_assigneduserid_in);
  exit when v_cur_result%notfound;
  end loop;

  dbms_output.put_line(' error code '||errcode||' message '||errmesg);
end;

As of right now I get values back when I just have one input value, but when I try to do two I dont get anything. Ive done research and it looks like my select statement is correct so Im at a loss as to what Im doing wrong. Any help is appreciated, thanks.

Answer 1

If you can change the definition of the procedure, you are better served passing in a proper collection.

CREATE TYPE status_tbl IS TABLE OF VARCHAR2(100);

procedure get_patient(
  p_statusmnemonic_in    in  status_tbl,
  p_return_cur_out       out sys_refcursor,
  p_err_code_out         out number,
  p_err_mesg_out         out varchar2)
is
begin
  open p_return_cur_out for
    select h.primarymemberplanid,
           h.assigneduserid,
           h.accountorgid,
           h.reconciliationstatusmnemonic,
           h.estimatedenddt,
           h.actualenddt,
           h.inserteddt,
           h.insertedby,
           h.updateddt,
           h.updatedby
      from membermedicalreconcilationhdr h
     where h.reconciliationstatusmnemonic in (SELECT * 
                                                FROM TABLE(p_statusmnemonic_in));
  ...

Otherwise, you would either have to resort to using dynamic SQL (which would have security and performance implications) or you would need to write code to parse the comma-separated string into a collection and then use the TABLE operator to use that collection in the query.

Assuming you modify the signature of the procedure, the call will also have to change so that you are passing in a collection.

get_patient      
(status_tbl('COMPLETE','SUSPENDED_PRIOR_TO_COMPARE'),
 v_cur_result, 
 errcode, 
 errmesg);

And just to point it out, writing procedures that have error code and error message OUT parameters rather than throwing exceptions is generally highly frowned upon. It makes far more sense to eliminate those parameters and to just throw exceptions when you encounter an error. Otherwise, you are relying on every caller to every procedure to correctly check the returned status code and message (which your sample code does not do). And you are losing a ton of valuable information about things like exactly what line an error occurred on, what the error stack was, etc.

Since you don't post your table definitions or your sample data, it is impossible for us to test this code. Here is a quick demonstration, though, of how it would work

SQL> create table patient (
  2    patient_id number primary key,
  3    status     varchar2(10),
  4    name       varchar2(100)
  5  );

Table created.

SQL> insert into patient values( 1, 'COMPLETE', 'Justin' );

1 row created.

SQL> insert into patient values( 2, 'SUSPENDED', 'Bob' );

1 row created.

SQL> insert into patient values( 3, 'NEW', 'Kerry' );

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE TYPE status_tbl IS TABLE OF VARCHAR2(100);
  2  /

Type created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure get_patients( p_statuses in status_tbl,
  2                                            p_cursor out sys_refcursor )
  3  as
  4  begin
  5    open p_cursor
  6     for select *
  7           from patient
  8          where status in (select *
  9                             from table( p_statuses ));
 10* end;
SQL> /

Procedure created.

SQL> variable rc refcursor;
SQL> exec get_patients( status_tbl('COMPLETE', 'SUSPENDED'), :rc );

PL/SQL procedure successfully completed.

SQL> print rc;

PATIENT_ID STATUS
---------- ----------
NAME
--------------------------------------------------------------------------------
         1 COMPLETE
Justin

         2 SUSPENDED
Bob