execute immediate in plsql

Static
declare
  vString varchar2(300);
begin
  begin
    dbms_output.put_line('output');
  end;

  --Execute immediate 
  vString := 'begin
               dbms_output.put_line(''execute immediate output.'');
              end;';

  execute immediate vString;
end;

Dynamic

declare
  vSql varchar2(1000);
  vPerson  number;
  vPrice number;
begin

  vPrice := 3000;

  vSql := 'select count(1)
               into :vPerson
              from hr.employees
             where salary < :vPrice
               and job_id in (''PU_APRIL'', ''ST_MARY'')';

  execute immediate vSql
    into vPerson
    using vPrice;

  dbms_output.put_line(vPerson || ' person's price ' || vPrice);
end;
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: