Use Stored Procedure Resources

You can leverage the business logic already developed in your database using stored procedure resourcesIf 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.

View Stored Procedures

You can view the stored procedures on the Integrate, Schema, Procedures tabThe following image shows this tab:

For reference, the get_employee procedure is coded as follows:

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 a Stored Procedure

Invoke a stored procedure using the GET method, as follows:

Note: Prefix argument definitions with arg.

http://serer.acme.com/rest/abl/demo/v1/get_employee?arg.given_employee_id=1&arg.plus_one=1

The following image shows the URL and GET response on the Execute, REST Lab, Request tab:

For reference, the get_employee procedure was defined. The argument definitions are referenced with the prefix arg.

Notes:

  • You can use pirate syntax (for example, arghhh.given_employee_id).
  • 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 procedure.

Args supported: input, output, input-output

The following example updates its first parameter (plus_one), and that it is returned in the JSON result (the arg{} object).

Security

You can control which users can access your procedure-based endpoints.

Default Security

To simplify administration, you can assign default access for your procedures for a given role, like this:

Specific Security

You can control access on a procedure-by-procedure basis. Control access on the Configure, Roles, REST End Points, Procedures tab. The following image shows this tab:

Oracle-specific Extensions

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 procedure. API Creator performs invocations in a single transaction and returns results in a procresults array in the resulting JSON.

For example a POST of:

[ { "given_employee_id": 1, "plus_one": 5 }, { "given_employee_id" : 2} ]

to the demo:get_employee procedure results in:

{
  "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": []
    }
  ]
}