oracle regexp_like with pattern from select

Question

I try to select rows from a table using a regex. The pattern is the result of a select.
I have the following compilation error on the select that use regexp_like:

PLS-00428: an INTO clause is expected in this SELECT statement

declare 
  pattern varchar2;

begin

  select columns
  into pattern
  from table a
  where conditions;

  select * 
  from table2 
  where regexp_like(column, pattern);

end;

I don't understand why I should use an into clause...

SELECT in plsql should have a INTO clause. You have already used it in your 1st statement. — Noel, Jul 05 '13 at 09:48
Your statement "select * from table2 where regexp_like(column, pattern); " also should have a INTO clause like the first one. Why do you need this statement anyway? are u using the o/p of this second select statement anywhere? — A Nice Guy, Jul 05 '13 at 10:34
I would use the second select as this : update my_table set col = value where col2 in ( select * from table2 where regexp_like(column, pattern) ) — plugandplay, Jul 05 '13 at 11:27
If you are going to use plain sql statement without INTO clause then simply you can use execute('sql statement') or execute immediate('sql statement'); — Mariappan Subramanian, Jul 05 '13 at 11:28
You have only one column in table2? 'where col2 in (select col2..)' it should be like this — Mariappan Subramanian, Jul 05 '13 at 11:30
Yes, it is 'where col2 in (select col2..)'. If I use execute immediate, I get '"pattern": invalid identifier'. — plugandplay, Jul 05 '13 at 11:43
I forgot the "using" statement with execute immediate. It works now. Thanks ! — plugandplay, Jul 05 '13 at 11:50
This question appears to be off-topic because it is about an obvious bug in the OP's code. — APC, Jul 05 '13 at 16:19

score 0 · Accepted Answer · answered Jul 08 '13 at 08:06

Finally, the solution is:

declare

  pattern varchar2;

begin

  select columns
  into pattern
  from table a
  where conditions;


  execute immediate '
      select col2 
      from table2 
      where regexp_like(column, :pattern)
  ' using pattern;

end;

Thanks!

oracle regexp_like with pattern from select

1 Answers1