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
- With your API open, in the Create section, clickSchema.The tables are listed by default.
- ClickProcedures.The stored procedures display.For example, in the Demo API, theget_employeestored procedure includes the following arguments:given_employee_idplus_oneFor reference, the following code snippet defines theget_employeestored 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 asREF CURSOR OUTparameters. A reference to this value is place in theargsection. The actual result data is placed in the "result" array. You can add multipleREF CURSOR OUTparameters 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
, andoutput
args.input-output
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": []}]}