Native Dynamic SQL, creating string

Question

I've just started with PL/SQL. My concern is as follows:

I want to create a string dynamically.

I tried the following, but it always results in ORA-00900 & ORA-06512 at the line of "Execute Immediate...".

Here we go:

    Declare

    l_pre_sql varchar2(4000) := 'schema.';
    l_sql varchar2(4000) := 'concat(concat(:a, :b), :c)';
    l_after_sql := '.procedure(a,b,c)';
begin

execute immediate l_sql using l_pre_sql, l_sql, l_after_sql;

end;

Is the syntax of execute immediate wrong? Are there any other working possibilities? As you may see, i am working around the problem that one cannot use schema name as a dynamic variable.

For clarification I basically want to do this:

execute immediate ':a'||'.'||':b'||'.procedure(a,b,c)' using schema, name;

Thanks in advance!

Why don't you just call EXECUTE IMMEDIATE l_pre_sql||l_after_sql? What you're calling now is concat(concat(schema.,concat(concat(:a,:b), :c), .procedure(a,b,c)) which makes no sense at all. — AndrewMcCoist
schema name and variable name can not be used as bind variable. it would be something like eval inside eval. — ibre5041

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-04-14T11:54:10

In prepared statements (in Oracle and other languages), you can replace constant values in the query string using parameters. However, you cannot replace column names, table names, users (schemas), procedure names, and so on.

In other words, the substitution is not just by replacing values with string representations. It is plugging parameters into a compiled statement.

So, you need to construct the string with the procedure names first and then call it.

I think what you want is something like:

execute immediate l_pre_sql || l_after_sql || '(:a, :b, :c)' using . . .

Native Dynamic SQL, creating string

1 Answers