EXECUTE IMMEDIATE – run PL\SQL from string

Imagine situation that you have SQL command created dynamically to nvarchar2 variable. There is a way to execute it!

Create sample table:

BEGIN
EXECUTE IMMEDIATE 'CREATE TABLE MYITEMS (i_id NUMBER, i_name NVARCHAR2(50))';
END;

Creating table, inserting with parameters:

DECLARE
   sql_stmt    VARCHAR2(250);
   item_id     NUMBER(2) := 50;
   item_name   VARCHAR2(14) := 'Test item';
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE MYITEMS (i_id NUMBER, i_name NVARCHAR2(50))';
   sql_stmt := 'INSERT INTO MYITEMS VALUES (:1, :2)';
   EXECUTE IMMEDIATE sql_stmt USING item_id, item_name;
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