How to use execute immediate statements in oracle? | Dynamic SQL -1

In my previous article I have given the details about Oracle PL SQL in detail. In this article i would like to brief about the dynamic SQL in detail and the key part is how and where to use execute immediate statements in oracle in detail. The execute immediate statement is used to execute the dynamic SQL. In PLSQL interviews most of the time the question is been asked about the dynamic SQL and its use. I want to throw a light on what exactly the dynamic SQL with multiple real life examples.

What you will see in this article?

1. What is dynamic SQL with examples?
2. How to use execute immediate statement in detail?
3. What is execution flow of SQL step by step?

What is execution flow of SQL step by step?

In this section I would like to start with the execution flow of SQL step by step. I have already given the detailed information about stepwise execution of SQL in detail. The following is steps of execution of SQL statement :

Step 1 : Parsing

The first step is checking the syntax of the statement in detail. The parsing step also contains the validation of the SQL statement. The oracle engine will check the privileges to execute the specified SQL statement. So the parsing step contains the Syntax, validation, privileges of the SQL statement in detail.

Example :

If user is executing the select * from Employee. It will check the syntax as per the oracle then it will check whether user has select privileges and then it will validate the select statement.

Step 2 :Checking the Bind variables

The step 2 is checking the bind variables in detail. If SQL statement using bind variables then this step is only used to validate that.

Step 3 : Execute the SQL statement

This is another important step where the oracle engine will execute the SQL statement.

Step 4 : Fetch the SQL

It will execute and retrieve the SQL data in detail.

If you want to check the detailed article on execution of SQL statement in detail then you can check here.

What is Dynamic SQL and where it is used?

The Dynamic SQL is the SQL statement which has been created dynamically at run time. The question here is actually where we require the dynamic SQL statement. I would like to explain one example of procedure where we require to create dynamic SQL then we will check the different bullet points about dynamic SQL.

The procedure name : Create a procedure to delete any table in oracle.

Create or Replace Procedure P_Delete_Table(P_name varchar2)

is

begin

delete from P_name;

dbms_output.put_line(‘The table is deleted :”||P_name);

commit;

End;

In above procedure when you try to pass the wrong table name which does not exist then it will throw error like ‘table or view does not exist’. To handle this kind of situation you can use the execute immediate statement or dbms_SQL statement.

The dynamic SQL are the SQL statements written at run time whose structure may change at run time. You can use the application in react js as well.

What is dynamic SQL with real examples?

The dynamic SQL is nothing but the run time sql statements whose structure may change during the runtime.Following are some bullet points :

1.Dynamic SQL is constructed and stored as a character string, string variable and string expressions within application.

2.Dynamic SQL is a SQL statement with varying the column data,or different conditions with or without the bind variables.

3.Dynamic SQL enables DDL,DCL or session control statements to be written and executed from PL SQL.

4.It can be executed with native SQL or dbms_SQL packages.

5.In simple words dynamic SQL statements can not be compiled at compile time and it can be directly compiled and run at runtime.

6.You can use the dynamic SQL statements to make your PL SQL programs are more general and flexible.

Where to use Dynamic SQL?

Case 1 : A select statement which includes an identifier which is unknown at compile time. Example : Table name

Case 2 : A where clause in which column name is unknown at compile time.

What is Native Dynamic SQL or execute immediate statement?

It provides native support for dynamic SQL directly in PLSQL language.

It provides ability to execute SQL statements whos structure is unknown until execution time.

If the dynamic SQL statement is SELECT statement that returns the multiple rows, native dynamic sql gives you the following choices :

1. Use execute immediate statement with bulk collect into clause.
2. Use open-for, fetch and close statements.

Execute Immediate Syntax :

Real life example :

I hope you will get the basic idea about execute immediate statement. We will take the same example of deleting the table. We will handle this situation using execute immediate statement.

Create or replace procedure P_delete_any_table
( p_table_name varchar2)
is
v_no_rec number;
begin
execute immediate ‘delete from ‘||p_table_name;
v_no_rec:=sql%rowcount;
commit; –same rules for commit and rollback
dbms_output.put_line(v_no_rec ||’ record(s) deleted form ‘||p_table_name );
end;

execute P_delete_any_table(’employee’);
select * from employee; —Will show the error table not exist

–try to give dummy table which not exists
–so the parsing will be on runtime
execute P_delete_any_table(’employeesssss’);

I hope you get the information about the dynamic SQL and execute immediate statement. If you like this article or if you have issues with the same kindly comment in comments section. If you want more information and examples about the dynamic sql or execute immediate statement kindly comment in comments section.