CODEWITHSUNDEEP
stored-proceduresoracle11gcursorpackage
Stanley Mungai
Stanley Mungai

Reputation: 4150

Open cursor for select statement with Input variables

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?

Upvotes: 0

Views: 1037

Answers (1)

Armunin
Armunin

Reputation: 996

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;

Upvotes: 1

Related Questions