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
– AndrewMcCoist, Commented Apr 14, 2016 at 11:43
schema name and variable name can not be used as bind variable. it would be something like eval inside eval. — ibre5041
– ibre5041, Commented Apr 14, 2016 at 11:54

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

1

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 . . .

answered Apr 14, 2016 at 11:54

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

Felix M Over a year ago

Hey, thanks first of all for your answer. I know that it is not allowed to replace users and procedure names with the given example of mine. I must admit that I tricked myself a bit in the first post. I was a bit in a hurry and excuse me for that. What I like to do is to dynamically call a procedure which is in several packages. Lets say I have a procedure 'Version' in each package I want to call. And now I want to have a dynamic sql statement to replace the package name. May I use variables here? Because all i tried (and above's is just a bad example) failed.

Collectives™ on Stack Overflow

Native Dynamic SQL, creating string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related