Skip to Main Content
  • Questions
  • How to pass a bind variable inside string inside exexute immediate string?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Andrei.

Asked: April 06, 2018 - 10:47 am UTC

Last updated: August 14, 2023 - 12:44 pm UTC

Version: 12g

Viewed 10K+ times! This question is

You Asked

The benefits of using bind variables inside dynamic SQL statements have been discussed many times here.

I have dynamic SQl statement like this where "id" is a pls_integer:

---- ENSURE THAT BULK PROCESSING IS DISABLED ----
              begin
                execute immediate 'begin
                                     if ... then
                                       ...;
                                       log#.warn('' some text ''''' || || id || || '''''more text'');
                                     end if;
                                   end;';
              exception
                when others then debug('some error');
              end;


This results in excessive memory usage.

How can I replace id with a bind variable to minimize that?

begin
  execute immediate 'begin
                       log#.warn(''- :1 -'');
                     end;'
  using 'Whoa!';
end;


But that gives the following error in SQL Developer

Error starting at line : 1 in command -
begin
  execute immediate 'begin
                       log#.warn(''- :1 -'');
                     end;'
  using 'Whoa!';
end;
Error report -
ORA-01006: bind variable does not exist
ORA-06512: at line 2
01006. 00000 -  "bind variable does not exist"
*Cause:    
*Action:


I have also tried

begin
  execute immediate 'declare
                       l_id pls_integer := :1;
                     begin
                       log#.warn(''- ''''' || l_id || ''''' -'');
                     end;'
  using 'Whoa!';
end;


But that gives

Error starting at line : 1 in command -
begin
  execute immediate 'declare
                       l_id pls_integer := :1;
                     begin
                       log#.warn(''- ''''' || l_id || ''''' -'');
                     end;'
  using 'Whoa!';
end;
Error report -
ORA-06550: line 5, column 47:
PLS-00201: identifier 'L_ID' must be declared
ORA-06550: line 2, column 3:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


Any help would be greatly appreciated!

and Chris said...

The trick is you need to concatenate the bind variable within your string.

Using the q operator makes ensuring you've lined the quotes up correctly much easier:

begin

execute immediate q'!begin
dbms_output.put_line('- ' || :1 || '-');
end;!'
using 'Whoa!';

end;
/

- Whoa!-


PL/SQL procedure successfully completed.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

JITHESH, August 13, 2023 - 6:26 pm UTC

as I know this :i variable works, but what is its use. How we can use it in the pl/sql procedure
as the next step

can we assign it a normal pl/sql variable
how wecan retrieve this bind value with q' operator
Chris Saxon
August 14, 2023 - 12:44 pm UTC

If you want to get a bind variable value FROM a dynamic PL/SQL block use USING OUT to pass it to a variable:

declare
  v varchar2(10);
begin

  execute immediate q'!begin :i := 'Whoa'; end;!'
  using out v;

  dbms_output.put_line ( v );

end;
/
Whoa

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library