DB-2-API: Calling Stored Procedure

Calling Procedures in Oracle Database with Apinizer DB to API Designer

Important

Apinizer does not support OracleTypes.CURSOR. It can only offer simple procedure calls as a service.

1. Creating DB-2-API

A new API is defined with Administration → Development → API Creator → DB-2-API.

The name and description fields of the DB-2-API are filled in and the Save and Next button is clicked.

The picture below shows the DB-2-API settings:

2. Creating Endpoint

By clicking the Add button, the method/endpoint creation panel opens.

2.1 Calling a Procedure That Takes Only IN Parameters

The Oracle database procedure used as a demo is shown in the example below:

create or replace
PROCEDURE sampleProcedureWithInParams (pId IN NUMBER, pName IN VARCHAR2, pDesc IN VARCHAR2, pCost IN NUMBER, pPrice IN NUMBER, pCat IN NUMBER)
IS   
begin
	INSERT INTO "TEST"."PRODUCTS" (PRODUCT_ID, PRODUCT_NAME, DESCRIPTION, STANDARD_COST, LIST_PRICE, CATEGORY_ID) VALUES (pId, pName, pDesc, pCost, pPrice, pCat);
	commit;
end;

POWERSHELL

The picture below shows the endpoint creation settings:

2.2 Calling the Procedure Taking IN and OUT Parameters

A. The Oracle database procedure used as a demo is shown below:

create or replace
PROCEDURE sampleProcedureWithInOutParams (pId IN NUMBER, pName OUT VARCHAR2)
IS   
begin
	select PRODUCT_NAME into pName from PRODUCTS where PRODUCT_ID = pId;
	commit;
end;

POWERSHELL

The picture below shows the endpoint creation settings:

B. The Oracle database procedure used as a demo is shown below:

create or replace
PROCEDURE sampleProcedureWithInOutCursorParams (cat_id IN NUMBER, total_cost OUT NUMBER, cursoroutparam OUT SYS_REFCURSOR)
IS   
begin
	SELECT sum (standard_cost) INTO total_cost FROM PRODUCTS where category_id = cat_id;
	OPEN cursoroutparam FOR SELECT * FROM PRODUCTS where category_id = cat_id ;
end;

POWERSHELL

The picture below shows the endpoint creation settings:

3. Viewing API Definition Documents

The message content can be viewed by clicking the Show Sample Message Body link.

4. Creating API Proxy

API Proxy can be created instantly by clicking the Create API Proxy button on the DB-2-API screen.

When creating the API Proxy, fill in the fields shown in the image below and click the Save button.

4.1 Go Live API Proxy

API Proxy can be quickly installed (deployed) on the defined environment and opened for consumption by clients.

The management of this process can be done through the dialog opened with the Deploy button on the API Proxy screen.

5. Testing

5.1 Testing IN Procedure

The following successful response is returned when the condition defined in the /post endpoint is added to the request and run.

5.2 Testing OUT Cursor Procedure

The following successful response is returned when the condition defined in /get endpoint is added to the request and run.

5.3 Testing OUT Procedure

The following successful response is returned when the condition defined in /get endpoint is added to the request and run.