DML with variables

BEGIN
INSERT INTO employees (emp_id, emp_name) VALUES (1, ‘Vladimir’);
END

Using DML statements with variables for table or column names or values require EXECUTE IMMEDIATE command to be used

DECLARE
emp_id NUMBER := 1;
emp_name VARCHAR2(128) := ‘Vladimir’;
table_name VARCHAR2(128) := ’employees’;
sql_query VARCHAR2(500);
BEGIN
— INSERT INTO employees VALUES (1, ‘Vladimir’)
— using bind variables to escape SQL INJECTION
http://docs.oracle.com/cd/E11882_01/appdev.112/e10472/dynamic.htm#CHDFCHHJ
sql_query := ‘INSERT INTO ‘ || table_name || ‘ VALUES (:emp_id,:emp_name)’;
EXECUTE IMMEDIATE sql_query USING emp_id, emp_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

%d bloggers like this: