how to implement refcursor when there is "execute immediate" in oracle?

Question

I have used sys_refcursor before to return the data of select statements.But,I am using execute immediate here,so I am not able to use the sys_refcursor in execute immediate statement.

Before I used to do

R_C OUT SYS_REFCURSOR //refcursor
    OPEN R_C FOR  //it used to handle the return of all select statement
    SELECT * FROM table_name;

But ,I tried execute immediate today and I am not able to print the data of select statement. So,following I tried is:

CREATE OR REPLACE PROCEDURE OT.check_data(DATA1 number,R_C OUT SYS_REFCURSOR)
    IS 
    vquery long;
    BEGIN
    vquery :='select * from ot.employee ';
    if data1 = 10 then
    vquery := vquery||' where deptno in (10)';
    else
     vquery := vquery||' where deptno in (20,30)';
    end if;
    execute immediate vquery;
    END;
    /

   exec OT.check_data(10);

The procedure is working fine,but I am unable to see the data of select statement.How can I display the data of execute immediate in my console?I am using toad.

Bob Jarvis - Слава Україні · Accepted Answer · 2019-11-24 04:53:23Z

3

You can return a SYS_REFCURSOR for a dynamically built SELECT statement by doing the following:

CREATE OR REPLACE PROCEDURE OT.check_data(DATA1    number,
                                          R_C OUT  SYS_REFCURSOR)
IS 
  vQuery VARCHAR2(32767);
  rc     SYS_REFCURSOR;
BEGIN
  vQuery :='select * from ot.employee ';

  if data1 = 10 then
    vQuery := vQuery ||' where deptno in (10)';
  else
    vQuery := vQuery ||' where deptno in (20,30)';
  end if;

  OPEN rc FOR vQuery;
  R_C := rc;
END;

I strongly suggest that you should not use the LONG datatype. This has been deprecated for years, and One Of These Days (tm) Oracle is finally going to drop support for it. Either use a VARCHAR2, which is more than sufficient in this case, or use a CLOB.

edited Nov 24, 2019 at 4:53

answered Nov 24, 2019 at 4:30

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

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

4 Comments

Random guy Over a year ago

[Warning] ORA-24344: success with compilation error 6/1 PLS-00103: Encountered the symbol "BEGIN" when expecting one of the following: := . ( @ % ; not null range default character The symbol ";" was substituted for "BEGIN" to continue. (1: 0): Warning: compiled but with compilation errors

Bob Jarvis - Слава Україні Over a year ago

Missed a semicolon in the SYS_REFCURSOR line.

Random guy Over a year ago

I tried to execute the procedure using VARIABLE R_C REFCURSOR; exec OT.check_data(10,R_C); but it is saying me PLS-00201: identifier 'R_C' must be declared. what is the mistake i Did?

Bob Jarvis - Слава Україні Over a year ago

VARIABLE references must be preceded by a colon. So :R_C instead of R_C.

Popeye · Accepted Answer · 2019-11-24 05:36:17Z

0

You can create a simple procrdure for it.

CREATE OR REPLACE PROCEDURE OT.check_data(DATA1    number,
                                          R_C OUT  SYS_REFCURSOR)
IS 
BEGIN
  Open R_C for 'select * from ot.employee where deptno in ('
               || case when data1 = 10 then '10' 
                       Else '10,20' end
               || ')';

END;
/

You can call the procedure like this:

Declare
Rc sys_refcursor;
Begin
Ot.check_data(10, rc);
-- do something with returned cursor
End;
/

You can print or use cursor data in pl/sql block: see this qna

Cheers!!

answered Nov 24, 2019 at 5:36

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

how to implement refcursor when there is "execute immediate" in oracle?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related