Setting up RESTful Data source using SQL Developer

We keep our AOP report queries (returning JSON formatted data to feed to AOP’s api package) in ORDS RESTful Services.

This method is fairly simple and keeps changes to individual reports isolated, without the need to compile a package. Each query is stored in a separate Resource Handler, and setting these up are a breeze and you can accomplish this one of two ways.

Stay tuned for my blog about how to accomplish this using the Oracle APEX UI, but today I will show how this is accomplished using SQL developer.

After setting up the data source(s), these JSON formatted data are accessible via a URI in your Web browser. The URI is in the following format:

Example URI: https://< host >:< port >/ords/gems/grants/org_pr_grant/grant_pk:{grant_pk}

What the parts mean:

gems/grants/org_pr_grant/grant_pk:{grant_pk}	gems - workspace name
gems/grants/org_pr_grant/grant_pk:{grant_pk}	grants - URI prefix
gems/grants/org_pr_grant/grant_pk:{grant_pk}	org_pr_grant - URI template
gems/grants/org_pr_grant/grant_pk:{grant_pk}	Parameter and bind variable

 Step 0 - First, go to SQL Developer

Choose your connection

(If you aren’t creating module from scratch, skip to step 1)

Right Click Module, New Module, 

Enter Name and URI Prefix(base path). Click Create Module. Click Next.

Step 1: Create Resource Template

If the module has been created already, step 0 includes instructions on creating a Module. If the module has been created already, right click on the module you want to create the template in and select Add Template.

 

 

Create your URI

·       Without parameter: /org_pr_grant/

·       With parameter: / org_pr_grant /grant_pk:{grant_pk}:start_date:{start_date}

·       Incoming bind variables (parameter) within curly braces. Colon (:) delimited

Step 2: Ensure HTTP Entity Tag selection is None. Click Next. Click Finish.

Step 3: Create GET query for handler

Right click your newly created handler and click Open

Click the Actions menu and select Add Handler > GET

 

Method: GET

Source Type: Query

Data Format: JSON

Click Apply.

Step 4: Add your query to the Source section.

Click Save.

You should get a message like the following in your Message - Log.

RESTful Editor: Mar 5, 2019 11:42:51 AM: Saved REST GET Handler for Template list_project_test/root_project_pk:{root_project_pk} in Module Project Management Reports.

Step 5: Add your Parameters.

Click Parameters tab then the green + button.

Add a row and add your bind variable. Name and bind variable are the same, Source Type – URI, Access Method – IN, Data Type – INTEGER.

Please note that your bind variable in both your query and here below must be in lower case.