Tag Archives: Dynamic SQL in PLSQL blocks

Use of EXECUTE IMMEDIATE for SQL Queries in Oracle

Use of EXECUTE IMMEDIATE for SQL Queries in Oracle

Dynamic SQL EXECUTE IMMEDIATE
Dynamic SQL Query is used directly in PL/SQL Block to use DDL Queries and certain operation which is not normally handle in PL/SQL blocks.
EXECUTE IMMEDIATE is directly used in PL/SQL blocks.

SYNTAX

EXECUTE IMMEDIATE() [INTO ] [USING ]

Use of SQL Query with input and output parameter in EXECUTE IMMEDIATE command

SET SERVEROUTPUT ON
DECLARE
v_sql VARCHAR2(500);
v_first_name VARCHAR2(50);
v_salary NUMBER(8,2);
BEGIN
v_sql:='SELECT first_name,salary FROM employees WHERE employee_id=:v_emp_no';

EXECUTE IMMEDIATE v_sql INTO v_first_name,v_salary USING 200;
Dbms_output.put_line('Employee Name:'||v_first_name);
Dbms_output.put_line('Salary:'||v_salary);
END;
/

OUTPUT:
Employee Name:Jennifer
Salary:4400

PL/SQL procedure successfully completed.

Execute DDL like CREATE, DROP, Insert, update, delete with EXECUTE IMMEDIATE

set serveroutput on
Declare
v_id number;
BEGIN
EXECUTE IMMEDIATE 'create table TEST (ID NUMBER)';
EXECUTE IMMEDIATE 'INSERT into test values (:1)' using '1';
EXECUTE IMMEDIATE 'SELECT id from test' into v_id;
dbms_output.put_line ('Number:'|| v_id);
EXECUTE IMMEDIATE 'UPDATE TEST SET id = :2' USING '2';
EXECUTE IMMEDIATE 'SELECT id from test' into v_id;
dbms_output.put_line ('Number:'|| v_id);
EXECUTE IMMEDIATE 'DELETE FROM TEST';
EXECUTE IMMEDIATE 'drop table TEST';
END;
/

OUTPUT:
Number:1
Number:2

PL/SQL procedure successfully completed.

Execute DDL for create index with EXECUTE IMMEDIATE command
It will Create index on the existing table.

declare
cmd varchar2(400);
sname varchar2(400);
begin
cmd := 'create index HR.IDX_EMPLOYEES_NAME on HR.EMPLOYEES("FIRST_NAME")';
EXECUTE IMMEDIATE cmd;
End;
/