EXECUTE IMMEDIATE in Oracle: Usage Guide

1 year ago
Isabella Edwards
1 minute

The EXECUTE IMMEDIATE statement is used in PL/SQL programs to dynamically execute SQL statements. Here is an example of using EXECUTE IMMEDIATE:

DECLARE
  sql_stmt VARCHAR2(200);
  emp_name VARCHAR2(100);
BEGIN
  sql_stmt := 'SELECT ename FROM emp WHERE empno = 7839';
  EXECUTE IMMEDIATE sql_stmt INTO emp_name;
  DBMS_OUTPUT.PUT_LINE('Employee name is: ' || emp_name);
END;

In this example, we begin by declaring a variable called sql_stmt to store the SQL statement to be executed. Next, we use the EXECUTE IMMEDIATE statement to execute the SELECT statement and store the result in the emp_name variable. Finally, we use the DBMS_OUTPUT.PUT_LINE statement to print out the employee name retrieved from the database.

It’s important to be cautious when using EXECUTE IMMEDIATE to execute dynamic SQL statements, as care must be taken to prevent SQL injection attacks. One way to avoid this is by using bind variables.

bannerAds