ODI 12C – Slowly Changing Dimensions

In this blog we will explore how we can setup Slowly Changing Dimension SCD2 in ODI 12c.

Slowly Changing Dimensions: Attributes that changes slowly over the period of time, rather than changing frequent. In order to generate the historical reports in DW we need to track the history of dimension.

Type 1: When there is no need to store historical data in the dimension table. So process overwrites the old data with the new data in the dimension table. This is normal incremental update.

Type 2: When there is need to stores the entire history of the attribute in the dimension table in the form of separate rows. Using dates & current flag you can identify which records is currently active. See below example.

scd2

Type 3: When there is need to store current as well as previous value of the attribute. In this scenario two separate columns required in table to store current as well as previous value. See below example.

scd3


Import the IKM: Oracle Slowly Changing Dimension

IKM

Step 1: Create the tables & reverse engineered the same in your model. I have created below tables in localhost database.

 

  CREATE TABLE "SRC_EMP_ASSIGNMENT"
   (	EMP_ID VARCHAR2(10 CHAR),
	ASSIGNMENT_ID VARCHAR2(150 CHAR)
   );
  CREATE TABLE "TRG_EMP_ASSIGNMENT"
   (  ROW_WID NUMBER,
      EMP_ID VARCHAR2(10 CHAR),
      ASSIGNMENT_ID VARCHAR2(150 CHAR),
      ASSIGNMENT_TYPE VARCHAR2(5 CHAR),
      CURRENT_FLAG CHAR(1 CHAR),
      EFF_START_DATE DATE,
      EFF_END_DATE DATE
   );
 CREATE SEQUENCE SEQ_TRG_EMP_ASSIGNMENT START WITH 1;

 

Step 2: Open the target data-store and change the OLAP type to “Slowly Changing Dimension“.

1

Step 3: Now you have to set the SCD behavior of columns for target SCD2 data-store.

  • Surrogate Key – Unique key for table which does not have any business meaning.
  • Natural Key – Unique Key from the source data.
  • Current Record Flag – Indicate whether the record is active or old.
  • Starting Timestamp – Indicate the starting date of that entry
  • Ending Timestamp – Indicate the ending date of that entry
  • Overwrite on Change – If you don’t want to enable on SCD2 on any column.
  • Add Row on Change – If you want to enable on SCD2 on any column.

In this example, for target table : TRG_EMP_ASSIGNMENT

  • Surrogate KeyROW_WID
  • Natural KeyEMP_ID
  • Current Record FlagCURRENT_FLAG
  • Starting TimestampEFF_START_DATE DATE
  • Ending TimestampEFF_END_DATE DATE
  • Overwrite on ChangeASSIGNMENT_TYPE (Don’t want to track history for this)
  • Add Row on Change ASSIGNMENT_ID

Open the data-store, attributes & select the SCD behavior for every column.

8

Step 4: Create Mapping: See below execute on hints for columns.

mappig

Executing Mapping 1st Time: Initial Run

Source Date:

src

Target: See the end dates of all the records, default 01-01-2400 EFF_END_DATE.

trg

Executing Mapping 1st Time: Incremental Run

Source Data:

src2

Target: 

trg2

In above screenshot you can notice that previous assignment for EMP_ID 100 has been updated with CURRENT_FLAG=0 and EFF_END_DATE updated. New assignment for EMP_ID 100 has been created with CURRENT_FLAG=1 and default 01-01-2400 EFF_END_DATE.

odiopr


Thanks!

Happy Learning! Your feedback would be appreciated!

ODI 12c | Master & Work Repository Setup

ODI 12c | Master & Work Repository Setup

In this blog we will see step by step how we can setup the ODI 12c Master & Work Repository:


Prerequisite: Setup the repository schema first in database(Supported by ODI). I have created the schema in the Oracle database. Refer the blog for creating new schema in Oracle schema Oracle Database 11g | Create User

CREATE USER ODI_REPO IDENTIFIED BY welcome;
GRANT CONNECT, RESOURCE, DBA TO ODI_REPO;
GRANT CREATE SESSION TO ODI_REPO;
GRANT DBA TO ODI_REPO;
GRANT ALL PRIVILEGES TO ODI_REPO;

You can create separate schema for master repository as well as work repository, but in this blog we going to use only one schema for both master as well as work repository. Refer this blog for more understanding of ODI Repositories | Master & Work Repository

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Setup Master Repository

Open the ODI 12c Studio. Go to File -> New. Select “Create  New Master Repository“.

1

Step 1. Provide all the details for repository connection.  Like schema name, password, JDBC details, DBA credential details etc. See below screenshot.

jdbc:oracle:thin:@localhost:1522/orcl.168.56.1

2

After providing all the details, test the connection. Click next.

Step 2: Provide authentication mode using ODI authentication, provide supervisor password & note it down somewhere as it is required for login setup.

3

Click on finish after providing supervisor password. It will start the setup of the master repository. It will take few minutes to complete the setup of master repository.

4

5

master done

Here the master repository has been created. Now you have create login for the master repository: Follow below steps


Setup Master Repository Login

Step 1: Open the ODI 12c Studio. Go to File -> New. Select “Create  New ODI Repository Login“.

master login

Step 2. Provide the detail like login name (provide any name), supervisor name & supervisor password you provided at the time of repository setup.

For Database Connection: Provide details for master reposotory schema details. Select radio button master repository. Test the connection.

login2

Click on ‘Ok’. You are done with the master repository login setup. Now you have to login your master repository & create the work repository inside it.

In ODI 12c Studio. Click on connect repository.

connect

Below screen will come with all pre-populated data, you just have to click ‘Ok’. Now you are inside the master repository. Check the repository details ODI->’Repository Connection Information’

ml2

ml3

Here you have logged in ODI Master Repository. Notice here the designer navigator is by default disabled. Basically you cannot do development work in master repository, you have to create work repository for this master to start the development work.

Now you have to setup work repository under this master repository: Follow below steps


Setup Work Repository:

Checkout this blog for understanding of relationship between ODI Master & Work Repository.

Go to Topology Navigator, Repositories -> New Work Repository

work1

Notice here I have provided the same schema, you can create separate schema for work schema which is recommended.

work2

Provide the password for work repository& select development type. As you are going to develop ETL in this work repository. For productions & UAT generally we select ‘Execution‘ as work repository type where Designer navigator is by default disabled. It means you can develop object in Execution type work repository.

work3

Click on Finish.

work4

It will ask you to setup the login for work repo. Click on yes & provide the login name.

worklogin

worklogin2

workdone

Here the work repository setup &  login has been done & disconnect from master repository and login the work repository.

connect

Check the repository details ODI->’Repository Connection Information‘.

Notice here the designer navigator is not disabled. As this is as development type work repository, you can start the development work by creating new project.

Dataserver, Physical Schema, Logical Schema & Context | Oracle Data Integrator 11g

Model & Datastore| Creating and Reverse-Engineering| Oracle Data Integrator 11g

worklogin3


Thanks!

Happy Learning! Your feedback would be appreciated!

PL/SQL Scenario Questions – Part 1

PL/SQL Scenario Questions – Part 1

In this blog we will explore some PL/SQL – Scenario questions.


Scenario 1. Write PL/SQL program to show overloading concept of oops in PL/SQL.

Solution 1: See below example for PL/SQL package PKG_TEST, it has two procedure. Both procedure has same name & same input parameter name. But parameter type is different. This is example of overloading of objects in PL/SQL.

CREATE OR REPLACE PACKAGE PKG_TEST
AS
  PROCEDURE PRC_SAMPLE(INPUT_ID IN VARCHAR2);
  PROCEDURE PRC_SAMPLE(INPUT_ID IN NUMBER);
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
  PROCEDURE PRC_SAMPLE(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
   DBMS_OUTPUT.PUT_LINE ('Calling VARCHAR2 procedure');
  END PRC_SAMPLE;
  PROCEDURE PRC_SAMPLE(INPUT_ID IN NUMBER)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Calling number procedure');
  END PRC_SAMPLE;
END PKG_TEST;
/

pls0

Scenario 2. Write PL/SQL block which raise the user-defined exception if the day is weekend.

SET SERVEROUTPUT ON;
DECLARE
  exp_weeknds EXCEPTION;
BEGIN
  IF TO_CHAR(SYSDATE, 'DY')IN ('SUN','SAT') THEN
    raise exp_weeknds;
  ELSE
    DBMS_OUTPUT.put_line( 'Record processed!');
  END IF;
EXCEPTION
WHEN exp_weeknds THEN
  dbms_output.put_line('Cannot process the record as its weekend!');
END ;
/

pls1

Scenario 3. Write PL/SQL program to show hiding concept of oops in PL/SQL.

Solution 3: See below example for PL/SQL package PKG_TEST, it has two procedure in its body but only one is declared in package spec. The one which is not declared but present in the body is basically hidden from outside world & can be used internally only.

CREATE OR REPLACE PACKAGE PKG_TEST
AS
  PROCEDURE PRC_TEST(INPUT_ID IN VARCHAR2);
END PKG_TEST;
CREATE OR REPLACE PACKAGE BODY PKG_TEST
AS
  PROCEDURE PRC_INTERNAL(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
   -- This procedure is not declared in package specification.
   DBMS_OUTPUT.PUT_LINE ('Calling PRC_INTERNAL procedure');
  END PRC_INTERNAL;
  PROCEDURE PRC_TEST(INPUT_ID IN VARCHAR2)
  IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE ('Calling PRC_TEST procedure');
    PRC_INTERNAL('100');
  END PRC_TEST;
END PKG_TEST;
/

pls3

Now try calling the procedure which was not defined in declaration.

EXEC PKG_TEST.PRC_INTERNAL(‘100’);

pls4

Calling procedure PRC_TEST which is calling the internal procedure PRC_INTERNAL.

pls5

Scenario 4. Can we write commit in PL/SQL exception block.

Solution 4: Yes, but it is not recommended to write the commit. As if you want to logs the exception you can use pragma autonomous transaction.  Below example showing that we can use commit in exception block.

CREATE TABLE err_logs
  (
    error_number  VARCHAR2(25),
    error_message VARCHAR2(1000)
  );

DECLARE
  lv_err_code VARCHAR2(25);
  lv_err_msg  VARCHAR2(1000);
  ln_num      NUMBER;
BEGIN
  SELECT to_number('X') INTO ln_num FROM dual;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Writing exception logs!');
  lv_err_code := SQLCODE;
  lv_err_msg  := SQLERRM ;
  INSERT INTO err_logs(error_number,error_message)
    VALUES(lv_err_code,lv_err_msg);
  COMMIT;
END;
/

pls6

Scenario 5. What is pragma autonomous transaction?

Solution 5: Fire an transaction which is independent of calling transaction and return to the calling transaction without affecting it’s state.

CREATE OR REPLACE PROCEDURE prc_log_errors (lv_err_code  IN  VARCHAR2,lv_err_msg IN VARCHAR2) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   INSERT INTO err_logs(error_number,error_message)
    VALUES(lv_err_code,lv_err_msg);
  COMMIT;
END prc_log_errors;
/

If you see below example, DML transaction performed before exception occurred. As we have called the procedure with pargma autonomous which has commit in it, still my main translation data is roll backed.

CREATE TABLE test_table (TEST_ID NUMBER);

DECLARE
  lv_err_code VARCHAR2(25);
  lv_err_msg  VARCHAR2(1000);
  ln_num      NUMBER;
BEGIN
 INSERT INTO test_table(TEST_ID)
    VALUES(1);
     INSERT INTO test_table(TEST_ID)
    VALUES(2);
  SELECT to_number('X') INTO ln_num FROM dual;
  commit;
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Writing exception logs!');
  lv_err_code := SQLCODE;
  lv_err_msg  := SQLERRM ;
 prc_log_errors(lv_err_code,lv_err_msg);
 rollback;
END;
/

pls6

pls5

Scenario 6. Can you fire DDLs in function?

Solution 6: Yes using dynamic sql, but can not use those function in SQL statement. See below example.

CREATE OR REPLACE FUNCTION FNC_SQUARE(
    INPUT_ID IN NUMBER)
  RETURN NUMBER
IS
  ln_out NUMBER;
BEGIN
  BEGIN
    EXECUTE immediate 'drop table TEST_TABLE';
     dbms_output.put_line('Table dropped');
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
EXECUTE immediate 'CREATE TABLE TEST_TABLE (TEST_ID NUMBER)';
dbms_output.put_line('Table created');
ln_out:= INPUT_ID*INPUT_ID;
RETURN ln_out;
END;
/

pls4

Now calling that function in SQL statement:

pls5

Same thing with the DML also you can use in function but cannot call using SQL statement. I have tried calling function from sql which has DML & commit;

pls6

Scenario 7. Can you call procedure from sql statement?

Solution 7: No

CREATE OR REPLACE PROCEDURE PRC_SQUARE(
    INPUT_NUM IN NUMBER,OUTPUT_NUM OUT NUMBER )
IS
BEGIN
OUTPUT_NUM:= INPUT_NUM*INPUT_NUM;
END;
/

SELECT PRC_SQUARE(2) FROM dual;

pls6

Call procedure using below method.

declare 
ln_out NUMBER; 
begin 
PRC_SQUARE(6,ln_out); 
dbms_output.put_line('Square is: '|| ln_out); 
end; /

pls5

Function you can using SQL statements. As it has always return type.

create or replace FUNCTION FNC_SQUARE(
    INPUT_ID IN NUMBER)
  RETURN NUMBER
IS
  ln_out NUMBER;
BEGIN
ln_out:= INPUT_ID*INPUT_ID;
RETURN ln_out;
END;
/

pls6


Thanks!

Happy Learning! Your feedback would be appreciated!

 

ODI 12C Installation & Setup | Windows 10 64-Bit

In this blog we will see how we can setup the ODI 12c on your Windows 10 – 64 Bit PC. First you have to download the latest ODI 12c using below link. I have downloaded the Oracle Data Integrator 12c (12.2.1.4.0) version.

Step 1. Lets begin the installation by  downloading & unzipping the file.

Oracle Data Integrator Download Link

Instal1

Step 2. Prerequisite: Check you java version & set the java variables.

2

java -version
set JAVA_HOME="C:\Program Files\Java\jdk1.8.0_221"
set path=C:\Program Files\Java\jdk1.8.0_221\bin;%path%

Step 3. Start the installation using below commands:

cd C:\Users\shobhit\Downloads\fmw_12.2.1.4.0_odi_Disk1_1of1
java -jar fmw_12.2.1.4.0_odi.jar

1

It will automatically start the installation.

Screenshot 23-02-2020 12_05_44

Installation steps are self explanatory you just have to check all the details like path & click on next.

3

4

Installation is completed. Go to windows start,  type ODI Studio and open the tool.

Step 3. Setup the Master Repository,Work Repository & Logins. Refer below blog to setup the master repository, work repository & logins.

Oracle Data Integrator 11g (ODI) Repositories | Master & Work Repository | Connection & Setup

tool

Repository configuration is done & now you can start building the project using ODI 12c.

If you encounter such issue while opening the ODI 12c Sudio. Please follow below steps.

issue

Delete all the temp files present in the below directory & try opening the ODI studio again.

C:\Users\<<Username>>\AppData\Roaming\odi\system12.2.1.4.0


Thanks!

Happy Learning! Your feedback would be appreciated!

 

Oracle SQL Scenario Questions – Part 3

In this blog we will explore some more latest Oracle SQL scenario questions. Here we will cover RPAD, PLAD, Null Function, Instr, Substr, regex_substr, translate.


 

Scenario 1. What will be the output of following SQL statement.

select rpad('TEST      ',12, '*') from dual;

length (‘TEST      ‘) = 10

Output:

rpad

Scenario 2. What will be the output of following SQL statement.

select lpad('1234',12, '*') from dual;

lpad

Scenario 3: What will be the output of following SQL statement.

select nullif(12,12) from dual;
select nullif(12,13) from dual;

nullif

NULLIF (expression 1, expression 2)

  • NULLIF function returns NULL if expression 1 and expression 2 are equal.
  • NULLIF function returns expression 1 if not equal.
  • expression 1 cannot be literal null.
  • Datatypes should be same for both expression.
select nullif('NULL',12) from dual;

nullif

Scenario 4: What will be the output of following SQL statement.

select NVL2('', 'Name avalailbe', 'Name not avalailbe') from dual;
select NVL2('Ram', 'Name avalailbe', 'Name not avalailbe') from dual;

vnl2

NVL2( input_string, value_if_not_null, value_if_null )

You can substitutes a value when a null value is encountered as well as when a non-null value is encountered.

Scenario 5: Rotating columns to rows, see below screenshot. See below source data.

unpv

select * from sales_data
unpivot
( SALES for MONTH_ in (JAN, FEB, MAR, APR) );

unpv2

Scenario 6: Draw this pattern.

* 
* * 
* * * 
* * * * 
* * * * *
select rpad('*', rownum, '*') from dual connect by rownum &lt;=5;

star

Scenario 7: Draw this pattern.

* * * * * 
* * * * 
* * * 
* * 
*
select rpad('*', rownum, '*') from dual connect by rownum <=5
order by 1 desc;

star

Scenario 8: What will be the output of following SQL statement.

select translate ( 'HeXXo WorXd' , 'X', 'l') from dual;

translate

Scenario 8: Extract the state code from these addresses.

insttr

Option 1: Using instr & substr

select ADDRESS1,  instr(ADDRESS1,','),
trim(substr(address1,instr(ADDRESS1,',')+1)) state_code from address;

instr2

Option 2: Easy way as you know that codes are last two char of string.

select address1, substr(address1,-2) state_code from address;

subsst

Scenario 8: Extract the state code from these addresses. Here after the 2nd occurrence of delimiter ~.

kerala

select ADDRESS1, regexp_substr(ADDRESS1, '[^~]+',1,2) state_name,
regexp_substr(ADDRESS1, '[^~]+',1,3) state_code from address;

kk


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle SQL Scenario Questions – Part 2

Oracle SQL Scenario Questions  – Part 2

In this blog we will explore some more latest Oracle SQL scenario questions. Here we will cover analytical functions, list aggregate, date functions,  union all, pivot & prime numbers.


Scenario 1. In below example remove the duplicate. Example: Records 1 & 2 are identical.

1Src

CREATE TABLE "DISTANCE"
  (
    "SOURCE_NAME" VARCHAR2(20 BYTE),
    "DEST_NAME"   VARCHAR2(20 BYTE),
    "DISTANCE"    NUMBER
  );

Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','JAIPUR',281);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('JAIPUR','DELHI',281);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','PUNE',1427);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('PUNE','DELHI',1427);
Insert into DISTANCE (SOURCE_NAME,DEST_NAME,DISTANCE) values ('DELHI','SPITI',731);

COMMIT;

Solution:  You can use the LEAST/GREATEST function to find out the dupes.

SELECT d.*,
row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME),
GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance )AS RNUM
FROM DISTANCE d;

2

From this data set you can filter the rows using the RNUM column to fetch distinct rows.

WITH data_set AS
(SELECT d.*,
row_number() over (partition BY LEAST(SOURCE_NAME, DEST_NAME), GREATEST(SOURCE_NAME, DEST_NAME), distance order by distance ) AS RNUM
FROM DISTANCE d
)
SELECT * FROM data_set WHERE RNUM =1;

3

Scenario 2. In below example customer data is present for product with dates. Fetch the customer who came in all months of quarter at least once. Like Jan, Feb, Mar or Apr, May, June etc. Notice in below data-set C1 & C3 came in all months of quarter Q1 & Q2 respectively.

11

Solution: You can use the date functions to find out the months & quarter.

SELECT CUST_ID,
listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP (
ORDER BY BILL_DATE) months_name,
TO_CHAR(BILL_DATE,'q')
FROM SALES
GROUP BY CUST_ID,
TO_CHAR(BILL_DATE,'YYYY'),
TO_CHAR(BILL_DATE,'q');

111

WITH dataset AS
  (SELECT CUST_ID,
    listagg (TO_CHAR(BILL_DATE,'MM'),'||') within GROUP (
  ORDER BY BILL_DATE) months_name,
    TO_CHAR(BILL_DATE,'YYYY') year_,
    TO_CHAR(BILL_DATE,'q') quarter_
  FROM SALES
  GROUP BY CUST_ID,
    TO_CHAR(BILL_DATE,'YYYY'),
    TO_CHAR(BILL_DATE,'q')
  )
SELECT *
FROM dataset
WHERE months_name IN ('01||02||03','04||05||06','07||02||09','10||11||12') ;
 

12

Scenario 3.  Output of this SQL: Select count(1), SUM(1) from dual where 1=2;

Select count(1), SUM(1) from dual where 1=2;

333

Scenario 4.  Print 1st day of Month, Last Day, Mid, Week, Quarter for given date.

WITH dataset AS
  (SELECT to_date('10-JAN-2019','dd-mon-yyyy') date_ FROM dual
  union
  SELECT to_date('20-FEB-2019','dd-mon-yyyy') date_ FROM dual
  union
  SELECT to_date('05-JUN-2019','dd-mon-yyyy') date_ FROM dual
  )
SELECT to_char(date_,'DD-MM-YYYY'), to_char(date_,'Q') Quarter ,to_char(date_,'W') week,
trunc((date_),'MM') FIRST_DATE_OF_MONTH, last_day(date_) LAST_DATE_OF_MONTH
FROM dataset;

4444

Print Day of the date:

day

Scenario 5.  In below example you have to print date twice. See sample date.

Input:  555  Output Required: 6666

Solution:

WITH data_set AS
  ( SELECT id_ FROM dataset
  UNION ALL
  SELECT id_ FROM dataset
  )
SELECT * FROM data_set ORDER BY 1;

555

Scenario 6.  In below example you have ~ tilde separated date in column VAL. You have to break the string using SQL like below. Refer required output screenshot.

Input: 7777 Required Output: 7777

CREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100));

INSERT INTO  DATASET VALUES (1, 'A~B~C~D');
INSERT INTO  DATASET VALUES (2, 'X~Y~Z');
INSERT INTO  DATASET VALUES (5, 'K');
COMMIT;

Solution: 

WITH data_set AS
  ( SELECT id_,val, regexp_count(val,'~') sep_cnt FROM dataset
  ),
  numseries AS
  (SELECT rownum rnum FROM DUAL CONNECT BY rownum =numseries.rnum
ORDER BY 1;

7777

Scenario 7.  Similar to Scenario 6, In below example you have data in VAL column & you have bring it in one row separated by double pipe ||.

Input : 7777 Required Output: 7777

DROP TABLE DATASET;
CREATE TABLE DATASET ( ID_ NUMBER, VAL VARCHAR2(100));
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'A');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'B');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'C');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('1', 'D');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Y');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'Z');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('2', 'X');
INSERT INTO "DATASET" (ID_, VAL) VALUES ('5', 'K');
COMMIT;

Solution: 

SELECT ID_,
  listagg (VAL, '||') within GROUP (
ORDER BY VAL) NEW_VAL
FROM dataset
GROUP BY ID_;

7777

Scenario 8. Rotating rows to column, see below screenshot.

Input: 7777 Output: 77

Solution : Using Pivot Clause

SELECT *
FROM
  (SELECT prod_id, month_, sales FROM sales_data
  ) pivot( MAX(sales) FOR month_ IN ('Jan' AS JAN, 'Feb' AS FEB, 'Mar' AS MAR, 'Apr' AS APR) );

 

7777

Scenario 9 : Print prime numbers using SQL

WITH a AS
  ( SELECT rownum rn FROM dual CONNECT BY rownum <=20
  ),
  b AS
  (SELECT rownum rn FROM dual CONNECT BY rownum =b.rn
  ),
  prime_num AS
  (SELECT rn FROM div WHERE mod_=0 GROUP BY rn HAVING COUNT(rn1)=2 ORDER BY 1
  )
SELECT * FROM prime_num;

prime


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Interval Partitioning

In the previous blog Oracle Range Partition we have explored how we can create range partition in oracle using date as partition key. Also we have seen one example where the range partition key is defined maximum upto ’01-APR-2019′ (Non-inclusive). But we are trying to insert ’20-OCT-2019′ date data, which is not getting mapped to any partition, that’s why ORA-14400 error occurred. See below error report provided by oracle.

SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”

Here comes the Interval Partition, which is extension of  Oracle Range Partition in which, interval partitions are automatically created by the database when data is inserted into the partition.

Monthly Partition:

CREATE TABLE T_ACCOUNT
  (
    V_ACCOUNT_NUM  VARCHAR2(100 CHAR),
    V_ACCOUNT_NAME VARCHAR2(200 CHAR),
    ACC_DATE DATE
  )
  PARTITION BY RANGE
  (
    ACC_DATE
  )
  INTERVAL
  (
    NUMTOYMINTERVAL(1, 'MONTH')
  )
  (
    PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY'))
  );

After that we have inserted the data of future months, i.e. SEP & OCT for which Partitions were not available. See below insert statements.

INSERT INTO "SAIL_IN_DEV"."T_ACCOUNT" (V_ACCOUNT_NUM, V_ACCOUNT_NAME, ACC_DATE) VALUES ('1234567890', 'ABC', TO_DATE('20-SEP-19', 'DD-MON-RR'));
INSERT INTO "SAIL_IN_DEV"."T_ACCOUNT" (V_ACCOUNT_NUM, V_ACCOUNT_NAME, ACC_DATE) VALUES ('1234567891', 'ABC', TO_DATE('20-OCT-19', 'DD-MON-RR'));
COMMIT;

After committing the data let see the automatic partitions created by Oracle :

2

Notice that a system generated partition named SYS_P601 & SYS_P402 has been created after inserting rows with a partition key greater than the provided partition.

Fetching the data from particular partition : In below example you will able to see only match partition data.

SELECT * FROM T_ACCOUNT partition (SYS_P601);

It would be really painful to look up the system generated partition name every time, if you have to query directly partition. Therefore, oracle provided a syntax to specify a partition is by using the partition for (DATE) clause in the query: See below example:

partition for (to_date(’15-OCT-2019′,’DD-MON-YYYY’));

SELECT * FROM T_ACCOUNT partition  for (to_date('01-SEP-2019','DD-MON-YYYY'));

The following restrictions apply on Interval Partition:

  • Only one partitioning key column allowed (NUMBER or DATE)
  • Minimum one partition must be defined when the table is created.
  • MAXVALUE partition cannot be defined.
  • NULL values are not allowed in the partition column.
  • Can’t be used at the subpartition level.
  • Not supported for index-organized tables.
  • You cannot create a domain index on an interval partitioned table.

 

Daily Partitions Example:

CREATE TABLE T_TRANSACTIONS
  (
    TRANSACTION_KEY NUMBER,
    AMOUNT          NUMBER,
    CUST_ID         NUMBER,
    TRANSACTION_DATE DATE
  )
  PARTITION BY RANGE
  (
    TRANSACTION_DATE
  )
  INTERVAL
  (
    NUMTODSINTERVAL(1, 'day')
  )
  (
    PARTITION P_01012019 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY'))
  );

 


Thanks!

Happy Learning! Your feedback would be appreciated!