5 Replies Latest reply on May 7, 2018 6:28 PM by Pierre Yotti

    Define Service Failing with No Data found

    NavneetU

      I am trying to create a service using ORDS and is failing with following error.

       

        1  BEGIN

        2    ORDS.define_service(

        3      p_module_name    => 'nupneja1',

        4      p_base_path      => 'nupneja1/',

        5      p_pattern        => 'emp/',

        6      p_method         => 'GET',

        7      p_source_type    => ORDS.source_type_collection_feed,

        8      p_source         => 'select * from sysadm.psoprdefn',

        9      p_items_per_page => 0);

      10    COMMIT;

      11* END;

      09:44:31  12

      09:44:32  13  /

      BEGIN

      *

      ERROR at line 1:

      ORA-01403: no data found

      ORA-06512: at "ORDS_METADATA.ORDS_INTERNAL", line 617

      ORA-06512: at "ORDS_METADATA.ORDS_SECURITY", line 63

      ORA-06512: at "ORDS_METADATA.ORDS_SERVICES", line 117

      ORA-06512: at "ORDS_METADATA.ORDS_SERVICES", line 52

      ORA-06512: at "ORDS_METADATA.ORDS", line 691

      ORA-06512: at line 2

        • 1. Re: Define Service Failing with No Data found
          thatJeffSmith-Oracle

          P_SOURCE_TYPE has to be a string, change yours to 'source_type_collection_feed'

          forums.png

           

          From the package spec...

           

          * @param p_source_type        The HTTP request method for this handler:

             *                             Valid values:

             *                               source_type_collection_feed :-

             *                                 Executes a SQL Query and transforms the result set into an ORDS Standard JSON representation.

             *                                 Available when the HTTP method is GET.

             *                                 Result Format: JSON

             *                               source_type_collection_item

             *                                 Executes a SQL Query returning one row of data into a ORDS Standard JSON representation.

             *                                 Available when the HTTP method is GET.

             *                                 Result Format: JSON

             *                               source_type_media :-

             *                                 Executes a SQL Query conforming to a specific format and turns the result set into a binary representation

             *                                 with an accompanying HTTP Content-Type header identifying the Internet media type of the representation.

             *                                 Result Format: Binary

             *                               source_type_plsql :-

             *                                 Executes an anonymous PL/SQL block and transforms any OUT or IN/OUT parameters into a JSON representation.

             *                                 Available only when the HTTP method is DELETE, PUT, or POST.

             *                                 Result Format: JSON

             *                               source_type_query || source_type_csv_query :-

             *                                 Executes a SQL Query and transforms the result set into either an ORDS legacy JavaScript Object Notation (JSON) or CSV

             *                                 representation, depending on the format selected. Available when the HTTP method is GET.

             *                                 Result Format: JSON or CSV

             *                               source_type_query_one_row :-

             *                                 Executes a SQL Query returning one row of data into an ORDS legacy JSON representation.

             *                                 Available when the HTTP method is GET.

             *                                 Result Format: JSON

             *                               source_type_feed :-

             *                                 Executes a SQL query and transforms the results into a JSON Feed representation.

             *                                 Each item in the feed contains a summary of a resource and a hyperlink to a full representation of the resource.

             *                                 The first column in each row in the result set must be a unique identifier for the row and is used to form a

             *                                 hyperlink of the form: path/to/feed/{id}, with the value of the first column being used as the value for {id}.

             *                                 The other columns in the row are assumed to summarize the resource and are included in the feed.

             *                                 A separate resource template for the full representation of the resource should also be defined.

             *                                 Result Format: JSON

             *

          • 2. Re: Define Service Failing with No Data found
            NavneetU

            Hi Jeff

             

            Thanks for replying to the thread.

             

            I tried with string but getting same error

             

            07:27:05 NUPNEJA/fstst/FSTST2>BEGIN

            07:29:05   2    ORDS.define_service(

            07:29:05   3      p_module_name    => 'nupneja1',

            07:29:05   4      p_base_path      => 'nupneja1/',

            07:29:05   5      p_pattern        => 'emp/',

            07:29:05   6      p_method         => 'GET',

            07:29:05   7      p_source_type    => 'source_type_collection_feed',

            07:29:05   8      p_source         => 'SELECT * FROM emp',

            07:29:05   9      p_items_per_page => 0);

            07:29:05  10

            07:29:05  11    COMMIT;

            07:29:05  12  END;

            07:29:05  13  /

            BEGIN

            *

            ERROR at line 1:

            ORA-01403: no data found

            ORA-06512: at "ORDS_METADATA.ORDS_INTERNAL", line 617

            ORA-06512: at "ORDS_METADATA.ORDS_SECURITY", line 63

            ORA-06512: at "ORDS_METADATA.ORDS_SERVICES", line 117

            ORA-06512: at "ORDS_METADATA.ORDS_SERVICES", line 52

            ORA-06512: at "ORDS_METADATA.ORDS", line 691

            ORA-06512: at line 2

            • 3. Re: Define Service Failing with No Data found
              thatJeffSmith-Oracle

              Do you have other existing ORDS services, or is this your first one?

               

              Have you REST enabled your schema?

               

              Have you tried building this with SQL Developer and our GUI for ORDS REST Services?

              • 4. Re: Define Service Failing with No Data found
                NavneetU

                thatJeffSmith-Oracle wrote:

                 

                Do you have other existing ORDS services, or is this your first one?

                 

                Have you REST enabled your schema?

                 

                Have you tried building this with SQL Developer and our GUI for ORDS REST Services?

                1. This is first service I am trying to create for this database. This ORDS is installed on another database but I added 2nd database on existing ORDS installation.

                2. Yes I enabled the schema

                07:26:11 _user"/"_connect_identifier"/"instance111>BEGIN

                07:26:12   2    ORDS.enable_schema(

                07:26:12   3      p_enabled             => TRUE,

                07:26:12   4      p_schema              => 'nupneja',

                07:26:12   5      p_url_mapping_type    => 'BASE_PATH',

                07:26:12   6      p_url_mapping_pattern => 'nupneja',

                07:26:12   7      p_auto_rest_auth      => FALSE

                07:26:12   8    );

                07:26:12   9

                07:26:12  10    COMMIT;

                07:26:12  11  END;

                07:26:12  12  /

                3. For this database, I have not tried with SQL Developer or GUI yet

                • 5. Re: Define Service Failing with No Data found
                  Pierre Yotti

                  You have that issue, because you have not rest enabled your shema