8-3. Writing a Dynamic INSERT Statement
Problem
Your application must insert data into a table, but you don't know until runtime which columns you will insert. For example, you are writing a procedure that will be used for saving records into the EMPLOYEES table. However, the exact content to be saved is not known until runtime because the person who is calling the procedure can decide whether they are including a DEPARTMENT_ID. If a DEPARTMENT_ID is included, then the department will be included in the INSERT.
Solution
Create a string at runtime that will contain the INSERT statement text to be executed. Use bind variables to substitute the values that are to be inserted into the database table. The following procedure accepts user input for entry of a new employee record. Bind variables are used to substitute those values into the SQL.
CREATE OR REPLACE PROCEDURE new_employee ( first IN VARCHAR2,
last IN VARCHAR2,
email IN VARCHAR2,
phone IN VARCHAR2,
hired IN DATE,
job IN VARCHAR2,
dept IN NUMBER DEFAULT 0) AS
v_sql VARCHAR2(1000);
BEGIN
IF dept != 0 THEN
v_sql := 'INSERT INTO EMPLOYEES ( ' ||
'employee_id, first_name, last_name, email, ' ||
'phone_number, hire_date, job_id, department_id) ' ||
'VALUES( ' ||
':id, :first, :last, :email, :phone, :hired, ' ||
':job_id, :dept)';
EXECUTE IMMEDIATE v_sql
USING employees_seq.nextval, first, last, email, phone, hired, job, dept;
ELSE
v_sql := 'INSERT INTO EMPLOYEES ( ' ||
'employee_id, first_name, last_name, email, ' ||
'phone_number, hire_date, job_id) ' ||
'VALUES( ' ||
':id, :first, :last, :email, :phone, :hired, ' ||
':job_id)';
EXECUTE IMMEDIATE v_sql
USING employees_seq.nextval, first, last, email, phone, hired, job;
END IF;
DBMS_OUTPUT.PUT_LINE('The employee has been successfully entered'),
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('YOU MUST SUPPLY A VALUE FOR DEPARTMENT'),
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('EMPLOYEE_ID ALREADY EXISTS'),
END;
If the data entry clerk includes a department ID number for the employee when executing the NEW_EMPLOYEE procedure, then the INSERT statement will differ slightly than it would if no department ID were provided. The basic native dynamic SQL in this example does not differ much from those examples demonstrated in Recipe 8-1 or Recipe 8-2 of this chapter.
Solution #2
The DBMS_SQL API can also be used to execute dynamic INSERT statements. Although dynamic DML is not usually performed with DBMS_SQL very often, it can still be useful in some circumstances. The following example performs the same task as Solution #1 to this recipe. However, it has been rewritten to use DBMS_SQL instead of native dynamic SQL.
CREATE OR REPLACE PROCEDURE new_employee(first IN VARCHAR2,
last IN VARCHAR2,
email IN VARCHAR2,
phone IN VARCHAR2,
hired IN DATE,
job IN VARCHAR2,
dept IN NUMBER DEFAULT 0)
AS
v_sql VARCHAR2(1000);
cursor_var NUMBER := DBMS_SQL.OPEN_CURSOR;
rows_compelete NUMBER := 0;
next_emp NUMBER := employee_seq.nextval;
BEGIN
IF dept != 0 THEN
v_sql := 'INSERT INTO EMPLOYEES ( ' ||
'employee_id, first_name, last_name, email, ' ||
'phone_number, hire_date, job_id, department_id) ' ||
'VALUES( ' ||
':id, :first, :last, :email, :phone, :hired, ' ||
':job_id, :dept)';
ELSE
v_sql := 'INSERT INTO EMPLOYEES ( ' ||
'employee_id, first_name, last_name, email, ' ||
'phone_number, hire_date, job_id) ' ||
'VALUES( ' ||
':id, :first, :last, :email, :phone, :hired, ' ||
':job_id)';
END IF;
DBMS_SQL.PARSE(cursor_var, v_sql, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_VARIABLE(cursor_var, 1, ':id', next_emp);
DBMS_SQL.BIND_VARIABLE(cursor_var, 2, ':first', first);
DBMS_SQL.BIND_VARIABLE(cursor_var, 3, ':last', last);
DBMS_SQL.BIND_VARIABLE(cursor_var, 4, ':email', email);
DBMS_SQL.BIND_VARIABLE(cursor_var, 5, ':phone', phone);
DBMS_SQL.BIND_VARIABLE(cursor_var, 6, ':hired'),
DBMS_SQL.BIND_VARIABLE(cursor_var, 7, ':job', job);
IF dept != 0 then
DBMS_SQL.BIND_VARIABLE(cursor_var, 8, ':dept', dept);
END IF;
rows_complete := DBMS_SQL.EXECUTE(cursor_var);
DBMS_SQL.CLOSE_CURSOR(cursor_var);
DBMS_OUTPUT.PUT_LINE('The employee has been successfully entered'),
END;
How It Works
Using native dynamic SQL, creating an INSERT statement is almost identical to working with a query string. As a matter of fact, the only difference is that you will not be making use of the INTO clause within the EXECUTE IMMEDIATE statement. Standard PL/SQL can be used to create the SQL statement string in order to process an INSERT statement that contains column names, table names, or WHERE clause values that are not known until runtime.
Note If your SQL string contains any SQL that requires the use of single quotes, double up on the quotes. Placing a single quote immediately after another signals the parser to place a single quote into the string that you are creating.
Similarly to SQL queries using dynamic SQL, you should use bind variables to substitute values into the SQL statement string where needed. As a refresher, bind variables are used within SQL queries or statements to act as placeholders for values that are to be substituted at runtime. A bind variable begins with a colon and is then followed by the variable name. The EXECUTE IMMEDIATE statement implements the USING clause to list variables that contain values that will be substituted into the bind variables at runtime. The order in which the variables are listed in the USING clause must concur with the positioning of the bind variables within the SQL. The following is an example of an EXECUTE IMMEDIATE statement to be used with a SQL statement such as an INSERT:
EXECUTE IMMEDIATE sql_statement_string
[USING variable1, variable2, etc];
It is usually a good idea to include an EXCEPTION block at the end of any code block. This is especially true when working with dynamic queries or statements. An Oracle error will be raised if the INSERT statement within the SQL string is invalid. If an EXCEPTION block were added to catch OTHERS, then you could provide a well-written error message that describes the exact issue at hand. In most cases, users of your application would prefer to see such a nice summary message rather than a cryptic Oracle error message.
It is a good rule of thumb to maintain consistency throughout your application code. If you prefer to use native dynamic SQL, then try to use it in all cases where dynamic SQL is a requirement. Likewise, DBMS_SQL should be used throughout if you plan to make use of it instead. There are certain situations when you may want to mix the two techniques in order to obtain information or use features that are not available with one or the other. In Recipe 8-13, you will learn more about using both techniques within the same block of PL/SQL code.