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 := [email protected]_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

 

 50,818 views last month,  272 views today

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply