RestAPI for Oracle DBaaS Cookbook

Hello Friends,

Great news that I wanted to share with you all!!! I am delighted to announce the launch of my first Oracle technical book named “RestAPI for Oracle DBaaS Cookbook”. 

This book authored by myself (Hariprasath Rajaram, Cloud OCP on DBAAS),  Skant Gupta (Oracle Certified Master) and Stuti Kaushal (Oracle Certified Associate)  describe the hands-on guide imparting practical working knowledge and guidance of RestAPI concepts.

The Target Audience

The primary target audience are Oracle DBAs and Oracle Cloud DBA’s (DBaas). This book has been diligently planned for all levels of DBA experts. Coming right from the real-world practicing DBA’s, this book is written as a hands-on guide imparting practical working knowledge and guidance of RestAPI concepts.

What this book covers

Chapter 1, Introduction to RestAPI covers the Fundamentals of RestAPI, verbs, methods and status codes.

Chapter 2, Access Rules discusses the method of creating a rule, viewing a rule, enabling a rule, disabling a rule and deleting an access rule using RestAPI in Oracle Database Cloud Service.

Chapter 3, Backup and Recovery discusses method to start a backup operation, viewing all backup operations, starting a recovery operation and viewing all recovery operations.

Chapter 4, Patches illustrates the details about various operations being performed on Oracle Database Cloud patches using RestAPI

Chapter 5, Service Instances gives an overview on the usage of Oracle Database Cloud Service instances using various RestAPI methods

Chapter 6, Snapshots discusses the procedure of creating, viewing and deleting a snapshot in database using RestAPI

Chapter 7, SSH Keys describes usage of Oracle Database Cloud service RestAPI to create and manage database endpoints to view and manage Secure Shell (SSH) public keys of a Database

This book is available on Paperback, Kindle Edition and PDF version.

RestAPI for Oracle DBaaS Cookbook-Paperback

  
BUY ON AMAZON   https://www.amazon.com/dp/1731311249/

Co-Author’s

Special thanks for Skant Gupta for initiated the writing of this book and helped me to complete it on time successfully along with Stuti Kaushal.

Skant Gupta
Stuti Kaushal
RestAPI for Oracle DBaaS Cookbook-Kindle Edition
BUY ON AMAZON   https://www.amazon.in/dp/B07L2C5GKH/

Family Members

Special thanks to my wife and son who have patience during this time and helped me to complete this book. Without your support, i would not have completed this!!!

Doyensys Team

Thanks Somu Chockalingam, president of Doyensys Inc, for his encouragement and motivation to complete this book successfully. Thanks to all Doyensys DBA team who has supported me during this journey. Special Thanks To Marimuthu Pandu

Aioug Chennai Chapter Team

Thanks for overall support and guidance from AIOUG team, especially Hariharaputhran and Harish for their support.

Hariharaputhran Vaithinathan
Harish Panduranga Rao
Justin Michael Raj
Veeratteshwaran Sridhar

 

Sincerely,
Hariprasath Rajaram
Oracle DBA Senior Consultant,
Doyensys Inc.Chennai,
India
Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DkeGfZsxfzXxHD6gTg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Oracle SQL*Loader (sqlldr) Utility Tips And Tricks

 

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the data file. You can use SQL*Loader to do the following:

  • Load data across a network if your data files are on a different system than the database.
  • Load data from multiple data files during the same load session.
  • Load data into multiple tables during the same load session.
  • Specify the character set of the data.
  • Selectively load data (you can load records based on the records’ values).
  • Manipulate the data before loading it, using SQL functions.
  • Generate unique sequential key values in specified columns.
  • Use the operating system’s file system to access the data files.
  • Load data from disk, tape, or named pipe.
  • Generate sophisticated error reports, which greatly aid troubleshooting.
  • Load arbitrarily complex object-relational data.
  • Use secondary data files for loading LOBs and collections.
  • Use conventional, direct path, or external table loads.

  1. Input Datafile contains file containing the data to be loaded.The record format can be specified in the control file with the INFILE parameter.
    cat /home/oracle/employee.txt
    
    100,Hari,MCA,5000
    200,Karthi,Technology,5500
    300,Sunil,Technology,7000
  2. Control file contains the instructions to the sqlldr utility. This tells sqlldr the location of the input file, the format of the input file, and other optional meta data information required by the sqlldr to upload the data into oracle tables.
load data
infile '/home/oracle/employee.txt'
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

The above control file indicates the following:

  • infile – Indicates the location of the input data file
  • into table – Indicates the table name where this data should be inserted
  • fields terminated by – Indicates the delimiter that is used in the input file to separate the fields
  • ( emp_id, emp_name, dept, salary ) – Lists the name of the column names in the table into which the data should be uploaded

3. Discard file contains rejected rows those were discarded because they were filtered due to a statement in SQL*Loader control file. Data written to any database table is not written to the discard file.

4. Bad File contains the records which are rejected either by SQL Loader or by the database because of the bad formatting or data type mismatch.It will have the same name as the data file, with a .bad extension.

5. Log File  contains a detailed summary of the load, including a description of any errors that occurred during the load.

Basics execution of sqlloader :-

Create the table structure:-

SQL> create table employee(emp_id integer,emp_name varchar2(10),dept varchar2(15),salary integer,Join_date date);

Table created.

Input Data :-(Datafile)

cat /home/oracle/employee.txt

100,Hari,MCA,5000
200,Karthi,Technology,5500
300,Sunil,Technology,7000

INSERT : Default value for loading data using SQL loader.

Sqlldr control file :-

[oracle@test]  cat > sqlldr-add-records.ctl
load data
infile '/home/oracle/employee.txt'
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Run the sqlloader utility:-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-add-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 13 02:40:28 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 3

Table EMPLOYEE:
3 Rows successfully loaded.

Check the log file:
sqlldr-add-records.log
for more information about the load.

Check the loaded data in table 

SQL> select * from hari.employee;

EMP_ID      EMP_NAME      DEPT        SALARY       JOIN_DATE
---------- ---------- --------------- -------      ---------
100           Hari        MCA          5000
200          Karthi       Technology   5500
300           Sunil       Technology   7000

APPEND :

Input data into existing table employee :-

cat /home/oracle/newemployee.txt

400,Ram,DBA,5500
500,Siva,Developer,7000
[oracle@test]  cat > sqlldr-append-records.ctl
load data
infile '/home/oracle/newemployee.txt'
append
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )
[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-append-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Thu Sep 13 02:47:18 2018Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-append-records.log
for more information about the load.
SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY    JOIN_DATE
---------- ---------- --------------- ---------- ---------
100        Hari      MCA              5000
200        Karthi    Technology       5500
300        Sunil     Technology       7000
400        Ram       DBA              5500
500        Siva      Developer        7000

5 rows selected.

TRUNCATE :

Table Structure :

SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY     JOIN_DATE
---------- ---------- --------------- ---------- ---------
100        Hari       MCA             5000
200        Karthi     Technology      5500
300        Sunil      Technology      7000
400        Ram        DBA             5500
500        Siva       Developer       7000

5 rows selected.

Input Data file :-

cat /home/oracle/newemployee.txt

400,Sam,DBA,5500
500,Scott,Developer,7000

Sqlloader control file :

cat > sqlldr-truncate-records.ctl
load data
infile '/home/oracle/newemployee.txt'
truncate
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Sqlloader execution :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-truncate-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 15:22:16 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-truncate-records.log
for more information about the load.

Check the table data,

SQL> select * from hari.employee;

EMP_ID     EMP_NAME   DEPT            SALARY     JOIN_DATE
---------- ---------- --------------- ---------- ---------
400        Ram        DBA             5500
500        Siva       Developer       7000

REPLACE : 

When REPLACE is specified, the entire table is replaced, not just individual rows.It uses an implicit truncate of the table to replace existing data. It does not look at specific rows but rather removes all rows and inserts new ones, even if the new data is the same as the original data.

Table Structure :-

SQL> select * from hari.employee;

EMP_ID     EMP_NAME    DEPT         SALARY  JOIN_DATE
---------- ----------  -----------  ------- ---------
4000        Steven     ITlead        50000
5000        Brad       SystemAdmin   10000

Input data :-

[oracle@orcl:~ orcldemo] cat /home/oracle/newemployee.txt
4000,Steven,ITlead,50000
5000,Brad,SystemAdmin,10000

Control file :-

[oracle@orcl:~ orcldemo] cat sqlldr-replace-records.ctl

load data
infile '/home/oracle/newemployee.txt'
replace
into table employee
fields terminated by ","
( emp_id,emp_name,dept,salary )

Sqlloader execution :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlldr-replace-records.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 17:08:22 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 2

Table EMPLOYEE:
2 Rows successfully loaded.

Check the log file:
sqlldr-replace-records.log
for more information about the load.

Check Table data,

SQL> select * from hari.employee;

EMP_ID     EMP_NAME    DEPT         SALARY  JOIN_DATE
---------- ----------  -----------  ------- ---------
4000        Steven     ITlead        50000
5000        Brad       SystemAdmin   10000

Bad and Discard file Scenario :-

Table Structure :-

SQL> create table dept (deptno number,dname varchar(20),location varchar(20));

Table created.

Input data:-

cat sqlloader.dat

10,ACCOUNTING,PAK
30,SALES,RUSSIA
4D,OPERATIONS,USA
50,HUMAN RESOURCE,USA
60,IT,USA
70,PRODUCTION,ENG
80,QUALITY,USA

Control file :

cat sqlload.ctl
load data
infile '/home/oracle/sqlloader.dat'
badfile '/home/oracle/badrecords.bad'
discardfile '/home/oracle/dicardload.dsc'
into table dept
WHEN LOCATION!='USA'
fields terminated by ","
(DEPTNO,DNAME,LOCATION)

Run the sqlloader utility :-

[oracle@orcl:~ orcldemo] sqlldr hari/hari control=/home/oracle/sqlload.ctl

SQL*Loader: Release 12.2.0.1.0 - Production on Fri Sep 14 14:43:30 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.

Path used: Conventional
Commit point reached - logical record count 7

Table DEPT:
3 Rows successfully loaded.

Check the log file:
sqlload.log
for more information about the load.

Discard file record:-

[oracle@test:~ orcldemo] cat dicardload.dsc
4D,OPERATIONS,USA
50,HUMAN RESOURCE,USA
60,IT,USA
80,QUALITY,USA

Bad file record:-

[oracle@test:~ orcldemo] cat badrecords.bad
10,ACCOUNTING,PAK
30,SALES,RUSSIA
70,PRODUCTION,ENG

Catch Me On:- Hariprasath Rajaram

Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba