Instruction
https://mlh.github.io/Oracle-GHW-API-Week-Challenges/#coding-challenge-3
Code Results
Lab 3
drelap@cloudshell:~ (ap-melbourne-1)$ curl --location \
'https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/'
{"items":[{"col1":"e40a9db6","col2":"1b","col3":27531,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/AAAevkAAAAAAJteAAA"}]},{"col1":"6182c817","col2":"73","col3":5355332,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/AAAevkAAAAAAJteAAB"}]},{"col1":"5ed9e437","col2":"d2","col3":4834758,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/AAAevkAAAAAAJteAAC"}]},{"col1":"d77868a1","col2":"64","col3":2129797,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/AAAevkAAAAAAJteAAD"}]},{"col1":"09bfaa73","col2":"89","col3":3294896,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/AAAevkAAAAAAJteAAE"}]},{"col1":"5054cca2","col2":"97","col3":3474577,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/AAAevkAAAAAAJteAAF"}]},{"col1":"d20e9eb4","col2":"7c","col3":2728720,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/AAAevkAAAAAAJteAAG"}]}],"hasMore":false,"limit":25,"offset":0,"count":7,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/"},{"rel":"edit","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/"},{"rel":"describedby","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/metadata-catalog/csv_data/"},{"rel":"first","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/csv_data/"}]}
Lab 4
Step 8:
Curl result:
#INFO Number of rows processed: 2,097,148
#INFO Number of rows in error: 0
#INFO Last row processed in final committed batch: 2,097,148
SUCCESS: Processed without errors
13.206698
Step 9:
Run Statement with result:
select count(*) from csv_data;
"COUNT(*)"
2097155
Step 10:
Run Script with result:
create or replace procedure return_count (p_input in varchar2,
p_output out number)
is
begin
select count(*)
into p_output
from csv_data
where col2 = p_input;
end return_count;
/
Procedure RETURN_COUNT compiled
Elapsed: 00:00:00.270
Step 11:
Run Script with result:
declare
l_output number;
begin
return_count(p_input => 'a1',
p_output => l_output);
dbms_output.put_line(l_output);
end;
/
8204
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.029
Lab 5:
Task1:
Create module
-- DEFINE MODULE
BEGIN
ORDS.DEFINE_MODULE(
p_module_name => 'com.oracle.livelab.api',
p_base_path => '/api/',
p_items_per_page=> 25,
p_status => 'PUBLISHED',
p_comments=> ''
);
COMMIT;
END;
Create template:
-- DEFINE TEMPLATE
BEGIN
ORDS.DEFINE_TEMPLATE(
p_module_name => 'com.oracle.livelab.api',
p_pattern => 'sqlreport/:id',
p_priority => 0,
p_etag_type => 'HASH',
p_comments => ''
);
COMMIT;
END;
Create handler:
-- DEFINE HANDLER
BEGIN
ORDS.DEFINE_HANDLER(
p_module_name => 'com.oracle.livelab.api',
p_pattern => 'sqlreport/:id',
p_method => 'GET',
p_source_type => ords.source_type_collection_feed,
p_source => 'select * from csv_data where col2 = :id',
p_items_per_page => 25,
p_comments => ''
);
COMMIT;
END;
Step 18 result:
Lab5-step18.csv (8205 lines)
Step 20 result:
{"items":[{"col1":"f8c87baa","col2":"a1","col3":852530},{"col1":"d98f3e5b","col2":"a1","col3":9864895},{"col1":"5cbb6ddc","col2":"a1","col3":60428923},{"col1":"474c024a","col2":"a1","col3":85183686},{"col1":"243f5660","col2":"a1","col3":87300261},{"col1":"f62af3d4","col2":"a1","col3":31094545},{"col1":"af2fc686","col2":"a1","col3":48206518},{"col1":"798812df","col2":"a1","col3":4166997},{"col1":"59fd433c","col2":"a1","col3":32470891},{"col1":"6c1298ef","col2":"a1","col3":506747},{"col1":"9d4f725e","col2":"a1","col3":36224185},{"col1":"041d6b03","col2":"a1","col3":23890702},{"col1":"3447e214","col2":"a1","col3":110333373},{"col1":"69face01","col2":"a1","col3":18449519},{"col1":"a0707a73","col2":"a1","col3":167176502},{"col1":"55789f0a","col2":"a1","col3":119272860},{"col1":"03801afd","col2":"a1","col3":75179648},{"col1":"dbdf5867","col2":"a1","col3":91475805},{"col1":"93adc64d","col2":"a1","col3":39287205},{"col1":"2b130ef8","col2":"a1","col3":206753925},{"col1":"1f6bec10","col2":"a1","col3":17745238},{"col1":"81f46a8d","col2":"a1","col3":54692392},{"col1":"2ebd5ecb","col2":"a1","col3":94437756},{"col1":"4d514c12","col2":"a1","col3":145885382},{"col1":"1df8aa45","col2":"a1","col3":209066451}],"hasMore":true,"limit":25,"offset":0,"count":25,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/sqlreport/a1"},{"rel":"describedby","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/metadata-catalog/api/sqlreport/item"},{"rel":"first","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/sqlreport/a1"},{"rel":"next","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/sqlreport/a1?offset=25"}]}
Task2:
Create template:
-- DEFINE TEMPLATE
BEGIN
ORDS.DEFINE_TEMPLATE(
p_module_name => 'com.oracle.livelab.api',
p_pattern => 'bizlogic',
p_priority => 0,
p_etag_type => 'HASH',
p_comments => ''
);
COMMIT;
END;
Create Handler:
-- DEFINE HANDLER
BEGIN
ORDS.DEFINE_HANDLER(
p_module_name => 'com.oracle.livelab.api',
p_pattern => 'bizlogic',
p_method => 'POST',
p_source_type => ords.source_type_plsql,
p_source => 'BEGIN
return_count(p_input => :id, p_output => :output);
end;',
p_items_per_page => 25,
p_comments => ''
);
COMMIT;
END;
Create parameter:
-- DEFINE PARAMETER
BEGIN
ORDS.DEFINE_PARAMETER(
p_module_name => 'com.oracle.livelab.api',
p_pattern => 'bizlogic',
p_method => 'POST',
p_name => 'output',
p_bind_variable_name => 'output',
p_source_type => 'RESPONSE',
p_access_method => 'OUT',
p_comments => '',
p_param_type => 'INT'
);
COMMIT;
END;
Step 22:
curl command:
curl --location --request POST \
'https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/bizlogic' \
--header 'Content-Type: application/json' \
--data-binary '{
"id": "a1",
"output": "<VALUE>"
}'
result: {"output":8204}
Task 3:
{
"openapi": "3.0.0",
"info": {
"title": "ORDS generated API for com.oracle.livelab.api",
"version": "1.0.0"
},
"servers": [
{
"url": "https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/"
}
],
"paths": {
"/bizlogic": {
"post": {
"description": "Create a new record on com.oracle.livelab.api",
"responses": {
"201": {
"description": "The successfully created record.",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {
"output": {
"type": "integer"
}
}
}
}
}
}
},
"parameters": [],
"requestBody": {
"content": {
"application/json": {
"schema": {
"$ref": "#/components/schemas/api_ITEM"
}
}
}
}
}
},
"/sqlreport/{id}": {
"get": {
"description": "Retrieve records from com.oracle.livelab.api",
"responses": {
"200": {
"description": "The queried record.",
"content": {
"application/json": {
"schema": {
"type": "object",
"properties": {
"items": {
"type": "array",
"items": {
"type": "object",
"properties": {
"col1": {
"$ref": "#/components/schemas/VARCHAR2"
},
"col2": {
"$ref": "#/components/schemas/VARCHAR2"
},
"col3": {
"$ref": "#/components/schemas/NUMBER"
}
}
}
}
}
}
}
}
}
},
"parameters": [
{
"name": "id",
"in": "path",
"required": true,
"schema": {
"type": "string",
"pattern": "^[^/]+$"
},
"description": "implicit"
}
]
}
}
},
"components": {
"schemas": {
"NUMBER": {
"type": "number"
},
"STRING": {
"type": "string"
},
"VARCHAR2": {
"type": "string"
},
"api_ITEM": {
"properties": {
"id": {
"type": "string"
}
}
}
}
}
}
Lab 6:
Task 1: Create Role
BEGIN
ORDS.CREATE_ROLE(
P_ROLE_NAME => 'oracle.livelabs.role.admin'
);
COMMIT;
END;
Task 2: Create Privilege
DECLARE
L_PRIV_ROLES owa.vc_arr;
L_PRIV_PATTERNS owa.vc_arr;
L_PRIV_MODULES owa.vc_arr;
BEGIN
L_PRIV_ROLES( 1 ) := 'oracle.livelabs.role.admin';
L_PRIV_MODULES( 1 ) := 'com.oracle.livelab.api';
ORDS.DEFINE_PRIVILEGE(
P_PRIVILEGE_NAME => 'oracle.livelabs.privilege.admin',
P_ROLES => L_PRIV_ROLES,
P_PATTERNS => L_PRIV_PATTERNS,
P_MODULES => L_PRIV_MODULES,
P_LABEL => 'Livelabs REST Privilege',
P_DESCRIPTION => 'Livelabs Privilege for Business Logic REST Services',
P_COMMENTS=> ''
);
COMMIT;
END;
Task 3: Create OAuth Client for secure REST Endpoint
BEGIN
ORDS_METADATA.OAUTH.CREATE_CLIENT(
P_NAME => 'oauthclient',
P_GRANT_TYPE => 'client_credentials',
P_OWNER => 'ADMIN',
P_DESCRIPTION => 'Security on my REST Service',
P_ORIGINS_ALLOWED => '',
P_REDIRECT_URI => '',
P_SUPPORT_EMAIL => '[email protected]',
P_SUPPORT_URI => 'https://www.oracle.com/rest/',
P_PRIVILEGE_NAMES => ''
);
ORDS_METADATA.OAUTH.GRANT_CLIENT_ROLE(
P_CLIENT_NAME => 'oauthclient',
P_ROLE_NAME => 'oracle.livelabs.role.admin'
);
COMMIT;
END;
Task 4: Obtain a Bearer Token for accessing a secure REST Endpoint
Step 3:
drelap@cloudshell:~ (ap-melbourne-1)$ curl --location --request POST \
> 'https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/bizlogic' \
> --header 'Content-Type: application/json' \
> --data-binary '{
> "id": "a1",
> "output": "<VALUE>"
> }'
{
"code": "Unauthorized",
"message": "Unauthorized",
"type": "tag:oracle.com,2020:error/Unauthorized",
"instance": "tag:oracle.com,2020:ecid/f79b6afdc44f4d828822599936a704b1"
}
Step 5:
drelap@cloudshell:~ (ap-melbourne-1)$ curl -X POST --header 'Authorization: Bearer 9qXH9NLToyRu0eFbxG2vhA' \
> 'https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/bizlogic' \
> --header 'Content-Type: application/json' \
> --data-binary '{
> "id": "a1",
> "output": ""
> }'
{"output":8204}
Step 7:
drelap@cloudshell:~ (ap-melbourne-1)$ curl -X GET --header 'Authorization: Bearer 9qXH9NLToyRu0eFbxG2vhA' 'https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/sqlreport/a1'
{"items":[{"col1":"f8c87baa","col2":"a1","col3":852530},{"col1":"d98f3e5b","col2":"a1","col3":9864895},{"col1":"5cbb6ddc","col2":"a1","col3":60428923},{"col1":"474c024a","col2":"a1","col3":85183686},{"col1":"243f5660","col2":"a1","col3":87300261},{"col1":"f62af3d4","col2":"a1","col3":31094545},{"col1":"af2fc686","col2":"a1","col3":48206518},{"col1":"798812df","col2":"a1","col3":4166997},{"col1":"59fd433c","col2":"a1","col3":32470891},{"col1":"6c1298ef","col2":"a1","col3":506747},{"col1":"9d4f725e","col2":"a1","col3":36224185},{"col1":"041d6b03","col2":"a1","col3":23890702},{"col1":"3447e214","col2":"a1","col3":110333373},{"col1":"69face01","col2":"a1","col3":18449519},{"col1":"a0707a73","col2":"a1","col3":167176502},{"col1":"55789f0a","col2":"a1","col3":119272860},{"col1":"03801afd","col2":"a1","col3":75179648},{"col1":"dbdf5867","col2":"a1","col3":91475805},{"col1":"93adc64d","col2":"a1","col3":39287205},{"col1":"2b130ef8","col2":"a1","col3":206753925},{"col1":"1f6bec10","col2":"a1","col3":17745238},{"col1":"81f46a8d","col2":"a1","col3":54692392},{"col1":"2ebd5ecb","col2":"a1","col3":94437756},{"col1":"4d514c12","col2":"a1","col3":145885382},{"col1":"1df8aa45","col2":"a1","col3":209066451}],"hasMore":true,"limit":25,"offset":0,"count":25,"links":[{"rel":"self","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/sqlreport/a1"},{"rel":"describedby","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/metadata-catalog/api/sqlreport/item"},{"rel":"first","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/sqlreport/a1"},{"rel":"next","href":"https://gdb7bab84c60435-adwfinance.adb.ap-melbourne-1.oraclecloudapps.com/ords/admin/api/sqlreport/a1?offset=25"}]}
