PL/SQL Scenario Questions – Part 2

In this blog we will explore some PL/SQL – Scenario questions related to Oracle PL/SQL subprogram functions only.

  • Function Input, Output Parameters & Return Type
  • Calling Function using Dynamic SQL
  • Function used in case & if statement
  • DDL & DMLs in Oracle Subprogram Function
  • Calling function from SQL statement
  • Table Function
  • Pipeline Table Function

Scenario 1. Can function have output parameter & return type both. If yes how you can call function & retrieve output.

Solution 1: Function can have input/output parameters (optional) & return type(mandatory).

Sample Function having input, output & return type:

create or replace function fnc_test (INPUT_PARAMETER IN NUMBER, OUTPUT_PARAMETER OUT NUMBER)
return number
as
begin
OUTPUT_PARAMETER:= 2;
return INPUT_PARAMETER;
end;
/

declare
ln_input number:=1;
ln_num_ret number;
ln_num_out number;
begin

ln_num_ret:=  fnc_test(ln_input,ln_num_out) ;
dbms_output.put_line('ln_num_ret:='|| ln_num_ret);
dbms_output.put_line('ln_num_out:='|| ln_num_out);
end;
/

Calling Function using Dynamic SQL

See below example calling function using CALL statement.

create or replace function fnc_square (INPUT_PARAMETER IN NUMBER, OUTPUT_PARAMETER OUT NUMBER)
return number
as
begin
OUTPUT_PARAMETER:= INPUT_PARAMETER;
return INPUT_PARAMETER*INPUT_PARAMETER;
end;
/

set serveroutput on;
declare
ln_input number:=4;
ln_num_ret number;
ln_num_out number;
lv_sql varchar2(100);
begin
 
lv_sql:= 'call fnc_square(:input,:output) into :return' ;

execute immediate lv_sql using  in ln_input , out ln_num_out, out ln_num_ret;

dbms_output.put_line('ln_num_ret:='|| ln_num_ret);
dbms_output.put_line('ln_num_out:='|| ln_num_out);
end;
/

Scenario 2: Can you use function in if statement or case statement in PL/SQL.

Solution: Yes we can use in if/case statement like variable if it does not have any arguments. Only return type.

create or replace function fnc_check 
return number
as
begin
return 1;
end;
/

set serveroutput on;
declare
begin
if (fnc_check=1) then
dbms_output.put_line('Hello from if statement');
else
dbms_output.put_line('Bye');
end if;
end;
/

set serveroutput on;
declare
begin
  case fnc_check
  when 1 then dbms_output.put_line('Hello from case');
   else dbms_output.put_line('Bye');
end case;
end;
/

Scenario 3. Can you fire DDLs & DMLs in function?

Solution 3: Yes we can, but can not use those function in SQL statement. See below example.

CREATE OR REPLACE FUNCTION FNC_TEST(
    ACCOUNT_ID IN NUMBER)
  RETURN VARCHAR2
IS
  ln_out VARCHAR2(100);
BEGIN
  BEGIN
    EXECUTE immediate 'DROP TABLE T_ACCOUNT';
     dbms_output.put_line('Table dropped T_ACCOUNT');
  EXCEPTION
  WHEN OTHERS THEN
    NULL;
  END;
  
EXECUTE immediate 'CREATE TABLE T_ACCOUNT (ACCOUNT_ID NUMBER)';
dbms_output.put_line('Table created: T_ACCOUNT');

EXECUTE immediate 'insert into T_ACCOUNT values ('|| ACCOUNT_ID||')';
commit;
dbms_output.put_line('DMLs done for T_ACCOUNT');

ln_out:= ACCOUNT_ID || ' got created!';
RETURN ln_out;
END FNC_TEST;
/

declare
ln_out varchar2(100);
begin
ln_out:=FNC_TEST(1000);
dbms_output.put_line('ln_out:='|| ln_out);
end
;

But you cannot call this function from SQL statement. See below:

You cannot call function from SQL statement if any DDL/DMLs or Output parameter is present.

You can call function easily if it has return type or input parameters. See below sample

Example 1: Function calling from SQL

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;
/ 
select FNC_SQUARE(2) from dual;

Example 2: Function having multiple input parameters & return type, calling from SQL.

create or replace FUNCTION FNC_SQUARE(
    INPUT_ID IN NUMBER, INPUT_NUM IN NUMBER)
  RETURN NUMBER
IS
  ln_out NUMBER;
BEGIN
ln_out:= INPUT_ID*INPUT_NUM;
RETURN ln_out;
END;
/ 
select FNC_SQUARE(2,3) from dual;

Example 3: Function having only return type, calling from SQL.

create or replace function fnc_check 
return number
as
begin
return 1;
end;
/

Scenario 4. Can Oracle Function return more that one value.

Solution: Function can only have one return type. But you an return a table using Table Functions. Check out below example of table function.

CREATE type objects_type
 AS
  OBJECT
  (
 OBJECT_NAME VARCHAR2(128),
    OBJECT_TYPE VARCHAR2(23) ,
    CREATED     DATE );
  /
CREATE OR REPLACE type t_row_tab
AS
  TABLE OF objects_type;
CREATE OR REPLACE FUNCTION get_objects(
    input_type VARCHAR2)
  RETURN t_row_tab
AS
  obj_record t_row_tab:=t_row_tab();
BEGIN
  SELECT objects_type(OBJECT_NAME,OBJECT_TYPE,CREATED) BULK COLLECT
  INTO obj_record
  FROM user_objects
  WHERE OBJECT_TYPE = input_type ;
  RETURN obj_record;
END;
/

Scenario 5. In which scenario you don’t specify anything after return keyword in function body,

Solution: While writing the pipeline table function. Pipelining enables a table function to return rows faster and can reduce the memory, which is required to cache results.

It include the PIPELINED clause and use the PIPE ROW call to push rows out of the function as soon as they are created, instead of complete table collection. 

CREATE OR REPLACE FUNCTION get_objects
  RETURN t_row_tab PIPELINED
IS
  obj_record t_row_tab:=t_row_tab();
BEGIN
  FOR i IN 1 .. 10 LOOP
    PIPE ROW(objects_type('TABLE'||i, 'TABLE ',sysdate));   
  END LOOP;
  RETURN;
END;
/

SELECT * FROM TABLE (get_objects);

Thanks!

Happy Learning! Your feedback would be appreciated!

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!

Cursors in PL/SQL

In this blog we will explore the feature available in PL/SQL to make data available within PL/SQL programs from database tables for data manipulation.


When we fire any SQL statement from a PL/SQL program, at back end Oracle Database assign a private work area for that SQL statement which holds the information for the statement like no of rows returned or affected.

There is also a way to assign a name to that private work area & manipulate the result or information of SQL statement i.e. declaring a cursor & using it in PL/SQL block.

Cursor is nothing but a pointer to result of a SQL query.

Life Cycle of Cursor:

  • Declare (Initializing the cursor in memory)
  • Open (Opening the cursor i.e. allocation memory or pointer to SQL Query )
  • Fetch (Retrieving data from the cursor)
  • Close (Closing Cursor i.e. releasing the allocated memory)

Types of Cursors

Implicit Cursors: Whenever you execute a SQL Statement (Select Into, Insert, Update, Delete or Merge) in PL/SQL program, automatically Oracle manage the cursor for that SQL statement. Oracle database handles many of cursor related operations example declaring, opening, fetching records and close operation.

Explicit Cursors: Select statement declared by user. Cursor related operations need to perform by user only explicitly.

If you see below simple example, I have declare a cursor CUST, opened the cursor, fetched the data in variable & finally closed the cursor.

Refer this blog for understanding the cursor based records: Records in PL/SQL

DECLARE
  CURSOR CUST IS SELECT * FROM CUSTOMERS;
  CUST_rec CUST%rowtype;
BEGIN
  OPEN CUST;
  FETCH CUST INTO CUST_rec;
  dbms_output.put_line( CUST_rec.FULL_NAME || ', ' ||CUST_rec.EMAIL_ADDRESS);
  CLOSE CUST;
END;
/

Cursor Attributes

Cursor attributes generally used to get the state of the cursor in PL/SQL program. For example if you want to check if cursor is opened or not. Or how many records got affected from the latest DML operation.

To reference a cursor attribute, append cursor_name or variable with % & attribute name. ex. cursor_name%FOUND

For Implicit Cursors: Cursor name always as SQL. example. SQL%FOUND

  • %FOUND – Return true or false based on most recent fetch. True if rows return in recent fetch. False if no row returned.
  • %NOTFOUND – Opposite of %FOUND. True if no row returned. False if ow returned.
  • %ROWCOUNT – Return no of rows fetched or affected in case of DML.
  • %ISOPEN – Return true if cursor is open, otherwise false.

%ISOPEN – Always return false in case of implicit cursors. As implicit cursors operations handles by the Oracle automatically.

Example of Implicit Cursors:
BEGIN
  UPDATE CUSTOMERS SET FULL_NAME = 'Gregory Sanchez' WHERE CUSTOMER_ID=10;
  IF (sql%found) THEN
    dbms_output.put_line( 'Record updated');
    dbms_output.put_line( 'No Record affected: ' ||sql%rowcount  );
  COMMIT;
  END IF;
    IF (sql%isopen) THEN
    dbms_output.put_line( 'Implicit cursor is open.');
    else
    dbms_output.put_line( 'Implicit cursor is closed.');
  END IF;
END;
/
Example of Explicit Cursor:
DECLARE 
CURSOR CUST IS select * from CUSTOMERS where CUSTOMER_ID<=10 order by 1 desc;
CUST_rec CUST%rowtype;
begin
open CUST;
LOOP
fetch CUST into CUST_rec;
dbms_output.put_line( CUST_rec.FULL_NAME || ', ' ||CUST_rec.EMAIL_ADDRESS);
EXIT WHEN CUST%NOTFOUND;
END LOOP;
close CUST;
end;
/

Another example of Explicit Cursor:

DECLARE
  CURSOR CUST
  IS
    SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID<=10;
  CUST_rec CUST%rowtype;
BEGIN
  OPEN CUST;
  FETCH CUST INTO CUST_rec;
  WHILE CUST%FOUND
  LOOP
    dbms_output.put_line( 'Customer Id: '|| CUST_rec.CUSTOMER_ID || ' ' || CUST_rec.FULL_NAME || ', ' ||CUST_rec.EMAIL_ADDRESS);
    FETCH CUST INTO CUST_rec;
  END LOOP;
  CLOSE CUST;
END;
/

Example of Parameterized Cursor:

DECLARE
  CURSOR CUST (INPUT_CUST_ID IN NUMBER)
  IS
    SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID=INPUT_CUST_ID;
  CUST_rec CUST%rowtype;
BEGIN
  OPEN CUST(11);
  FETCH CUST INTO CUST_rec;
    dbms_output.put_line( 'Customer Id: '|| CUST_rec.CUSTOMER_ID || ' ' || CUST_rec.FULL_NAME || ', ' ||CUST_rec.EMAIL_ADDRESS);
  CLOSE CUST;
END;
/

You can have default values for the parameters in Parameterized Cursor . See below structure.

CURSOR cursor_name (
    parameter_name1 datatype := default_value, 
    parameter_name2 datatype := default_value, 
    parameter_name3 datatype := default_value, 
    ...
) IS SQL_Query;

Cursor For Loop

Here you don’t have to worry about cursor operations like open, fetch, close. In Cursor For Loop Oracle automatically take care of all these operation. Refer below sample example:

DECLARE
  CURSOR CUST
  IS SELECT * FROM CUSTOMERS WHERE CUSTOMER_ID<=10;
BEGIN
  for i in CUST
  LOOP
  if mod(i.CUSTOMER_ID,2)=0 then
    dbms_output.put_line( 'Customer Id: '|| i.CUSTOMER_ID || ' ' || i.FULL_NAME || ', ' ||i.EMAIL_ADDRESS);
    end if;
  END LOOP;
END;
/

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

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


Thanks!

Happy Learning! Your feedback would be appreciated!

Records in PL/SQL

In this blog we will explore the record in PL/SQL.

Record is composite data structure i.e. composed of one or more fields. Similar in concept to rows of any database table. Likewise database table has one or many columns of various datatypes, and each column store individual values. Similarly a record, composed of one or more fields. As a whole it does not store any values, each individual element has its own value.

Declaring Records

There are three ways we can declare a record.

  • Table-Based Records
  • Cursor-Based Records
  • User-Defined Records

Table-Based Records

Creating record ‘my_record_table‘ based on row of table user-objects : Using %ROWTYPE – attribute provides a record type that represents a row in a database table. See below example:

set serveroutput on;
DECLARE
  my_record_table user_objects%ROWTYPE;
BEGIN
  SELECT * INTO my_record_table FROM user_objects WHERE object_name='DUAL';
  dbms_output.put_line('OBJECT_TYPE : '|| my_record_table.OBJECT_TYPE);
END;
/

Cursor-Based Records

Creating record ‘my_record_cursor‘ based on row of explicit cursor. See below example.

DECLARE
  CURSOR my_cursor
  IS
    SELECT * FROM user_objects WHERE object_name='DUAL';
  my_record_cursor my_cursor%rowtype;
BEGIN
  OPEN my_cursor;
  FETCH my_cursor INTO my_record_cursor;
  dbms_output.put_line('OBJECT_TYPE : '|| my_record_cursor.OBJECT_TYPE);
  CLOSE my_cursor;
END;
/

User-Defined Records

Defining a rcords using TYPE & Records statement. Later on creating instance of that type. my_record

TYPE type_name IS RECORD
   (field1 data_type1 [NOT NULL] := [DEFAULT VALUE],
    field2 data_type2 [NOT NULL] := [DEFAULT VALUE],
    field3 data_type3 [NOT NULL] := [DEFAULT VALUE]
    ...
    );

See below example.

DECLARE
type my_rec_type
IS
  record
  (
    OBJECT_TYPE user_objects.OBJECT_TYPE%TYPE ,
    CREATED user_objects.CREATED%TYPE);
  my_record my_rec_type;
BEGIN
  SELECT OBJECT_TYPE,
    CREATED
  INTO my_record
  FROM user_objects
  WHERE object_name='DUAL';
  dbms_output.put_line('OBJECT_TYPE : '|| my_record.OBJECT_TYPE);
  dbms_output.put_line('CREATED : '|| my_record.CREATED);
END;
/

Creating Record Type at Database Level

It can be created at database level, which can be stored type as database object. Later on you can use in your PL/SQL subprograms or anonymous block. It like you have created a new data type.

CREATE type obj_type
AS
  OBJECT
  (
    OBJECT_NAME VARCHAR2(128),
    OBJECT_TYPE VARCHAR2(23) ,
    CREATED     DATE );
  /
  SELECT * FROM user_objects WHERE OBJECT_NAME='OBJ_TYPE' ;

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!

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!