4 Replies Latest reply on Jul 25, 2016 12:06 PM by itshak

    My first RESTful service on SQL Developer




      I'm new about using ORDS and I try to create any RESTful service but without success.

      (Our target is to check the possibility of change all our SOAP web services by RESTful services.)


      I make the next statements


      1) Install SQL Developer Build MAIN-20.78  on Win7 64bits

      2) Tools > REST Data Services > Install

         a) (Step1)    Use REST .. included with SQL Developer

                            Select some location for the REST config file

          b) (step2)    Fill DB connection data

                            Entry password for ORDS_PUBLIC_USER

                            NOT check Skip verify ...

                            Entry data for SYS user

          c) (step3)    Next

          d) (step4)    Check Run in standalone ...

                            Http port 8080

          e) (step5)    Display Summary and finish


          according to the logs, I think that the installation was ok


          2016-07-22 12:13:06.575:INFO:/ords:main: INFO: Oracle REST Data Services initialized|Oracle REST Data Services version :|Oracle REST Data Services server info: jetty/9.2.z-SNAPSHOT|

          2016-07-22 12:13:06.576:INFO:oejsh.ContextHandler:main: Started o.e.j.s.ServletContextHandler@33b37288{/ords,null,AVAILABLE}

          2016-07-22 12:13:06.601:INFO:oejs.ServerConnector:main: Started ServerConnector@70a9f84e{HTTP/1.1}{}

          2016-07-22 12:13:06.601:INFO:oejs.Server:main: Started @18517ms


      3) View > REST Data Services > Development

      4) Right click > New RESTful services

      5) Right click > New module

          a) (step1)    Module name --> MaholServices, URI Prefix --> MaharWSREST/, Publish ... --> Check

          b) (step2)    URI Pattern --> Patients/

          c) (step3)    Source Type --> Query One Row

          d) (step4)    Display Summary and finish

      6) Double click on MaholServices > Patients/ > GET

      7) At the SQL Worksheet -->  select lib_patient.wsget( '1', null) from dual   --> Run  --> Entry the connection to run(test) the script


      What it is the next step? In which  way I "save" the service to check it?

      At this moment we want only check the RESTfull services in the browser without any deploy to weblogic for example.


      I must to sat that I tried these steps a lot of times (with previous uninstall ).

      On some of my tries, I created an admin user  "adminlistener"   ( java -jar ords.war user adminlistener "Listener Administrator")

      Also I performed "Enable REST services" for the DB user in 7) ( I don't sure that it's a must)


      Thanks very much on advance

        • 1. Re: My first RESTful service on SQL Developer



          To develop RESTful Services in SQLDev, you have to create an ORDS user using its user command, for example:

          java -jar ords.war user ords_sqldev "SQL Developer"


          The role Listener Administrator is used for Administration not for REST Development.


          Once you have the user, in SQLDev:

          1. Right click on a database connection, for example scott, enable REST on it

          2. Click View -> REST Data Services -> Development

          3. Click the Connect icon, enter the following fields:

          Username: ords_sqldev

          Hostname: your_host (localhost for example)

          Port: 8080

          Server Path: /ords

          Schema/Workspace: scott

          4. Do what you did to define a service above

          5. Click the Validate RESTful Services icon to validate the service

          6. Right click on the module you defined and upload it

          7. Access the endpoint from a web browser


          Hope this helps.




          1 person found this helpful
          • 2. Re: My first RESTful service on SQL Developer

            Hi Xilan,


            Your answer help me very much ! I success to crate and test the RESTful service.


            There is any limitation about select statement that return an user defined object type?

            My statement:

               select lib_patient.wsget( '18', null) from dual


            return one record based on some DB user defined object.

            At the browser, I see Capture.JPG





            • 3. Re: My first RESTful service on SQL Developer

              Hi Isaac,

              Currently, seems you cannot use a complex data type.



              • 4. Re: My first RESTful service on SQL Developer

                Hi Xilan,


                Thanks very much again.


                We found an workaround:


                select     *

                from        table( cast( tya_patient( lib_patient.wsget( '18', null) ) as tya_patient ) )




                Even, Hebrew characters are displayed selecting "Unicode" at Browser Text Encoding.



                Do you have an RESTful example, If I want to run a pl/sql block that return some value?