Oracle Stored Procedure Doubt.

• • Reply

  17 years ago

  by lavanya

  Lavanya Arul

  , Malaysia

  Joined 17 years ago

  Hi,
  I have a stored procedure in oracle with one input parameter. For that the values will be coming as a,b,c,d.
  

  
  How do i process this in my stored procedure so as my query will be
  

  
  select * from tab where x in ('a','b','c','d')
  

  
  thnks in advance
  

  
  Chio!!!!
  Lavy

  Report abuse
• • Reply

  17 years ago

  by jaqogreen

  jaqogreen

  , United States

  Joined 17 years ago

  You can't exactly do that with a single input parameter.  If you do, Oracle would interpret that code like this:
  select * from tab where x in ('a,b,c,d')
  It won't see the individual strings as separate search criteria.
  

  
  There are workarounds, however.  Pass in 'a,b,c,d' and change the query to:
  SELECT * FROM tab WHERE REPLACE(parameter, x, '~~') != parameter
  If '~~' can actually be one of the values you're looking for, change it to something that can't be there.  Anything longer than the column will do the trick.  Also, if commas can appear in the column, use a delimiter that can't.  
  This method will allow you to use bind variables and pre-parsed SQL, but you'll be giving up indexes on column x.
  

  
  Otherwise used dynamic sql and pass in '''a'',''b'',''c'',''d''' and use code like:
  vsql VARCHAR2(2000);
  BEGIN
   vsql := 'SELECT * FROM tab WHERE x IN (' || parameter || ')';
   OPEN cursorvariable FOR
     vsql;
  END;
  Where cursor_variable is the output cursor for the stored procedure.
  This lets the precompiler use the indexes on column x, but it will have to parse the statment each time the parameter changes.
  

  
  If there are other criteria that significantly narrow down the result set, or if the table has few rows; you'll probably be better off with the former method.  If it's a large table and the only selective criteria is that one column, then go with the latter.  Try testing them first on a large enough data set to mimic production conditions.  
  

  
  Good Luck

  Report abuse