Refer sys_refcursor value

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Refer sys_refcursor value

Postby Jakob » Mon Jun 17, 2013 1:27 pm

Hi Tim,

I have the following function

Code: Select all
CREATE OR REPLACE FUNCTION my_func (
        v_dt events.raised_date%TYPE
        )
        RETURN NUMBER
    IS   
            p_events    SYS_REFCURSOR;
            p_value events%ROWTYPE;
            p_status number;

        OPEN p_events FOR
                SELECT  event_id, max(event_code) - event_code as new_val
                  FROM  events
                 WHERE  raised_date = v_dt;

loop
    fetch p_events into p_value;
    exit when p_events%NOTFOUND;
    if (p_value.new_val = 1) then
      p_status := 1;
      exit;
    end if;
  end loop;

RETURN p_status;
END;


How can I refer to a computed value of sys_refcursor? E.g. I have
Code: Select all
if (p_value.new_val = 1) then


When I compile I am getting error new_val is not declared as I am not sure how can I refer to new_val?

Any help is highly appreciable.

Thanks
Jakob
Member
 
Posts: 4
Joined: Mon Jun 17, 2013 1:19 pm

Re: Refer sys_refcursor value

Postby Tim... » Mon Jun 17, 2013 2:32 pm

Hi.

You can only FETCH INTO a value of the correct type. You must have wither separate valiables of the correct type, or a rowtype that exactly matches the select list of the query. If the EVENTS table has only two columns (EVENT_ID and EVENT_CODE), then fetching into a variable defined using that ROWTYPE is fine. If it has more columns, then what you are doing is illegal.

Why does it not fail compilation at that point? You are using SYS_REFCURSOR, a loosely typed REF CURSOR, so there is no way Oracle can validate this at compile time. As a result, it seems to compile OK, even though it may fail at runtime. The first time you get a failure is when you try and reference the "new_val" column, which does not exist in the table the %ROWTYPE is based on.

Actually, using a REF CURSOR is not appropriate here. It would be better to use a regular cursor FOR LOOP. Something like this.

Code: Select all
CREATE OR REPLACE FUNCTION my_func (
        v_dt events.raised_date%TYPE
        )
        RETURN NUMBER
    IS   
  p_status number;
begin
  for cur_rec IN (SELECT event_id, max(event_code) - event_code as new_val
                  FROM   events
                  WHERE  raised_date = v_dt;)
  loop
    if (cur_rec.new_val = 1) then
      p_status := 1;
      exit;
    end if;
  end loop;

  RETURN p_status;
END;


Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Refer sys_refcursor value

Postby Jakob » Tue Jun 18, 2013 6:37 am

It does failed at compilation time when I was trying to refer
Code: Select all
p_value.new_val


Any way thanks for the solution as now it is rather simple without using sys_refcursor. Semicolon is not required for the cursor sql statement.

Regards
Jakob
Member
 
Posts: 4
Joined: Mon Jun 17, 2013 1:19 pm

Re: Refer sys_refcursor value

Postby Tim... » Tue Jun 18, 2013 7:20 am

Hi.

LOL. Dodgy copy&paste. :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17935
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 6 guests

cron