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!