In this blog we will explore the three different service models of cloud computing.
IaaS – Infrastructure as Service
Paas – Platform as Service
SaaS – Software as Service
Infrastructure as Service
When user/business is only interested to purchase infrastructure like physical machines, storage, server, network etc. So instead of buying & setting up on-premise hardware, go for Iaas from cloud service provider.
This type of model allows user to purchase computing resources such as server, storage & networking on-demand from service provider. Rest of the thing will be manage by user/business like OS, applications environment, runtime, application data etc.
Common Service Providers: Oracle Cloud, AWS, Azure, GCP.
Platform as Service
When user/business is interested to purchase a platform where they can start develop, manage & operate applications.
For example you want to develop a web application. For that you need a platform where you can write your code, execute the code & database to store the application data. So instead of taking purchasing Iaas and setup everything else by own. You can go with the option of Paas i.e. Iaas + software required to build application.
Enable user/business to focus on development instead of setting up the infra required to build that application. User just have to manage its application, rest of the things manage by service provider.
Software as Service
Cloud based software available over the internet. User can avail the services by purchasing the license/subscription. These application completely managed by the service providers. User does not have to worry about Infrastructure, runtime environment or any application code upgrade.
Best Saas software example: Salesforce.com, JIRA, Netflix, Dropbox, Slack etc.
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 PIPELINEDclause and use the PIPE ROWcall 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!
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;
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 datain 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.
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;
/
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!
Problem Statement: Let say we have to setup multiple application (App 1, App 2 & App 3) on machine. All these application require different version of software/libraries dependencies. How we can setup applications so that there is no conflict of libraries version between applications.
Earlier times approach where we can setup different physical machines for all the applications. Following are some disadvantages:
Huge cost involved for setup different machines.
Wastage of resources in individual machines.
Difficult to scale & manage the applications.
Old Approach: Setup VMs (Virtual Machines) on top of one host machine & run applications separately. See below diagram
In this kind of hypervisor based virtualization model each VM has its own OS i.e. separate resources allocated for VM (RAM, HDD and CPU). Following are some disadvantages:
Entire OS loads first then the app start. So Boot time is slow.
VMs are generally huge is size (GB’s).
Wastage of unused resources in VM.
Deployment is not easy.
Latest Approach:Containerization – Process of packaging of an application with its required files, libraries/dependencies to run in efficient way in isolated user space called container.
Its a form of OS virtualization, where containers shares the host OS kernel. Containers are light weight as it holds only required files & libs & consume resources whenever required.
‘Container based technology’ which enable developer to create, run & deploy application as efficient light weight container. You can create the containers using the docker images i.e. read only template. You can get the images for OS, Programming Language, Databases, DevOps etc.
Docker is easy to install application, available for variety of Linux, macOS & Windows. There are several methods to install the Docker on your machine, you can refer all the methods at docs.docker.com/
In this blog we will install docker using the shell script on Ubuntu box.
curl -fsSL https://get.docker.com -o get-docker.sh
sudo sh het-docker.sh
Checking the docker version after installation using docker -v or docker –version
Testing Docker Installation:
Docker is now installed on the system, lets run the first container to test the docker installation using docker official image “Hello-World”. You can refer the complete documentation of this image here.
Pulling the image of ‘hello-word‘ from docker hub. You can check all downloaded images in your local repository using docker images.
Now we have hello-world image present in local repository. We can use docker run command to launch the container using this image.
Listing all the container using docker ps -a command.
We are able to test the doker installation by running first container successfully. Let’s move to next step by creating the custom images by passing our own set of code.
Creating Custom Docker Image:
There are many options to create the custom docker image. Here we are going to use the Dockerfile to create multiple custom docker custom images.
Here I’m going to create two custom images using the base images of Python version 2.7 & 3.8 and will provide common python code for both the apps. Later going to create the containers for these images.
I have create one Dockerfile, first I’m using Python 2.7 version image as base.
Sample python script:
Creating custom image for python 2.7 using docker build command.
python2.7app custom image is created
After this I have edited the Dockerfile & changed the Python version to 3.8
Creating second custom docker image for python 3.8:
python3.8app custom image is created
Checking all the docker images: So we have our own custom images created. See below image python2.7app & python3.8app.
Launching Docker Containers:
Containers created for separate images & code is also executed.
Checking all the container using docker ps -a command
Check the details of containers like IP, Ports etc using inspect command.
Pushing custom images to Docker Hub:
First you have to sign up for docker hub: hub.docker. You will get you docker id i.e. username.
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:
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.
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."
}]
}
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.
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;
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.
