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

Question

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;

Kaushik Nayak · Accepted Answer · 2018-08-15 03:36:24Z

1

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

edited Aug 15, 2018 at 3:36

answered Aug 14, 2018 at 18:47

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

Comments

Zynon Putney II · Accepted Answer · 2018-08-14 14:03:17Z

0

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

answered Aug 14, 2018 at 14:03

Zynon Putney II

6954 silver badges12 bronze badges

2 Comments

csel Over a year ago

Sorry I forgot to mention that I am actually planning to call this procedure from a Java code and assign the results into some kind of container. I was checking the results via test window in PL/SQL Developer. And I am not allowed to insert table into database (I should have mentioned these sorry ). Is there any other way to do this? Thank you

Zynon Putney II Over a year ago

Create an array in Java and hold the results in memory? Although you may run into performance problems depending on how many records you intend on retrieving

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related