Thursday, February 5, 2009

Substituting PL/SQL Variables within the SQL while executing

CREATE TABLE employees_temp AS SELECT first_name, last_name FROM employees;
DECLARE
x VARCHAR2(20) := 'my_first_name';
y VARCHAR2(25) := 'my_last_name';
BEGIN
INSERT INTO employees_temp VALUES(x, y);
UPDATE employees_temp SET last_name = x WHERE first_name = y;
DELETE FROM employees_temp WHERE first_name = x;
COMMIT;
END;
/

To use variables in place of table names, column names, and so on, requires the EXECUTE IMMEDIATE statement.

No comments:

search engine

Custom Search