This forum is currently locked. You can't register or post questions at this time. (read more)

Refer sys_refcursor value

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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

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

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

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

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


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 0 guests