Skip to main content

Dynamic SQL in Oracle PL/SQL #DOTC_Mdb

                                   Dynamic SQL in Oracle PL/SQL

Hello Everyone,

Today we will discuss about Dynamic SQL in Oracle PL/SQL and its benefits. Before reading this blog I will suggest you to see my previous blogs related with SQL and PL/SQL blog for better understanding as some of PL/SQL feature I will use in this blog. Let’s start to understand Dynamic SQL in Oracle. Dynamic SQL in Oracle PL/SQL used for generating and running SQL statements at run time. Through Dynamic SQL we can use DDL statements in PL/SQL block.  Dynamic SQL is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements.

We can write Dynamic SQL in two ways.

1. Native Dynamic SQL

2. DBMS_SQL

1. Native dynamic SQL in PL/SQL language enables for building and running dynamic SQL statements at run time. Dynamic SQL uses the EXECUTE IMMEDIATE command to create and execute the SQL at run time, the data type and number of variable that to be used at a run time need to be known before. Dynamic SQL gives better performance and less complexity as compares to DBMS_SQL.

2. DBMS_SQL package in Dynamic SQL is an API for building, running, and describing dynamic SQL statements. DBMS_SQL package is a Dynamic SQL defines an entity called a SQL cursor number. Because the SQL cursor number is a PL/SQL integer, you can pass it across call boundaries and store it.

Let’s start with Native Dynamic SQL with simple example in PL/SQL Anonymous block.

Note: We can use Dynamic SQL by using EXECUTE IMMEDIATE and open-for, fetch and Close block.

Syntax is as below:

EXECUTE IMMEDIATE (<SQL Statement>)

[INTO<variable>]

[USING <bind_variable_value>]

Here the EXECUTE IMMEDIATE statement is the means by which native dynamic SQL processes most dynamic SQL statements. If the dynamic SQL statement is self-contained (that is, if it has no placeholders for bind variables and the only result that it can possibly return is an error), then the EXECUTE IMMEDIATE statement needs no clauses.

Example: Creating object (Table) in PL/SQL Block.

DECLARE

V_QUERY VARCHAR2(200);

begin

 V_QUERY :='CREATE TABLE TEST (EMPNO NUMBER ,NAME VARCHAR2(20) , ADDRESS VARCHAR2(30))';

 EXECUTE IMMEDIATE V_QUERY;

 END;

 /

 

After Executing above PL/SQL block TEST table is created by using Dynamic SQL at run time. Below is the output of above query.



Note : You can use any DDL like alter , drop , etc by using Execute Immediate command.

Writing another Dynamic SQL by using EXECUTE IMMEDIATE to see the count of emp table from database. Below is the PL/SQL block

DECLARE

V_QUERY VARCHAR2(200);

V_COUNT NUMBER(3);

BEGIN

 V_QUERY :='SELECT COUNT(*) FROM EMP';

 EXECUTE IMMEDIATE V_QUERY INTO V_COUNT;

DBMS_OUTPUT.PUT_LINE('TOTAL RECORDS IN EMP TABLE IS '||' '||V_COUNT);

 END;

 /

 

Below is the output of above PL/SQL block.

 


Simple insert statement by using Dynamic SQL is as below:

DECLARE

V_TAB VARCHAR2(10):='TEST';

V_EMPNO NUMBER:=101;

V_NAME VARCHAR2(20):='DOTC';

V_ADDRESS VARCHAR2(30) :='MADHUBANI';

V_QUERY VARCHAR2(100);

BEGIN

 V_QUERY :='INSERT INTO '||V_TAB||' VALUES ('||V_EMPNO||','||''''||V_NAME||''''||')';

DBMS_OUTPUT.PUT_LINE(V_QUERY);

 EXECUTE IMMEDIATE V_QUERY;

 END;

 /

 

One record is inserted in a table and output of above PL/SQL block is as below.



Now we will see performance of Query by using Dynamic SQL with Bulk Collect and simple cursor to see the performance of Program. Below is the code.

//DYNAMIC SQL WITHOUT BULK COLLECT

DECLARE

V_QUERY VARCHAR2(1000);

V_REC EMP%ROWTYPE;

V_REFCUR SYS_REFCURSOR;

BEGIN

   V_QUERY :='SELECT * FROM EMP WHERE ROWNUM<=7';

     OPEN V_REFCUR FOR V_QUERY;

       LOOP

          FETCH V_REFCUR INTO V_REC;

          DBMS_OUTPUT.PUT_LINE(V_REC.ENAME||' '||V_REC.JOB||' '||V_REC.HIREDATE);

           EXIT WHEN V_REFCUR%NOTFOUND;

         END LOOP;

      END;

/

 

Below is the output of above PL/SQL code.

 


Now writing same code for getting the count from emp table with  BULK COLLECT to check the performance.

//DYNAMIC SQL WITH BULK COLLECT

DECLARE

  TYPE ABC IS TABLE OF EMP%ROWTYPE;

  V_REC    ABC;

  V_CUR  SYS_REFCURSOR;

BEGIN

  OPEN V_CUR FOR 'SELECT * FROM EMP';

  FETCH V_CUR  BULK COLLECT INTO V_REC;

  CLOSE V_CUR;

  DBMS_OUTPUT.PUT_LINE('DYNAMIC FETCH FROM TABLE : ' || V_REC.COUNT);

  EXECUTE IMMEDIATE 'SELECT * FROM EMP'

  BULK COLLECT INTO V_REC;

  DBMS_OUTPUT.PUT_LINE('DYNAMIC EXECUTE: ' || V_REC.COUNT);

END;

/

Below is the output of above query :

 


Writing Dynamic SQL with BULK collect using loop to see the performance of fetching records from emp table .

DECLARE

      V_QUERY VARCHAR2(1000);

      TYPE ABC IS TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;

      V_REC ABC;

      V_CUR SYS_REFCURSOR;

   begin

      V_QUERY := 'SELECT * FROM  EMP';

      OPEN V_CUR FOR V_QUERY;

      LOOP

              FETCH V_CUR BULK COLLECT INTO V_REC LIMIT 7;

              FOR I IN 1..V_REC.COUNT

             LOOP

                    DBMS_OUTPUT.PUT_LINE(V_REC(I).ENAME||' '||V_REC(I).JOB||' '||V_REC(I).HIREDATE);

                   

           END LOOP;

              EXIT WHEN V_CUR%NOTFOUND;

      END LOOP;

    CLOSE V_CUR;

   END;

/

 

Below is the output as this taken only 0.01 sec to retrieve the record.

 


Note:  You can write Multiple query using native Dynamic SQL to fetch records and also create and modify objects at run time in PL/SQL block.

DBMS_SQL is a package supplied by Oracle Database to perform dynamic SQL operations. DBMS_SQL became a method of last resort for dynamic SQL. We will Discuss more on DBMS_SQL in next blog.

Please go through this blog as the PL/SQL language makes it easy to implement dynamic SQL requirements. When it comes to querying multiple rows of data from a dynamic query, you can choose between EXECUTE IMMEDIATE, OPEN FOR, and DBMS_SQL. Also try to fetch huge data from a table and see the performance of using Dynamic SQL also you can create and modify the object at run time by using Dynamic SQL . This blog is important for fresher’s as well as experienced and also good for people who is preparing for interview . In coming blog I will come up with some practical example of Dynamic SQL DBMS_SQL . If you have any questions let me know.

Thanks.

 

Comments

  1. Glad to find this. Your site very helpful and this post gives lots of information. Do share more updates.
    PHP course in Chennai
    PHP Training Online
    PHP course in Coimbatore

    ReplyDelete
  2. This data is magnificent. I am impressed with your writing style and how properly you define this topic. After studying your post, my understanding has improved substantially.

    Best Online Oracle Training Institute in Delhi, NCR
    Certified Online Python Training Institute in Delhi, NCR

    ReplyDelete
  3. Great Post! Thanks for sharing. Keep sharing such information.

    Apply Now for Oracle Training in Noida

    For more details about the course fee, duration, classes, certification, and placement call our expert at 70-70-90-50-90

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete

Post a Comment

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li