Using variables content in select statement

Question

I have a plsql function where I'm first receiving the field name of an other table in a select statement. Now I want to get the value of the field of this other table.

In the variable fieldName is the fieldname of the table I need.

What I tried so far:

SELECT fieldName FROM prj WHERE ID = 12345

But I'm rightly receiving an error, that the fieldname does not exist. How to do this?

TIA frgtv10

This question could be a bit more straightforward. Could you post the exact error message you get when running SELECT fieldName FROM prj WHERE ID = 12345? — bpgergo
– bpgergo, Commented Sep 9, 2013 at 8:09
@bpgergo, the "fieldName" as Column does not exists. Here should stand the content of the variable, thats the problem. — frgtv10
– frgtv10, Commented Sep 9, 2013 at 8:15

Robert · Accepted Answer · 2013-09-09 08:16:31Z

2

Try to use EXECUTE IMMEDIATE as below

DECLARE
   sql_stmt   VARCHAR2(200);
   id         NUMBER(8) := 12345;   
   colval     VARCHAR2(200);
   fieldName  VARCHAR2(200) := 'columnname';
BEGIN
   sql_stmt := 'SELECT '||fieldName||' FROM prj WHERE id = :id';
   EXECUTE IMMEDIATE sql_stmt INTO colval USING id;   
END;

edited Sep 9, 2013 at 8:16

answered Sep 9, 2013 at 8:11

Robert

25.8k8 gold badges70 silver badges86 bronze badges

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

Comments

the_slk · Accepted Answer · 2013-09-09 08:26:55Z

There is a different, more correct approach for such a case. It is not the best idea to dynamically select column from a table There should be a separate SELECT statement for it.

CREATE TABLE test
(
    col1 NUMBER
,   col2 NUMBER
);

INSERT INTO test VALUES (11, 12);

DECLARE
    l_field_name VARCHAR2(30);
    l_selected   VARCHAR2(30);

    FUNCTION col_name RETURN VARCHAR2
    AS
    BEGIN
        RETURN 'col1';
    END col_name;
BEGIN
    l_field_name := col_name();

    IF l_field_name = 'col1' THEN
        SELECT  col1
        INTO    l_selected
        FROM    test;
    ELSIF l_field_name = 'col2' THEN
        SELECT  col2
        INTO    l_selected
        FROM    test;
    ELSE
        RAISE_APPLICATION_ERROR(-20000, 'Column does not exist');
    END IF;
END;

Collectives™ on Stack Overflow

Using variables content in select statement

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related