CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
csel
csel

Reputation: 317

How to return multiple rows with multiple columns using cursor in pl/sql procedure?

I am trying to write a procedure (in PL/SQL) for returning users that are enrolled to a course between specific dates. There will be 2 inputs (date1, date2) and 3 outputs (enrollno, error_code and enroll_date). I want it to display the info of multiple rows of users that are enrolled between date1 and date2. This is the first time that I am writing a procedure, I was able to write it in a way that it can return one row. But since there could be many users enrolled between these dates I want to display many rows. I see that I can use sys_refcursor but I couldn't do it. The examples on the internet was mostly for one output procedures so I could not adapt them for mine.

For example I looked at the example here https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets but I was confused with declare statements.

Edit: I am planning to call this procedure from Java code and assign the returned result into something and I am not allowed to add a new table into the database.

Here is my procedure:

create or replace procedure display_users(pi_date1       in date,
                                          pi_date2       in date,
                                          po_enrollno    out number,
                                          po_error_code  out varchar2,
                                          po_enroll_date out date) is
  cursor user_display is
    select u.enrollno, u.error_code, u.enroll_date,
      from user_table u
     where u.enroll_date between pi_date1 and pi_date2;

begin
  open user_display;
  loop
    fetch user_display
      into po_enrollno, po_error_code, po_enroll_date;
    EXIT WHEN user_display%notfound;

  end loop;
  close user_display;

end;

Upvotes: 1

Views: 8966

Answers (2)

Kaushik Nayak
Kaushik Nayak

Reputation: 31656

You may use a single REFCURSOR out parameter instead of multiple out parameters.

CREATE OR REPLACE PROCEDURE display_users (
    pi_date1          IN DATE,
    pi_date2          IN DATE,
    po_userdisp_cur   OUT SYS_REFCURSOR 
)
    IS
BEGIN
    OPEN po_userdisp_cur FOR SELECT u.enrollno,
                                    u.error_code,
                                    u.enroll_date
                             FROM user_table u
                             WHERE u.enroll_date BETWEEN pi_date1 AND pi_date2;
END;

This can be easily used in java to fetch the records as shown in this link:

Using oracle ref cursors in java

Upvotes: 1

Zynon Putney II
Zynon Putney II

Reputation: 695

If you're just trying to output it to the screen, you can use dbms_output.put_line in each iteration of the loop, and make sure you've "set serveroutput on" in your environment when running the code. You can also concatenate these into one line of output per row if you'd like.

dbms_output.put_line('Enrolled No: ' || to_char(po_enrollno));
dbms_output.put_line('Error Code: ' || po_error_code);
dbms_output.put_line('Enrolled Date: ' || to_char(po_enroll_date));

If you want to gather the data for use later, then you'll want to insert the records into a new table you create. We would call this a temp table, and you would want to make sure you delete all records out of it before starting.

insert into my_temp_table values( po_enrollno, po_error_code, po_enroll_date);

Upvotes: 0

Related Questions