PLSQL dynamic code = execute immediate

Some time ago i was searching for examples of running dynamic pieces of code in pl/sql. 'execute immediate' - hero helped me :)

You can run some super fancy code with it like:

execute immediate 'select 1 from dual' into w_variable;

or

declare
column varchar2(15) :='name';
table_name varchar2(15) := 'test_table';
begin
execute immediate 'select ' || column || ' from ' || table_name;
end;

which is still not impressive enough to surprise even not-pl/sql developer, you can easily achieve this by using sys_refcursor.

Basically you can run any kind of code in this block. Only thing you have to remember - this is anymous blok. There is no straight-access to fields of surrounding code. So if you need to set dynamically some fields of type you should do it like this:

execute immediate '
declare
some_type SOME_TYPE;
begin
some_type := :1;
'|| 'some_type.' || field_name  || ' := ''' || column_value ||''';
:2 :=some_type;
end;'
using in SELF, out SELF;

You need to declare field of type SOME_TYPE in this block, otherwise it wont work.

Execute immediate can be used to create types, packages etc dynamically - so its really powerful when you require such a functionality.