Docs‎ > ‎Logic Designer‎ > ‎Database‎ > ‎

Oracle

Espresso supports the Oracle extensions shown here.  In addition, see Oracle-specific Procedure Extensions.


Sequences (aka Auto Numbering)

Oracle provides SEQUENCES for automatic value generation.   In many ways, they are superior to other databases identifier columns, but unless you are familiar with Oracle, they may seem obtuse.

Espresso Logic supports the 3 major approaches to using sequences in Oracle.

1 - Traditional SEQ.NEXTVAL on insert.   Inserts are of the form:

INSERT INTO FOO(A, B, C) VALUES (MYSEQUENCE.NEXTVAL, 1.23, 'Hello')

In the Logic Designer, under Database/Sequences, you can specify that column A in table FOO should use the sequence MYSEQUENCE. Once that is specified, the sequence will be used to generate a value for the specified column for all inserts. Note that the same sequence can be used for more than one table.

2 - BEFORE UPDATE Trigger - This is the approach often taken when porting from a database that has IDENTIFIER or AUTONUM columns.  The trigger is usually implemented using a sequence.  Insert statements are of the form
INSERT INTO FOO(B, C) VALUES (1.23, 'Hello')
or 
INSERT INTO FOO(A, B, C) VALUES (NULL, 1.23, 'Hello') 

with special queries to get back the inserted value.  To specify this behavior in the Logic Designer, under Database/Sequences, you can simply select the column that will be set by the trigger, and leave the sequence empty. This tells Espresso Logic that it should expect this column to be automatically set by the database.

3 - Oracle 12c introduces an 'automatic' sequence.  Inserts are performed as
INSERT INTO FOO(A, B, C) VALUES (NULL, 1.23, 'Hello')
or 
INSERT INTO FOO(B, C) VALUES (1.23, 'Hello')
 

To specify this behavior in the Logic Designer, In Espresso Logic, just specify the column name, just like with the previous case.  A future enhancement will automatically recognize the new sequence columns.


Case Sensitivity

Oracle is normally used in a manner where case does not matter.  select 1 from dual and select 1 from DUAL both read from the "DUAL" table.  Espresso Logic treats Oracle tables and columns as case-sensitive.  For example, a table defined as:
create table "FooBar"("A" number, "a" number) 
will be displayed in the Tables as FooBar with two columns 'A' and 'a'.  Espresso Logic will generate SQL expressions that honor the Oracle rules and emit:
select A, "a" from "FooBar" order by A
"a" providing the proper quotations as needed.  

Access in JavaScript can be done with row.A and row.a respectively.  Columns that have non-alphanumeric columns such as "CUST!NUM" will be correctly emitted in SQL statements.  In JavaScript, object property notation must be used, e.g. 
row["CUST!NUM"] = 'Yes';

Boolean

Oracle does not have a boolean type.  Oracle databases traditionally use char(1) with 'Y' and 'N' for boolean values.  These are tested in rules as column == 'Y'.  We are considering recognizing these as booleans in JavaScript, but are looking for feedback from our Oracle customers.  If you would like this feature, please contact us.

Synonyms

Synonyms are not currently supported. Please contact us if you would like this feature implemented.

User Defined Types, Nested Tables, VARRAYs
Espresso automatically recognizes Oracle's User Defined Types, Nested Tables and VARRAYs.  They are fully supported for GET, PUT, POST, DELETE.  In addition, support exists for Stored Procedures and Oracle Functions that return or take UDTs, Nest Tables and VARRAYS.  Stored Procedures can now be invoked through the POST interface with a JSON payload representing the arguments.

For example, given a schema such as

create or replace type address_ty as object (

  street_num number

 ,street varchar2(30)

 ,city varchar2(50)

 ,zipcode char(5)

)

/


create or replace type person_ty as object (

  name varchar2(30)

 ,address address_ty

)

/


create or replace type person_nt as table of person_ty

/


create or replace type person_va as varray(5) of person_ty

/


create table staff (

  id number(9) not null primary key

 ,person person_ty not null

 ,friends person_va

 ,relatives person_nt

)

nested table relatives store as relatives_nt_tab

/

A simple GET request might return

[
  {
    "ID": 1,
    "PERSON": {
      "NAME": "David",
      "ADDRESS": {  "STREET_NUM": 123,  "STREET": "Main Street", "CITY": "Happyville", "ZIPCODE": "90210"  }
    },
    "FRIENDS": [
      {
        "NAME": "Alpha",
        "ADDRESS": {"STREET_NUM": 123, "STREET": "Main Street", "CITY": "Happyville", "ZIPCODE": "90210" }
      }
    ],
    "RELATIVES": [
      {
        "NAME": "Beta",
        "ADDRESS": { "STREET_NUM": 124, "STREET": "Main Street", "CITY": "Happyville", "ZIPCODE": "90210" }
      },
      {
        "NAME": "Beta",
        "ADDRESS": { "STREET_NUM": 124, "STREET": "Main Street", "CITY": "Happyville", "ZIPCODE": "90210" }
      }
    ],
    "@metadata": {
      "href": "https://demodev.espressologic.com/rest/el-dev/demo/v1/test_demooracle:STAFF/1",
      "checksum": "A:549420f2213e6beb",
      "links": []
    }
  },
  {
    "ID": 2,
    "PERSON": {
      "NAME": "Max",
      "ADDRESS": { "STREET_NUM": 345, "STREET": "Rue de Paris", "CITY": "Ville de Joyeux", "ZIPCODE": "00000"   }
    },
    "FRIENDS": null,
    "RELATIVES": null,
    "@metadata": {
      "href": "https://demodev.espressologic.com/rest/el-dev/demo/v1/test_demooracle:STAFF/2",
      "checksum": "A:0ca691a1500e31b5",
      "links": []
    }
  }
]

Note the automatic JSON structure for the PERSON fields, and the VARRAYs and NESTED TABLES.  POST (aka INSERT), and PUT (aka update) use the same style for JSON.

Similar approach for POSTing to a Stored Procedure expecting a single arg "FIRST_ARG" can be done using
{
"FIRST_ARG": { "NAME": "David", "OCCUPATION": "Software Specialist" }
}

This can also be provided in the GET interface using query parameter args={"FIRST_ARG": { "NAME": "David", "OCCUPATION": "Software Specialist" }}

where the curlies etc. should be quoted according to RFC 3896.

Pagination

For Oracle versions prior to 12c, we use a strategy of nested queries to perform pagination.  The Oracle optimizer does wonderful job on these.  In 12c, Oracle has added support for LIMIT/OFFSET syntax.  We use this syntax where possible.

Supported Versions

We support the Oracle 10.2 and higher. Support for earlier versions is possible.