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