SQL Fundamentals

Introduction to SQL and Syntax

What is SQL?

SQL stands for Structured Query Language.

is a standard programming language for accessing databases.

What can SQL do?

execute, retrieve , insert , update, delete, create, set permission.

Types of SQL Commands/Syntax

Data retrieval is a language used to allow users select records from the database

DML is a language that enables users to access or manipulate data as organized by the appropriate data model.

DDL is a language used to allow users to define the database and its objects. 

TC includes SQL transactions used to control transactions. 

DCL is a language that provides users with privilege commands

Constraints

Joins

Sorting and Filtering Data

Filtering

Use of WHERE clause

The use of LIKE, IN, EXISTS, BETWEEN, ANY, SOME, and ALL comparison conditions

Logical condition precedence: (), NOT, AND, OR

NULL values and sorting

Sorting

Use of ORDER BY clause

Sorting results in ascending and descending order 

Sorting by Multiple Columns 

Sorting by one column, then another 2. Understanding the order of sorting columns

Grouping and Aggregating Data (GROUP BY, HAVING)

Indexes

Naming Conventions

Oracle SQL Functions

There are predefined functions within SQL that can be used to perform specific actions on the data retrieved. Commonly used in our current code is:

Single Row Functions

Numeric Functions (i.e. ABS, CEIL, FLOOR, MOD, TRUNC, ROUND)

Character Functions returning Character Values (i.e. REPLACE, LPAD, RPAD, LTRIM, RTRIM, CONCAT, TRIM, SUBSTR)

Character Functions returning Numeric Values (i.e. LENGTH, INSTR)

Aggregate Functions- Returns a Single Row for the Entire Set

Typically needs the GROUP BY Clause to work (i.e. AVG, MIN, MAX, SUM, RANK, DENSE_RANK, COUNT)

Analytic Functions – Can return a multi-row data set, with one column having the calculated value

PARTITION BY

ORDER BY in the SELECT clause

OVER

WHERE clause

Used to filter data based on a set criteria with the use of different filtering conditions:

Equi (=), Anti (!=, <>), and Range (<, >, <=, >=). 

LIKE / NOT LIKE

IN / NOT IN

EXISTS / NOT EXISTS

BETWEEN

IS NULL / IS NOT NULL

Indexes

B-Tree Index

Bitmap Index

Function-Based Index

Reverse Key Index

Index-Organized Table (IOT)

Domain Index

Bitmap Join Index

Spatial Index

Stop using Dynamic SQL! Seriously.

From my experience, research and general knowledge, Dynamic SQL should be avoided as much as possible,

There are multiple reliable sources that say this, even Oracle’s revered top DBA, Tom Kyte, and Oracle’s own PL/SQL Evangelist and Oracle ACE, Steven Feuerstein has mentioned this multiple times in AskTOM and Oracle Blogs.

To list some of the disadvantages of Dynamic SQL (from research and my own inputs):

It cannot be detected by the Oracle Enterprise Manager to have bad statistics because it is not parsed before it is run. It’s basically invisible to the DB parser.

The query this is not being “recorded” in the pool, therefore, we cannot predict if this certain piece of code will perform badly or not, we cannot quantify its statistics.

There are great efficiencies to be gained by using static sql, as PLSQL will cacheopen cursors and reuse statements, even before they are run.

Dynamic SQL is not scalable. Static SQL is scalable.

Does not really have any big advantage over static SQL apart from being “flexible”, but the disadvantages greatly outweighs these advantages.

It’s actually not true that Dynamic SQL performs better, in fact: dynamic SQL is more complex than static SQL, it executes more slowly than static SQL. Especially when done wrong.

We should Re-use code whenever possible, but only when that is appropriate. And I believe we can re-use code without doing Dynamic SQL. Do we REALLY need that SQL to be dynamic? 

Dynamic SQL is only really considered when we don’t know what parameters are used before runtime, something like in OBIEE, where the user will be the one to input the columns they want and need, then run the query.

I believe we can re-write these programs to use fixed, structured, and static queries instead.

Dynamic SQL is prone to SQL-Injection in General. I don’t know if there will be a time where we will encounter this issue, but it will be a huge mess if it does happen.

Personally, I find them extremely hard to debug, hard to read, and impractical because we only find out the actual query at runtime.

We cannot see the dependencies between programs because as I mentioned, they are invisible and only appear during runtime.

It will be hard to see which programs get impacted when we change a certain code, which in turn, will result in another bug, etc. 

I can list down more Pros and Cons, but I believe these are enough details to establish that the disadvantages of Dynamic SQL outweighs the advantages.

I strongly urge that our dev teams start to write Static SQL instead of Dynamic SQL.

To be edited further...

Converting Financial Reports' Amounts to Words with Currencies

If ever you need to add currencies into Words and have to include their Decimals (i.e. Cents), this query below demonstrates that using to TO_CHAR(TO_DATE(X, 'J'), 'JSP') trick:

SELECT  AMOUNT_SIGN||AMOUNT_IN_WORDS COMPLETE_AMOUNT_IN_WORDS
FROM    (SELECT  AMOUNT
        ,   CASE WHEN SIGN_AMT is not null then SIGN_AMT||' ' END AMOUNT_SIGN
        ,   CASE WHEN DOLLAR_AMT IS NOT NULL AND CENT_AMT IS NOT NULL THEN
                DOLLAR_AMT||' AND '||CENT_AMT
                 WHEN DOLLAR_AMT IS NULL AND CENT_AMT IS NOT NULL THEN
                CENT_AMT
                 WHEN DOLLAR_AMT IS NOT NULL AND CENT_AMT IS NULL THEN
                DOLLAR_AMT
            END AMOUNT_IN_WORDS
    FROM    (select  AMOUNT
                ,   CASE WHEN INSTR(AMOUNT, '-') > 0 THEN
                        'NEGATIVE'
                    END SIGN_AMT
                ,   CASE WHEN ABS(amount) >= 1 THEN -- ONLY THE ONES WITH WHOLE NUMBERS
                        to_char( to_date(TRUNC(ABS(amount)), 'J'), 'JSP') || ' BAHT' 
                    END DOLLAR_AMT
                ,   CASE WHEN INSTR(AMOUNT, '.') > 0 THEN -- ONLY THE ONES WITH DECIMALS
                        TO_CHAR(to_date(RPAD(SUBSTR(AMOUNT, INSTR(AMOUNT, '.') + 1), 2, '0'), 'J'), 'JSP') || ' SATANG'
                    END CENT_AMT
            from    (select 30 amount from dual
                    union 
                    select 30.500 amount from dual
                    union 
                    select 0.501 amount from dual
                    union
                    select -30 amount from dual
                    union 
                    select -30.500 amount from dual
                    union 
                    select -0.501 amount from dual
                    )
                )
            );

This sample above shows six scenarios:

1. Positive Whole Numbers only
2. Positive Whole Numbers with Decimals
3. Positive Decimals Only
4. Negative Whole Numbers only
5. Negative Whole Numbers with Decimals
6. Negative Decimals Only

Just change the Currencies if needed.

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Oracle SQL Tuning Fundamentals

Introduction & Objectives

Structured Query Language (SQL) is basically a language for storing, manipulating and retrieving data in databases. Although there are a lot of “flavors” of SQL (MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres). The American National Standards Institute (ANSI) has set a standard in 1986 that most RDBMS’s employ.
While most simple SQL Queries are straight-forward and quick to execute, there are instances wherein we experience a slowness in the query execution. In this deck, we will explore, identify and address the numerous factors that come into play in this performance degradation.

Target Audience

The target audience of this deck would be individuals with sufficient experience in writing SQL queries and have sufficient knowledge in Database Fundamentals.
You should be able to:

• Understand why SQL Tuning is Important
• Understand when to Tune a SQL Script
• Understand the needed skills and knowledge to Tune SQL
• Understand how to achieve your tuning goals

Why should we tune SQL?

Two Words: Optimum Performance!

• Query Performance can be affected by the following:
• Hardware
• Network
• Application
• Program (i.e. PL/SQL Code)
• SQL Code

When the underlying SQL is Tuned effectively, The PL/SQL program is tuned as well! I know what you’re thinking:

“Isn’t there an automatic way to Tune SQL?”

• Yes, but it is costly and proprietary.
• Yes, but sometimes its not good enough.
• Yes, but sometimes it’s even worse than the manual method.

“If that’s the case, then what’s the solution?”

• Manual SQL Tuning Addresses the concerns above. Why? Because:
  1. It’s Free
  2. Improves coding skill
  3. Provides more control over your code

When do we need to Tune SQL?

Ideally, the time we develop code is the time we need to consider efficient and fast queries.

• A good, well-thought out and defined structure will yield the best results when it comes to performance.
• Planning the table structure, indexes, data types and unique identifiers and the data is highly valuable when it comes to performance.

However, if it is unavoidable for the objects have already been existing for some time, tuning is still possible. Consider tuning when the following occurs:

1. There is a Change of Hardware
   • If there is a hardware update in terms of processor, memory, storage, it might affect the performance of the SQL Query.
   • Usually occurs during an upgrade or downgrade of the actual server.
2. There is a Change of Table Structure (i.e. New or Modified Columns, Indexes, etc.)
   
   • If there are changes in the table that is referenced by the query, this may impact the retrieval of data.
   • If indexes are changed, there will be a significant performance impact to the query.
3. Significant change in the amount of Data
   • Data count significantly affects the query’s performance, affecting cardinality, selectivity and cost.
4. Change of Query Script
   • If a Report’s query has been changed, there is a high chance that the performance of the report will also change

What you need to know before you tune a SQL?

Before doing the actual work of tuning a script, one needs to know first the environment. This is highly important as this will lead you to the use best approach for the current problem at hand and ensuring that it doesn’t happen in the future.

Below are the things you need to know before turning the script.

1. Knowledge and Skill in SQL and Database Fundamentals
   • A good grasp of SQL skill and knowledge will be the first and foremost thing you need to know to tune SQL.
   • What SQL operators you need to use and what clauses you need to add will greatly reduced the work you need to put into tuning a script.
2. Familiarity with the Table Structure
   • Knowing what the table is used for, its structure, and its data is essential for effective and efficient tuning.
   • Correct Identification of each column’s use allows us to formulate a better plan to tune the SQL.
3. Familiarity with Index Types and the Explain Plan
   • Knowing the different index types, their usages and their advantage and disadvantages is highly recommended for you to identify if the index you want to use is actually being used by the explain plan.

How to Tune SQL?

Below are my personal guidelines on how to tune SQL scripts:

1. Identify High-Load SQLs
   1. Unless specified, you would need to figure out the under performing queries.
   2. There are numerous methods to know the Top High Load SQL statements in a Database

      1. Using the Oracle Enterprise Manager (OEM)

      2. Using the Dynamic Performance Views such as v$SQL and v$SQL_PLAN

2. Measure current performance metric
   1. For us to compare if the changes that will be applied to the script has any real improvement, it is imperative that we take a snapshot of how the performance was before it was modified.
   2. We would then need to take either of the following before Modification of the SQL Script:
      1. AWR
      2. SQLHC
      3. Explain Plan
3. Identify the root cause
4. Analyze and Identify the Best approach
   1. Re-write the script?
   2. Correct the Data?
   3. Add Indexes?
   4. Change the Table Structure?
   5. Split the table?
   6. Convert to Materialized Views?
   7. Use Hints?
   8. Change the Database Parameters?
5. Test, Test and Test
6. Measure the new performance metric
   1. Similar to Step #2, we would then need to take a snapshot of the performance after the script modification. The same tools apply to getting the metrics.

Cardinality, Selectivity and Cost

Oracle uses the Cost Estimator, to estimate the resources that will be used to execute a given query using the following measurements:

1. Selectivity
   1. The percentage of rows in the row set that the query selects, with 0 meaning no rows and 1 meaning all rows. Selectivity is tied to a query predicate, such as WHERE last_name LIKE 'A%', or a combination of predicates. Simplest term: Uniqueness
   2. A record becomes more selective as the selectivity value approaches 0 and less selective (or more unselective) as the value approaches 1.
2. Cardinality
   1. The cardinality is the estimated number of rows returned by each operation in an execution plan. Cardinality can be derived from the table statistics collected by DBMS_STATS, or derived after accounting for effects from predicates (filter, join, and so on), DISTINCT or GROUP BY operations, and so on.
3. Cost
   1. This measure represents units of work or resource used. The query optimizer uses disk I/O, CPU usage, and memory usage as units of work.

These three are very important for the estimator in figuring out how to map the Explain Plan, what access paths to be used and what Indexes should be used to execute the query.

Indexes

What are Indexes?

By definition: An index is a schema object that contains an entry for each value that appears in the indexed column(s) of the table or cluster and provides direct, fast access to rows.
Oracle Database supports the following indexes:

1. Binary Tree (B-Tree Index)
   • These indexes are the standard index type and uses only 1 column
   • They are excellent for primary key and highly-selective indexes.
   • B-tree indexes have the following subtypes
     1. Composite Indexes
     2. Index-organized tables (IoT)
     3. Reverse key indexes
     4. Descending indexes
     5. B-tree cluster indexes
2. Bitmap Index - In a bitmap index, an index entry uses a bitmap to point to multiple columns. In contrast, a B-tree index entry points to a single row. A bitmap join index is a bitmap index for the join of two or more tables.
3. Partitioned Indexes
4. Function-based Indexes
5. Application Domain Index - An Index that is application-specific, can be used inside or outside the Oracle Database.

Binary Tree (B-Tree Index)

Bitmap Index

Partitioned Indexes

Function-based Indexes

Application Domain Index

The Explain Plan

Access Paths

Types of Access Paths

Access Paths (aka Execution Paths), is basically the "road" that the parser plans to traverse to execute the query script, step-by-step.
An Access Path is shown inside an Explain plan. Treat Explain Plan as the Map, and the Access Path as the Road.
Depending on your various factors (i.e. Tables, Complexity, Database Parameters), the Explain plan will show you the recommended approach the parser will use to execute your query.

There are multiple types of Access Paths, as listed below:

• Full Table Scans
• Table Access by RowId
• Sample Table Scans
• By Index
  • Index Unique Scans
  • Index Range Scans
  • Index Full Scans
  • Index Fast Full Scans
  • Index Skip Scans
  • Index Join Scans
• By Bitmap
• Bitmap Index Single Value
• Bitmap Index Range Scans
• Bitmap Merge
• Bitmap Index Range Scans
• Cluster Scans
• Hash Scans

References

• Manuel, J., Oracle SQL Tuning Training (Oracle University – TechEdge Solutions)
• http://stackoverflow.com/questions/10621077/what-is-cardinality-in-databases
• http://docs.oracle.com/cd/A87860_01/doc/inter.817/a77063/aoptim2.htm
• https://docs.oracle.com/database/121/TGSQL/tgsql_optcncpt.htm
• https://docs.oracle.com/cloud/latest/db112/CNCPT/indexiot.htm#CNCPT1182
• https://use-the-index-luke.com/
• https://www.w3schools.com/sql/
• https://www.iso.org/standard/63555.html
• https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5010.htm#:~:text=Oracle%20Database%20supports%20several%20types,key%20value%20as%20a%20bitmap

Print XML to DBMS_OUTPUT.PUT_LINE or FND_FILE.PUT_LINE

I recently faced an issue in needing to generate a huge XML file without using the UTL_FILE package.

Since I was working on Oracle EBS and/or Fusion, I can only use FND_FILE.PUT_LINE.

FND_FILE is a seeded package within Oracle Applications (both EBS and Fusion) and prints to a specific directory in the file system. Two common usages are:

fnd_file.put_line (fnd_file.OUTPUT, p_message );

and 

fnd_file.put_line (fnd_file.LOG, p_message );

p_message has a data type of VARCHAR2. This means it's limited to 32767 bytes. So how can we print a huge XML when we're limited to 32767 characters? The Answer is Chunking.

Taken from a StackOverflow post, we will chunk the XML into pieces that FND_FILE.PUT_LINE can process.

Below is an Example:

declare
  
  xml_out xmltype;
  
  -- Internal procedure to print a CLOB using dbms_output in chunks
  procedure print_clob( p_clob in clob ) is
    v_offset number := 1;
    v_chunk_size number := 10000;
  begin
    loop
      exit when v_offset > dbms_lob.getlength(p_clob);
      dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
      v_offset := v_offset + v_chunk_size;
    end loop;
  end print_clob;
  
begin

        -- generate an XML --
	select  xmlAgg(xmlconcat(xmlelement("dbaObjects"
            ,   xmlelement("objName", object_name)
            ,   xmlelement("objType", object_Type)
            )))
	into	xml_out
	from    dba_objects;
	print_clob(xml_out.getClobVal);
	
end;

However, if you want to use it in Oracle Apps (EBS and/or Fusion), you can use FND_FILE.PUT_LINE instead of DBMS_OUTPUT.

	procedure print_clob( p_clob in clob ) is
		v_offset number := 1;
		v_chunk_size number := 10000;
	  begin
		loop
		  exit when v_offset > dbms_lob.getlength(p_clob);
		  --dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
		  fnd_file.put_line (fnd_file.OUTPUT, dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
		  
		  v_offset := v_offset + v_chunk_size;
		end loop;
	end print_clob;	  

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)
Subscribe to The Oracle Nerd on Youtube! https://www.youtube.com/c/OracleNerd

Script to Delete Concurrent Programs from Oracle E-Business Suite 11i and R12

There might be instances wherein one needs to remove an invalid Concurrent Program setup in Oracle E-Business Suite, including its Concurrent Executable setup. This cannot be done from the front-end application, as it only allows you to disable the Concurrent Program.

Fortunately, we have the option to do this from the back-end:

declare

    cursor conc_cur
    is
    SELECT  appl.application_short_name application
          , CONC.USER_CONCURRENT_PROGRAM_NAME
          , EXEC.EXECUTABLE_NAME
          , CONC.CONCURRENT_PROGRAM_NAME
          , lookup.meaning execution_method_code
          , EXEC.EXECUTION_FILE_NAME      
    FROM    FND_CONCURRENT_PROGRAMS_VL CONC
          , FND_EXECUTABLES EXEC
          , FND_APPLICATION_VL APPL
          , fnd_lookup_values lookup
    WHERE   EXEC.EXECUTABLE_ID = CONC.EXECUTABLE_ID
    AND     EXEC.EXECUTION_FILE_NAME = :pkg_name.procedure_name -- change this to the Concurrent Program Executable
    AND     APPL.APPLICATION_ID = EXEC.APPLICATION_ID
    AND     LOOKUP.LOOKUP_CODE = EXEC.EXECUTION_METHOD_CODE
    and     LOOKUP.lookup_type='CP_EXECUTION_METHOD_CODE';

Begin

    for c1 in conc_cur loop

        fnd_program.delete_program(c1.CONCURRENT_PROGRAM_NAME, c1.application);
        fnd_program.delete_executable(c1.EXECUTABLE_NAME, c1.application);
    
    end loop;
    
    commit;
    
End;

Please note that you would need to have access to the APPS schema or a user with similar or higher privileges.

Update: June 28, 2019: For EBS 12.2.5, the above query might not return any values because of the Views. Use the following code instead:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

declare cursor conc_cur is SELECT appl.application_short_name application --, CONC.USER_CONCURRENT_PROGRAM_NAME , EXEC.EXECUTABLE_NAME , CONC.CONCURRENT_PROGRAM_NAME , lookup.meaning execution_method_code , EXEC.EXECUTION_FILE_NAME FROM FND_CONCURRENT_PROGRAMS CONC , FND_EXECUTABLES EXEC , FND_APPLICATION APPL , fnd_lookup_values lookup WHERE EXEC.EXECUTABLE_ID = CONC.EXECUTABLE_ID AND EXEC.EXECUTION_FILE_NAME = 'GLMSUMMACCNEW' AND APPL.APPLICATION_ID = EXEC.APPLICATION_ID AND LOOKUP.LOOKUP_CODE = EXEC.EXECUTION_METHOD_CODE and LOOKUP.lookup_type='CP_EXECUTION_METHOD_CODE'; Begin for c1 in conc_cur loop fnd_program.delete_program(c1.CONCURRENT_PROGRAM_NAME, c1.application); fnd_program.delete_executable(c1.EXECUTABLE_NAME, c1.application); end loop; commit; End;

For more full-detailed Tutorials and Tips, check out #TheOracleProdigy at https://lifeofanoracleprodigy.blogspot.com/
Follow The Oracle Prodigy on Facebook (https://www.facebook.com/theOracleProdigy/) and Twitter (https://twitter.com/D_OracleProdigy)

Script to assign a responsibility to an application user in Oracle E-Business Suite

Use the Script below to assign a certain responsibility to an application user in Oracle E-Business Suite, applicable to both 11i and R12:

DECLARE 

    cursor add_resp is
 SELECT  DISTINCT 
         APPL.APPLICATION_SHORT_NAME
       , RESP.RESPONSIBILITY_KEY       
       , SEC.SECURITY_GROUP_KEY
 FROM    FND_RESPONSIBILITY_VL RESP
       , FND_APPLICATION_VL APPL
       , FND_SECURITY_GROUPS_VL SEC
       , FND_USER_RESP_GROUPS RESPG
 WHERE   RESP.RESPONSIBILITY_NAME = 'System Administrator'
 AND     RESP.APPLICATION_ID = APPL.APPLICATION_ID
 AND     SEC.SECURITY_GROUP_ID = RESPG.SECURITY_GROUP_ID
 AND     RESPG.RESPONSIBILITY_APPLICATION_ID = RESP.APPLICATION_ID
 AND     RESP.RESPONSIBILITY_ID = RESPG.RESPONSIBILITY_ID;

 lc_user_name varchar2(100) := 'ISIPJ85';
 
BEGIN 

 FOR i in add_resp loop

      fnd_user_pkg.addresp 
      (  
       username             => lc_user_name, 
         resp_app             => i.APPLICATION_SHORT_NAME
     ,resp_key             => i.RESPONSIBILITY_KEY
     ,security_group    => i.SECURITY_GROUP_KEY
     ,description          => NULL
     ,start_date           => SYSDATE
     ,end_date             => NULL 
     );
    
    end loop;

 COMMIT; 

EXCEPTION 
 WHEN OTHERS THEN 
    ROLLBACK; 
    DBMS_OUTPUT.PUT_LINE('Error Encountered: '||SQLERRM); 
END; 
/

SHOW ERR;

Take note that you would need enough database privilege to the APPS schema to run this script.

Script to Reset Password in Oracle E-Business Suite 11i and R12

Use the Script below to reset a User's password in Oracle E-Business Suite, applicable to both 11i and R12:

declare

 v_user_name  varchar2(100) := 'SYSADMIN';
 v_unencrypted_password varchar2(100) := 'oracle123';

begin

 apps.nd_user_pkg.updateuser
 (
     x_user_name   => v_user_name
   , x_owner    => null
   , x_unencrypted_password  => v_unencrypted_password
   , x_password_date   => sysdate
 );
  
 commit;
 
end;
/

Take note that you would need enough database privilege to the APPS schema to run this script.