Mix of Knowledge

PLSQ Gateway an alternative to REST services

Oracle PLSQL Gateway is the best method to use web services in old versions of Oracle Databases (10g).

I found that using oracle PLSQL Gateway , builtin feature is very useful in my case .

PLSQL Gateway depends on DAD (DataBase Access Descriptor), In the following steps we'll configure PLSQL Gateway.

* Run the following commands under SYS privilege, All are CASE sensitive.

Create DAD :

BEGIN
  DBMS_EPG.create_dad (
                         DAD_NAME => 'sit_dad',
                         PATH     => '/sit_dad/*');
END;
/

2. Associate DAD with User who privileges must be used with DAD:

BEGIN
  DBMS_EPG.set_dad_attribute (
    dad_name   => 'sit_dad',
    attr_name  => 'database-username',
    attr_value => 'SCOTT'); -- Is the target USER
END;
/

3.Setting GATEWAY PORT:

1.First Check if port is set before:

SELECT DBMS_XDB.gethttpport FROM dual;

2. If not exists then, set it :

EXEC DBMS_XDB.sethttpport(8080);

4. To gain Anonymous Access:

EXEC DBMS_EPG.SET_DAD_ATTRIBUTE('sit_dad', 'database-username', 'ANONYMOUS');

ALTER USER ANONYMOUS ACCOUNT UNLOCK ;

then , Recreate DAD Again STEP #1

5. Grant execute on DBMS_EPG:

GRANT EXECUTE ON DBMS_EPG TO SCOTT;

* Run the following commands using SCOTT user:

EXEC DBMS_EPG.AUTHORIZE_DAD('sit_dad');

Here's an example For Get Data:

CREATE OR REPLACE PROCEDURE TEST(P_Id IN NUMBER) IS 
CURSOR GET_Dept_EMPS IS
  SELECT Empno, Ename, Job, Sal,Deptno
  FROM   EMP
  WHERE  DeptNo = P_Id;
BEGIN
  --------------
  FOR  i IN GET_Dept_EMPS LOOP
       HTP.P('Emp: '||i.Empno||', Name: '||i.Ename||' Job: '||i.Job||', Sal: '||i.Sal||', Dept: '||i.Deptno);
  END LOOP;
  --------------  
END;

To call it:

http://localhost:8080/sit_dad/test?p_id=10

POST (Insert Data) Example :

CREATE OR REPLACE PROCEDURE ADD_DEPT( P_Depto   IN  NUMBER,
                                      P_DName   IN  VARCHAR,
                                      P_Loc     IN  VARCHAR) IS
BEGIN
  --------------------------
  INSERT INTO DEPT ( DeptNo  , DName   , Loc)
          VALUES  (  P_Depto , P_DName , P_Loc);
  COMMIT;
  --------------------------
  HTP.PRINT('Done, data Inserted');
  --------------------------
  EXCEPTION
    WHEN OTHERS THEN
      HTP.PRINT('ERROR: '||SQLERRM );
END ADD_DEPT;

To call it:

http://localhost:8080/sit_dad/ADD_DEPT?P_Depto=11&P_DName=ITDEV&P_Loc=CAIRO

Update :
Arabic is readable inside Android Studio, but when it comes to Flutter , you must change NLS Language attribute :

BEGIN
  DBMS_EPG.set_dad_attribute (
    dad_name   => 'sit_dad',
    attr_name  => 'nls-language',
    attr_value => 'American_America.UTF8');
END;
/

#8 Export ALL REST Definitions

Exporting REST Definitions as bulk is helpful when you want to backup REST Definitions.

Oracle provides very good , simple and lightweight tool , it's SQLcl (SQL command line) to interact with DataBase.

Download SQLcl from here

Here are the steps to EXPORT REST:

Extract downloaded file to D:\Oracle\sqlcl
Go to : D:\Oracle\sqlcl\bin
Run the sql.exe
Enter user name and password :

Username? (''?) TREST/trest@ORCL
now, you must be connected to Oracle Database

Type in : SPOOL D:\REST_Export.SQL
Type in : REST EXPORT
Type in : SPOOL OFF;

Explain:

Step#5 : locate the file path where to save the output
Step#6 : Identify which Module to export , you can choose one module or all modules
Step#7 : End writing data to output file, and saving data

Full copy of REST definitions are saved in the path : D:\REST_Export.SQL you

#7 Your first DELETE RESTful Service

Here's the case we'll work on using the following example:
The case is : How to Delete specific data from EMP_TABLE using API ?

The Fix: Using DELETE method
Before You Begin: In contrast to GET methods, all of POST,PUT and DELETE methods require third party editor to run .
There are many applications for this purpose, you can install any of them using Google Chrome web store .
YARC may be the simplest one to use , you can download it from here .

Using PLSQL copy the following code:

BEGIN
  -----------------
  ORDS.DEFINE_MODULE(P_Module_Name    => 'emp',
                     P_Base_Path      => 'emp_data',
                     P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_TEMPLATE(P_Module_Name => 'emp', 
                       P_Pattern     => 'dml/:id');
  -----------------
  ORDS.DEFINE_HANDLER(P_Module_Name    => 'emp',
                      P_Pattern        => 'dml/:id',
                      P_Method         => 'DELETE',
                      P_Source_Type    => 'plsql/block',
                      P_Source         => 'BEGIN 
                                           DELETE FROM EMP_TABLE
                                                 WHERE Id   = :id;
                                           COMMIT;
                                           :P_Status := SQLERRM;
                                           END;',
          p_mimes_allowed  => '',
          P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml/:id',
                        P_Method             => 'DELETE',
                        p_name               => 'id',
                        p_bind_variable_name => 'id',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN');   
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml/:id',
                        P_Method             => 'DELETE',
                        p_name               => 'P_Status',
                        p_bind_variable_name => 'P_Status',
                        p_source_type        => 'RESPONSE',
                        p_param_type         => 'STRING',
                        p_access_method      => 'OUT');
  -----------------
  COMMIT;
  -----------------
END;

In This POST Request:

Employee Id = 1 was DELETED from EMP_TABLE
P_Status Parameter defined to return message after inserting data

To run POST request :

Click YARC extension from google chrome
In the URL add: http://localhost:8080/ords/trest/emp_data/dml/1
Choose from the list : DELETE

Once you have finished, click Send Request.

you should get reply of 200 and the following message:


 {
  "P_Status": "ORA-0000: normal, successful completion"
}

In the next post, we'll export all REST Defined Services