How to convert complex Oracle sqls in the Incorta way

[amit_kothari]

Introduction

This article elaborates the steps to convert complex Oracle SQLS or views to Incorta.

What you should know before reading this article

We recommend that you be familiar with these Incorta concepts before exploring this topic further 

• Designing Spark MVs

Applies to

These concepts apply to all Incorta versions.

Let’s Go

This scenario is commonly encountered during Incorta  with Oracle EBS implementations. Ingesting data from complex SQL queries or views often proves inefficient, leading to slow performance and challenges with incremental data extraction.

Our goal is to streamline this process by leveraging Incorta’s approach. Whenever possible, we recommend performing the data modeling directly in Incorta. However, if modeling poses difficulties due to various constraints, the alternative would be to convert the SQL into a materialized view (MV) to optimize the process.

Steps to convert complex Oracle sql to MV

• Table Identification
  • Identify table names from the Oracle query.
  • Ensure all tables exist in the Incorta schemas, you can start with the EBS data app schemas
  • Add columns to existing tables
• Convert oracle joins to ANSI standard joins - for eg LEFT OUTER JOIN, INNER JOIN etc
• Syntax Transformation
  • SYSDATE  to CURRENT_DATE
  • NVL to COALESCE
  • DECODE() to CASE()
  • Replace TRUNC(Datetime_field) to CAST(Datetime_field as date) 
  • Replace To_Char(Sysdate, 'dd-mon-rr') to To_Char(current_date, 'mm-yyyy')
  • Convert TO_DATE (period_name, 'MON-RRRR') as CAST(date_format(to_date(PERIOD_NAME, 'MMM-yy'), 'yyyy-MM-dd') AS TIMESTAMP)
  • TO_DATE and TO_CHAR() are supported but syntax vary, for eg to_date('03/02/2022', 'MM/dd/yyyy')
  • Replace fields like this TO_DATE(SYS_CONTEXT ('XXIN_DISCO_CONTEXT','P_DATE'),'DD-MON-RRRR') to CURRENT_DATE
  • Round(col1,2) should be converted to Round(col1)
  • Remove parameters like :1. :2 etc from sql
  • Functions in joins are not supported ,  
  •  Convert out pl/sql function calls in the select clause
  • Replace USERENV (‘LANG’) with ‘US’
  • Replace  ROWNUM = 1 with LIMIT 1 for eg
• Locate Oracle-specific syntax in the query and Replace with appropriate Spark SQL equivalents.
• Oracle pl/sql function Conversion
  • Create a subquery and join it to main table
  • Or Convert that function to another Incorta table and make it a parent of the MV
  • Identify Oracle functions used.
  • Collect corresponding function queries.
  • Adapt them for use in Spark SQL
• Scalar Subqueries Optimization
  • Identify scalar subqueries (inside SELECT clause).
  • Write appropriate joins in the FROM clause.
  • Enhance query performance.
• Validation and Execution
  • Create a Materialized View (MV) or a table.
  • Execute the converted query.
  • Validate resultant table data against the original table.

Please check the sample Oracle EBS view and converted MV attached to the article

Related Material

• Best practices