Oracle SODA for Python

Simple Oracle Document Access (SODA) set of APIs that allow you to create collection of documents in Oracle Database, so that later you can retrieve/query those documents. Using SODA implementation you can perform CRUD (create,read,update,delete) on documents. Document can be of any MIME type, in this blog we will focus on JSON.

There are separate SODA implementations available for languages like Java, Python, PL/SQL & C etc. however SODA for REST can be accessed from all those languages which support REST framework. Refer Oracle documentation for other implementation : Oracle SODA.

In this blog we will explore SODA for Python implementation. We will see how we can store/retrieve/filter JSON document in Oracle Database using Python. Oracle Database supports storing and querying JSON data natively.

Refer this blog for understanding JSON: What is JSON?


Prerequisite: Oracle Client 18.3 or higher, Oracle Database 18.1 or higher required for SODA. After that assign SODA_APP role to the database user in which you are going to create SODA collections.

-- Check client version using below query
SELECT distinct client_version
FROM v$session_connect_info
WHERE sid = SYS_CONTEXT('USERENV', 'SID');

-- Check database version using below query
SELECT * FROM v$version;
--or
SELECT * FROM v$instance; 
-- SODA grant
GRANT SODA_APP TO <DB-USER>;

I’m using Oracle 19c Database, Python3 & cx_Oracle version 8. SODA is part of Oracle Python module cx_Oracle no need to install any other module.

Other Python Module used: configparser, json & requests.


SODA Collection

Soda collection is nothing but a database table. Collections contains document which can be represent as row of database table. createCollection method create a new collection in database & if collection already exists its just open without error.

SodaDatabase.createCollection(name, metadata=None, mapMode=False)

Lets create one simple collection providing only name argument.

print('Starting session...')
try:
    dsn = cx_Oracle.makedsn(hostname, port, service_name=servicename)
    conn = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    print('Connected:' + conn.version)

    soda = conn.getSodaDatabase()
    collection = soda.createCollection("first_collection")
    conn.close()
    print('connection closed...')
except cx_Oracle.Error as e:
    print('Error:', e)

Collection FIRST_COLLECTION i.e. created using Python code: See below objects created by default in the database: Table, Index & Object

Collection Metadata: ‘FIRST_COLLECTION’

As metadata argument is not specified at the time of collection creation. Hence by default Oracle created a collection that support JSON document only & uses system generated keys. You can also provide metadata at the time of collection creation: Refer soda-collection-metadata-components-reference.

See below “JSON_DOCUMENT” constraint added automatically. You cannot store non json document in this collection, it will raise exception.

Insert Documents

Let’s insert some JSON document in this collection “FIRST_COLLECTION”. Here we have used GitHub free API to fetch some JSON data using request module.

We will use insertOne method to insert document. Input argument must be a SODA document or a dictionary or list. Refer below how to create a SODA document.

print('Starting session...')
try:
    dsn = cx_Oracle.makedsn(hostname, port, service_name=servicename)
    conn = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    print('Connected:' + conn.version)

    soda = conn.getSodaDatabase()
    collection = soda.createCollection("first_collection")

    url = 'https://api.github.com/users/shobhit-singh/repos'
    req = requests.get(url)
    text = req.text

    parsed_dict = json.loads(text)
    collection.insertOne(parsed_dict)

    conn.close()
    print('connection closed...')
except cx_Oracle.Error as e:
    print('Error:', e)

A document is created in the table FIRST_COLLECTION: Refer JSON_DOCUMENT column for JSON data.

Code snippet for inserting json document using file:

try:
    dsn = cx_Oracle.makedsn(hostname, port, service_name=servicename)
    conn = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    print('Connected:' + conn.version)

    soda = conn.getSodaDatabase()
    collection = soda.createCollection("first_collection")

    with open('jsonndata.json') as f:
        data = json.load(f)

    collection.insertOne(data)
    conn.commit()
    conn.close()
    #print('connection closed...')
except cx_Oracle.Error as e:
    print('Error:', e)

There are other ways also by which you can insert document in collection. Like insertOneAndGet, insertMany etc. Refer api_manual/soda

Get Collection Names

To get all collection names present in database. Use getCollectionNames method:

print('Starting session...')
try:
    dsn = cx_Oracle.makedsn(hostname, port, service_name=servicename)
    conn = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    print('Connected:' + conn.version)

    soda = conn.getSodaDatabase()
    name = soda.getCollectionNames(limit=5)
    print(name)

    conn.close()
    print('connection closed...')
except cx_Oracle.Error as e:
    print('Error:', e)
        

There are various method available for collections like drop collection, find, truncate etc. Refer this link for details : soda-collection-object

Printing metadata of collection
print('Starting session...')
try:
    dsn = cx_Oracle.makedsn(hostname, port, service_name=servicename)
    conn = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    print('Connected:' + conn.version)

    soda = conn.getSodaDatabase()
    coll = soda.openCollection("git_repos")
    print(coll.metadata)

    conn.close()
    print('connection closed...')
except cx_Oracle.Error as e:
    print('Error:', e)
Get all documents inside a collection
print('Starting session...')
try:
    dsn = cx_Oracle.makedsn(hostname, port, service_name=servicename)
    conn = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    print('Connected:' + conn.version)

    soda = conn.getSodaDatabase()
    coll = soda.openCollection("first_collection")
    doc = coll.find()
    print(doc.getDocuments())

    conn.close()
    print('connection closed...')
except cx_Oracle.Error as e:
    print('Error:', e)

Retrieving Data

Retrieve JSON data from the document. Let’s just print all GitHub repo names present in that JSON.

print('Starting session...')
try:
    dsn = cx_Oracle.makedsn(hostname, port, service_name=servicename)
    conn = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    print('Connected:' + conn.version)

    soda = conn.getSodaDatabase()
    coll = soda.openCollection("first_collection")
    doc = coll.find()
    docs = doc.getDocuments()
    res = docs[0].getContent()

    for i in res:
        print (i["name"])

    # dumping complete data as json file
    json.dump(res, open ('data.json', 'w'), indent=4)

    conn.close()
    print('connection closed...')
except cx_Oracle.Error as e:
    print('Error:', e)
Filtering JSON documents data

Filter documents records in collection on the basis of some input dictionary or JSON string. Filter specification is also called a query-by-example (QBE).

Refer this guide for filter specification details. overview-soda-filter-specifications-qbes

try:
    dsn = cx_Oracle.makedsn(hostname, port, service_name=servicename)
    conn = cx_Oracle.connect(username,password,dsn,encoding=encoding)
    print('Connected:' + conn.version)

    soda = conn.getSodaDatabase()
    coll = soda.openCollection("first_collection")
    doc = coll.find()    
    docs = doc.filter({"Numbers": {"0": "Zero", "1": "One"}})
    #docs = doc.filter({"name": "BigDataEnthusiast"})

    print( 'Count' ,docs.count())
    print( 'Doc Key:' , docs.getDocuments())

    conn.close()
    print('connection closed...')
except cx_Oracle.Error as e:
    print('Error:', e)

There are so many method available for collections/documents. You can refer below mentioned cx_orale documentation for more learning.

Refer GitHub link all code used in blog: github.com/shobhit-singh/Python-Programs/tree/master/cx_oracle_soda

References:


Thanks!
Happy Learning! Your feedback would be appreciated!

What is JSON?

What is JSON?

While working with the Web Applications, REST APIs or document databases (eg. Oracle Autonomous JSON Database) we always deals with JSON. What is JSON & why its ideal data interchange format. Lets explore!

JSON – Java Script Object Notation

  • Derived from JavaScript.
  • Language independent text format for structuring data.
  • Light weight, easy to read/parse information.
  • Almost all language support this format eg. Python, R, Java, PL/SQL etc.
  • File extension: .json
  • Internet MIME Type: application/json

JSON is built using: Key Value Pair & Array – Ordered elements

  • Key:Value Pair ( Like Hash Map, Dict in Python, Associative array in PL/SQL)
  • Ordered list of values. (Array, List) example [1,2,3]

Sampe JSON:

{
  "firstName": "Calos",
  "lastName": "Smith",
  "isAlive": true,
  "age": 33,
  "address": {
    "streetAddress": "XYZ Street",
    "city": "New York",
    "state": "NY",
    "postalCode": "10021-3100"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "111 111-1234"
    },
    {
      "type": "office",
      "number": "111 555-4567"
    }
  ],
  "children": ["alex","marry"],
  "spouse": null
}

Some JSON Fundamentals

  • Key: Always string (double quotes only)
  • Value: String (double quotes only), Number, Bool (true/False), Object or List & Null.
  • Object: Unordered set of key value pairs. Object begins with { & ends with }. Inside object – Key:Value (key followed by a colon followed by the value always) separated by comma ex. { key1:par1, key2:pair2 }
  • Array: Ordered collection of values. Start with [ & ends with ]. Values separated by comma. [1,2,3,4] or [“A”, “B”,”C”,”D”].

See below example :

  • address is object – As it contains key values pairs. Example of nested object also as address is inside one big object.
  • children is example of array – you can access elements with index like children[0]
  • phoneNumbers . Objects inside Array.
{
	"age": 33,
	"address": {
		"streetAddress": "XYZ Street",
		"city": "New York",
		"state": "NY",
		"postalCode": "10021-3100"
	},
	"phoneNumbers": [{
			"type": "home",
			"number": "111 111-1234"
		},
		{
			"type": "office",
			"number": "111 555-4567"
		}
	],
	"children": ["alex", "marry"]
}

Python In-Built Module JSON

De-serialization – Decoding JSON data into python native datatype dict.

a) Reading JSON Data from API & converting it to Python Dictionary

import json
import requests

url = 'https://api.publicapis.org/entries'
req = requests.get(url)
text = req.text
print (type(text)) 

# json.loads - It will create a Python Dictionary
parsed_dict = json.loads(text)
print (type(parsed_dict))
print ((parsed_dict['entries'][2]))

Other example: Normal JSON String. Converting back to Python Dict

import json

normal_json_str = '{"id":101,"parent_id":null, "name":"XYZ", "is_owner":true}'
print(normal_json_str)
print(type(normal_json_str))

# json.loads - It will create a Python Dictionary
parsed_dict = json.loads(normal_json_str)

print(parsed_dict)
print(type(parsed_dict))

b) Reading JSON Data from file & converting it to Python Dictionary

import json

with open('sampledata.json') as f:
    data = json.load(f)
    print (type(data))
    print ((data['entries'][2]))

Serialization – Encoding data into native JSON.

a) Converting Python Dict to JSON Data & printing it. Output is string

import json

my_dict = { 'Aphabets' : { 'A':{'Fruit':'Apple','Object':'Aeroplane'},'B':{'Fruit':'Banana','Object':'Ball'}} , 
             'Numbers'  : {  0:'Zero', 1:'One'} 
           } 
print(type(my_dict))

print(json.dumps(my_dict, indent=4))

b) Converting Python Dict to JSON & saving it as file.

import json

my_dict = { 'Aphabets' : { 'A':{'Fruit':'Apple','Object':'Aeroplane'},'B':{'Fruit':'Banana','Object':'Ball'}} , 
             'Numbers'  : {  0:'Zero', 1:'One'} 
           } 
print(type(my_dict))

json.dump(my_dict, open ('jsonndata.json', 'w'), indent=4)

I have used jsonlint.com for validating JSON.

References:


Thanks!
Happy Learning! Your feedback would be appreciated!

PL/SQL Associative Array

  • Similar to hash tables in other programming languages. Dictionary in Python, like Key-Value pair.
  • Index-by tables, also known as associative arrays. Associative arrays are set of Key-Value pair.
  • Each key is unique & locate to use corresponding value in the array.
  • Key – String/PLS_Integer.  Value can be any PL/SQL datatype like Varchar2, Integer. (Check Oracle Documentation for restrictions if any )
  • It can be dense or sparse – i.e. no consecutive index order.
SET SERVEROUTPUT ON;
SET DEFINE OFF

DECLARE
        TYPE direction IS
        TABLE OF VARCHAR2(15) INDEX BY VARCHAR2(2);
    TYPE error_code IS
        TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER;

    TYPE state_info IS RECORD (
        state_code  CHAR(2),
        state_name  VARCHAR2(20)
    );
    TYPE states IS
        TABLE OF state_info INDEX BY PLS_INTEGER;
    lv_direction_dict   direction;
    lv_error_code_dict  error_code;
    lv_states           states;
    ln_index            VARCHAR2(2);
BEGIN
/* TABLE OF VARCHAR2(10) INDEX BY VARCHAR2(2) */
        lv_direction_dict('N') := 'North';
    lv_direction_dict('S') := 'South';
    lv_direction_dict('E') := 'East';
    lv_direction_dict('W') := 'West';
    lv_direction_dict('NE') := 'North-East';
    dbms_output.put_line(lv_direction_dict('N')); -- Accessing Single Value
  --dbms_output.put_line(lv_direction_dict('E')); -- Accessing Single Value
    
        ln_index := lv_direction_dict.first;

/*Iteration*/
    WHILE ln_index IS NOT NULL LOOP
        dbms_output.put_line(ln_index
                             || ':'
                             || lv_direction_dict(ln_index));
        ln_index := lv_direction_dict.next(ln_index);
    END LOOP;

    dbms_output.put_line('*************************************************'); 
    /* TABLE OF VARCHAR2(100) INDEX BY PLS_INTEGER */
    
        lv_error_code_dict(100) := 'Continue';
    lv_error_code_dict(200) := 'Ok';
    lv_error_code_dict(400) := 'Non-Authorized';
    lv_error_code_dict(203) := 'Bad Request';
    dbms_output.put_line('Error Code 200 Description :' || lv_error_code_dict(200)); -- Accessing Signle Value
        dbms_output.put_line('Error Code 400 Description :' ||
    lv_error_code_dict(400)); -- Accessing Signle Value
    
    /* TABLE OF Composite Datatype INDEX BY PLS_INTEGER */
        dbms_output.put_line('*************************************************');
    lv_states(1).state_code := 'JK';
    lv_states(1).state_name := 'Jammu & Kashmir';
    lv_states(2).state_code := 'HP';
    lv_states(2).state_name := 'Himachal Pradesh';
    lv_states(3).state_code := 'PB';
    lv_states(3).state_name := 'Punjab';
    lv_states(7).state_code := 'DL';
    lv_states(7).state_name := 'Delhi';
    lv_states(9).state_code := 'UP';
    lv_states(9).state_name := 'Uttar Pradesh';
    lv_states(29).state_code := 'KA';
    lv_states(29).state_name := 'Karnataka';
    lv_states(32).state_code := 'KL';
    lv_states(32).state_name := 'Kerala';

    FOR i IN 1..37 LOOP
        BEGIN
            dbms_output.put_line(lv_states(i).state_code
                                 || ':'
                                 || lv_states(i).state_name);

        EXCEPTION
            WHEN OTHERS THEN
                NULL;--key not available
            END;
    END LOOP;

END;
/

Output:

GitHub Link : https://github.com/shobhit-singh/PLSQL-Programs/blob/master/README.md

Referencehttps://docs.oracle.com/cd/A97630_01/appdev.920/a96624/05_colls.htm

Thanks!

Happy Learning! Your feedback would be appreciated!

PL/SQL VARRAY

  • Array in other languages – VARRAY in PL/SQL
  • Collection of items of same datatype.
  • When defining a VARRAY type, need to specify its maximum size. So upper bound is fixed.
  • Subscript is integer (i.e. index) starts from 1
  • VARRAY is always dense (consecutive subscript). You cannot delete an item in middle, but we can trim elements from end.
  • VARRAY is preferred when no. of elements known & accessed using sequence i.e. index.

VArray Example:

TYPE va_int_type IS VARRAY(7) OF NUMBER;
TYPE va_int_type IS VARRAY(7) OF VARCHAR2(100);
Index1234567
Value21344445777899

You cannot add elements after 7th index. As upper bound is fixed at the time of array creation. You can access element using index like A[2]

Please refer below GitHub code for more details.

VArray of Composite Datatype:

CREATE TYPE emails AS OBJECT (
    email_id       VARCHAR2(100),
    email_type     VARCHAR2(100),
    primary_email  CHAR(1)
);

CREATE OR REPLACE TYPE emaillist AS
    VARRAY(5) OF emails;
/

Reference: https://docs.oracle.com/cd/A97630_01/appdev.920/a96624/05_colls.htm

Thanks!

Happy Learning! Your feedback would be appreciated!

Securing Oracle Database REST APIs using BasicAuth

Securing Oracle Database REST APIs using BasicAuth

In previous blogs we have created REST APIs using SQL Developer & ORDS without any authorization for learning. These APIs are accessible without any authorization. In real world scenarios this is not the case, we have to secure our rest services APIs, so that only authorized person/application can access. Refer below links for previous blog: (without any authorization)

In this blog we will explore how we can secure our Oracle REST Services APIs for database tables using BasicAuth (username & password). Refer below video.


When you enable the schema for REST. Go to SQL Developer. Connect your schema. Right click on connection name-> Rest Services -> “Enable Rest Service”. Check the ‘Authorization required’.

Same thing you have to enable for the object level. Enable the Rest Services on the object level using the ‘Authorization required’.

You will notice some Roles & Privileges got automatically created by the REST services.

If you explore these Privileges, you will get to know that be default some pattern got restricted. It means now you cannot access the REST service url normally. You will got 401 status as pattern got protected. You need to create the user with some role. Assigned that role to these privileges in order to access the REST services. See below example protected resources i.e. URI pattern.

User – Roles – Privileges

See below reference image. How user, roles & privilege are connected. See below reference – User is assigned to roles. You can assign roles to privileges. Privileges actually protects the patterns & modules.

I have created the role API_ROLE & assigned to privileges for Customer Table.

###Creating User with role "API_ROLE"
cd C:\ords20\ords-20.2.0.178.1804
java -jar ords.war user api_user "API_ROLE"
Check here user & roles in credential files.

User can access the APIs using BasicAuth (username & password) if its role is assigned to right privileges.

Accessing the protecting REST service without any authorization keys.

Accessing the REST Service with BasicAuth (username & password).

References: https://www.thatjeffsmith.com/archive/2018/10/ords-the-easy-way-to-protect-all-services-for-a-rest-enabled-schema/


Thanks!

Happy Learning! Your feedback would be appreciated!

Exploring git life-cycle

Exploring git life-cycle

In this blog we will see the basic life-cycle of a file in git. Refer below diagram.


Round 1 Changes

I have created the ‘git-learning’ repository using GitHub. Initialized the repository using the README.md file. That is my initial commit on the master branch. See below unique commit id: 1d4ce0714c2e9a8d873c1a5e97cc0ce7bc12fd1b

As of now we have only one branch that is master, we can create other branches also, that we will cover later.

I have cloned the repository & run git log list down the version history for the current branch i.e. master. Showing all information like when & who performed the commit.

Lets do some changes in README.md file. After that run git status

So after changing the file, git saying there is ‘Changes not staged. Use git add to update….’ i.e. there are some changes in your working area. Let’s run the git diff to see the changes.

Let’s run git add command to move the changes from working to staging area & see the git status again.

Here if you notice that, git is saying you are up to date with master. You have staged your changes, now ready for commit. It is also showing changes to be committed.

Here if you run git diff, it will not show any data, as you have pushed changes in staging area. You have to run the git diff –staged

Let’s commit our changes using git commit command. This will move the changes from staging area to local repository & records file permanently in version history.

You have committed your changes. Permanently noted in version history locally. This commit is not available in remote repository. Check latest commit id using git log.

commit 77016c10f68af68b355884bdce65e98f6e7642bc

Notice below screenshot the red line ‘origin/master, origin/Head‘ – is on previous commit. Which is remote repository latest commit. Local repository latest commit is on top, that we have done above.

You can compare the commits using git diff <new-commit-id> <old-commit-it>

Final thing is to push the changes in remote repository using git push

git push is done i.e. local commits uploaded to remote repository. Checking remote repository:

commit 77016c10f68af68b355884bdce65e98f6e7642bc

Check git log now. After push, notice the red line ‘origin/master, origin/Head‘ – is now present on latest commit.


Round 2 Changes

I have added two new files & changed the README.md

Checking git status

Checking git diff

Moving all changes to staging area: git add . –all

Commit & Checking git log

commit: 177a091e9b72c3b97799aaac97c5ffa8e7478c23

Final git push.


Restoring Files (git restore)

When you have changed file in workarea & want to restore with original one. Run git restore <file> command.

When you have staged the file in staging area & want to restore. Run git restore –Staged <file> command.

Reset Commit (git reset)

I have commit the change in local repository & commit got generated f975da9d4ecb7c45db061b72fd1b450fd61cfa0f. Now want to reset the commit.

Lets reset to last commit. i.e. 179359643be893c489a61b50233752a299b01f4a

Lets run git reset –soft HEAD1 command to reset to last commit

Provide HEADn for nth last commit.

Thanks!

Happy Learning! Your feedback would be appreciated!

Jupyter Notebook Setup | Python & R

Jupyter Notebook Setup | Python & R

In this blog we will explore what is Jupyter Notebook & how we can setup in Windows 10 for Python & R.


Jupyter Notebook – open source web application that you can use to create and share documents that contain code, code output, visualizations, and text. You can include markdown in the jupyter notebooks.

Best way to write, execute code & share the output as document. See below sample notebook for R. Refer below screenshot contains markdown text, code & its output for R & Python.

Jupyter supports many programming languages, including Python, R and Scala.

Installation for Python & R:

For R: Just need to run below commands:

install.packages('IRkernel')
IRkernel::installspec()	
IRkernel::installspec(user = FALSE)

Installation is completed, now you can start creating & sharing notebooks.

Starting Jupyter Notebook

Go to the command prompt, change directory where you want to start the jupyter notebook, it will store all files in this directory only. Execute command: jupyter notebook , it will start the notebook application : http://localhost:8888/tree

Creating Notebook

Go the jupyter notebook home page & click on the new. Refer below screenshot, it is asking for the language, I have setup jupyter for R as well as Python. You can create new as well as run existing notebook files also.

You can create normal text file & folder in the directory where you have started the jupyter notebook.

Running Notebook using R

See below reference example of running the notebook for R language. It has markdown text, R code as well as code output.

Running Notebook using Python

See below reference example of running the notebook for Python language.

Exporting Notebook

You have option to download the notebook as html /ipynb/pfd… etc. Later you can share the notebook in GitHub, email or any blog.

Refer sample .ipynb notebooks I have pushed in GitHub: https://github.com/shobhit-singh/Python-Learning-Journey


Thanks!

Happy Learning! Your feedback would be appreciated!

Calling Oracle Database REST APIs using Python

Calling Oracle Database REST APIs using Python

In previous blog we have seen how we can enable REST Services in database schema using ORDS. Also we have created REST API using GET,POST & DELETE Method. Oracle Rest Data Services (ORDS) enable REST interface for the Oracle Database, so that you can create RESTfull APIs that uses HTTP/HTTPS requests to GET, PUT, POST & DELETE resource in the database table.

Refer below blogs for REST Service & APIs setup.

In this blog we will call Oracle Database REST APIs using Python.


REST API 1: For retrieving customer email based on text pattern (GET Method)

import requests
import json

url = "http://localhost:8080/ords/customerorders/search/adam"
req = requests.get(url)
print('Response code received: {code} '.format(code=req.status_code))
if req.status_code == 200:
    req_text_dict = json.loads(req.text)
    for i in req_text_dict['items']:
        print(i)
else:
    print("Some issue in API")

REST API 2: For retrieving orders based on customer email & status (GET Method)

import requests
import json

url = "http://localhost:8080/ords/customerorders/serachorder/orders/"
payload = (('CUSTOMER_ID', 272), ('ORDER_STATUS', "CANCELLED"))
req = requests.get(url, params=payload)

print('Response code received: {code} '.format(code=req.status_code))
if req.status_code == 200:
    req_text_dict = json.loads(req.text)
    for i in req_text_dict['items']:
        print(i)
else:
    print("Some issue in API")

REST API 3: For creating new customer entry (PUT Method)

import requests
import json

url = "http://localhost:8080/ords/customerorders/customer/addcust"
payload = (('EMAIL_ADDRESS', "devuser1@test.com"), ('FULL_NAME', "DevUser1"))
req = requests.post(url, params=payload)
print('Response code received: {code} '.format(code=req.status_code))
if req.status_code == 200:
    req_text_dict = json.loads(req.text)
    print(req_text_dict)
else:
    print("Some issue in API")

REST API 4: For deleting customer entry based using email id (DELETE Method)

import requests
import json

url = "http://localhost:8080/ords/customerorders/customer/addcust"
payload = ('EMAIL_ADDRESS', "devuser1@test.com")
req = requests.delete(url, data=json.dumps(payload))
print('Response code received: {code} '.format(code=req.status_code))
if req.status_code == 200:
    req_text_dict = json.loads(req.text)
    print(req_text_dict)
else:
    print("Some issue in API")

Schema Used: Customer-Orders Sample Schema | Oracle 12c

Thanks!

Happy Learning! Your feedback would be appreciated!

Building REST APIs | SQL Developer | POST & DELETE

Building REST APIs | SQL Developer | POST & DELETE

In previous blog we have seen how we can enable REST services in database schema using ORDS. Also we have created RESTfull API to get the customer details.

Previous Blog: Building REST APIs using SQL Developer & ORDS

In this blog we will build the APIs – POST Method to insert a record & Delete Method – delete record in database using REST Services.


Building RESTfull API – POST Method

Check the connection: Go to Rest Development Tab & Click on connect.

Step 1: Create New Module

Provide the module name & URI prefix. Check the Publish check box. Pagination is set to 5 for testing purpose. Click on next.

Step 2: Provide URI Pattern here. Click on next.

Step 3: Select the method handler as ‘POST

Open the POST method, go to SQL Worksheet. Enter the PL/SQL Block.

DECLARE
ln_cust_id CUSTOMERS.CUSTOMER_ID%TYPE;
BEGIN
Insert into CUSTOMERS (EMAIL_ADDRESS,FULL_NAME) values (:EMAIL_ADDRESS,:FULL_NAME)
RETURNING CUSTOMER_ID INTO ln_cust_id;
commit;
:string_out := 'Record Inserted. Note Customer Id:'|| ln_cust_id ;
 EXCEPTION
  WHEN OTHERS THEN
   :string_out := 'Something is missing!';
   :status := 400;
END;

Setup Parameters:

Adding the Input & Output parameters required for the POST Method.

Checking the details here:

Upload Module

Module is uploaded. Now test the API to post the customer details.

Testing Rest API

Providing input parameters as EMAIL_ADDRESS & FULL_NAME. Check on send.

Customer got created.


Building RESTfull API – Delete Method

Create a new handler in existing module.

Add handler like this in existing module.

After creating the handler, open the Delete handler & edit the SQL Worksheet.

DECLARE
ln_cust_id CUSTOMERS.CUSTOMER_ID%TYPE;
BEGIN
DELETE FROM CUSTOMERS WHERE upper(EMAIL_ADDRESS) = upper(:EMAIL_ADDRESS)
RETURNING CUSTOMER_ID INTO ln_cust_id;
commit;
:string_out := 'Record Deleted. Customer Email:'|| ln_cust_id ;
 EXCEPTION
  WHEN OTHERS THEN
   :string_out := 'Something is missing!';
    :status := 400;
END;

Setup Parameters

Adding the Input & Output parameters required for the Delete Method.

Testing Rest API

Records got deleted from the database table.


Thanks!

Happy Learning! Your feedback would be appreciated!

Building REST APIs using SQL Developer & ORDS | GET

Building REST APIs using SQL Developer & ORDS | GET

In this blog we will explore how we can create REST APIs for database tables using Oracle SQL Developer & Oracle Rest Data Services (ORDS). Also we are going to test those APIs using Postman.

We will create APIs so that we can GET some data from database table, POST some data in database table i.e. Inserting rows & DELETE Method for deleting record.

ORDS enable REST interface for the Oracle Database, so that you can create RESTfull APIs that uses HTTP/HTTPS requests to GET, PUT, POST & DELETE resource in the database table.


Prerequisite

Step 1: You can install the ORDS in three different mode like Standalone, Apache Tomcat or using Oracle Weblogic Server.

I have already installed the ORDS – 20.2 running on Apache tomcat (localhost:8080) server as a web listener for my Oracle Apex. Check this blog for Apex & ORDS Install: Oracle Apex 20.1 Installation. Apex is not pre-requisite for the ORDS. Refer complete documentation here for system requirement & installation : Link

Make sure your ords is up & running.

Step 2: Created one developer user with the role “SQL Developer”

Step 3: Setup/Enable sample schema in database. Enabled it for REST. I’m using Customer-Orders Sample Schema | Oracle 12c in this blog. Database 12c Pluggable.

Steps – Enable Rest Service – Schema

Go to SQL Developer. Connect your schema. Right click on connection name-> Rest Services -> “Enable Rest Service”.

Provide the alias name for your schema. In this blog for simplicity as of now, we are not setting up the authorization. Click on “Enable Schema”

Click on next & you can see the SQL for enabling schema. After that finish.

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'CO',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'customerorders',
                       p_auto_rest_auth => FALSE);

    commit;

END;

Step 4: Identify individual database tables on which you want to build RESTfull APIs. Enable the Rest Services on the object level. See below one sample example.

Go to SQL Developer. CO schema connected. Right Click on table name-> “Enable Rest Service”. For simplicity as of now not setting up the authorization. Click on “Enable Object”

Provide table alias name: If you want to access with some other name.

Click on next & you can see the SQL also. After that finish.

DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN

    ORDS.ENABLE_OBJECT(p_enabled => TRUE,
                       p_schema => 'CO',
                       p_object => 'CUSTOMERS',
                       p_object_type => 'TABLE',
                       p_object_alias => 'customers',
                       p_auto_rest_auth => FALSE);

    commit;

END;
Now you can access the table using the Rest Services

I have enabled the “Rest Services” for below tables in the Customer Order Schema. As I’m going to build the APIs using all these tables in my CO schema.

select * from customers;
select * from orders;
select * from order_items;
select * from products;

Exploring REST Services

For example here is my customers table in CO schema, schema alias customerorders provided in Step 3.

URL: <host>:<port>/ords/<schema-alias>/<object-alias>

Customer Table

Now try to access the REST service url giving details like schema & object name.

Get Data: http://localhost:8080/ords/customerorders/customers/

Below of this JSON output, there are links for next set of data, as limit is 25.

Metadata http://localhost:8080/ords/customerorders/metadata-catalog/customers/

Getting information for single customer: Customer Id = 306

http://localhost:8080/ords/customerorders/customers/306


Building RESTfull API – GET Method

Go to SQL Developer. Click on View. Enable Development tab.

Go to Rest Development Tab & Click on connect.

Create the RESTfull services connection – Using SQL Developer. Provide username & password created in the Step 2.

After successfully login, you can validate the REST Data Services.

Connection setup is done. Lets build our first API.

Requirement: Let say we want to build API, to retrieve customer name & email whose name starts with some text provided at runtime. Like below SQL:

Step 1: Lets build GET Method API. Create ‘New Module’

Provide the module name & prefix for your URI. Check the Publish check box. Pagination is set to 5 for testing purpose. Click on next.

Step 2: Provide URI Pattern here. Click on next.

Step 3: Select Method Handler: GET

Click on Finish.

Step 4: Change the SQL Query. Open the Method.

Execute the SQL to test.

Upload Module.

Module is uploaded. Now test the API to get the customer details based on the name. You can get the details in this tab.

Example 1: Searching users whose name starts with san

URL: http://localhost:8080/ords/customerorders/search/san

Example 2: Searching users whose name starts with adam

URL: http://localhost:8080/ords/customerorders/search/adam


Building RESTfull API – Multiple Parameter – GET

Here is the order table. Let say we want a API which can fetch orders based on customer id & status. See below sample SQL for reference.

Follow the same procedure as we have done above API for name search. Create new module provide all details. Edit the GET method & provide the query.

Setup Parameters

Adding the Input & Output parameters required for the GET Method.

API: http://localhost:8080/ords/customerorders/serachorder/orders/

Testing Rest API

Let see first data. For customer 272 we have multiple orders & status.

Example 1: Get COMPLETE Orders for customer 272

Example 2: Get REFUNDED Orders for customer 272

Refer this blog for POST & DELETE Method: Building REST APIs | SQL Developer | POST & DELETE

Testing APIs using Python: Calling Oracle Database REST APIs using Python

Securing REST API: Securing Oracle Database REST APIs using BasicAuth


Tools & Software used:

  • SQL Developer : Version 20.2.0.175
  • ORDS: select ords.installed_version from dual — 20.2.0.r1781804
  • Chrome – Postman & JSON Formatter
  • Database : 12c Pluggable Database

References:

Thanks!

Happy Learning! Your feedback would be appreciated!