regexp_like not accepting variable pattern

Question

I have a table

> create table employee(employeeid number(10), name varchar2(100),deptno
> 
>     number(10));

there are 1000 rows in this table. when i am trying to create a procedure which will search a value and create a temporary table at run time & insert all results in that table. In procedure: tableName is the name of temporary table which will be passed by application. Search field is column name on which search will occur. Searchvalue will be the pattern.

create or replace PROCEDURE quick_search (tableName IN varchar2,searchfield IN 
VARCHAR2,searchvalue IN varchar2) IS
v_tableName varchar2(100);
begin
select count(tname) into v_tableName from tab where lower(tname) = tableName;
if v_tableName = 1 then
EXECUTE IMMEDIATE 'DROP TABLE '||tableName||'';
case searchfield
 when 'name' then
                    execute immediate 'create table '||create_table||'  as
                          Select Distinct employeeid FROM employee  where 
regexp_like(NAME ,'||searchvalue||',''i'')';


when 'deptno' then
                   execute immediate 'create table '||create_table||'  as
                          Select Distinct employeeid FROM employee  where 
regexp_like(deptno ,'||searchvalue||',''i'')';

end case;
end if;
if v_tableName =0 then
case searchfield
 when 'name' then
                    execute immediate 'create table '||create_table||'  as
                          Select Distinct employeeid FROM employee  where 
regexp_like(NAME ,'||searchvalue||',''i'')';


when 'deptno' then
                   execute immediate 'create table '||create_table||'  as
                          Select Distinct employeeid FROM employee  where 
regexp_like(deptno ,'||searchvalue||',''i'')';

end case;
end if;
end;

when i am executing this:

exec exec quick_search ('temp1','name','barbara') ;

there is no object which exixt with name temp1 and barbara exist in my employee table.

I am getting error Error report - ORA-00904: "BARBARA": invalid identifier ORA-06512: at "SCOTT.QUICK_SEARCH", line 53 ORA-06512: at line 1 00904. 00000 - "%s: invalid identifier" *Cause: *Action:

pablomatico pablomatico · Accepted Answer · 2016-05-31T05:54:15

You put the double quotes on the case-insensitive parameter but forgot to put it on the pattern parameter too. You'd have to modify each execute immediate of your procedure as follows:

EXECUTE IMMEDIATE 'CREATE TABLE '||create_table||' AS SELECT DISTINCT employeeid FROM employee WHERE REGEXP_LIKE(name, ''' || searchvalue || ''',''i'')';

regexp_like not accepting variable pattern

1 Answers