Oracle tips and tricks

Generate JSON from SQL and PLSQL

There are many ways to generate JSON files from Oracle Database (11g, 12c, ...), but when it comes to Oracle DataBase 10g it's a bit of difficult.

PLSQL-utils is a method can convert SQL and REF_CURSOR to JSON.
You can download it from here: https://code.google.com/archive/p/plsql-utils/downloads

To install only the required Package:

create or replace type t_str_array as table of varchar2(4000);
/
create or replace package json_util_pkg
as

  /*

  Purpose:    JSON utilities for PL/SQL

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created

  */

  -- generate JSON from REF Cursor
  function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
                               p_max_rows in number := null,
                               p_skip_rows in number := null) return clob;

  -- generate JSON from SQL statement
  function sql_to_json (p_sql in varchar2,
                        p_param_names in t_str_array := null,
                        p_param_values in t_str_array := null,
                        p_max_rows in number := null,
                        p_skip_rows in number := null) return clob;


end json_util_pkg;
/

create or replace package body json_util_pkg
as

  /*

  Purpose:    JSON utilities for PL/SQL

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created

  */


  g_json_null_object             constant varchar2(20) := '{ }';


function get_xml_to_json_stylesheet return varchar2
as
begin

  /*

  Purpose:    return XSLT stylesheet for XML to JSON transformation

  Remarks:    see http://code.google.com/p/xml2json-xslt/

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  MBR     30.01.2010  Added fix for nulls

  */


  return '<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<!--
  Copyright (c) 2006, Doeke Zanstra
  All rights reserved.

  Redistribution and use in source and binary forms, with or without modification,
  are permitted provided that the following conditions are met:

  Redistributions of source code must retain the above copyright notice, this
  list of conditions and the following disclaimer. Redistributions in binary
  form must reproduce the above copyright notice, this list of conditions and the
  following disclaimer in the documentation and/or other materials provided with
  the distribution.

  Neither the name of the dzLib nor the names of its contributors may be used to
  endorse or promote products derived from this software without specific prior
  written permission.

  THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND
  ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
  WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.
  IN NO EVENT SHALL THE COPYRIGHT OWNER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
  INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,
  BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE,
  DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF
  LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR
  OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF
  THE POSSIBILITY OF SUCH DAMAGE.
-->

  <xsl:output indent="no" omit-xml-declaration="yes" method="text" encoding="UTF-8" media-type="text/x-json"/>
  <xsl:strip-space elements="*"/>
  <!--contant-->
  <xsl:variable name="d">0123456789</xsl:variable>

  <!-- ignore document text -->
  <xsl:template match="text()[preceding-sibling::node() or following-sibling::node()]"/>

  <!-- string -->
  <xsl:template match="text()">
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="."/>
    </xsl:call-template>
  </xsl:template>

  <!-- Main template for escaping strings; used by above template and for object-properties
       Responsibilities: placed quotes around string, and chain up to next filter, escape-bs-string -->
  <xsl:template name="escape-string">
    <xsl:param name="s"/>
    <xsl:text>"</xsl:text>
    <xsl:call-template name="escape-bs-string">
      <xsl:with-param name="s" select="$s"/>
    </xsl:call-template>
    <xsl:text>"</xsl:text>
  </xsl:template>

  <!-- Escape the backslash (\) before everything else. -->
  <xsl:template name="escape-bs-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <xsl:when test="contains($s,''\'')">
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''\''),''\\'')"/>
        </xsl:call-template>
        <xsl:call-template name="escape-bs-string">
          <xsl:with-param name="s" select="substring-after($s,''\'')"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="$s"/>
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!-- Escape the double quote ("). -->
  <xsl:template name="escape-quot-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <xsl:when test="contains($s,''&quot;'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''&quot;''),''\&quot;'')"/>
        </xsl:call-template>
        <xsl:call-template name="escape-quot-string">
          <xsl:with-param name="s" select="substring-after($s,''&quot;'')"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="$s"/>
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!-- Replace tab, line feed and/or carriage return by its matching escape code. Can''t escape backslash
       or double quote here, because they don''t replace characters (&#x0; becomes \t), but they prefix
       characters (\ becomes \\). Besides, backslash should be seperate anyway, because it should be
       processed first. This function can''t do that. -->
  <xsl:template name="encode-string">
    <xsl:param name="s"/>
    <xsl:choose>
      <!-- tab -->
      <xsl:when test="contains($s,''&#x9;'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''&#x9;''),''\t'',substring-after($s,''&#x9;''))"/>
        </xsl:call-template>
      </xsl:when>
      <!-- line feed -->
      <xsl:when test="contains($s,''&#xA;'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''&#xA;''),''\n'',substring-after($s,''&#xA;''))"/>
        </xsl:call-template>
      </xsl:when>
      <!-- carriage return -->
      <xsl:when test="contains($s,''&#xD;'')">
        <xsl:call-template name="encode-string">
          <xsl:with-param name="s" select="concat(substring-before($s,''&#xD;''),''\r'',substring-after($s,''&#xD;''))"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise><xsl:value-of select="$s"/></xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!-- number (no support for javascript mantise) -->
  <xsl:template match="text()[not(string(number())=''NaN'')]">
    <xsl:value-of select="."/>
  </xsl:template>

  <!-- boolean, case-insensitive -->
  <xsl:template match="text()[translate(.,''TRUE'',''true'')=''true'']">true</xsl:template>
  <xsl:template match="text()[translate(.,''FALSE'',''false'')=''false'']">false</xsl:template>

  <!-- item:null -->
  <xsl:template match="*[count(child::node())=0]">
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="local-name()"/>
    </xsl:call-template>
    <xsl:text>:null</xsl:text>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">}</xsl:if> <!-- MBR 30.01.2010: added this line as it appeared to be missing from stylesheet -->
  </xsl:template>

  <!-- object -->
  <xsl:template match="*" name="base">
    <xsl:if test="not(preceding-sibling::*)">{</xsl:if>
    <xsl:call-template name="escape-string">
      <xsl:with-param name="s" select="name()"/>
    </xsl:call-template>
    <xsl:text>:</xsl:text>
    <xsl:apply-templates select="child::node()"/>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">}</xsl:if>
  </xsl:template>

  <!-- array -->
  <xsl:template match="*[count(../*[name(../*)=name(.)])=count(../*) and count(../*)&gt;1]">
    <xsl:if test="not(preceding-sibling::*)">[</xsl:if>
    <xsl:choose>
      <xsl:when test="not(child::node())">
        <xsl:text>null</xsl:text>
      </xsl:when>
      <xsl:otherwise>
        <xsl:apply-templates select="child::node()"/>
      </xsl:otherwise>
    </xsl:choose>
    <xsl:if test="following-sibling::*">,</xsl:if>
    <xsl:if test="not(following-sibling::*)">]</xsl:if>
  </xsl:template>

  <!-- convert root element to an anonymous container -->
  <xsl:template match="/">
    <xsl:apply-templates select="node()"/>
  </xsl:template>

</xsl:stylesheet>';

end get_xml_to_json_stylesheet;


function ref_cursor_to_json (p_ref_cursor in sys_refcursor,
                             p_max_rows in number := null,
                             p_skip_rows in number := null) return clob
as
  l_ctx         dbms_xmlgen.ctxhandle;
  l_num_rows    pls_integer;
  l_xml         xmltype;
  l_json        xmltype;
  l_returnvalue clob;
begin


  /*

  Purpose:    generate JSON from REF Cursor

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created

  */

  l_ctx := dbms_xmlgen.newcontext (p_ref_cursor);

  dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);

  -- for pagination

  if p_max_rows is not null then
    dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
  end if;

  if p_skip_rows is not null then
    dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
  end if;

  -- get the XML content
  l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);

  l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);

  dbms_xmlgen.closecontext (l_ctx);

  close p_ref_cursor;

  if l_num_rows > 0 then
    -- perform the XSL transformation
    l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
    l_returnvalue := l_json.getclobval();
  else
    l_returnvalue := g_json_null_object;
  end if;

  l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);

  return l_returnvalue;

end ref_cursor_to_json;


function sql_to_json (p_sql in varchar2,
                      p_param_names in t_str_array := null,
                      p_param_values in t_str_array := null,
                      p_max_rows in number := null,
                      p_skip_rows in number := null) return clob
as
  l_ctx         dbms_xmlgen.ctxhandle;
  l_num_rows    pls_integer;
  l_xml         xmltype;
  l_json        xmltype;
  l_returnvalue clob;
begin

  /*

  Purpose:    generate JSON from SQL statement

  Remarks:

  Who     Date        Description
  ------  ----------  -------------------------------------
  MBR     30.01.2010  Created
  MBR     28.07.2010  Handle null value in bind variable value (issue and solution reported by Matt Nolan)

  */


  l_ctx := dbms_xmlgen.newcontext (p_sql);

  dbms_xmlgen.setnullhandling (l_ctx, dbms_xmlgen.empty_tag);

  -- bind variables, if any
  if p_param_names is not null then

    for i in 1..p_param_names.count loop
      dbms_xmlgen.setbindvalue (l_ctx, p_param_names(i), nvl(p_param_values(i), ''));
    end loop;

  end if;

  -- for pagination

  if p_max_rows is not null then
    dbms_xmlgen.setmaxrows (l_ctx, p_max_rows);
  end if;

  if p_skip_rows is not null then
    dbms_xmlgen.setskiprows (l_ctx, p_skip_rows);
  end if;

  -- get the XML content
  l_xml := dbms_xmlgen.getxmltype (l_ctx, dbms_xmlgen.none);

  l_num_rows := dbms_xmlgen.getnumrowsprocessed (l_ctx);

  dbms_xmlgen.closecontext (l_ctx);

  -- perform the XSL transformation
  if l_num_rows > 0 then
    l_json := l_xml.transform (xmltype(get_xml_to_json_stylesheet));
    l_returnvalue := l_json.getclobval();
    l_returnvalue := dbms_xmlgen.convert (l_returnvalue, dbms_xmlgen.entity_decode);
  else
    l_returnvalue := g_json_null_object;
  end if;

  return l_returnvalue;

end sql_to_json;


end json_util_pkg;
/

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

#6 Your first PUT RESTful Service

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

The Fix: Using PUT 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         => 'PUT',
                      P_Source_Type    => 'plsql/block',
                      P_Source         => 'BEGIN 
                                           UPDATE EMP_TABLE
                                                  SET  Name = :Name
                                                 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             => 'PUT',
                        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             => 'PUT',
                        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 UPDATED 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 : PUT
In Payload,add the new data: {"Name":"Ismael"}

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 create DELETErequest.

#5 Your first POST RESTful Service

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

The Fix: Using GET 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');
  -----------------
  ORDS.DEFINE_HANDLER(P_Module_Name    => 'emp',
                      P_Pattern        => 'dml',
                      P_Method         => 'POST',
                      P_Source_Type    => 'plsql/block',
                      P_Source         => 'BEGIN 
                                           INSERT INTO EMP_TABLE(Id, Name) 
                                                         VALUES (:Id, :Name); 
                                           COMMIT;
                                           -----------
                                           :P_Status := SQLERRM;
                                           -----------
                                           END;',
          p_mimes_allowed  => '',
          P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml',
                        P_Method             => 'POST',
                        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:

Data INSERTED into 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
Choose from the list : POST
In Payload,add the new data: {"Id":1, "Name":"Omar"}

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 create PUT request.

#4 Your first GET RESTful Service

Here's the case we'll work on using the following example:
The case is : How to Fetch specific data from EMP_TABLE using API ?
The Fix: Using GET method

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/:P_Emp_Id');
  -----------------
  ORDS.DEFINE_HANDLER(P_Module_Name    => 'emp',
                      P_Pattern        => 'dml/:P_Emp_Id',
        P_Method         => 'GET',
        P_Source_Type    => Ords.Source_Type_Collection_Feed,
        P_Source         => 'SELECT * FROM EMP_TABLE WHERE Id = :P_Emp_Id',
        P_Items_Per_Page => 0);
  -----------------
  ORDS.DEFINE_PARAMETER(P_Module_Name        => 'emp',
                        P_Pattern            => 'dml/:P_Emp_Id',
                        P_Method             => 'GET',
                        p_name               => 'P_Emp_Id',
                        p_bind_variable_name => 'P_Emp_Id',
                        p_source_type        => 'HEADER',
                        p_param_type         => 'INT',
                        p_access_method      => 'IN');   
  COMMIT;
  -----------------
END;
/

You now have created your first REST API , the URL IS: http://localhost:8080/ords/trest/emp_data/dml/1

To get Employee Number 1 , add 1 at the end of URI

Oracle tips and tricks

Generate JSON from SQL and PLSQL

PLSQ Gateway an alternative to REST services

#8 Export ALL REST Definitions

#7 Your first DELETE RESTful Service

#6 Your first PUT RESTful Service

#5 Your first POST RESTful Service

#4 Your first GET RESTful Service

Remove unused Layouts in Oracle Apex

Report Abuse

Labels