Oracle EXECUTE IMMEDIATE and PostgreSQL EXECUTE and PREPARE
Feature compatibility | AWS SCT / AWS DMS automation level | AWS SCT action code index | Key differences |
---|---|---|---|
|
|
N/A |
N/A |
Oracle Usage
You can use Oracle EXECUTE IMMEDIATE
statement to parse and run a dynamic SQL statement or an anonymous PL/SQL block. It also supports bind variables.
Examples
Run a dynamic SQL statement from within a PL/SQL procedure:
-
Create a PL/SQL procedure named
raise_sal
. -
Define a SQL statement with a dynamic value for the column name included in the
WHERE
statement. -
Use the
EXECUTE IMMEDIATE
command supplying the two bind variables to be used as part of theSELECT
statement:amount
andcol_val
.CREATE OR REPLACE PROCEDURE raise_sal (col_val NUMBER, emp_col VARCHAR2, amount NUMBER) IS col_name VARCHAR2(30); sql_stmt VARCHAR2(350); BEGIN -- determine if a valid column name has been given as input SELECT COLUMN_NAME INTO col_name FROM USER_TAB_COLS WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_col; -- define the SQL statment (with bind variables) sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' || col_name || ' = :2'; -- Run the command EXECUTE IMMEDIATE sql_stmt USING amount, col_val; END raise_sal; /
-
Run the DDL operation from within an
EXECUTE IMMEDIATE
command.EXECUTE IMMEDIATE 'CREATE TABLE link_emp (idemp1 NUMBER, idemp2 NUMBER)'; EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
-
Run an anonymous block with bind variables using
EXECUTE IMMEDIATE
.EXECUTE IMMEDIATE 'BEGIN raise_sal (:col_val, :col_name, :amount); END;' USING 134, 'EMPLOYEE_ID', 10;
For more information, see EXECUTE IMMEDIATE Statement
PostgreSQL Usage
The PostgreSQL EXECUTE
command prepares and runs commands dynamically. The EXECUTE
command can also run DDL statements and retrieve data using SQL commands. Similar to Oracle, you can use the PostgreSQL EXECUTE
command with bind variables.
Examples
Execute a SQL SELECT query with the table name as a dynamic variable using bind variables. This query returns the number of employees under a manager with a specific ID.
DO $$DECLARE Tabname varchar(30) := 'employees'; num integer := 1; cnt integer; BEGIN EXECUTE format('SELECT count(*) FROM %I WHERE manager = $1', tabname) INTO cnt USING num; RAISE NOTICE 'Count is % int table %', cnt, tabname; END$$; ;
Run a DML command with no variables and then with variables.
DO $$DECLARE BEGIN EXECUTE 'INSERT INTO numbers (a) VALUES (1)'; EXECUTE format('INSERT INTO numbers (a) VALUES (%s)', 42); END$$; ;
Note
%s
formats the argument value as a simple string. A null value is treated as an empty string.
%I
treats the argument value as an SQL identifier and double-quoting it if necessary. It is an error for the value to be null.
Run a DDL command.
DO $$DECLARE BEGIN EXECUTE 'CREATE TABLE numbers (num integer)'; END$$; ;
For more information, see String Functions and Operators
Using a PREPARE
statement can improve performance for reusable SQL statements.
The PREPARE
command can receive a SELECT
, INSERT
, UPDATE
, DELETE
, or VALUES
statement and parse it with a user-specified qualifying name so you can use the EXECUTE
command later without the need to re-parse the SQL statement on each run.
-
When using
PREPARE
to create a prepared statement, it will be viable for the scope of the current session. -
If you run a DDL command on a database object referenced by the prepared SQL statement, the next
EXECUTE
command requires a hard parse of the SQL statement.
Example
Use PREPARE
and EXECUTE
commands together.
-
The SQL command is prepared with a user-specified qualifying name.
-
The SQL command runs several times, without the need for re-parsing.
PREPARE numplan (int, text, bool) AS INSERT INTO numbers VALUES($1, $2, $3); EXECUTE numplan(100, 'New number 100', 't'); EXECUTE numplan(101, 'New number 101', 't'); EXECUTE numplan(102, 'New number 102', 'f'); EXECUTE numplan(103, 'New number 103', 't');
Summary
Functionality | Oracle EXECUTE IMMEDIATE | PostgreSQL EXECUTE |
---|---|---|
Execute SQL with results and bind variables |
EXECUTE IMMEDIATE 'select salary from employees WHERE ' || col_name || ' = :1' INTO amount USING col_val; |
EXECUTE format('select salary from employees WHERE %I = $1', col_name) INTO amount USING col_val; |
Execute DML with variables and bind variables |
EXECUTE IMMEDIATE 'UPDATE employees SET salary = salary + :1 WHERE ' || col_name || ' = :2' USING amount, col_val; |
EXECUTE format('UPDATE employees SET salary = salary + $1 WHERE %I = $2', col_name) USING amount, col_val; |
Execute DDL |
EXECUTE IMMEDIATE 'CREATE TABLE link_emp (idemp1 NUMBER, idemp2 NUMBER)'; |
EXECUTE 'CREATE TABLE link_emp (idemp1 integer, idemp2 integer)'; |
Execute anonymous block |
EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE ("Anonymous Block"); END;'; |
DO $$DECLARE BEGIN ... END$$; |
For more information, see Basic Statements