CODEWITHSUNDEEP
oracle-databaseplsqloracle9i
Remya
Remya

Reputation: 21

Searching data from table by passing table name as a parameter in PL/SQL

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;

Upvotes: 2

Views: 1720

Answers (1)

Peter Lang
Peter Lang

Reputation: 55524

If you need this procedure, then I guess that you have several tables with the columns bankcode, bankname and dist_code. If this is true, then try to normalize your model if possible.

The USING term is the correct approach, but you have to use the parameter in your query.

To avoid SQL injection, you could use dbms_assert.sql_object_name.

This should work for you:

v_tem := 'SELECT bankcode, bankname, dist_code FROM '
         || dbms_assert.sql_object_name(p_tablename)
         || ' WHERE bankname LIKE :1';

Your EXECUTE IMMEDIATE will throw an exception when finding no row or more than one row, so using LIKE might not be a good idea.

Questions that you should ask yourself:

  • Is the model properly normalized?
  • Do you really need to use LIKE, or is = what you want?
  • If you want to use LIKE, how should the program deal with NO_DATA_FOUND / TOO_MANY_ROWS exceptions?

Upvotes: 1

Related Questions