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;
/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.