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:
1) Dont create tables on the fly. Consider using global temporary tables, or a permanent table with a session ID key.
2) In terms of your SQL, you are generating an SQL that ends up looking like this:
where regexp_like(NAME ,BARBARA,'i')
Notice the missing quotes around the search term.
3) If you can fix (1), then you wont need *dynamic* SQL and the whole thing becomes a lot simpler, and you can use data types better. For example:
SQL> create global temporary table search_Results( empno int );
Table created.
SQL>
SQL> create or replace
2 PROCEDURE quick_search (searchfield IN VARCHAR2,searchvalue IN varchar2) IS
3 begin
4
5 case searchfield
6 when 'name' then
7 insert into search_Results
8 Select Distinct empno FROM emp
9 where regexp_like(ename ,searchvalue,'i');
10 when 'deptno' then
11 insert into search_Results
12 Select Distinct empno FROM emp
13 where deptno = to_number(searchvalue);
14 end case;
15 end;
16 /
Procedure created.
SQL>
SQL> exec quick_search('name','ford')
PL/SQL procedure successfully completed.
SQL> select * from search_results;
EMPNO
----------
7902
1 row selected.
SQL> commit;
Commit complete.
SQL>
SQL> exec quick_search('deptno','20')
PL/SQL procedure successfully completed.
SQL> select * from search_results;
EMPNO
----------
7369
7566
7788
7876
7902
5 rows selected.
SQL> commit;
Commit complete.
SQL>
SQL>