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;
/
Smart way of Technology 