Simple EXECUTE IMMEDIATE : Execute immediate « PL SQL Statements « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. PL SQL Statements
3. Execute immediate

The EXECUTE IMMEDIATE command can be

1. a VARCHAR2 variable,
2. a literal quoted string, or
3. any string expression.

begin
    execute immediate 'whatever_text_string_you_want';
end;

or

declare
   v_variable_tx VARCHAR2(32000);
begin
   v_variable_tx:='whatever_you_want';
   execute immediate v_variable_tx;
end;

The code to be executed can be passed as a variable or directly as a string in the command.

The string cannot exceed 32K.

The code can be a single SQL command or a large block of PL/SQL.

All PL/SQL blocks passed as a string should have a semicolon at the end, as shown here: execute immediate 'begin p_test; end;';

All SQL statements passed as a string should not have a semicolon at the end, as shown here: execute immediate 'select 1 from dual' into a;