CODEWITHSUNDEEP
sqloracle-databaseplsql
abderrahim
abderrahim

Reputation: 33

How can I pass column and table name as parameters to a SQL stored procedure

CREATE OR REPLACE FUNCTION getIdFromNameParameter 
    (columnName VARCHAR2, tableName VARCHAR2, whereColumn VARCHAR2, parameterColumn VARCHAR2)
RETURN VARCHAR2
AS
    idCatc VARCHAR2(50);
BEGIN
    execute immediate 'SELECT ' || columnName || ' INTO ' || idCatc || ' FROM ' || tableName || ' WHERE ' || whereColumn || ' = ' ||  parameterColumn;
    RETURN idCatc;
END;
/

I get this warning:

Warning: Function created with compilation errors

Upvotes: 0

Views: 1705

Answers (2)

Kris Rice
Kris Rice

Reputation: 3410

If you create a function like this, ensure it's safe from sql injection with something like the following. This uses dbms_assert to sanitize the inputs against thing like ';drop table xyz;'

 CREATE OR REPLACE FUNCTION getidfromnameparameter (
        in_columnname        VARCHAR2,
        in_tablename         VARCHAR2,
        in_wherecolumn       VARCHAR2,
        in_parametercolumn   VARCHAR2
    ) RETURN VARCHAR2 AS
        idcatc   VARCHAR2(50);
    BEGIN
        EXECUTE IMMEDIATE 'SELECT '
                         || sys.dbms_assert.qualified_sql_name(in_columnName)
                         || ' FROM '
                         || sys.dbms_assert.sql_object_name(in_tableName)
                         || ' WHERE '
                         || sys.dbms_assert.qualified_sql_name(in_whereColumn)
                         || ' = '
                         || sys.dbms_assert.enquote_literal(in_parametercolumn)
       INTO idcatc;

       RETURN idcatc;
  END;
  /

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271031

For one thing, the into is part of execute immediate:

CREATE OR REPLACE FUNCTION getIdFromNameParameter (
    in_columnName VARCHAR2,
    in_tableName VARCHAR2,
    in_whereColumn VARCHAR2,
    in_parameterColumn VARCHAR2)
RETURN VARCHAR2
AS
    idCatc VARCHAR2(50);
BEGIN
    execute immediate 'SELECT ' || in_columnName || ' FROM ' || in_tableName || ' WHERE ' || in_whereColumn || ' = ' ||  in_parameterColumn
    INTO idCatc;
    RETURN idCatc;
END;

You were also using + for string concatenation.

Upvotes: 4

Related Questions