How to declare / assign a variable to a cursor type in PL/SQL procedure

Question

I have two cursors declared in a stored procedure (inside a package).

procedure RECONCILE_CC_TRX (p_to_date in date,
                            p_nz_flag in varchar2,
                            p_Reconcile_Header_ID out NUMBER
                            ) is

CURSOR LOADED_TRXS_AU IS 
  SELECT
      CC_REC_LOAD_TRX_ID,     
      CC_REC_LOAD_HEADER_ID,
....

CURSOR LOADED_TRXS_NZ IS
  SELECT
      CC_REC_LOAD_TRX_ID,     
      CC_REC_LOAD_HEADER_ID,

The only difference between the two cursors is the where clause.

What I want to do, is open one of those cursors based on the p_nz_flag passed in above. ie:

  IF NVL(p_nz_flag, 'F') = 'F' THEN
    v_load_trx_cursor := LOADED_TRXS_AU;
  ELSE
    v_load_trx_cursor := LOADED_TRXS_NZ;
  END IF;

  FOR bitem IN v_load_trx_cursor LOOP
  ...

My initial thinking was to declare a variable and assign it the appropriate cursor, however, I can't get the procedure to compile with this. eg, I have tried:

v_load_trx_cursor sys_refcursor;

but I get a compilation error when assigning v_load_trx_cursor of "PLS-00382: Expression is of wrong type". If I change my declaration to:

  v_load_trx_cursor cursor;

I get compilation error at the declaration point stating "PLS-00201: Identifier 'Cursor' must be declared.

Is it possible to do what I want to do? At the end of the day, I just want to iterate the appropriate cursor based on the p_nz_flag parameter passed in.

Thanks

If you can do what @vishad has suggested, i.e. make p_nz_flag a parameter to a single cursor, I'd do that. Otherwise you could put the queries into a string and use the OPEN cursor FOR 'query' syntax. — Jeffrey Kemp
– Jeffrey Kemp, Commented Apr 8, 2014 at 5:37

vishad · Accepted Answer · 2014-04-08 04:28:47Z

1

It looks like you want to use only one cursor in your code, based on the value of p_nz_flag In this case, believe it will be better to make the where clause dynamically in your code and then use refcursor to return the data of the query.

Something like given in Example 7-4 on this link Dynamic Query with Refcursor

Hope it Helps

Vishad

answered Apr 8, 2014 at 4:28

vishad

1,1647 silver badges11 bronze badges

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

Comments

Avrajit · Accepted Answer · 2014-04-08 12:37:48Z

1

Hi if your whole work for doing this procedure is just to populate the appropriate cursor i think this code may help you.

CREATE OR REPLACE PROCEDURE av_nst_cursor(
    flag_in IN VARCHAR2,
    av_cur OUT sys_refcursor)
AS
BEGIN
  IF flag_in = 'Y' THEN
    OPEN av_cur FOR SELECT Query;
  ELSE
    OPEN av_cur FOR SELECT query;
  END IF;
END;

answered Apr 8, 2014 at 12:37

Avrajit

2301 gold badge2 silver badges8 bronze badges

Collectives™ on Stack Overflow

How to declare / assign a variable to a cursor type in PL/SQL procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related