Cursor Inside if is it Possible?

[leonardo ginting]

Hi Guys,,

is it possible for me to Declare cursor inside if or if statement inside cursor?

if possible how could i make it?

because i just made cursor like this

 CURSOR cur IS
        if param_number = 0 then
          
            SELECT myField
            FROM MYTABLE
            WHERE field1 = param1
            AND field2 BETWEEN param2 AND param3;
           
        Else 
                      select somefield where field1 =param4;          
        
        End if;

But I got This error

Error(33,9): PLS-00103: Encountered the symbol "IF" when expecting one of the following:     ( select <a SQL statement> 
Error(129,18): PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:     begin case declare end exception exit for goto if loop mod    null pragma raise return select update while with    <an identifier> <a double-quoted delimited-identifier>    <a bind variable> << close current delete fetch lock insert    open rollback savepoint set sql execute commit forall merge    pipe 

any Help would be appreciate

thanks in advance

[Fatih Keles]

There are several way that comes to my mind
1. Why not declare 2 different cursors than use if statements to decide which cursor will be opened?
2. If you need some kind of fancy cursor declaration, you may union both SQLs (they need to select same types) and then make only one of them fetches data. Something like this
CURSOR csr_smth (p_csr_selector IN VARCHAR2) /* use p_csr_selector to decide which one fetches data */ IS SELECT x.some_field FROM some_table x WHERE 1 = 1 AND x.field1 = 'some condition' AND p_csr_selector = 'Use First Select' UNION SELECT x.some_field FROM some_table x WHERE 1 = 1 AND x.field1 = 'some condition' AND p_csr_selector = 'Use Second Select';
3. You may use dynamic sql with open cursor for ... using ... OPEN-FOR-USING

You may extend the list, but simple is safer so if I were you I would go for 1.

[leonardo ginting]

hi fatih thanks for replying

i already use point 1 as your suggestion

and thanks for your help