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!

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!

PL/SQL Scenario Questions – Part 1

PL/SQL Scenario Questions – Part 1

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

Visit Part 2: PL/SQL Scenario Questions – Part 2


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!

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 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!

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!