CODEWITHSUNDEEP
oracle-databasestored-proceduresplsql
Jason
Jason

Reputation: 2572

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

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

Upvotes: 4

Views: 3964

Answers (2)

Avrajit
Avrajit

Reputation: 230

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;

Upvotes: 1

vishad
vishad

Reputation: 1164

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

Upvotes: 1

Related Questions