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

Question

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

Regardless of the solution you choose , please please ensure you are not enabling SQL Injection. Scrub all inputs. Use binds. — Kris Rice
– Kris Rice, Commented Feb 4, 2018 at 17:52
Why not just pass in a full sql statement at this point? There's already no sql injection protection. — Brad
– Brad, Commented Feb 4, 2018 at 17:57
Mobile right now. I’ll post a longer example later when I have a keyboard. — Kris Rice
– Kris Rice, Commented Feb 4, 2018 at 17:57
Looks like the real question was "How can I use dynamic SQL to return a value?" — William Robertson
– William Robertson, Commented Feb 4, 2018 at 17:59

Gordon Linoff · Accepted Answer · 2018-02-04 17:44:29Z

4

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.

answered Feb 4, 2018 at 17:44

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

abderrahim Over a year ago

this is also work 'execute immediate 'SELECT ' || columnName || ' INTO ' || idCatc || ' FROM ' || tableName || ' WHERE ' || whereColumn || ' = ' || parameterColumn;'

Wernfried Domscheit Over a year ago

execute immediate 'SELECT ' || in_columnName || ' FROM ' || in_tableName || ' WHERE ' || in_whereColumn || ' = :par' INTO idCatc using in_parameterColumn;

would be even better.

Kris Rice · Accepted Answer · 2018-02-04 20:47:36Z

3

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;
  /

answered Feb 4, 2018 at 20:47

Kris Rice

3,44017 silver badges34 bronze badges

2 Comments

abderrahim Over a year ago

it's work good how EXECUTE IMMEDIATE work with OPEN curosr FOR SELECT ...

JosephDoggie Over a year ago

I cannot use this to get an entire column. Is there a way to make the output multi-valued?

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related