Category Archives: CodeCard

Use ORDS to run the CodeCard backend on your own free Oracle Cloud Database

January 29, 2020 bcarter Leave a comment

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

1. Create a compartment in your Oracle Cloud Account (Optional but recommended)
2. Create an Always Free Autonomous Database on the Oracle Cloud
3. Go to the Service Console for your new database, click on “Development” and save the URL in the “RESTful Services and SODA” section.
4. CodeCard Sketch changes: Howto disable fingerprint check and increase REST URI size
5. 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.
  
  buttona1={{URL}} cc/functions/master buttona2={{URL}} cc/functions/master buttonb1={{URL}} cc/functions/master buttonb2={{URL}} cc/functions/master
  
  1
  2
  3
  4
  
  buttona1={{URL}} cc/functions/master
  buttona2={{URL}} cc/functions/master
  buttonb1={{URL}} cc/functions/master
  buttonb2={{URL}} cc/functions/master
  
  Make sure there’s only one / between your URL and cc. For example:
  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.

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;

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.

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
   )
/

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.

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;

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’.

  ORDS.DEFINE_MODULE(
      p_module_name    => 'functions',
      p_base_path      => '/functions/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => NULL);

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.

  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_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/master

GET 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.

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_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’.

  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_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.

  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, ''&nbsp;'', '' '');
    tmpSubtitle := regexp_replace(:subtitle, ''<.*?>'');
    tmpSubtitle := replace(tmpSubtitle, ''&nbsp;'', '' '');
    tmpBody := regexp_replace(:bodytext, ''<.*?>'');
    tmpBody := replace(tmpBody, ''&nbsp;'', '' '');

    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_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.

  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_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.

  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;');

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.

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, ''&nbsp;'', '' '');
    tmpSubtitle := regexp_replace(:subtitle, ''<.*?>'');
    tmpSubtitle := replace(tmpSubtitle, ''&nbsp;'', '' '');
    tmpBody := regexp_replace(:bodytext, ''<.*?>'');
    tmpBody := replace(tmpBody, ''&nbsp;'', '' '');

    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;

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

{
    "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"
}

{

"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):

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"
}'

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

{
"badgeId":"YourBadgeId"
,"reacordId":2
}

{

"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):

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'

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.

{
    "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"
}

{

"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.

CodeCard, Information, REST

CodeCard Sketch changes: Howto disable fingerprint check and increase REST URI size

October 23, 2019 bcarter Leave a comment

Pre-Requisites

You have followed the instructions here.
You are comfortable using the Arduino IDE and making changes to the Sketch.

Fingerprint Check

In the comments on the post You Went to Oracle Open World 19 and got a Code Card, now what? we talked about how to update the fingerprint of the security certificate for the REST API when it changes.

I’m not a big fan of doing things multiple times and since I’m not planning on doing anything of a sensitive nature with my CodeCard I decided I would just disable the fingerprint check altogether.

How to tell if the fingerprint check is causing your badge to stop working.

If you push one of the buttons and it hangs on the “Please wait…” screen forever, check the Serial Monitor for a message similar to this.

>>>
Request:
  host: apex.oracle.com
  port: 443
  url: https://apex.oracle.com/pls/apex/appslab/functions/master
  fingerprint: 8B 8A B0 33 DE B9 77 A7 4D D1 7D 48 EB DD FF 15 4D 51 E0 A6
  method: GET
  Connection insecure! Halting execution.
>>>
Shuting down...

>>>

Request:

host: apex.oracle.com

port: 443

url: https://apex.oracle.com/pls/apex/appslab/functions/master

fingerprint: 8B 8A B0 33 DE B9 77 A7 4D D1 7D 48 EB DD FF 15 4D 51 E0 A6

method: GET

Connection insecure! Halting execution.

>>>

Shuting down...

Warning, If you’re planning to use your CodeCard for anything you’d consider sensitive, do not make this change!

Upgrade the driver

Clone or download the CodeCard repository
In the Arduino IDE, open the file codecard/arduino/codecard/codecard.ino
Open the Board Manager. Tools/Board/Boards Manager
Search for esp
Select version 2.5.2
Click Install
Close the Boards Manager

Code Changes

Click on the httpClient.h tab

Replace line 74 with BearSSL::WiFiClientSecure client;

String secureRequest(String host, int port, String url, String btnLabel, int btnFunction) {
  BearSSL::WiFiClientSecure client;
  
  String methodKey =  "method" + btnLabel + String(btnFunction);

String secureRequest(String host, int port, String url, String btnLabel, int btnFunction) {

BearSSL::WiFiClientSecure client;

String methodKey = "method" + btnLabel + String(btnFunction);

On line 102 add client.setInsecure();

} client.setInsecure(); client.setTimeout(10000); if (!client.connect(host, port)) {

100
101
102
103
104
105

  }

  client.setInsecure();

  client.setTimeout(10000);
  if (!client.connect(host, port)) {

Comment out the fingerprint check starting on line 112

//  if (!client.verify(fingerprint.c_str(), host.c_str())) {
//    Serial.println(F("  Connection insecure! Halting execution."));
//    Serial.println(F(">>>"));
//    return "";
//  }

112

113

114

115

116

// if (!client.verify(fingerprint.c_str(), host.c_str())) {

// Serial.println(F(" Connection insecure! Halting execution."));

// Serial.println(F(">>>"));

// return "";

// }

Uncomment line 152 and remove line 153

void httpsImage(String host, int port, String url, int16_t x, int16_t y, String fingerprint, bool with_color) {

  // WiFiClientSecure secureClient;
   BearSSL::WiFiClientSecure secureClient;
  
  bool connection_ok = false;

149

150

151

152

153

154

void httpsImage(String host, int port, String url, int16_t x, int16_t y, String fingerprint, bool with_color) {

// WiFiClientSecure secureClient;

BearSSL::WiFiClientSecure secureClient;

bool connection_ok = false;

On line 176 add secureClient.setInsecure();

  //secureClient.setFingerprint(fp);

  secureClient.setInsecure();
  
  secureClient.setTimeout(10000);
  if (!secureClient.connect(host, port)) {

174

175

176

177

178

179

//secureClient.setFingerprint(fp);

secureClient.setInsecure();

secureClient.setTimeout(10000);

if (!secureClient.connect(host, port)) {

Comment out the fingerprint check starting on line 185

//  if (!secureClient.verify(fingerprint.c_str(), host.c_str())) {
//    Serial.println(F("  Connection insecure! Halting execution."));
//    Serial.println(F(">>>"));
//    return;
//  }

185

186

187

188

189

// if (!secureClient.verify(fingerprint.c_str(), host.c_str())) {

// Serial.println(F(" Connection insecure! Halting execution."));

// Serial.println(F(">>>"));

// return;

// }

Increase arrayToStore variable size

If you’re planning to change the REST calls to use a different and longer URI you will want to increase the size of the arrayToStore variables. Currently they are set to 100, I changed mine to 200, but you can use whatever you’d like.

Click on the memory.h tab
On lines 18 and 46, change the arrayToStore variable to the new size

String getFromMemory(int addr) { char arrayToStore[200]; if (EEPROM.read(addr*maxValue) == 255){

17
18
19

String getFromMemory(int addr) {
  char arrayToStore[200];
  if (EEPROM.read(addr*maxValue) == 255){

void saveToMemory(int addr, String val) { char arrayToStore[200]; val.toCharArray(arrayToStore, val.length()+1) ;

45
46
47

void saveToMemory(int addr, String val) {
  char arrayToStore[200];
  val.toCharArray(arrayToStore, val.length()+1) ;
Click on the wifi.h tab
On line 7, change the arrayToStore variable to the new size

bool wifiConnect() { char arrayToStore[200]; String ssid = EEPROM.get(0*maxValue, arrayToStore);

6
7
8

bool wifiConnect() {
char arrayToStore[200];
String ssid = EEPROM.get(0*maxValue, arrayToStore);

Troubleshooting

Sketch uses too much program storage space

I have seen this error off and on when I compile the sketch. If you get this error, the easiest way to reduce the size of the sketch is to remove some of the larger icons in icons.h.

Click on icons.h
Find a 128x128px image that you don’t plan to use and either comment it or remove it.
Click on templates.h
Find the drawIcon128(int x, int y, String icon, long color) method and comment or remove the reference to the image you removed.
You may have to remove more than one image.

“Connection failed” or you receive an empty response “{}”

This one can be tricky. The way I’ve solved this is to erase the ESP8266 flash memory.

Usually this works:

Connect with the Serial Monitor
Press and release the A and B buttons
Wait for the Serial Monitor to display all of the data
Enter “reset” and send
Enter “ls” and update any settings as needed

If that doesn’t work you may need to search for another way to reset the memory. Please leave a comment if you have a better way.

CodeCard, Information, Python, Tools

You Went to Oracle Open World 19 and got a Code Card, now what?

September 24, 2019 bcarter 20 Comments

We handed out several hundred Code Cards this year and I’m guessing a few of you are wondering what to do next.

If you got home and pressed one of the buttons you more than likely saw this screen.

Don’t worry, it’s not broken, you just need to get it connected to the internet. There are a few different ways to do this.

Note: The Code Card uses a 2.4 GHz WiFi connection.

Create a WiFi Network

The easiest way to get it connected is to create a WiFi network using the credentials that are already set on the Code Card.

SSID=pmac851
Password=thinkcodetrigger

1 2	SSID=pmac851 Password=thinkcodetrigger

One way you can do this is to create guest network on your WiFi router. Please refer to your router manual for instructions on setting this up.

Alternatively, you could create a hotspot on your phone, but make sure you’re OK with any charges from your service provider.

Edit:

At Oracle we like to keep everything patched and up to date. After I published this post, the security certificate for the REST back end was updated. This means you will also need to update the fingerprint settings for the four button options.

This also means that creating a WiFi SSID as in the paragraph above will only work if you also update the following four settings. For that you’ll need to choose one of the methods below. (I have added them to the examples below.)

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Change the WiFi credentials on the card

If you’d rather just connect the Code Card to your existing WiFi network (2.4 GHz only), you can change the settings on the card.

In order to do this you need to open a serial connection to the card and send the following commands (using your WiFi credentials):

ssid=pmac851
password=thinkcodetrigger
fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

ssid=pmac851

password=thinkcodetrigger

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

A Little Python

If you’d like to use Python:

Install Python 3 if you don’t already have it.
Open a terminal and install pyserial.

Shell

python3 -m pip install pyserial

1

python3 -m pip install pyserial

Create a file using the following code. I named mine ccSerial.py.

import serial
import serial.tools.list_ports as port_list
import time

ser = serial.Serial()

settings = ['ssid=myHomeSSID',
            'password=myPassword',
            'fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',
            'fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',
            'fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',
            'fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47']

def findPort():
    ports = list(port_list.comports())
    cardPort = None
    for p in ports:
        if "USB to UART" in str(p):
            cardPort = p.device
            break
    return cardPort

def openPort():
    global ser
    if ser.is_open:
        print('Closing Port')
        ser.close()

    usbPort = findPort()

    if usbPort:
        ser.baudrate = 115200
        ser.timeout = 5
        ser.port = usbPort
        print("Using Port:", ser.port)
        ser.open()

    return ser

def readAll(ser):
    response = b''
    while True:
        chunk = ser.read(size=200)
        response += chunk
        if not len(chunk) == 200:
            break

    return response.decode('utf-8', 'ignore')

def sendSettings(ser):
    print("Updating Settings\n")
    for setting in settings:
        print(setting)
        ser.write(str.encode(setting))

        print(readAll(ser).split('\r\n')[0] + '\n')

def cardReady():

    cardPort = openPort()

    attempts = 30
    for attempt in range (1, attempts + 1):
        print('Attempt %i of %i.' % (attempt, attempts))

        try:
            if cardPort.is_open == False:
                print('CodeCard not detected on USB')
                cardPort = openPort()
                time.sleep(2)
            else:
                cardPort.write(str.encode('status'))
                cardStatus = readAll(cardPort).split('\r\n')

                if cardStatus[0].startswith('IP address: '):
                    cardPort.close()
                    return True
                elif len(cardStatus[0]) == 0:
                    print('****************************************************')
                    print('*      Press A and B Buttons on the CodeCard.      *')
                    print('* The screen should flash and display the barcode. *')
                    print('****************************************************\n')
                else:
                    while len(cardStatus[0]) > 0:
                        print('Clearing Card Buffer')
                        # print(cardStatus)
                        cardStatus = readAll(cardPort).split('\r\n')
                        time.sleep(1)

            time.sleep(1)
        except serial.SerialException:
            print('Connection Error. Attempting to re-connect.')
            cardPort = openPort()
            time.sleep(1)
    
    if cardPort.is_open:
        cardPort.close()

def setDefaults():
    if cardReady():
        print('Setting Configuration Values')
        cardPort = openPort()
        sendSettings(cardPort)

        if cardPort:
            print('Restarting Code Card')
            print('Please turn it off and on again\n')
            cardPort.write(str.encode('restart'))
            cardPort.close()

if __name__ == '__main__':
    setDefaults()

100

101

102

103

104

105

106

107

108

109

110

111

112

import serial

import serial.tools.list_ports as port_list

import time

ser = serial.Serial()

settings = ['ssid=myHomeSSID',

'password=myPassword',

'fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',

'fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',

'fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47',

'fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47']

def findPort():

ports = list(port_list.comports())

cardPort = None

for p in ports:

if "USB to UART" in str(p):

cardPort = p.device

break

return cardPort

def openPort():

global ser

if ser.is_open:

print('Closing Port')

ser.close()

usbPort = findPort()

if usbPort:

ser.baudrate = 115200

ser.timeout = 5

ser.port = usbPort

print("Using Port:", ser.port)

ser.open()

return ser

def readAll(ser):

response = b''

while True:

chunk = ser.read(size=200)

response += chunk

if not len(chunk) == 200:

break

return response.decode('utf-8', 'ignore')

def sendSettings(ser):

print("Updating Settings\n")

for setting in settings:

print(setting)

ser.write(str.encode(setting))

print(readAll(ser).split('\r\n')[0] + '\n')

def cardReady():

cardPort = openPort()

attempts = 30

for attempt in range (1, attempts + 1):

print('Attempt %i of %i.' % (attempt, attempts))

try:

if cardPort.is_open == False:

print('CodeCard not detected on USB')

cardPort = openPort()

time.sleep(2)

else:

cardPort.write(str.encode('status'))

cardStatus = readAll(cardPort).split('\r\n')

if cardStatus[0].startswith('IP address: '):

cardPort.close()

return True

elif len(cardStatus[0]) == 0:

print('****************************************************')

print('* Press A and B Buttons on the CodeCard. *')

print('* The screen should flash and display the barcode. *')

print('****************************************************\n')

else:

while len(cardStatus[0]) > 0:

print('Clearing Card Buffer')

# print(cardStatus)

cardStatus = readAll(cardPort).split('\r\n')

time.sleep(1)

except serial.SerialException:

print('Connection Error. Attempting to re-connect.')

cardPort = openPort()

time.sleep(1)

if cardPort.is_open:

cardPort.close()

def setDefaults():

if cardReady():

print('Setting Configuration Values')

cardPort = openPort()

sendSettings(cardPort)

if cardPort:

print('Restarting Code Card')

print('Please turn it off and on again\n')

cardPort.write(str.encode('restart'))

cardPort.close()

if __name__ == '__main__':

setDefaults()

Change lines 7 and 8 to use your WiFi credentials.

In the terminal run the new module and follow the instructions.
You should see something similar to the following.

[Projects]$ python3 ccSerial.py 
Using Port: /dev/ttyUSB0
Attempt 1 of 30.
****************************************************
*      Press A and B Buttons on the CodeCard.      *
* The screen should flash and display the barcode. *
****************************************************

Attempt 2 of 30.
Clearing Card Buffer
Clearing Card Buffer
Attempt 3 of 30.
Setting Configuration Values
Using Port: /dev/ttyUSB0
Updating Settings

ssid=myHomeSSID
Value saved for ssid: myHomeSSID

password=myPassword
Value saved for password: myPassword

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
Value saved for fingerprinta1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
Value saved for fingerprinta2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
Value saved for fingerprintb1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
Value saved for fingerprintb2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Restarting Code Card
Please turn it off and on again
[Projects]$

[Projects]$ python3 ccSerial.py

Using Port: /dev/ttyUSB0

Attempt 1 of 30.

****************************************************

* Press A and B Buttons on the CodeCard. *

* The screen should flash and display the barcode. *

****************************************************

Attempt 2 of 30.

Clearing Card Buffer

Attempt 3 of 30.

Setting Configuration Values

Using Port: /dev/ttyUSB0

Updating Settings

ssid=myHomeSSID

Value saved for ssid: myHomeSSID

password=myPassword

Value saved for password: myPassword

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Value saved for fingerprinta1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Value saved for fingerprinta2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Value saved for fingerprintb1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Value saved for fingerprintb2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Restarting Code Card

Please turn it off and on again

[Projects]$

Arduino IDE

If you have a Serial communication tool that you like, you should be able to use it to change the settings.

If not the Arduino IDE includes a Serial Monitor tool that work great.

Do not connect your Code Card to the computer yet.
Download, install and run the Arduino IDE.
Click on Tools / Port.
See what Ports are already in use.
Connect your Code Card and turn it on.
Click on Tools / Port.
The newest Port should be your Code Card; select it.
Click on Tools / Serial Monitor.
On the bottom of the Serial Monitor, choose 115200 baud.
Turn your Code Card off and on again.

Press and release the A and B buttons at the same time.
You should see something like this.

rll⸮⸮|⸮l⸮|⸮l⸮b|⸮⸮⸮⸮r⸮blb⸮⸮nn⸮lnn⸮⸮⸮bp⸮lrlrlp⸮n⸮⸮l⸮⸮bn⸮|l⸮⸮b⸮⸮nn⸮l⸮⸮l`⸮nnl`nr⸮⸮⸮nb⸮lr⸮⸮nb⸮l⸮bbb⸮⸮⸮⸮l`⸮⸮n⸮***************************************************************************************
  Code Card v1.0
  Oracle Developer Community
  developer.oracle.com/codecard
***************************************************************************************
Commands:
  ls                Show all stored key/values
  help              Show this help
  shortpress[a|b]   Simulate the press of a button
  longpress[a|b]    Simulate the long press of a button
  connect           Connect to wifi
  disconnect        Disconnect wifi
  restart           Restart wifi
  status            Show wifi status
  home              Show home screen
  reset             Reset to factory settings

Usage:
  Read saved key value:
    key
  Save new key value:
    key=[value]

Available keys:
  ssid, password, buttona1, buttona2, buttonb1, buttonb2, fingerprinta1, fingerprinta2, 
  fingerprintb1, fingerprintb2, methoda1, methoda2, methodb1, methodb2, 
>>>
Connecting to 'pmac851' ....................
Could not connect to 'pmac851'. Please check your ssid and password values. Type 'help' for more info.
>>>

rll⸮⸮|⸮l⸮|⸮l⸮b|⸮⸮⸮⸮r⸮blb⸮⸮nn⸮lnn⸮⸮⸮bp⸮lrlrlp⸮n⸮⸮l⸮⸮bn⸮|l⸮⸮b⸮⸮nn⸮l⸮⸮l`⸮nnl`nr⸮⸮⸮nb⸮lr⸮⸮nb⸮l⸮bbb⸮⸮⸮⸮l`⸮⸮n⸮***************************************************************************************

Code Card v1.0

Oracle Developer Community

developer.oracle.com/codecard

***************************************************************************************

Commands:

ls Show all stored key/values

help Show this help

shortpress[a|b] Simulate the press of a button

longpress[a|b] Simulate the long press of a button

connect Connect to wifi

disconnect Disconnect wifi

restart Restart wifi

status Show wifi status

home Show home screen

reset Reset to factory settings

Usage:

Read saved key value:

key

Save new key value:

key=[value]

Available keys:

ssid, password, buttona1, buttona2, buttonb1, buttonb2, fingerprinta1, fingerprinta2,

fingerprintb1, fingerprintb2, methoda1, methoda2, methodb1, methodb2,

>>>

Connecting to 'pmac851' ....................

Could not connect to 'pmac851'. Please check your ssid and password values. Type 'help' for more info.

>>>

In the input field at the top enter (use your SSID)

ssid=myHomeSSID

1

ssid=myHomeSSID

Click Send.
You should see something like

Value saved for ssid: myHomeSSID >>> Use the command 'restart' for changes to take effect. >>>

1
2
3
4

Value saved for ssid: myHomeSSID
>>>
Use the command 'restart' for changes to take effect.
>>>
At the top enter (use your password)

password=myPassword

1

password=myPassword

Click Send.
You should see something like

Value saved for password: myPassword >>> Use the command 'restart' for changes to take effect. >>>

1
2
3
4

Value saved for password: myPassword
>>>
Use the command 'restart' for changes to take effect.
>>>
At the top enter (use your password)

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

1

fingerprinta1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Click Send.
You should see something like

Value saved for fingerprinta1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47 >>>

1
2

Value saved for fingerprinta1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
>>>
At the top enter (use your password)

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

1

fingerprinta2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Click Send.
You should see something like

Value saved for fingerprinta2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47 >>>

1
2

Value saved for fingerprinta2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
>>>
At the top enter (use your password)

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

1

fingerprintb1=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Click Send.
You should see something like

Value saved for fingerprintb1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47 >>>

1
2

Value saved for fingerprintb1: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
>>>
At the top enter (use your password)

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

1

fingerprintb2=19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47

Click Send.
You should see something like

Value saved for fingerprintb2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47 >>>

1
2

Value saved for fingerprintb2: 19 D1 5D 53 82 89 49 F6 92 7F 93 E5 06 EE 6D 40 16 41 4A 47
>>>
Unplug your Code Card.
Turn it off and on again.
It should now connect to your network and the buttons should work.

What Else Can You Do?

There are a lot of things you can do with your Code Card.

For some ideas check out this GitHub page for some how to guides and other information.

Prerequisites

Database Setup

As Admin, create the CODE_CARD schema.

As code_card, create the database objects

Oracle REST Data Services API

This step was completed above

Define a module

Define a Template

GET request handler

Parameters for the bind variables

Handle POST requests

Parameters for the bind variables

Register template (Optional)

Full ORDS PL/SQL code

Setup a button press

Testing

POST

Test GET

Have Fun

Share this:

Pre-Requisites

Fingerprint Check

How to tell if the fingerprint check is causing your badge to stop working.

Warning, If you’re planning to use your CodeCard for anything you’d consider sensitive, do not make this change!

Upgrade the driver

Code Changes

Increase arrayToStore variable size

Troubleshooting

Sketch uses too much program storage space

“Connection failed” or you receive an empty response “{}”

Share this:

Create a WiFi Network

Change the WiFi credentials on the card

A Little Python

Arduino IDE

What Else Can You Do?

Share this: