CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Safirah
Safirah

Reputation: 375

How to output the a record that is the result of a procedure?

So, let's say I have a table like this one:

CREATE TABLE MEETINGS(
    MEETING_DAY DATE,
    MEETING_ROOM NVARCHAR2(10),
    MEETING_DURATION NUMBER(2)
);

And a procedure like this one, that receives a date and a meeting room and that returns a record with the duration of the meetings that'll happen on the room on the given day:

CREATE OR REPLACE PROCEDURE meetings_for_today(
    v_room IN NVARCHAR2, v_day IN DATE, result_meetings OUT SYS_REFCURSOR)
    IS BEGIN
        OPEN result_meetings FOR
            SELECT  MEETING_DAY, MEETING_DURATION
            FROM MEETINGS
            WHERE MEETING_DAY = v_day AND MEETING_ROOM = v_room;
END;
/

But I'd like to check if the procedure is actually working, I've search a lot on this topic, but I'm finding really difficult to find a solution.

So I've tried doing:

DECLARE
  meeting_res MEETINGS%ROWTYPE; 
  v_refcur sys_refcursor;

BEGIN
    meetings_for_today('1A', to_date('12/12/2016', 'dd/mm/yyyy'), v_refcur);
    LOOP
    fetch v_refcur into meeting_res;
    exit when v_refcur%notfound;
    dbms_output.put_line(meeting_res.MEETING_DAY || ' ' || meeting_res.MEETING_DAY);
end loop;
End;
/

Which gives me the following error:

ERROR at line 1: ORA-06504: PL/SQL: Return types of Result Set variables or query do not match ORA-06512: at line 8

What am I doing wrong?

PS: Please don't give me a code that works; explain to me what's wrong on my reasoning and how should I better use Oracle's resources :)

Upvotes: 2

Views: 1586

Answers (1)

krokodilko
krokodilko

Reputation: 36087

PS: Please don't give me a code that works, explain me what's wrong on my reasoning and how should I better use oracle's resources :)

This variable:

meeting_res MEETINGS%ROWTYPE;

is of type: MEETINGS%ROWTYPE;
It means, that this is a row (record) that consists of all 3 columns of MEETINGS table:

CREATE TABLE MEETINGS(
    MEETING_DAY DATE,
    MEETING_ROOM NVARCHAR2(10),
    MEETING_DURATION NUMBER(2)
);

But the cursor returns only 2 columns, look at SELECT clause here, there are only 2 columns there: MEETING_DAY + MEETING_DURATION

 OPEN result_meetings FOR
            SELECT  MEETING_DAY, MEETING_DURATION
            FROM MEETINGS .....

So, you are trying to fetch a record from this cursor which contains 2 columns, and put it into a variable that consist of 3 columns.
They certainly don't match (3 cols vs 2 cols), and you get ORA-06504 error.

You can declare your own record type that consist of 2 columns

  type my_type is record(
     MEETING_DAY date , MEETING_DURATION number(2)
  );

and use it in your code in this way:

DECLARE
  type my_type is record(
     MEETING_DAY date , MEETING_DURATION number(2)
  );
  meeting_res my_type; 
  v_refcur sys_refcursor;

BEGIN
    meetings_for_today('1A', to_date('12/12/2016', 'dd/mm/yyyy'), v_refcur);
    LOOP
    fetch v_refcur into meeting_res;
    exit when v_refcur%notfound;
    dbms_output.put_line(meeting_res.MEETING_DAY || ' ' || meeting_res.MEETING_DAY);
end loop;
End;
/

NOTE - the number of columns as well as types of corresponding columns must match, or at least their dattypes must be "compatibile" - they are compatibile, if Oracle is able to convert one value into another (for example NUMBER can be cast to VARCHAR2), otherwise if Oracle is not able to convert some value to another datatype, you will get ORA-06504 (for example NUMBER to DATE).

Upvotes: 2

Related Questions