Diksha IT Training Centre

                                    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 ti

  Importance of SQL  Character Functions  in Oracle Database   Hello Everyone, Today we will discuss about SQL functions in Oracle which plays important role in writing SQL queries. Before Starting to read this blog I suggest you to please go through my previous basic SQL blog for your reference. We will discuss the below Functions in today’s blog. 1. ROUND 2. TRUNC 3. CEIL and FLOOR 4. SIGN AND ABS 5. NVL and NVL2   Let’s discuss Round Function in SQL: ROUND function is used to round a numeric value to a specified precision. If the value is less than 5, the function rounds down. If the value is greater than or equal to 5, the function rounds the value up. The function contains two parameters: The first is the value or column name, and the second is the precision. Round Function also used to suppress decimal values. ROUND Function enables you to round a numeric value that is returned in a result set. Example of ROUND function to round one number up and another

  BULK COLLECT IN ORACLE AND IT’S ADVATAGE IN DATABASE   Hello Everyone, In this blog we will discuss about BULK collect and its importance in Oracle Database. First we need to understand what is Bulk collect and how it’s improves the performance of a improving the speed of data retrieval. Before going through this I suggest you to please go through my previous blogs related with PL/SQL block for better understanding. BULK Collect: BULK COLLECT reduces context switches between SQL and PL/SQL engine and allows SQL engine to fetch the records at one time only. In simple word we can say BULK COLLECT SELECT statements that retrieve multiple rows with a single fetch, improving the speed of data retrieval. BULK COLLECT fetches the record in BULK, the INTO clause should always contain a collection type variable. The main advantage of using BULK COLLECT is it increases the performance by reducing the interaction between SQL and PL/SQL engine to improve the performance of our program.