CODEWITHSUNDEEP
sqloracle-databaseplsqldynamic-sql
thealchemist
thealchemist

Reputation: 407

Dynamic SQL with table name as a parameter

I am trying to execute a procedure into which i send the table name and 2 column names as parameters:

EXECUTE IMMEDIATE 'select avg(@column1) from @Table1 where REF_D = @column2' into ATTR_AVG;

I have tried using the variables in combiations of '@', ':', '||' but nothing seems to work.

Has anyone used table names as a parameter. there are a few solutions here but for SQL Server

Upvotes: 1

Views: 5317

Answers (2)

William Robertson
William Robertson

Reputation: 15991

You need to construct the executable statement using || (or else define it as one string containing placeholders that you can then manipulate with replace). Something like:

create or replace procedure demo
    ( p_table   user_tab_columns.table_name%type
    , p_column1 user_tab_columns.column_name%type
    , p_column2 user_tab_columns.column_name%type )
is
    attr_avg number;
begin
    execute immediate
        'select avg(' || p_column1 || ') from ' || p_table ||
        ' where ref_d = ' || p_column2
    into attr_avg;

    dbms_output.put_line('Result: ' || attr_avg);
end demo;

It's generally a good idea to build the string in a debugger-friendly variable first, i.e. something like:

create or replace procedure demo
    ( p_table   user_tab_columns.table_name%type
    , p_column1 user_tab_columns.column_name%type
    , p_column2 user_tab_columns.column_name%type )
is
    attr_avg number;
    sql_statement varchar2(100);
begin
    sql_statement :=
        'select avg(' || p_column1 || ') from ' || p_table ||
        ' where ref_d = ' || p_column2;

    execute immediate sql_statement into attr_avg;

    dbms_output.put_line('Result: ' || attr_avg);
end demo;

Depending on what ref_d is, you may have to be careful with what you compare it to, so the above could require some more work, but hopefully it gives you the idea.

Edit: however see Alex Poole's answer for a note about the use of bind variables. If ref_d is a variable that may need to become:

    sql_statement :=
        'select avg(' || p_column1 || ') from ' || p_table ||
        ' where ' || p_column2 || ' = :b1';

    execute immediate sql_statement into attr_avg using ref_d;

(The convention is to put the search expression on the right e.g. where name = 'SMITH' rather than where 'SMITH' = name, though they are the same thing to SQL.)

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191235

You can only use bind variables (denoted by colons) for values, not for parts of the structure. You will have to concatenate the table and column names into the query:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where REF_D = ' || column2 into ATTR_AVG;

Which implies REF_D is a fixed column name that can appear in any table you'll call this for; in a previous question that seems to be a variable. If it is actually a string variable then you'd need to bind and set that:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where ' || column2 || ' = :REF_D' into ATTR_AVG using REF_D;

If it's supposed to be a date you should make sure the local variable is the right type, or explicitly convert it.

Upvotes: 3

Related Questions