3 Replies Latest reply on Jan 28, 2020 10:08 AM by steph0h

    ords.define_sercice with source_type_csv_query gives me PLS-00201

    steph0h

      Hello,

       

      Trying to define my first ords-service. Requirement is to get the data in CSV format. It should work like this, I suppose:

       

      BEGIN

        ORDS.define_service(

          p_module_name    => 'module_1',

          p_base_path      => 'module_1/',

          p_pattern        => 'as_syslogs/',

          p_method         => 'GET',

          p_source_type    => ords.source_type_csv_query,

          p_source         => 'select * from AS_SYSLOGS where rownum<100',

          p_items_per_page => 0);

        COMMIT;

      END;

      /

       

      But unfortunately it gives me following error:

       

      ORA-06550: Zeile 7, Spalte 25:

      PLS-00201: identifier 'ORDS.SOURCE_TYPE_CSV_QUERY' must be declared

      ORA-06550: Zeile 2, Spalte 3:

       

       

      Database is 11.2.0.4.0

       

      Thanks for any hints :-)

        • 1. Re: ords.define_sercice with source_type_csv_query gives me PLS-00201
          thatJeffSmith-Oracle

          you do that on the HANDLER, not the MODULE

           

          So like this -

           

          BEGIN

            ORDS.DEFINE_HANDLER(

                p_module_name    => 'csv',

                p_pattern        => 'emps/',

                p_method         => 'GET',

                p_source_type    => 'csv/query',

                p_items_per_page =>  25,

                p_mimes_allowed  => '',

                p_comments       => NULL,

                p_source         =>

          'select * from employees'

                );

           

           

            COMMIT;

          END;

           

          If you use the UI in SQL Developer or APEX, it constructs the ORDS API calls for you.

          • 2. Re: ords.define_sercice with source_type_csv_query gives me PLS-00201
            EJ-Egyed

            What version of ORDS are you using?  This code works for me in ORDS 19.2:

             

            BEGIN
                ords.enable_schema (p_url_mapping_pattern => 'test');
            
            
                ords.define_service (p_module_name   => 'v1',
                                     p_base_path     => 'test_path/',
                                     p_pattern       => 'test_pattern',
                                     p_method        => 'GET',
                                     p_source_type   => ords.source_type_csv_query,
                                     p_source        => 'select level, level + 1 from dual connect by rownum <= 10');
            
            
                COMMIT;
            END;
            

             

            Then sending a request through cURL works fine:

             

            C:>curl http://host:port/ords/test/test_path/test_pattern

            1,2

            2,3

            3,4

            4,5

            5,6

            6,7

            7,8

            8,9

            9,10

            10,11

            • 3. Re: ords.define_sercice with source_type_csv_query gives me PLS-00201
              steph0h

              Thanks, I managed to get it to work with this:

               

              BEGIN

                ORDS.define_service(

                  p_module_name    => 'module_1',

                  p_base_path      => 'module_1/',

                  p_pattern        => 'as_syslogs/',

                  p_method         => 'GET',

                  p_source_type    => 'csv/query',

                  p_source         => 'select * from AS_SYSLOGS where rownum<100',

                  p_items_per_page => 0);

                COMMIT;

              END;

              /

               

              kind regards,

              Stephan