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.

1. 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;
/