PL/SQL Cursor Variable – Ref Cursors (Strong & Weak Type)

In previous blog we have explored the Cursors in PL/SQL. Where we have explored Implicit & Explicit Cursors. Implicit & Explicit cursors are static in nature and always tied up with the some defined SQL statement. Once the explicit cursor is declared, you can not change the SQL statement in the PL/SQL program.

Cursor variable is like an explicit cursor that is not limited to one query. Cursor variable can be opened for any query, and for different query in one PL/SQL program.

Declaring REF Cursors :

Create a reference cursor type & declare the actual cursor variable based on type.

TYPE CURSOR_NAME IS REF CURSOR [RETURN return_type];
CURSOR_VARIABLE CURSOR_NAME;

Return clause is optional with REF CURSOR type statement. See below both type of declaration.

-- Declaration without return type
TYPE CUST_CO IS REF CURSOR;
CUST_CO_VAR CUST_CO;

-- Declaration with return type
TYPE CUST_CO IS REF CURSOR RETURN CUSTOMER%ROWTYPE;
CUST_CO_VAR CUST_CO;

The first declaration of REF CURSOR is Weak Type, as there is no return type defined. Here cursor variable is not associated with any data structure. Cursor variable can be used for any query or any structure.

Second declaration where return type is defined is called Strong Type, as it is associated with the data structure. Cursor variable declared for this type can only fetch into data structure match with the return type.

Example for Weak Type:
DECLARE
TYPE CUST_CO IS REF CURSOR;
CUST_CO_VAR CUST_CO;

CUST_REC CUSTOMERS%ROWTYPE;
ORDITEMS_REC ORDER_ITEMS%ROWTYPE;
ORD_REC ORDERS%ROWTYPE;
BEGIN
OPEN CUST_CO_VAR FOR SELECT * FROM CUSTOMERS;
FETCH CUST_CO_VAR INTO CUST_REC;
dbms_output.put_line(CUST_REC.CUSTOMER_ID || ' ' || CUST_REC.EMAIL_ADDRESS || ' - ' ||    CUST_REC.FULL_NAME );

OPEN CUST_CO_VAR FOR SELECT * FROM ORDER_ITEMS;
FETCH CUST_CO_VAR INTO ORDITEMS_REC;
dbms_output.put_line(ORDITEMS_REC.ORDER_ID || ' ' || ORDITEMS_REC.PRODUCT_ID );

OPEN CUST_CO_VAR FOR SELECT * FROM ORDERS;
FETCH CUST_CO_VAR INTO ORD_REC;
dbms_output.put_line(ORD_REC.ORDER_ID || ' ' || ORD_REC.CUSTOMER_ID || ' '|| ORD_REC.ORDER_STATUS );
CLOSE CUST_CO_VAR;
END;
/

SYS_REFCURSOR – predefined weak ref cursor which comes built-in with the Oracle database. So no need to define a weakly typed REF CURSOR type.

DECLARE
cust_cursor  SYS_REFCURSOR;

CUST_REC CUSTOMERS%ROWTYPE;
PRDCT_REC PRODUCTS%ROWTYPE;
BEGIN
dbms_output.put_line('==============Customers==============' );
OPEN cust_cursor FOR SELECT * FROM CUSTOMERS where CUSTOMER_ID <=5;
LOOP
FETCH cust_cursor INTO CUST_REC;
exit when cust_cursor%NOTFOUND;
dbms_output.put_line(CUST_REC.CUSTOMER_ID || ' ' || CUST_REC.EMAIL_ADDRESS || ' - ' ||    CUST_REC.FULL_NAME );
END LOOP;

dbms_output.put_line('==============Products==============' );
OPEN cust_cursor FOR SELECT * FROM PRODUCTS where PRODUCT_ID <=5;
LOOP
FETCH cust_cursor INTO PRDCT_REC;
exit when cust_cursor%NOTFOUND;
dbms_output.put_line(PRDCT_REC.PRODUCT_ID || ' ' || PRDCT_REC.PRODUCT_NAME || ' - ' ||    PRDCT_REC.UNIT_PRICE );
END LOOP;

CLOSE cust_cursor;
END;
/
Example for Strong Type:
DECLARE
TYPE CUST_CO IS REF CURSOR RETURN CUSTOMERS%ROWTYPE;
CUST_CO_VAR CUST_CO;
CUST_REC CUSTOMERS%ROWTYPE;
BEGIN
OPEN CUST_CO_VAR FOR SELECT * FROM CUSTOMERS where CUSTOMER_ID <=10;
LOOP
FETCH CUST_CO_VAR INTO CUST_REC;
exit when CUST_CO_VAR%NOTFOUND;
dbms_output.put_line(CUST_REC.CUSTOMER_ID || ' ' || CUST_REC.EMAIL_ADDRESS || ' - ' ||    CUST_REC.FULL_NAME );
END LOOP;
CLOSE CUST_CO_VAR;
END;
/

SYS_REFCURSOR as Argument in PL/SQL Subprogram:

This the best way to provide sql query result as output i.e. passing out parameter as SYS_REFCURSOR. Refer below sample code.

CREATE OR REPLACE PROCEDURE get_customer_orders(
    p_cust_id IN CUSTOMERS.CUSTOMER_ID%TYPE,
    p_out_cursor OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_out_cursor FOR 
  SELECT CUSTOMERS.FULL_NAME , CUSTOMERS.EMAIL_ADDRESS, PRODUCTS.PRODUCT_NAME 
  FROM orders JOIN ORDER_ITEMS ON orders.ORDER_ID= ORDER_ITEMS.ORDER_ID 
  JOIN PRODUCTS ON ORDER_ITEMS.PRODUCT_ID=PRODUCTS.PRODUCT_ID 
  JOIN CUSTOMERS ON orders.CUSTOMER_ID=CUSTOMERS.CUSTOMER_ID 
  WHERE orders.CUSTOMER_ID = p_cust_id;
END get_customer_orders;
/
DECLARE
  lc_cursor  SYS_REFCURSOR;
  lv_custname   CUSTOMERS.FULL_NAME%TYPE;
  lv_custemail   CUSTOMERS.EMAIL_ADDRESS%TYPE;
  lv_prodcutname   PRODUCTS.PRODUCT_NAME%TYPE;
BEGIN
  get_customer_orders (5,lc_cursor);      
  LOOP 
    FETCH lc_cursor INTO  lv_custname,lv_custemail, lv_prodcutname;
    EXIT WHEN lc_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(lv_custname || ' - ' || lv_custemail || ' - '|| lv_prodcutname );
  END LOOP;
  CLOSE lc_cursor;
END;

Oracle Schema used in blog: Customer-Orders Sample Schema | Oracle 12c


Thanks!

Happy Learning! Your feedback would be appreciated!

ORA-01033 | Oracle 12c Database

Problem: While opening the session of one of the schema in pluggable database, getting error ‘ORACLE initialization or shutdown in progress‘.

An error was encountered performing the requested operation:

ORA-01033: ORACLE initialization or shutdown in progress
01033. 00000 -  "ORACLE initialization or shutdown in progress"
*Cause:    An attempt was made to log on while Oracle is being started up or shutdown.
*Action:   Wait a few minutes. Then retry the operation.
Vendor code 1033

Solution: Login the database using the sysdba. Check the status of the pluggable database.

select name, open_mode from v$pdbs where name='PDB';

So the status of the database is mounted, we have to open this database.

alter pluggable database PDB open;

Checking the database status & connection details.

Connection tested successfully.

Refer below blogs for setting Oracle 12c Database:


Thanks!

Happy Learning! Your feedback would be appreciated!

Customer-Orders Sample Schema | Oracle 12c

Customer-Orders Sample Schema | Oracle 12c

Customer Orders is a new sample schema in Oracle Database launched in Aug’19, its a simple retail application database. You can use this for learning SQL, writing blog posts or any kind of application demo. In this schema you will also get to know the usage of JSON in Oracle Database.

Below is the relational diagram of CO schema created using Oracle Data Modeler.


Customer Orders schema requires Oracle Database 12c or higher version. Refer below blogs for installing Oracle Database 12c & setting up the pluggable database.

Installing CO schema

Download the schemas from the GitHub link: db-sample-schemas . After downloading the schemas you have to run below sql

@co_main <CO_password> <connect string> <tablespace> <temp tablespace>

I have created the table spaces in my pluggable database, which is required for running the co_main script for schema creation.

CREATE SMALLFILE TABLESPACE "USERS" DATAFILE 'C:\APP\SHOBHIT\VIRTUAL\ORADATA\ORCL\PDB\USERS01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M ;
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE 'C:\APP\SHOBHIT\VIRTUAL\ORADATA\ORCL\PDB\TEMP1' SIZE 2G;

Now cloning the Oracle sample schemas from GitHub or you can download the Zip extract & unzip from GitHub.

Executing the co_main script under my pluggable database.

This command will first drop the CO schema & then create it, after that it will start the DDL script & DML script. You can check the script logs in co_install txt file in the same directory where your schema is present.

Connecting the CO schema using the SQL Developer.

Checking out the JSON data in Product table:

Sample JSON data for one of the product:

{
    "colour": "green",
    "gender": "Women's",
    "brand": "FLEETMIX",
    "description": "Excepteur anim adipisicing aliqua ad. Ex aliquip ad tempor cupidatat dolore ipsum ex anim Lorem aute amet.",
    "sizes": [0, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20],
    "reviews": [{
        "rating": 8,
        "review": "Laborum ipsum adipisicing magna nulla tempor incididunt."
    }, {
        "rating": 10,
        "review": "Cupidatat dolore nulla pariatur quis quis."
    }, {
        "rating": 9,
        "review": "Pariatur mollit dolor in deserunt cillum consectetur."
    }, {
        "rating": 3,
        "review": "Dolore occaecat mollit id ad aliqua irure reprehenderit amet eiusmod pariatur."
    }, {
        "rating": 10,
        "review": "Est pariatur et qui minim velit non consectetur sint fugiat ad."
    }, {
        "rating": 6,
        "review": "Et pariatur ipsum eu qui."
    }, {
        "rating": 6,
        "review": "Voluptate labore irure cupidatat mollit irure quis fugiat enim laborum consectetur officia sunt."
    }, {
        "rating": 8,
        "review": "Irure elit do et elit aute veniam proident sunt."
    }, {
        "rating": 8,
        "review": "Aute mollit proident id veniam occaecat dolore mollit dolore nostrud."
    }]
}

Parsing the JSON data using JSON_TABLE:

select p.product_name, j.gender, j.colour,j.brand,j.sizes
from   products p,
       json_table (
         p.product_details, '$'
         columns ( 
             colour varchar2(4000) path '$.colour',
             gender varchar2(4000) path '$.gender',
             brand  varchar2(4000) path '$.brand',
             sizes varchar2(4000) FORMAT JSON path '$.sizes'
))j;
SELECT p.product_name,  j.value
FROM products p,
  json_table ( p.product_details, '$.sizes[*]' 
columns ( value PATH '$' ) 
)j;

For more details please visit: announcing-a-new-sample-schema-customer-orders


Thanks!

Happy Learning! Your feedback would be appreciated!

Container & Pluggable Database | Oracle 12c

Container & Pluggable Database | Oracle 12c

In this blog we will explore the container database concept in Oracle Database 12c. We are going to create the pluggable database manually & schema under it.


I have installed the Oracle 12c Database in Windows 10 & logged in using sys as dba. Refer this blog for installation & setup Oracle 12c Database Oracle Database 12c Installation & Setup| Windows 10

In connected session V$DATABASE showing database which was created at the time of installation of Oracle Database 12c. If you refer below screenshot column value CDB=’YES’ i.e. its a container database(CDB).

v$database output

Multitenant architecture enable the Oracle database to work as multi-tenant container database (CDB). All Oracle databases before 12c were non-CDBs.

Container Database (CDB) has following containers in it :

  • Root: Container is named as CDB$ROOT. Oracle supplied metadata & main database holding all control files, redo etc.
  • Seed PDB: Named as PDB$SEED. System supplied template that CDB can use to create new PDBs.
  • User-created PDB: Pluggable database created by user for business requirements. Actual schemas will be created under here for code & data.

You can run below command to check your connected container. I’m running this command in my sys as dba connection. Here it is showing that you are connected to the CDB$ROOT i.e. root container.

sho con_name;
sho con_name ouput

Create Pluggable Database :

Here we will see how we can create pluggable database under our root container. See below sample SQL for creating manually the pluggable database. You have to provide DB name, admin user name i.e. which will be admin of your PDB.

You can also create PDB using dbca. Just do Windows search.

CREATE PLUGGABLE DATABASE <database-name> ADMIN USER <username> IDENTIFIED BY <password>
DEFAULT TABLESPACE USERS
DATAFILE '<location>'
SIZE <size> AUTOEXTEND ON
FILE_NAME_CONVERT=(
'<location of pdbseed pdb>',
'<location of new pdb>');
Pluggable Database Setup

I have created the pluggable database using my sys connection i.e. root. You can check your data files created in the provided location.

Datafiles

After creating the pluggable database you can check its initial status should be present as MOUNTED. Check the data in v$pbs

v$pdbs output

You have to fire below command to put your database in read write mode.

alter pluggable database PDB open;
v$pdbs output

Now your pluggable database is altered & its showing “Read Write” mode. You can also check the status of PDBs using sho pdbs;

Creating Pluggable Database Schema:

In this step we are going to create the database schema under our pluggable container. Just need to alter the container.

alter session set container=PDB;
sho con_name;

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

Setting up SQL Developer Connection:

Refer this blog for setting up the new sample schema ‘Customer-Orders’ : Customer-Orders Sample Schema | Oracle 12c


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle SQL – Scenario Questions Part 4

In this blog we will explore some more Oracle SQL scenario questions. Here we will cover more about indexes.


Scenario 1: Create table T_ACCOUNT, inserted some data & created index on three columns (ACCOUNT_ID,ACCOUNT_TYPE,ACCOUNT_STATUS).

Now running below query, will index picked up by Oracle optimizer or not?

SELECT * FROM T_ACCOUNT where ACCOUNT_TYPE ='C' and ACCOUNT_STATUS='O';
CREATE TABLE T_ACCOUNT
  ( account_id     NUMBER ,
    account_name   VARCHAR2(100) ,
    account_type   VARCHAR2(10) ,
    account_status CHAR(1),
    open_date      DATE );
CREATE INDEX IDX_ACCOUNT_1 ON T_ACCOUNT(ACCOUNT_ID,ACCOUNT_TYPE,ACCOUNT_STATUS);

Explanation : The order of the columns can make a difference. IDX_ACCOUNT_1 would be most useful if you fire such queries. Table would be full accessed if you don’t follow the order.

SELECT * FROM T_ACCOUNT where ACCOUNT_ID=100 and ACCOUNT_TYPE ='C' and ACCOUNT_STATUS='O';
SELECT * FROM T_ACCOUNT where ACCOUNT_ID=100 and ACCOUNT_TYPE ='C';
SELECT * FROM T_ACCOUNT where ACCOUNT_ID=100;

Example: I have used my columns as per the order of index. Here Table accessed by Index.

EXPLAIN PLAN FOR SELECT * FROM T_ACCOUNT where ACCOUNT_ID=100 and ACCOUNT_TYPE ='C';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Now using columns not in order or index. Here tables is full accessed.

EXPLAIN PLAN FOR SELECT * FROM T_ACCOUNT where ACCOUNT_TYPE ='C' and ACCOUNT_STATUS='O';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Scenario 2: Can you create the Unique Index on the column having some dupes records.

Explanation: No

Scenario 3: Created one table with primary key, so oracle created the default index on that column. What will happen if you are going to create unique index on same column.

Explanation: Cannot create the index on already indexed columns.

You can first create the Index & then enforce the constraint.

DROP TABLE T_SAMPLE;
CREATE TABLE T_SAMPLE
  ( ID NUMBER ,
  DATA_SOURCE NUMBER
  );
CREATE UNIQUE INDEX IDX_T_SAMPLE ON T_SAMPLE(ID);

ALTER TABLE T_SAMPLE ADD CONSTRAINT PK_T_SAMPLE PRIMARY KEY(ID);

Though you can create index if the primary key having more than one column.

CREATE TABLE T_SAMPLE
  ( ID NUMBER ,
  DATA_SOURCE NUMBER,
  CONSTRAINT T_SAMPLE_pk PRIMARY KEY (ID,DATA_SOURCE)
  );
CREATE INDEX IDX_T_SAMPLE ON T_SAMPLE(ID);

Scenario 4: Created the tables & index on two columns ID, CODE. In query I’m using the condition like ID||CODE = ‘100E’ . Will optimizer pick up the index, if not how to solve this, as some times we have to use like this.

CREATE TABLE T_SAMPLE ( ID NUMBER, CODE VARCHAR2(1));
CREATE INDEX T_SAMPLE_IDX ON T_SAMPLE (ID,CODE);

INSERT INTO T_SAMPLE
SELECT level,
  DECODE(mod(level, 2), 0, 'E', 'O')
FROM dual
  CONNECT BY level <= 100000;
BEGIN
  dbms_stats.gather_table_stats(USER, 'T_SAMPLE');
END;
/

select * from T_SAMPLE where Id =100 and code='E';-- Index Picked
select * from T_SAMPLE where Id||code='100E';-- Index Not Picked

Explanation: Optimizer will not pick the index in this case. You have to create one functional index.

After creating functional index:

CREATE INDEX T_SAMPLE_IDX2 ON T_SAMPLE (ID||CODE);
SELECT * FROM T_SAMPLE WHERE Id||code='100E' ;

Scenario 5: See below scenario, column is number datatype & while querying you have provided the single quotes. Will Index picked by optimizer.

DROP TABLE T_SAMPLE;
CREATE TABLE T_SAMPLE( ID NUMBER);
CREATE UNIQUE INDEX T_SAMPLE_IDX ON T_SAMPLE  (ID);

INSERT INTO T_SAMPLE
SELECT level
FROM dual
  CONNECT BY level <= 100000;
BEGIN
  dbms_stats.gather_table_stats(USER, 'T_SAMPLE');
END;
/
select * from T_SAMPLE where Id =100 ; -- Index Picked
select * from T_SAMPLE where Id =to_char('100');
Index got picked up by optimizer

Scenario 6: See below scenario, column is number datatype & while querying you have provided the single quotes. Will Index picked by optimizer.

DROP TABLE T_SAMPLE;

CREATE TABLE T_SAMPLE( ID VARCHAR2(4000));
CREATE UNIQUE INDEX T_SAMPLE_IDX ON T_SAMPLE (ID);

INSERT INTO T_SAMPLE
SELECT level
FROM dual
  CONNECT BY level <= 100000;

BEGIN
  dbms_stats.gather_table_stats(USER, 'T_SAMPLE');
END;
/

select * from T_SAMPLE where Id ='100';
select * from T_SAMPLE where Id = 100 ;-- Table Access Full

Visit below blogs for other SQL Scenario questions.


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Database 12c Installation & Setup| Windows 10

Oracle Database 12c  Installation & Setup| Windows 10

In this blog we will see how we can setup the Oracle Database 12c on Windows 10 – 64 Bit PC. First you have to download the latest Oracle Database 12c using below link.

https://www.oracle.com/database/technologies/oracle12c-windows-downloads.html


I have downloaded the Oracle Database 12c (12.2.1.4.0) version.

Go to the database directory & start the setup. It will automatically start the Oracle Universal Installer.

Here creating database as container database
Setup is in-progress
Checking the status of database after installation using lsnrctl status
Oracle Database 12c is now up & running.

Refer this blog for creating pluggable database & schema : Container & Pluggable Database – Oracle 12c


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

Visit below blogs for more scenario questions.


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. Breaking single string to multiple rows using dual, rownum or level providing the separator.


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: You can use the level also instead of rownum to generate numbers.

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

Visit below blogs for more scenario questions.


Thanks!

Happy Learning! Your feedback would be appreciated!

Oracle Range Partition

Range partitioning is useful when data has some sort of logical range. Example dates or numbers. See below example where monthly range partitions created based on date column in account table.

CREATE TABLE T_ACCOUNT
  (
    V_ACCOUNT_NUM  VARCHAR2(100 CHAR),
    V_ACCOUNT_NAME VARCHAR2(200 CHAR),
    ACC_DATE DATE
  )
  PARTITION BY RANGE
  (
    ACC_DATE
  )
  (
    PARTITION P_DEC2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')),
    PARTITION P_JAN2019 VALUES LESS THAN (TO_DATE('01-FEB-2019','DD-MON-YYYY')),
    PARTITION P_FEB2019 VALUES LESS THAN (TO_DATE('01-MAR-2019','DD-MON-YYYY')),
    PARTITION P_MAR2019 VALUES LESS THAN (TO_DATE('01-APR-2019','DD-MON-YYYY'))
  );

 

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

SELECT * FROM T_ACCOUNT PARTITION(P_MAR2019);

 

Check partitions metadata in user_tab_partitions & subpartitions.

SELECT * FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='T_ACCOUNT';

1

To check the Partition KEY of the table, use below query:

SELECT partition_name,
  column_name,
  high_value,
  partition_position
FROM USER_TAB_PARTITIONS PART ,
  USER_PART_KEY_COLUMNS PART_KEY
WHERE table_name ='T_ACCOUNT'
AND PART.table_name = PART_KEY.name;

In the above example any record having acc_date less than ’01-JAN-2019′ will go to P_DEC2018 partition. See below example:

INSERT
INTO "T_ACCOUNT"
  (
    V_ACCOUNT_NUM,
    V_ACCOUNT_NAME,
    ACC_DATE
  )
  VALUES
  (
    '1234567890',
    'ABC',
    TO_DATE('20-OCT-2010', 'DD-MON-YYYY')
  );
COMMIT;
SELECT * FROM T_ACCOUNT PARTITION (P_DEC2018) ;

In the this example any record having acc_date greater than ’31-MAR-2019′ will error out, as no partition exists for that date.

Example:

INSERT
INTO "T_ACCOUNT"
  (
    V_ACCOUNT_NUM,
    V_ACCOUNT_NAME,
    ACC_DATE
  )
  VALUES
  (
    '1234567890',
    'ABC',
    TO_DATE('20-OCT-2019', 'DD-MON-YYYY')
  );

Error report:
SQL Error: ORA-14400: inserted partition key does not map to any partition
14400. 00000 – “inserted partition key does not map to any partition”
*Cause: An attempt was made to insert a record into, a Range or Composite
Range object, with a concatenated partition key that is beyond
the concatenated partition bound list of the last partition -OR-
An attempt was made to insert a record into a List object with
a partition key that did not match the literal values specified
for any of the partitions.
*Action: Do not insert the key. Or, add a partition capable of accepting
the key, Or add values matching the key to a partition specification

1

Here in this example we can see the partition key is defined maximum upto 2019-04-01 (Non-inclusive) . But we are trying to insert 20-OCT-2019, which is not getting mapped to any partition, that’s why this error occured as date value which we are trying to insert is T_ACCOUNT not satisfying the partition key range criteria.

Solution: To fix it, you may have to add new partitions or add a Maxvalue partition.

ALTER TABLE T_ACCOUNT ADD partition P_OCT2019 VALUES less than (TO_DATE('01-NOV-2019', 'DD-MON-YYYY'));
ALTER TABLE T_ACCOUNT ADD partition P_MAXVALUE VALUES less than (MAXVALUE);

You can also go with : Oracle Interval Partitioning to resolve this issue, but this not preferable.

Drop Partition:

Once historical data is no longer required for business analysis, after retention period the whole partition can be dropped.

DROP PARTITION P_DEC2018;

Updating Key:

When you are trying to update the partition key column for any record.

UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';

Error report:
SQL Error: ORA-14402: updating partition key column would cause a partition change
14402. 00000 – “updating partition key column would cause a partition change”
*Cause: An UPDATE statement attempted to change the value of a partition
key column causing migration of the row to another partition
*Action: Do not attempt to update a partition key column or make sure that
the new partition key is within the range containing the old
partition key.

You can’t move a row with an update: Row movement disabled, which is the default option.

Solution:  We can enable Row movement to partition table T_ACCOUNT. It allows rows to be moved across partitions.

ALTER TABLE T_ACCOUNT ENABLE ROW MOVEMENT;
UPDATE T_ACCOUNT SET ACC_DATE='20-JAN-2019' WHERE v_account_num='1234567890';
COMMIT;

 


Thanks!

Happy Learning! Your feedback would be appreciated!

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

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

Folks,

In this blog we will explore some areas of ODI Topology Navigaor.

  • We will explore Dataserver, Physical Schema, Logical Schema & Context.
  • Relationship between Physical, Logical Schema & Context.

Regarding setup & configuration of Dataserver, Physical Schema, Logical Schema in ODI using Oracle Technology please refer below video.


Let’s explore Topology Navigator in detail.

topology section
Topology Navigator

In Topology Navigator we have ‘Physical Architecture‘ section where we define the physical connection details of any Instance ( either Oracle DB, SQL Server,  IBM DB2, PostgreSQL, MySQL etc.) or any file system.

What is Dataserver?

A ODI object that defines the physical connection to any database instance or any file system. It basically store the host, username & password related details of an instance.

To define any physical connection in ‘Physical Architecture‘, you have to create a Data Server. Just click on your desired technology in Physical Architecture & click on ‘ New Data Server’.

NEWDATASERVER
Physical Architecture

After that you have to provide instance details. In case of any Database instance provide host, user/password & JDBC details.

Database server new
Data Server: DS_ORACLE_DEV

JDBC Details in case of oracle database as instance:

JDBC details
JDBC Details Data Sever: Localhost

In case of any file. Just provide the folder path from where you pick raw files in interface.

file1
Data Server File Type

JDBC Details in case of file:

file2
JDBC Details File Type

Now after adding the Data-server, next step is to add the ‘Physical Schema‘ in Data-server.

What is Physical Schema?

Physical Schema represents the actual connection to the data source (any database instance or any file system).

ODI Physical Schema corresponds to a pair of schema:

  • Main Schema (Data) – In which ODI look for the source and target objects for the interfaces.
  • Work Schema – Generally used by ODI for staging purpose, here ODI create temporary data objects associated to the sources and targets. Example C$, I$, E$ tables. It is always preferred to create & use separate work schema.

To define any physical schema, go to Data Server you created and then just click on new ‘New Physical Schema’.

PS.png
New Physical Schema

After that you have to provide Schema Name & Work Schema name using drop down. It will show you all the schema present in selected data-server instance.

PS02
Physical Schema Configuration

  • RAW_SCHEMA – schema where my source/target tables lies.
  • ODI_WORK_SCHEMA – Created for ODI staging purpose.

ps03.PNG
Physical Schema – RAW_SCHEMA present in Dataserver DS_ORACLE_DEV

Now after adding the Physical Schema, next step is to create Logical Schema & Context.

What is Logical Schema & Context?

  • Logical schema represents the logical name associated to that source/target objext.
  • One logical schema can be associated with multiple physical schema along with context, i.e. one logical schema is associated with different physical schema using different context.

Create Context:

To add any new context, go to Context Section and then just click on new ‘New Context’.

contextContext 02

Create Logical Schema:

To define any logical schema in ‘Logical Architecture‘ . Just click on your desired technology & click on ‘New Logical Schema’.

LA

LS02
Add New Logical Schema

After that you have to provide the logical schema name & Physical Schema mapping with context.

LS03
Logical Schema

So in Dev Context, Logical Schema LS_RAW_SCHEMA is pointing to Physical Schema DS_ORACLE_DEV.RAW_SCHEMA.

It may be possible that in Test Context, Logical Schema LS_RAW_SCHEMA is pointing to different Physical Schema DS_ORACLE_TEST.RAW_SCHEMA.

lS_Context.PNG

Refer this video clip for better understanding of Logical Schema & Context relationship.


Thanks!

Happy Learning! Your feedback would be appreciated!