Open cursor for select statement with Input variables

Question

I have two cursors in my package. the first Cursor does not have an input parameter so I have Successfully Opened it like this:

OPEN c1 for
       SELECT foracid,
         acct_name,
         addr_1,
         addr_2,
         sol_desc,
         city_code,
         gam.FUTURE_UN_CLR_BAL_AMT
       FROM tbaadm.gam,
         tbaadm.sol
       WHERE gam.sol_id   = sol.sol_id
         AND schm_type     IN ('CAA','ODA','LAA')
         AND schm_code NOT IN ('CTSTF');
     CLOSE c1;

But My second Cursor has an input Parameter and I can't do:

 OPEN c2 (vMyName varchar2) for select .....

because of this error:

Found Varhcar2 Expecting : ) and -or...

Can I really Open This cursor this way or what should I do?

Yeah Sorry, my fault... but I don't think it's helping you with the particular problem anyways... :-/ — Armunin
– Armunin, Commented Dec 10, 2013 at 8:04

Armunin · Accepted Answer · 2013-12-10 08:58:50Z

1

You can just do the following:

create or replace procedure proc (c2 OUT SYS_REFCURSOR) AS

vMyName varchar2(100);

begin
  vMyName := 'Some Value';

  open c2 for
    select col1, col2, col3
    from tab1
    where val = vMyName;
end;

edited Dec 10, 2013 at 8:58

answered Dec 10, 2013 at 8:07

Armunin

9967 silver badges18 bronze badges

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

1 Comment

Dba Over a year ago

Parameter c2 should be an OUT variable. c2 OUT SYS_REFCURSOR.

Collectives™ on Stack Overflow

Open cursor for select statement with Input variables

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related