Use Stored Procedure Resources

You can leverage the business logic already developed in your database using stored procedures.
lac32
You can leverage the business logic already developed in your database using stored procedures. If the API database supports stored procedures, API Creator discovers the stored procedures in your database and makes them available as RESTful resource endpoints by reading the schema. You can supply arguments and read results in JSON.
In this article:
View your Stored Procedures
  1. With your API open, in the Create section, click 
    Schema
    .
    The tables are listed by default.
  2. Click 
    Procedures
    .
    The stored procedures display.
    For example, in the Demo API, the 
    get_employee
     stored procedure includes the following arguments:
    given_employee_id
    plus_one
    For reference, the following code snippet defines the 
    get_employee
     stored procedure:
    DELIMITER $$
    PROCEDURE get_employee(
    IN given_employee_id BIGINT
    ,INOUT plus_one BIGINT
    )
    COMMENT 'given an employee id and a number ''plus_one'', adds one to the number and returns the employee info as well as picture, voice and icon'
    begin
    set plus_one = plus_one + 1;
    select e.employee_id
    ,plus_one
    ,e.login
    ,ep.icon
    ,ep.picture
    ,ep.voice
    from employee e
    right outer join employee_picture ep
    on e.employee_id = ep.employee_id
    where given_employee_id = e.employee_id;
    select *
    from purchaseorder
    where given_employee_id = salesrep_id
    order by order_number;
    end
    $$
Invoke Stored Procedures
Invoke a stored procedure using the GET method, as follows:
Prefix argument definitions with 
arg
.
http://serer.acme.com/rest/abl/demo/v1/get_employee
?arg.given_employee_id=1&arg.plus_one=1
For reference, the 
get_employee
 stored procedure was defined. The argument definitions are referenced with the prefix 
arg
.
  • If the stored procedure updates the database, any update counts arising from the SQL are returned.
  • Metadata for each result set is returned.
  • Oracle result sets are returned as 
    REF CURSOR OUT
     parameters. A reference to this value is place in the 
    arg
     section. The actual result data is placed in the "result" array. You can add multiple 
    REF CURSOR OUT
     parameters in the same stored procedure.
Use Oracle-specific Extensions in API Creator
You can use Oracle extensions in API Creator.
For more information about the Oracle extensions, see Oracle Extensions.
POST to Stored Procedures
You can POST to a stored procedure. Contain the arguments in the body. You can include an array of separate arguments to multiple invocations to the same stored procedure. API Creator performs invocations in a single transaction and returns results in a 
procresults
 array in the resulting JSON.
The following example updates its first parameter 
(plus_one
). The parameter is returned in the JSON result (the 
arg{}
 object). You can use the
input
,
output
, and
input-output
args.
For example, the following POST to the 
demo:get_employee
 stored procedure:
 
[ { "given_employee_id": 1, "plus_one": 5 }, { "given_employee_id" : 2} ]
The following result is expected:
{ "statusCode": 200,
"procresults": [
{
"arg": {
"given_employee_id": 1,
"plus_one": 6 },
"result": [
{
"truncated": false,
"rowCount": 1,
"filteredCount": 0,
"columnMeta": [
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee",
"columnName": "employee_id",
"columnLabel": "employee_id",
"columnType": "BIGINT"
},
{
"catalog": "",
"schema": "",
"tableName": "",
"columnName": "plus_one",
"columnLabel": "plus_one",
"columnType": "BIGINT"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee",
"columnName": "login",
"columnLabel": "login",
"columnType": "VARCHAR"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee_picture",
"columnName": "icon",
"columnLabel": "icon",
"columnType": "VARBINARY"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee_picture",
"columnName": "picture",
"columnLabel": "picture",
"columnType": "LONGBLOB"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee_picture",
"columnName": "voice",
"columnLabel": "voice",
"columnType": "MEDIUMBLOB"
}
],
"rows": [
{
"employee_id": 1,
"plus_one": null,
"login": "sam",
"icon": {
"type": "base64",
"length": 1185,
"value": "iVBORw0KGgoAAAANSUhEUgAAADIAAABKCAIAAAB2LJBKAAAEaElEQVRoBdWZi5bbIAxE657+/y+7AsEwCIlHnE3aPV2vDNLoMsbOo9d937/o57ro5Hvhb279jzAJ0h9gHTJNXO3sh/5RkLDeB6StlfgRnEgc1U9Miuw40i8iR1gvMIH1DG4f6wnTMVx3J6L6x4LdtX0Ya3e9n8faMuzzWGLYmuwrWEuy9CTduXXX69vdNU6eA/Attxw6Hvq/sRyfeXEPYl/5627JrnU27iaWU/nAobHU6m9i+VaP8u8YSf5tYtnVvKP9TGMTaybxE3ObWB+4iN0Fae/lny9a2Tv5A1GsPAWbbh30gvw2klTYok2srRaG3em2JZOS3oalECCzyw+B3MStBwR6hdplon5Cd1utinn+ft0tYagYWVG/NLhvLEICxNyT4oh/yy3SqSGAENSZ9Fdp+i83eF7jiElm125NFzx8Hr/qSC7r7WxgAjRhSnlSPs/wsbSG59KIXMHryiZ1mmDNYN1UHnEO88cp93WKZQh8KdVj8stWo3OssFp5hQngcxMoUyrmuanpgzuRdkDrCsy2ojJELE5SS8/RhH1RTG2Kphak8fJ7iZncgBTH6g6Mq3iCFHiYYlfYLePM3K8920ivC10sV7wr4046YYRUwk3L+eNMp//63upk6jZWmogJJQsobHnNy48c1YSCH0RJRadS+sUyere7sb5opRGJ01FuJn71yI/oqKNtoQR2tD4yVrPENUrArTr1lKnqtOcZRrogoq5Jb9tbKsjL4ri2K9eufznCZAs6LL6gLeUbUYclABOy9H4g/0Scoz3jCGrnhlksJpMNMOdAj0kwIZtUOVjIhiLDuauUTCSjfBm4UlrlYwmHTOvRqK/uIZPun+br4E/pqI8lcy5TqtHHn1bTMRhOGcbIlJl/qdqGPtbEXhFQYjSrwZaPOblkhisfHqeFuhTUN+Z2LRmNByOrRtJxhHUQR26lhGpDSRYLjYsmAaLPAx9LVx+arFciJxWyANAMy6kZiRbgY0XZdVdlcflAUfNSv8kVr2neX59zjYV2MK/C7S5daNBcanUB9eihRluec6PtLHckpx3FS2vXbkX9BBf+RTk8DsN0UGsjhQMsXE1uthOb3vl04fQBFnYILgGCGE4YEgHItCTeGEXpCOv4dovaL9eziwUhCbD02CSdMVeKTzl2ZLZ6gKl0600ws2ji0muyO4VCCZJb8708duWReS130liAlkwJK/cwN29TA0HWas4TTao1s3Wk6UgEKartEvgk/CIpF6Mll9hYmNDSztXzHkVk2wprSvc3xJKsidvYXYmpE9QT21XyhUz+obArqhLKK8fwTjT1ME/lYE/q1HVIJ6ZW52mRfUX+sklQlI2PWapPVi0cDVYeL43GKSKAwBDk/goxzDW3ogSUmITWevQGXqJ4DETOKHLObG9xnokNSmM0efGpKmSnxyT674K2RKyiBkHxKHc8InD9CtXE7jaymwsFQvyCH0eM2gLWVEOSRsMC0JH082Rg8d4SxJ82ZZe8Yf0rRJn8LyOOamREwQ06AAAAAElFTkSuQmCC"
},
"picture": {
"type": "base64",
"length": 138795,
"procInlineLimitExeceeded": 2000
},
"voice": {
"type": "base64",
"length": 127187,
"procInlineLimitExeceeded": 2000
}
}
]
},
{
"truncated": false,
"rowCount": 5,
"filteredCount": 0,
"columnMeta": [
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "order_number",
"columnLabel": "order_number",
"columnType": "BIGINT"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "amount_total",
"columnLabel": "amount_total",
"columnType": "DECIMAL"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "paid",
"columnLabel": "paid",
"columnType": "TINYINT"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "notes",
"columnLabel": "notes",
"columnType": "VARCHAR"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "customer_name",
"columnLabel": "customer_name",
"columnType": "VARCHAR"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "salesrep_id",
"columnLabel": "salesrep_id",
"columnType": "BIGINT"
}
],
"rows": [
{
"order_number": 7,
"amount_total": 1860,
"paid": false,
"notes": "",
"customer_name": "Echo Environmental Services",
"salesrep_id": 1
},
{
"order_number": 9,
"amount_total": 735,
"paid": false,
"notes": "Deliver to Frank Jones",
"customer_name": "Quebec Geologic Services",
"salesrep_id": 1
},
{
"order_number": 10,
"amount_total": 2024,
"paid": false,
"notes": "",
"customer_name": "Romeo Restaurant Design",
"salesrep_id": 1
},
{
"order_number": 25,
"amount_total": 65,
"paid": false,
"notes": "",
"customer_name": "Lima Citrus Supply",
"salesrep_id": 1
},
{
"order_number": 1038,
"amount_total": 635,
"paid": false,
"notes": "",
"customer_name": "Baja Software Ltd",
"salesrep_id": 1
}
]
}
],
"updateCount": []
},
{
"arg": {
"given_employee_id": 2,
"plus_one": null
},
"result": [
{
"truncated": false,
"rowCount": 0,
"filteredCount": 0,
"columnMeta": [
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee",
"columnName": "employee_id",
"columnLabel": "employee_id",
"columnType": "BIGINT"
},
{
"catalog": "",
"schema": "",
"tableName": "",
"columnName": "plus_one",
"columnLabel": "plus_one",
"columnType": "BIGINT"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee",
"columnName": "login",
"columnLabel": "login",
"columnType": "VARCHAR"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee_picture",
"columnName": "icon",
"columnLabel": "icon",
"columnType": "VARBINARY"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee_picture",
"columnName": "picture",
"columnLabel": "picture",
"columnType": "LONGBLOB"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "employee_picture",
"columnName": "voice",
"columnLabel": "voice",
"columnType": "MEDIUMBLOB"
}
],
"rows": []
},
{
"truncated": false,
"rowCount": 4,
"filteredCount": 0,
"columnMeta": [
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "order_number",
"columnLabel": "order_number",
"columnType": "BIGINT"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "amount_total",
"columnLabel": "amount_total",
"columnType": "DECIMAL"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "paid",
"columnLabel": "paid",
"columnType": "TINYINT"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "notes",
"columnLabel": "notes",
"columnType": "VARCHAR"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "customer_name",
"columnLabel": "customer_name",
"columnType": "VARCHAR"
},
{
"catalog": "dbdev_demo",
"schema": "",
"tableName": "PurchaseOrder",
"columnName": "salesrep_id",
"columnLabel": "salesrep_id",
"columnType": "BIGINT"
}
],
"rows": [
{
"order_number": 1,
"amount_total": 1079,
"paid": false,
"notes": "This is a small order",
"customer_name": "Alpha and Sons",
"salesrep_id": 2
},
{
"order_number": 4,
"amount_total": 720,
"paid": false,
"notes": "Deliver by overnight with signature required",
"customer_name": "Charlie's Construction",
"salesrep_id": 2
},
{
"order_number": 11,
"amount_total": 1279,
"paid": false,
"notes": "",
"customer_name": "Juliet Dating Inc.",
"salesrep_id": 2
},
{
"order_number": 14,
"amount_total": 84,
"paid": false,
"notes": "",
"customer_name": "Charlie's Construction",
"salesrep_id": 2
}
]
}
],
"updateCount": []
}
]
}