Execute Multiple SQL Queries in PL/SQL Procedure based on parameter

[Skanda Raman]

I have requirement to execute different sql queries using pl/sql procedure.

 Create or replace procedure testproc(PARAM NUMBER,userdata OUT REFCURSOR_PKG.user_ref_cursor)     
IS     
begin    
OPEN userdata  FOR   
IF PARAM=1 THEN  
select user_id from usertable;
ELSIF PARAM=2 THEN
SELECT COUNT(*) FROM usertable;
END IF;
END;   

In my above proc if i pass PARAM as 1 i need to execute the first query and if PARAM is 2 the second query.

I am using OUT parameter as ref cursor, so that i want to display this results in JSP

However, on compiling this procedure, i am getting below error.

  LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1      PLS-00103: Encountered the symbol "IF" when expecting one of the
         following:
         ( - + mod not null select <an identifier>
         <a double-quoted delimited-identifier rel="nofollow"> <a bind variable rel="nofollow"> avg
         count current exists max min prior sql stddev sum variance
         execute forall time timestamp interval date
         <a string literal with character set specification rel="nofollow">
         <a number rel="nofollow"> <a single-quoted SQL string rel="nofollow">

11/0     PLS-00103: Encountered the symbol "end-of-file" when expecting
         one of the following:
         begin function package pragma procedure form  

Please let me know where i am doing wrong. I am confused

[Skanda Raman]

Any Help On this..please

[Agador Paloi]

try restructuring your code something like:

  
 Create or replace procedure testproc( param IN  NUMBER, userdata OUT REFCURSOR_PKG.user_ref_cursor)        
IS        
begin      

IF PARAM = 1 THEN  
   OPEN userdata  FOR         
    select user_id from usertable;   
ELSIF PARAM=  2 THEN   
   OPEN userdata  FOR      
    select COUNT(*) FROM usertable;   
END IF;   
END;