Skip to main content

Oracle APEX – Amazon ALEXA integration WITHOUT Amazon Lambda ! (PART 1)

Image
By
I found few examples how to integrate Amazon Alexa with Oracle. All solutions used Amazon Lambda microservice (node.js). But it’s not necessary. You need only two elements: Oracle with ORDS and Amazon Alexa Skill.



Prerequsites:
- Amazon account (https://developer.amazon.com/ )
- Oracle 11g +
- Oracle ORDS (accessed thru SSL)
- APEX (optionaly)

1. For demo I used only one simple table:


create table alexa_orders  
 (
  alo_id      raw(16) default sys_guid() primary key,
  alo_name    varchar2(4000),
  alo_qty     number,
  alo_value   number,
  alo_date    date,
  alo_status  varchar2(1024),
  alo_ship_date date
 );

You can use these inserts to populate sample datas 

insert into alexa_orders ( alo_name, alo_qty , alo_value , alo_date, alo_status, alo_ship_date) 
values ('Pizza Pepperoni', round(dbms_random.value(1,10),0), round(dbms_random.value(5,1000),2), sysdate-dbms_random.value(1,3),'In progress',sysdate+((1/1440)*dbms_random.value(1,120)));
insert into alexa_orders ( alo_name, alo_qty , alo_value , alo_date, alo_status, alo_ship_date) 
values ('Pizza Margarita',round(dbms_random.value(1,10),0), round(dbms_random.value(5,1000),2), sysdate-dbms_random.value(1,3),'Prepared raw',sysdate+(1/1440)*dbms_random.value(1,120));
insert into alexa_orders ( alo_name, alo_qty , alo_value , alo_date, alo_status, alo_ship_date) 
values ('Pizza Diabolo',round(dbms_random.value(1,10),0), round(dbms_random.value(5,1000),2), sysdate-dbms_random.value(1,3),'Completed',sysdate+(1/1440)*dbms_random.value(1,120));
insert into alexa_orders ( alo_name, alo_qty , alo_value , alo_date, alo_status, alo_ship_date) 
values ('Pizza Pepperoni',round(dbms_random.value(1,10),0), round(dbms_random.value(5,1000),2), sysdate-dbms_random.value(1,3),'Delivered',sysdate+(1/1440)*dbms_random.value(1,120));
insert into alexa_orders ( alo_name, alo_qty , alo_value , alo_date, alo_status, alo_ship_date) 
values ('Pizza Diabolo',round(dbms_random.value(1,10),0), round(dbms_random.value(5,1000),2), sysdate-dbms_random.value(1,3),'Delivered',sysdate+(1/1440)*dbms_random.value(1,120));
commit;

2. All Oracle logic I packaged into one PLSQL package:


CREATE OR REPLACE PACKAGE alexa_pkg IS
    
    PROCEDURE parse_alexa_skill (
        p_body IN BLOB
    );

    PROCEDURE add_order (
        pr_alexa_orders IN OUT alexa_orders%rowtype
    );

END alexa_pkg;
/

create or replace PACKAGE BODY ALEXA_PKG AS

/**
* Transform blob to clob datatype.

* Author: Andrzej Nowakowski
* Since: 28-02-2018
* Version: 1.0.0

*
* @param p_date - blob data
*/   
FUNCTION blob_to_clob (p_data  IN  BLOB)
  RETURN CLOB
AS
  l_clob         CLOB;
  l_dest_offset  PLS_INTEGER := 1;
  l_src_offset   PLS_INTEGER := 1;
  l_lang_context PLS_INTEGER := DBMS_LOB.default_lang_ctx;
  l_warning      PLS_INTEGER;
BEGIN

  DBMS_LOB.createTemporary(
    lob_loc => l_clob,
    cache   => TRUE);

  DBMS_LOB.converttoclob(
   dest_lob      => l_clob,
   src_blob      => p_data,
   amount        => DBMS_LOB.lobmaxsize,
   dest_offset   => l_dest_offset,
   src_offset    => l_src_offset, 
   blob_csid     => DBMS_LOB.default_csid,
   lang_context  => l_lang_context,
   warning       => l_warning);
   
   RETURN l_clob;
END blob_to_clob;

/**
* Translate date type to Alexa date type.

* Author: Andrzej Nowakowski
* Since: 28-02-2018
* Version: 1.0.0

*
* @param p_date - some date
* @return Alexa date type "Monday, first May"
*/   
function say_date (p_date in date)
return varchar2
is
begin

if p_date is null then
return null;
else
return trim(to_char(p_date,'Day'))||'. ????'||trim(to_char(p_date,'MMDD'))||'';
end if;

end say_date;


/**
* Prepare message for ALEXA session.

* Author: Andrzej Nowakowski
* Since: 28-02-2018
* Version: 1.0.0

*
* @param p_voice_message - what alexa must say
* @param p_card_title - what display on Alexa card title (you can see it on Alexa app)
* @param p_card_content - what display on Alexa card content (you can see it on Alexa app)
*
* @return ALEXA specific message
*/   
function alexa_answer (    p_voice_message in varchar2,
                           p_card_title    in varchar2 default 'APEXUTIL',
                           p_card_content  in varchar2 default 'Apex Pizza orders information')
return varchar2
is
l_message varchar2(32000);
begin

l_message := '
{
  "version": "1.0",
  "sessionAttributes": {},
  "response": {
    "outputSpeech": {
      "type": "SSML",
      "ssml": "'||p_voice_message||'"
                    },
    "card": {
      "type": "Simple",
      "title": "'||p_card_title||'",
      "content": "'||p_card_content||'"
    },

    "shouldEndSession": true
  }
}

';

return l_message;

end alexa_answer;

/**
* Prepare data for alexa.

* Author: Andrzej Nowakowski
* Since: 28-02-2018
* Version: 1.0.0

*
* @param p_values - ALEXA'a JSON message
* @param p_json_count - quantity of alexa variables
* @param p_voice_message - what alexa must say
* @param p_card_title - what display on Alexa card title (you can see it on Alexa app)
* @param p_card_content - what display on Alexa card content (you can see it on Alexa app)
*/   
procedure message_howmanyorders(p_values        in apex_json.t_values,
                                p_json_count    in number,
                                p_voice_message in out varchar2,
                                p_card_title    in out varchar2,
                                p_card_content  in out varchar2)
is

l_slots_name varchar2(4000);
l_slot_value varchar2(4000);
l_return_howmanyorders number;

begin

   
    for x in 1.. p_json_count
    loop     


     l_slots_name := apex_json.get_members(p_path=>'request.intent.slots',p_values=>p_values)(x) ;
     l_slot_value := apex_json.get_varchar2(p_path=>'request.intent.slots.'||l_slots_name||'.value',p0=>x,p_values=>p_values);

    

    if l_slot_value is not null then
        
        if      REGEXP_COUNT(l_slot_value, '-')=2 then --ex. today, now, tomorrow, november twenty-fifth, next monday, right now
            select count(*) into l_return_howmanyorders from alexa_orders where trunc(alo_date,'DD') = to_date(l_slot_value,'YYYY-MM-DD');
        elsif   REGEXP_COUNT(l_slot_value, '-')=1 and REGEXP_COUNT(l_slot_value, 'W')= 0 then --this month, last month
            select count(*) into l_return_howmanyorders from alexa_orders where to_char(trunc(alo_date,'DD'),'YYYY-MM') =  l_slot_value;
        elsif   REGEXP_COUNT(l_slot_value, '-')=1 and REGEXP_COUNT(l_slot_value, 'W')= 1 then --ex. this week, next week
            select count(*) into l_return_howmanyorders from alexa_orders where to_char(trunc(alo_date,'DD'),'YYYY-"W"IW') =  l_slot_value;
        elsif   REGEXP_COUNT(l_slot_value, '-')=0 then --ex. this yesr, last year
            select count(*) into l_return_howmanyorders from alexa_orders where to_char(alo_date,'YYYY') =  l_slot_value;
        elsif   REGEXP_COUNT(l_slot_value, '-')=1 and REGEXP_COUNT(l_slot_value, 'W')= 0 then --this month, last month
            null;
        end if;
    
        p_voice_message := 'Apex Pizza processed '||l_return_howmanyorders||'  orders';
    
    else
        p_voice_message := 'Please say again';
    end if;
  end loop;

p_card_content  := p_voice_message;

end message_howmanyorders;

/**
* Main procedure. Parse ALEXA data and return answer

* Author: Andrzej Nowakowski
* Since: 28-02-2018
* Version: 1.0.0

*
* @param p_values - ALEXA'a JSON message
* @param p_json_count - quantity of alexa variables
* @param p_voice_message - what alexa must say
* @param p_card_title - what display on Alexa card title (you can see it on Alexa app)
* @param p_card_content - what display on Alexa card content (you can see it on Alexa app)
*/   
procedure parse_alexa_skill (p_body in blob)
is
l_body_clob     clob;
l_values        apex_json.t_values;
l_intent_name   varchar2(4000);
l_json_count    number;

l_voice_message varchar2(4000);
l_card_title    varchar2(4000);
l_card_content  varchar2(4000);
begin

--transfer blob to clob
l_body_clob := blob_to_clob (p_data => p_body);

--Parse clob to json object
apex_json.parse(l_values,l_body_clob); 


l_intent_name := apex_json.get_varchar2(p_path=>'request.intent.name',p0=>1,p_values=>l_values);

--Check how many rows in json object
l_json_count := apex_json.get_count
                                   (
                                     p_path      => 'request.intent.slots',
                                     p_values    => l_values
                                   );

if l_intent_name in ('howmanyorders') then
    
message_howmanyorders(  p_values        =>  l_values,
                        p_json_count    =>  l_json_count,
                        p_voice_message =>  l_voice_message,
                        p_card_title    =>  l_card_title,
                        p_card_content  =>  l_card_content);  
 
htp.p(alexa_answer (p_voice_message =>  l_voice_message,
                    p_card_title    =>  l_card_title,
                    p_card_content  =>  l_card_content));

end if;



end parse_alexa_skill;




  procedure add_order (pr_alexa_orders in out alexa_orders%rowtype) AS
  BEGIN
    -- TODO: Implementation required for procedure ALEXA_PKG.add_order
    NULL;
  END add_order;

END ALEXA_PKG;
In the package you can find:
  • BLOB_TO_CLOB function - Used to convert http response to readable clob
  • SAY_DATE function - Returning date in Alexa format (very specific)
  • ALEXA_ANSWER function - Used to prepare ALEXA JSON message
  • MESSAGE_HOWMANYORDERS procedure - Preparing information from ALEXA_ORDERS table (in Oracle)
  • PARSE_ALEXA_SKILL procedure - Used as a main API. It will be used by RESTService

3. Next prepare REST web service. You can use APEX, SQL Developer or PLSQL API. In this case SQLDeveloper is used.

Remember, to create webservice you have to use ORDS 3.08 or newest.
On begining enable RESTService on schema layer, my schema name is "aps":


Enable RESTService on schema layer STEP 1


Enable RESTService on schema layer STEP 2


Enable RESTService on schema layer STEP 3

Next create REST module. On Connections pannel expand REST Data Services branch and try to add new module. 


Add REST module. 

Add REST module. STEP 1

Add REST module. STEP 2

Add REST module. STEP 3

After this create Handler.

Add Handler


Add Handler Next step

Finaly connect PLSQL with RESTService. You can see SQL Worksheet with Parameters and Details tabs. (In this place SQLDeveloper is not validating syntax - be careful )




Copy, paste and Save this code into SQL Worksheet:


begin
ALEXA_PKG.parse_alexa_skill (p_body => :body);
:status := 200;
end;

You can see two parameters :body and :status. These parameters are build in so it's not allowed to define them into Parameters tab.

That's all on Oracle layer!

In the next post on blog I will describe how to prepare ALEXA skills and how to connect all together.

Second part is here:
https://apexutil.blogspot.com/2018/03/oracle-apex-amazon-alexa-integration_28.html
Labels:

Comments

Post a Comment

Popular posts from this blog

TWO-Factor Authentication with APEX application.

By
Two-factor authentication means that you must have two factors, to access your account. The first factor is something you know, like your login and password combination. The second is something you have, like a mobile device with a security app installed. That second factor — the mobile device with a security app — adds an extra layer of protection to your account. Even if hackers steal your password, they can’t log in, because they don't have your mobile device. Try 2FA on our SANDBOX environment! It is the most popular 2FA option based on Times-based One-Time Password (TOTP) alghoritm described in RFC 6238 and RFC 4226. TOTP is supported by Google Authenticator, Microsoft Authenticator, Authy 2-Factor Authentication and many other mobile apps. The most popular online services like Facebook, Google, Twitter use TOTP to protect user's accounts. TOTP is a standard algorithm based on cryptographic hash method SHA-1. SHA-1 algorithm's implementation is available sin
26 comments
Read more

ORACLE APEX with MySQL !

By
About 10 years ago I tried to build APEX application on MS SQLServer. It was possible but performance was not satisfactory. (You can read this post here) ... Today I will describe how to use APEX with MySQL. Obviusly I don't think it is good idea to build APEX app only on MySQL. It can be used as part of Hybryd solution (Oracle + MySQL or Postrgres). My description covers integration with MySQL. As a result will be APEX application with Interactive Report and CRUD form based on MySQL data. Description contains two parts: First part is a MySQL site, Second part is a APEX application part Prerequisites: Oracle 11g+ APEX 18.1+ MySQL/Postgres RestSQL I. MySQL Part 1. MySQL contains simple  apexdb database with one apexutil table. 2. To use this database install RestSQL. What is RestSQL? RestSQL is an open-source, ultra-lightweight data access layer for HTTP clients. RestSQL is a persistence framework or engine in the middle tier of a classic three tier archi
11 comments
Read more

How to return json with null value using apex_json.write(ref_cursor)

By
Few days ago I had to develop dynamic query json export module. As a sandbox I prepared PLSQL block: DECLARE c sys_refcursor; l_sql varchar2(4000) := 'select null as "col1", 100 as "col2" from dual union all select 200 as "col1", null as "col2" from dual'; l_file clob; BEGIN open c for l_sql; apex_json.initialize_clob_output; apex_json.open_object; apex_json.write('rowset', c); apex_json.close_object; l_file := apex_json.get_clob_output; apex_json.free_output; DBMS_OUTPUT.PUT_line(l_file); END; { "rowset": [{ "col2": 100 }, { "col1": 200 } ] } As you can see above PLSQL code doesn't return null value pairs : ( apex_json.write(p_name IN VARCHAR2, p_cursor IN OUT NOCOPY sys_refcursor) procedure Signature 14 doesn't support null. How to fix it... ? APEX API provides apex_js
Post a Comment
Read more