EXPERT RESPONSE
There are a couple of ways to pass local variables or values in to dynamic native SQL. I'll illustrate with examples. Both examples use a very simple procedure we'll create, NEXT_MONTH.
create or replace procedure next_month
( inDate in date )
as
begin
dbms_output.put_line(add_months(inDate,1));
end next_month;
/
For the first example, we'll convert our date to a string (with TO_CHAR) that will be implicitly converted back to a date by our call to NEXT_MONTH. We will set SERVEROUTPUT to ON in order to see the output from the NEXT_MONTH procedure.
set serveroutput on
declare
myDate date := sysdate;
sqlText varchar(100);
begin
sqlText := 'begin next_month(''' || to_char(mydate) || '''); end;';
execute immediate sqltext;
end;
/
For the next example (which I happen to like better), we'll use a bind variable and the USING clause of the EXECUTE IMMEDIATE statement. The colon (:) that comes before "boundDate" indicates that it is a bind variable. The USING clause indicates what value to pass in for the bind variable. Here it is.
declare
myDate date := sysdate;
sqlText varchar(100);
begin
sqlText := 'begin next_month(:boundDate); end;';
execute immediate sqltext using myDate;
end;
/
The results of running these two PL/SQL blocks are the same:
01-DEC-2003
PL/SQL procedure successfully completed
|
