ORDS (Oracle Rest Data Services) is a fast and easy way to host a REST API you can use to take full control of your Oracle CodeCard.
In this post I’ll walk through how to create the ORDS API on your own Always Free Oracle Cloud Database.
Prerequisites
-
- Create a compartment in your Oracle Cloud Account (Optional but recommended)
- Create an Always Free Autonomous Database on the Oracle Cloud
- Go to the Service Console for your new database, click on “Development” and save the URL in the “RESTful Services and SODA” section.
- CodeCard Sketch changes: Howto disable fingerprint check and increase REST URI size
- You Went to Oracle Open World 19 and got a Code Card, now what?
Ignore the fingerprint settings since you just disabled that in the previous step but add the following.
Replace the below {{URL}} with the URL you saved above.
1234buttona1={{URL}} cc/functions/masterbuttona2={{URL}} cc/functions/masterbuttonb1={{URL}} cc/functions/masterbuttonb2={{URL}} cc/functions/master
buttona1=https://asdr34edyjtyi4j-codecard.adb.us-ashburn-1.oraclecloudapps.com/ords/cc/functions/master
When all of that is done, go to the Development section of the Service Console for your ATP instance and click “SQL Developer Web”. Sign in as admin with the admin password you created in step 2.
Database Setup
As Admin, create the CODE_CARD schema.
Execute the following code in the worksheet. Use a good password and remember it for later.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
CREATE USER CODE_CARD IDENTIFIED BY "setAgoodPassword"; ALTER USER CODE_CARD TEMPORARY TABLESPACE temp; GRANT CONNECT, CREATE SESSION, RESOURCE, UNLIMITED TABLESPACE, CREATE ANY TABLE, ALTER ANY TABLE, CREATE ANY VIEW, CREATE ANY SEQUENCE, CREATE ANY PROCEDURE, CREATE ANY TYPE, CREATE ANY SYNONYM TO CODE_CARD; GRANT EXECUTE ON DBMS_LOCK TO CODE_CARD; BEGIN ords_admin.enable_schema( p_enabled => TRUE, p_schema => 'CODE_CARD', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'cc', p_auto_rest_auth => NULL ); commit; END; |
This will create the new CODE_CARD schema and REST enable the schema so you can use it with ORDS. Notice the p_url_mapping_pattern is set to ‘cc’, this will be part of the REST URI in the following sections.
oraclecloudapps.com/ords/cc/As code_card, create the database objects
Switch to the code_card schema by changing the URL In your browser. Replace ‘admin’ with ‘cc’.
...oraclecloudapps.com/ords/admin/_sdw/?nav=worksheet to
...oraclecloudapps.com/ords/cc/_sdw/?nav=worksheet
Log into SQL Developer Web as code_card using your new password.
In a worksheet, use the following to create the tables.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
CREATE TABLE "CODEMAKER" ( "ID" NUMBER GENERATED ALWAYS AS IDENTITY, "BADGEID" VARCHAR2(20), "BUTTONLABEL" VARCHAR2(2), "BUTTONFUNCTION" VARCHAR2(10), "TEMPLATE" VARCHAR2(12), "TITLE" VARCHAR2(50), "SUBTITLE" VARCHAR2(100), "BODYTEXT" VARCHAR2(200), "ICON" VARCHAR2(200), "BADGE" VARCHAR2(5), "BACKGROUNDIMAGE" VARCHAR2(200), "BACKGROUNDCOLOR" VARCHAR2(10), "BARCODE" VARCHAR2(50), "FINGERPRINT" VARCHAR2(200), CONSTRAINT "CODEMAKER_PK" PRIMARY KEY ("ID") USING INDEX ENABLE, CONSTRAINT "CODEMAKER_UK1" UNIQUE ("BADGEID", "BUTTONLABEL", "BUTTONFUNCTION") USING INDEX ENABLE ) / CREATE TABLE "CODECARD" ( "ID" NUMBER GENERATED ALWAYS AS IDENTITY, "BADGEID" VARCHAR2(100), "NAME" VARCHAR2(1000), "CREATED" TIMESTAMP (6), CONSTRAINT "CODECARD_PK" PRIMARY KEY ("ID") USING INDEX ENABLE, CONSTRAINT "CODECARD_CON" UNIQUE ("BADGEID") USING INDEX ENABLE ) / |
Oracle REST Data Services API
You will need two rest endpoints. One to POST the setting for each button press and one your CodeCard will use to GET those settings.
There are multiple ways you can create an ORDS API, this post will walk through how to create the API using PL/SQL. I will explain the settings that are most important for the CodeCard interface. For the other settings, you can find the full documentation here.
This step was completed above
When you created the new schema above, you also REST enabled the new schema with this PL/SQL.
1 2 3 4 5 6 7 8 9 10 |
BEGIN ords_admin.enable_schema( p_enabled => TRUE, p_schema => 'CODE_CARD', p_url_mapping_type => 'BASE_PATH', p_url_mapping_pattern => 'cc', p_auto_rest_auth => NULL ); commit; END; |
This command was included in the above step so that you could log into SQL Developer WEB as code_card.
The following will explain the individual PL/SQL procedures used to create the REST API.
If you want to skip the explanation, you can jump straight to the complete code included below.
Define a module
A module is a collection of related REST services. Think of a module as a package that contains REST endpoints.
Create a module called ‘functions’.
1 2 3 4 5 6 |
ORDS.DEFINE_MODULE( p_module_name => 'functions', p_base_path => '/functions/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); |
The base path is set to ‘/functions/’, this will be part of the REST URI.
oraclecloudapps.com/ords/cc/functions/Define a Template
A Template is the endpoint for your REST API.
1 2 3 4 5 6 7 |
ORDS.DEFINE_TEMPLATE( p_module_name => 'functions', p_pattern => 'master', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); |
The pattern is set to ‘master’, this will be the final part of the REST URI.
oraclecloudapps.com/ords/cc/functions/masterGET request handler
The CodeCard sends a GET request to the REST API to retrieve the settings assigned to a specific button (A or B) and press function (long or short).
In order to handle this request we need to define a GET handler.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
ORDS.DEFINE_HANDLER( p_module_name => 'functions', p_pattern => 'master', p_method => 'GET', p_source_type => 'plsql/block', p_mimes_allowed => '', p_comments => NULL, p_source => 'declare tmp_badgeId VARCHAR2(20); tmp_buttonLabel VARCHAR2(2); tmp_buttonFunction VARCHAR2(10); tmp_template VARCHAR2(12); tmp_title VARCHAR2(50); tmp_subtitle VARCHAR2(100); tmp_bodytext VARCHAR2(200); tmp_icon VARCHAR2(200); tmp_badge VARCHAR2(5); tmp_backgorundImage VARCHAR2(200); tmp_backgroundColor VARCHAR2(10); tmp_fingerprint VARCHAR2(200); tmp_barcode VARCHAR2(50); tmp_function VARCHAR2(20); begin select BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE into tmp_badgeId, tmp_buttonLabel, tmp_buttonFunction, tmp_template, tmp_title, tmp_subtitle, tmp_bodytext, tmp_icon, tmp_badge, tmp_backgorundImage, tmp_backgroundColor, tmp_fingerprint, tmp_barcode from CODEMAKER where upper(BADGEID) = upper(:badgeid) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction; APEX_JSON.open_object; APEX_JSON.write(''template'', tmp_template); APEX_JSON.write_raw(''title'', ''"'' || tmp_title || ''"''); APEX_JSON.write_raw(''subtitle'', ''"'' || tmp_subtitle || ''"''); APEX_JSON.write_raw(''bodytext'', ''"'' ||tmp_bodytext || ''"''); APEX_JSON.write(''icon'', tmp_icon); APEX_JSON.write(''badge'', tmp_badge); APEX_JSON.write(''backgroundImage'', tmp_backgorundImage); APEX_JSON.write(''backgroundColor'', tmp_backgroundColor); APEX_JSON.write(''fingerprint'', tmp_fingerprint); APEX_JSON.write(''barcode'', tmp_barcode); APEX_JSON.close_object; EXCEPTION WHEN OTHERS THEN if :buttonfunction = ''1'' then tmp_function := ''Button '' || UPPER(:buttonlabel) || '' short press''; elsif :buttonfunction = ''2'' then tmp_function := ''Button '' || UPPER(:buttonlabel) ||'' long press''; else tmp_function := ''No button pressed''; end if; APEX_JSON.open_object; APEX_JSON.write(''template'', ''template1''); APEX_JSON.write(''title'', ''Uh oh!''); APEX_JSON.write(''subtitle'', tmp_function); APEX_JSON.write(''bodytext'', ''You have not configured your Code Card''); APEX_JSON.write(''icon'', ''fail''); APEX_JSON.close_object; end;'); |
Using a source type of ‘json/collection’ would allow you to use a simple SQL query and ORDS would handle all of the JSON formatting for us. This would be easier.
However, we want to be able to return a functioning screen layout to the CodeCard even if there’s an error. Using a source type of ‘plsql/block’ lets you create and return a custom APEX_JSON object even if there’s an error.
Parameters for the bind variables
The PL/SQL code uses 3 bind variables that are mapped to incoming request header values.
The bind variables are mapped to the header values by defining parameters. These parameters will all be assigned to the ‘functions’ module, ‘master’ template and ‘GET’ handler. They will be passed ‘IN’ through the ‘HEADER’ as a ‘STRING’ type. The value of the incoming header defined in ‘p_name’ will be mapped to value of the PL/SQL bind variable defined in ‘p_bind_variable_name’.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'GET', p_name => 'X-CODECARD-BUTTON-FUNCTION', p_bind_variable_name => 'buttonfunction', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'GET', p_name => 'X-CODECARD-BUTTON-LABEL', p_bind_variable_name => 'buttonlabel', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'GET', p_name => 'X-CODECARD-ID', p_bind_variable_name => 'badgeid', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); |
-
X-CODECARD-ID is the id assigned to your CodeCard.
- X-CODECARD-BUTTON-LABEL will either be the A or B button.
- X-CODECARD-BUTTON-FUNCTION will be 1 for a short press or 2 for a long press.
Handle POST requests
The CodeCard sends a POST request to the REST API to store the settings assigned to a specific button and press function.
In order to handle this request we need to define a POST handler.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
ORDS.DEFINE_HANDLER( p_module_name => 'functions', p_pattern => 'master', p_method => 'POST', p_source_type => 'plsql/block', p_mimes_allowed => 'application/json', p_comments => NULL, p_source => 'declare tmpId number; tmpTitle varchar2(1000); tmpSubtitle varchar2(1000); tmpBody varchar2(1000); begin tmpTitle := regexp_replace(:title, ''<.*?>''); tmpTitle := replace(tmpTitle, '' '', '' ''); tmpSubtitle := regexp_replace(:subtitle, ''<.*?>''); tmpSubtitle := replace(tmpSubtitle, '' '', '' ''); tmpBody := regexp_replace(:bodytext, ''<.*?>''); tmpBody := replace(tmpBody, '' '', '' ''); insert into CODEMAKER ( BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE ) values ( upper(:badgeId), :buttonLabel, :buttonFunction, :template, tmpTitle, tmpSubtitle, tmpBody, :icon, :badge, :backgorundImage, :backgroundColor, :fingerprint, :barcode ) returning id into tmpId; APEX_JSON.open_object; APEX_JSON.write(''badgeId'', upper(:badgeId)); APEX_JSON.write(''status'', ''new''); APEX_JSON.write(''recordId'', tmpId); APEX_JSON.close_object; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN update CODEMAKER set TEMPLATE = :template, TITLE = tmpTitle, SUBTITLE = tmpSubtitle, BODYTEXT = tmpBody, ICON = :icon, BADGE = :badge, BACKGROUNDIMAGE = :backgroundImage, BACKGROUNDCOLOR = :backgroundColor, FINGERPRINT = :fingerprint, BARCODE = :barcode where BADGEID = upper(:badgeId) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction returning id into tmpId; commit; APEX_JSON.open_object; APEX_JSON.write(''badgeId'', upper(:badgeId)); APEX_JSON.write(''reacordId'', tmpId); APEX_JSON.close_object; end;'); |
This PL/SQL block will either create a new record or update an existing record for the incoming CodeCard id, button label and button function.
Parameters for the bind variables
The parameters for the POST handler are the same as for the GET handler.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'POST', p_name => 'X-CODECARD-BUTTON-FUNCTION', p_bind_variable_name => 'buttonFunction', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'POST', p_name => 'X-CODECARD-BUTTON-LABEL', p_bind_variable_name => 'buttonLabel', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'POST', p_name => 'X-CODECARD-ID', p_bind_variable_name => 'badgeId', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); |
Register template (Optional)
The register template is used by the CodeCard designer to map your name to your CodeCard id, if you choose to enter it. Another post will detail how to install the CodeCard designer in an always free Oracle compute instance. If you plan on implementing the designer, include this handler template and handler. Even if you do not plan on implementing the designer, it won’t hurt anything to include these objects.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
ORDS.DEFINE_TEMPLATE( p_module_name => 'functions', p_pattern => 'register', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'functions', p_pattern => 'register', p_method => 'POST', p_source_type => 'plsql/block', p_mimes_allowed => 'application/json', p_comments => NULL, p_source => 'declare tmpBadgeId varchar2(100); begin insert into CODECARD ( BADGEID, NAME, CREATED ) values ( UPPER(:badgeId), INITCAP(:name), systimestamp ); APEX_JSON.open_object; APEX_JSON.write(''badgeId'', upper(:badgeId)); APEX_JSON.write(''status'', ''registered''); APEX_JSON.close_object; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE CODECARD set NAME = INITCAP(:name) where BADGEID = upper(:badgeId); APEX_JSON.open_object; APEX_JSON.write(''badgeId'', upper(:badgeId)); APEX_JSON.write(''status'', ''logged''); APEX_JSON.close_object; end;'); |
If you are piecing all of the above sections together you will need to execute a ‘COMMIT’ to save the changes to your database. Or, you can use the following.
Full ORDS PL/SQL code
In a worksheet use the following code to create the Oracle REST Data Services API.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 |
set define off; DECLARE BEGIN ORDS.DEFINE_MODULE( p_module_name => 'functions', p_base_path => '/functions/', p_items_per_page => 25, p_status => 'PUBLISHED', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'functions', p_pattern => 'master', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'functions', p_pattern => 'master', p_method => 'GET', p_source_type => 'plsql/block', p_mimes_allowed => '', p_comments => NULL, p_source => 'declare tmp_badgeId VARCHAR2(20); tmp_buttonLabel VARCHAR2(2); tmp_buttonFunction VARCHAR2(10); tmp_template VARCHAR2(12); tmp_title VARCHAR2(50); tmp_subtitle VARCHAR2(100); tmp_bodytext VARCHAR2(200); tmp_icon VARCHAR2(200); tmp_badge VARCHAR2(5); tmp_backgorundImage VARCHAR2(200); tmp_backgroundColor VARCHAR2(10); tmp_fingerprint VARCHAR2(200); tmp_barcode VARCHAR2(50); tmp_function VARCHAR2(20); begin select BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE into tmp_badgeId, tmp_buttonLabel, tmp_buttonFunction, tmp_template, tmp_title, tmp_subtitle, tmp_bodytext, tmp_icon, tmp_badge, tmp_backgorundImage, tmp_backgroundColor, tmp_fingerprint, tmp_barcode from CODEMAKER where upper(BADGEID) = upper(:badgeid) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction; APEX_JSON.open_object; APEX_JSON.write(''template'', tmp_template); APEX_JSON.write_raw(''title'', ''"'' || tmp_title || ''"''); APEX_JSON.write_raw(''subtitle'', ''"'' || tmp_subtitle || ''"''); APEX_JSON.write_raw(''bodytext'', ''"'' ||tmp_bodytext || ''"''); APEX_JSON.write(''icon'', tmp_icon); APEX_JSON.write(''badge'', tmp_badge); APEX_JSON.write(''backgroundImage'', tmp_backgorundImage); APEX_JSON.write(''backgroundColor'', tmp_backgroundColor); APEX_JSON.write(''fingerprint'', tmp_fingerprint); APEX_JSON.write(''barcode'', tmp_barcode); APEX_JSON.close_object; EXCEPTION WHEN OTHERS THEN if :buttonfunction = ''1'' then tmp_function := ''Button '' || UPPER(:buttonlabel) || '' short press''; elsif :buttonfunction = ''2'' then tmp_function := ''Button '' || UPPER(:buttonlabel) ||'' long press''; else tmp_function := ''No button pressed''; end if; APEX_JSON.open_object; APEX_JSON.write(''template'', ''template1''); APEX_JSON.write(''title'', ''Uh oh!''); APEX_JSON.write(''subtitle'', tmp_function); APEX_JSON.write(''bodytext'', ''You have not configured your Code Card''); APEX_JSON.write(''icon'', ''fail''); APEX_JSON.close_object; end;'); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'GET', p_name => 'X-CODECARD-BUTTON-FUNCTION', p_bind_variable_name => 'buttonfunction', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'GET', p_name => 'X-CODECARD-BUTTON-LABEL', p_bind_variable_name => 'buttonlabel', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'GET', p_name => 'X-CODECARD-ID', p_bind_variable_name => 'badgeid', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'functions', p_pattern => 'master', p_method => 'POST', p_source_type => 'plsql/block', p_mimes_allowed => 'application/json', p_comments => NULL, p_source => 'declare tmpId number; tmpTitle varchar2(1000); tmpSubtitle varchar2(1000); tmpBody varchar2(1000); begin tmpTitle := regexp_replace(:title, ''<.*?>''); tmpTitle := replace(tmpTitle, '' '', '' ''); tmpSubtitle := regexp_replace(:subtitle, ''<.*?>''); tmpSubtitle := replace(tmpSubtitle, '' '', '' ''); tmpBody := regexp_replace(:bodytext, ''<.*?>''); tmpBody := replace(tmpBody, '' '', '' ''); insert into CODEMAKER ( BADGEID, BUTTONLABEL, BUTTONFUNCTION, TEMPLATE, TITLE, SUBTITLE, BODYTEXT, ICON, BADGE, BACKGROUNDIMAGE, BACKGROUNDCOLOR, FINGERPRINT, BARCODE ) values ( upper(:badgeId), :buttonLabel, :buttonFunction, :template, tmpTitle, tmpSubtitle, tmpBody, :icon, :badge, :backgorundImage, :backgroundColor, :fingerprint, :barcode ) returning id into tmpId; APEX_JSON.open_object; APEX_JSON.write(''badgeId'', upper(:badgeId)); APEX_JSON.write(''status'', ''new''); APEX_JSON.write(''recordId'', tmpId); APEX_JSON.close_object; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN update CODEMAKER set TEMPLATE = :template, TITLE = tmpTitle, SUBTITLE = tmpSubtitle, BODYTEXT = tmpBody, ICON = :icon, BADGE = :badge, BACKGROUNDIMAGE = :backgroundImage, BACKGROUNDCOLOR = :backgroundColor, FINGERPRINT = :fingerprint, BARCODE = :barcode where BADGEID = upper(:badgeId) and BUTTONLABEL = :buttonlabel and BUTTONFUNCTION = :buttonfunction returning id into tmpId; commit; APEX_JSON.open_object; APEX_JSON.write(''badgeId'', upper(:badgeId)); APEX_JSON.write(''reacordId'', tmpId); APEX_JSON.close_object; end;'); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'POST', p_name => 'X-CODECARD-BUTTON-FUNCTION', p_bind_variable_name => 'buttonFunction', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'POST', p_name => 'X-CODECARD-BUTTON-LABEL', p_bind_variable_name => 'buttonLabel', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_PARAMETER( p_module_name => 'functions', p_pattern => 'master', p_method => 'POST', p_name => 'X-CODECARD-ID', p_bind_variable_name => 'badgeId', p_source_type => 'HEADER', p_param_type => 'STRING', p_access_method => 'IN', p_comments => NULL); ORDS.DEFINE_TEMPLATE( p_module_name => 'functions', p_pattern => 'register', p_priority => 0, p_etag_type => 'HASH', p_etag_query => NULL, p_comments => NULL); ORDS.DEFINE_HANDLER( p_module_name => 'functions', p_pattern => 'register', p_method => 'POST', p_source_type => 'plsql/block', p_mimes_allowed => 'application/json', p_comments => NULL, p_source => 'declare tmpBadgeId varchar2(100); begin insert into CODECARD ( BADGEID, NAME, CREATED ) values ( UPPER(:badgeId), INITCAP(:name), systimestamp ); APEX_JSON.open_object; APEX_JSON.write(''badgeId'', upper(:badgeId)); APEX_JSON.write(''status'', ''registered''); APEX_JSON.close_object; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN UPDATE CODECARD set NAME = INITCAP(:name) where BADGEID = upper(:badgeId); APEX_JSON.open_object; APEX_JSON.write(''badgeId'', upper(:badgeId)); APEX_JSON.write(''status'', ''logged''); APEX_JSON.close_object; end;'); COMMIT; END; |
Setup a button press
One final piece of information you’ll need is your CodeCard’s id.
- Turn your CodeCard on.
- Press and release the A and B buttons at the same time. Your CodeCard will display a barcode with a 12 character id string under it.
- Save that ID string.
Testing
POST
If you are using a tool such as Postman to test your API you will need to create a POST request with the following pieces.
- URI: {{URL from the prerequisite section}}/cc/functions/master
For example: https://asdr34edyjtyi4j-codecard.adb.us-ashburn-1.oraclecloudapps.com/ords/cc/functions/master - Headers
- X-CODECARD-ID: Us the ID string from above
- X-CODECARD-BUTTON-LABEL: either ‘a’ or ‘b’
- X-CODECARD-BUTTON-FUNCTION: use ‘1’ for a short press or ‘2’ for a long press
- Content-Type: application/json
- Body: use a JSON object like this
123456789{"template": "template5","title": "I DID IT!!!","subtitle": "My CodeCard","bodytext": "is running on my Oracle Always Free ATP database!","icon": "opensource","backgroundColor": "white","barcode": "Oracle Code"}
If you’d prefer to use a CURL command (replace the {{ }} sections with your values):
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
curl --location --request POST '{{URL}}/cc/functions/master' \ --header 'X-CODECARD-ID: {{CodeCardID}}' \ --header 'X-CODECARD-BUTTON-LABEL: a' \ --header 'X-CODECARD-BUTTON-FUNCTION: 1' \ --header 'Content-Type: application/json' \ --data-raw '{ "template": "template5", "title": "I DID IT!!!", "subtitle": "My CodeCard", "bodytext": "is running on my Oracle Always Free ATP database!", "icon": "opensource", "backgroundColor": "white", "barcode": "Oracle Code" }' |
With either method, you should receive a response with a status of 200 and a JSON object in the body like this
1 2 3 4 |
{ "badgeId":"YourBadgeId" ,"reacordId":2 } |
Test GET
Assuming you completed the prerequisite section, you should be able to short or long press the A or B button on your CodeCard and your new template settings will be displayed on your screen.
You can also test the GET handler by sending a GET request to the same URI used above. Use the same values for the headers, you do not need a body.
- X-CODECARD-ID: Use the ID string from above
- X-CODECARD-BUTTON-LABEL: either ‘a’ or ‘b’
- X-CODECARD-BUTTON-FUNCTION: use ‘1’ for a short press or ‘2’ for a long press
If you’d rather use CURL (replace the {{ }} sections with your values):
1 2 3 4 |
curl --location --request GET '{{URL}}/cc/functions/master' \ --header 'X-CODECARD-ID: {{CodeCardID}}' \ --header 'X-CODECARD-BUTTON-LABEL: a' \ --header 'X-CODECARD-BUTTON-FUNCTION: 1' |
You should receive a JSON object like the one you sent in the body of your POST.
1 2 3 4 5 6 7 8 9 |
{ "template": "template5", "title": "I DID IT!!!", "subtitle": "My CodeCard", "bodytext": "is running on my Oracle Always Free ATP database!", "icon": "opensource", "backgroundColor": "white", "barcode": "Oracle Code" } |
If the GET request works but your CodeCard doesn’t, go back to the prerequisite section and make sure you followed steps 4 and 5 correctly.
Have Fun
Once you get everything working you could experiment with changing the ORDS GET handler to do something else when a button is pressed.
- Create a new table and log which button is pressed and if it’s a short or long press.
- Run a query on a table and return the results in the bodytext. (There are some size limits.)
- Execute a stored procedure that does whatever you want it to do.
- Make a different module/template/GET API for each button/press.
You must be logged in to post a comment.