Execute Immediate in Oracle

I will explain Execute Immediate in Oracle in this post.

 

Execute Immediate in Oracle

You can execute a dynamic SQL statement or anonymous PL/SQL block using the EXECUTE IMMEDIATE statement.

 

EXECUTE IMMEDIATE statement can be used to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance.

 

 

 

 

 

The EXECUTE IMMEDIATE statement syntax is as follows:

EXECUTE IMMEDIATE <SQL or SPL Commands>
[INTO <variable list>]
[USING <bind variable list>];

 

 

You can review the following Execute Immediate examples to understand how it works.

 

BEGIN
   EXECUTE IMMEDIATE 'BEGIN DBMS_OUTPUT.PUT_LINE(''semicolons''); END;';
END;
/

CREATE PROCEDURE employee (id NUMBER) AS
BEGIN
   EXECUTE IMMEDIATE
      'DELETE FROM employees WHERE employee_id = ' || TO_CHAR(id);
END;
/





set serveroutput on;
DECLARE
v_diff NUMBER := 0;
v_hrs NUMBER := 0;
v_min NUMBER := 0;
v_sec NUMBER := 0;
p_dte1 DATE;
p_dte2 DATE;
date1 long;
date2 long;
BEGIN
date1 := 'select sysdate from dual';
date2 := 'select max(TIMESTAMP) from v$recovery_progress';
execute immediate date1 into p_dte1;
execute immediate date2 into p_dte2;
v_diff := ABS(p_dte2 - p_dte1);
v_hrs := TRUNC(v_diff, 0)*24;
v_diff := (v_diff - TRUNC(v_diff, 0))*24; 
v_hrs := v_hrs + TRUNC(v_diff, 0);
v_diff := (v_diff - TRUNC(v_diff, 0))*60;
v_min := TRUNC(v_diff, 0);
v_sec := TRUNC((v_diff - TRUNC(v_diff, 0))*60, 0);
DBMS_OUTPUT.put_line(
TO_CHAR(v_hrs) ||' '||
TO_CHAR(v_min) ||' '||
TO_CHAR(v_sec) );
END;
/

DECLARE
   TYPE EmpCurTyp IS REF CURSOR;
   emp_cv   EmpCurTyp;
   emp_rec  employees%ROWTYPE;
   sql_stmt VARCHAR2(200);
   v_job   VARCHAR2(10) := 'ST_CLERK';
BEGIN
   sql_stmt := 'SELECT * FROM employees WHERE job_id = :j';
   OPEN emp_cv FOR sql_stmt USING v_job;
   LOOP
     FETCH emp_cv INTO emp_rec;
     EXIT WHEN emp_cv%NOTFOUND;
     DBMS_OUTPUT.PUT_LINE('Name: ' || emp_rec.last_name || ' Job Id: ' ||
                           emp_rec.job_id);
   END LOOP;
   CLOSE emp_cv;
END;
/

CREATE FUNCTION row_count (tab_name VARCHAR2) RETURN NUMBER AS
   rows NUMBER;
BEGIN
   EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || tab_name INTO rows;
   RETURN rows;
END;
/
-- From an anonymous block, you might call the function remotely, as follows:
DECLARE
   emp_count INTEGER;
BEGIN
   emp_count := row_count@hr_db('employees');
   DBMS_OUTPUT.PUT_LINE(emp_count);
END;
/

 

 

 

Do you want to learn Oracle SQL for Beginners, then read the following articles.

Oracle SQL Tutorials For Beginners – Learn Oracle SQL from scratch with Oracle SQL Online Course

See more