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