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

    My first RESTful service on SQL Developer

    itshak

      Hi,

       

      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 4.1.3.20 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 : 3.0.3.351.13.24|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}{0.0.0.0:8080}

          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
          xilan

          Hi,

           

          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.

           

          Thanks,

          Xiaohe

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

            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

             

            Thanks

             

            Isaac

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

              Hi Isaac,

              Currently, seems you cannot use a complex data type.

              Thanks,

              Xiaohe

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

                Hi Xilan,

                 

                Thanks very much again.

                 

                We found an workaround:

                 

                select     *

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

                 

                Capture.JPG

                 

                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?

                 

                Isaac