stored procedure for search

kannama · #1 (**permalink**) 02-09-12, 00:47

Is this stored procedure in oracle is correct for searching data from table by passing table name as a parameter

CREATE OR REPLACE PROCEDURE bank_search_sp
(
p_tablename IN VARCHAR2,
p_searchname IN VARCHAR2,
p_bankcode OUT VARCHAR2,
p_bankname OUT VARCHAR2,
p_dist_code OUT NUMBER
)
AS
v_tem VARCHAR2(5000);
BEGIN
v_tem := 'SELECT bankcode,bankname,dist_code FROM ' || UPPER (p_tablename) || '
WHERE bankname LIKE '''|| p_searchname||'''';
EXECUTE IMMEDIATE v_tem
INTO p_bankcode,p_bankname,p_dist_code
USING p_searchname ;
END bank_search_sp;

Littlefoot · #2 (**permalink**) 02-09-12, 01:40

Is it correct? You wrote it, you tested it. What do you say?

kannama · #3 (**permalink**) 02-09-12, 01:44

When i execute this the procedure is getting created but it is not working properly it shows errors like this

Running "PENSIONS"."BANK_SEARCH_SP" ( P_TABLENAME = bank, P_SEARCHNAME = S ).

ORA-01006: bind variable does not exist
ORA-06512: at "PENSIONS.BANK_SEARCH_SP", line 15
ORA-06512: at line 1

Littlefoot · #4 (**permalink**) 02-09-12, 03:06

I don't have your tables, so here's an example based on Scott's schema. See how I did it, and then adjust your code.

Code:

SQL> create or replace procedure p_search
  2    (p_tablename in char,
  3     p_job in char,
  4     p_ename out char
  5    )
  6  is
  7    v_tem varchar2(500);
  8  begin
  9    v_tem := 'select max(ename) from ' || p_tablename || ' where job like ''%' ||
 10             p_job || '%''';
 11    execute immediate v_tem into p_ename;
 12  end;
 13  /

Procedure created.

SQL> declare
  2    l_out varchar2(30);
  3  begin
  4    p_search('emp', 'CLERK', l_out);
  5    dbms_output.put_line(l_out);
  6  end;
  7  /
SMITH

PL/SQL procedure successfully completed.

SQL>

kannama · #5 (**permalink**) 02-09-12, 03:46

Its working

Internet Services

Web Development

Digital Marketing

Consumer Tech