Docs‎ > ‎Integrate Menu‎ > ‎Data Sources‎ > ‎

Oracle Extensions

API Creator supports the following Oracle 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.

You can use sequences in Oracle using one of the following approaches:
                  • Traditional SEQ.NEXTVAL on insert
                  • BEFORE UPDATE Trigger
                  • 'Automatic' sequence
The following image shows an example of the Data Sources, Sequences tab.

Traditional SEQ.NEXTVAL on Insert

Inserts are of the form:
INSERT INTO FOO(A, B, C) VALUES (MYSEQUENCE.NEXTVAL, 1.23, 'Hello')

For example, you can specify that column A in table FOO should use the sequence MYSEQUENCE. Select Integrate, Data Sources, Sequences and specify the Table, Column, and Sequence. The sequence is used to generate a value for the specified column for all inserts. You can use the same sequence for more than one table.

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 following forms:
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.

For example, you can tell API Creator that it should expect a column to be automatically set by the database. Select Integrate, Data Sources, Sequences, select the column that you want the trigger to send, and leave the sequence empty.

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

Select Integrate, Data Sources, Sequences, select the column for the automatic sequence, and leave the sequence empty.

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. API Creator treats Oracle tables and columns as case-sensitive. For example, a table defined as:
create table "FooBar"("A" number, "a" number) 
is displayed in the Tables as FooBar with two columns 'A' and 'a'. API Creator generates SQL expressions that honor the Oracle rules and emit:
select A, "a" from "FooBar" order by A
"a" providing the proper quotations as needed.  

You can access in JavaScript with row.A and row.a respectively. Columns that have non-alphanumeric columns such as "CUST!NUM" are correctly emitted in SQL statements. In JavaScript, use object property notation, for example:  
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'.

User Defined Types, Nested Tables, VARRAYs

CA Live API Creator automatically recognizes Oracle's User Defined Types, Nested Tables, and VARRAYs. These extensions are fully supported for GET, PUT, POST, DELETE. In addition, Stored Procedures and Oracle Functions that return or take UDTs, Nest Tables, and VARRAYS are supported. You can invoke stored procedures 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://server.acme.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://server.acme.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.

The following code block shows an example of a POST to a Stored Procedure expecting a single arg "FIRST_ARG":

{
"FIRST_ARG": { "NAME": "David", "OCCUPATION": "Software Specialist" }
}
You can also provide this in the GET interface using query parameter args={"FIRST_ARG": { "NAME": "David", "OCCUPATION": "Software Specialist" }} where you quote the curlies according to RFC 3896.

Pagination

Oracle supports LIMIT/OFFSET syntax. Live API Creator uses this syntax where possible.

XMLTYPE

Live API Creator supports the Oracle XMLTYPE data type for inbound and outbound JSON messages. The extension is formatted as a text string of the XML content. The following code block shows an example: 
{
    "ID": 1,
    "XMLTYPE_DEFAULT": "<foo/>",
    "@metadata": {
      "href": "http://example.com/rest/default/demo_oracle/v1/demo:STRESS_XMLTYPE/1",
      "checksum": "A:79513c82a7740b47",
      "links": []
    }

ċ
Screen Shot 2015-10-25 at 12.31.44 PM.png
(208k)
Michael Holleran,
Feb 16, 2016, 2:04 PM
Comments