5 Replies Latest reply on Feb 18, 2016 4:41 AM by Anthony Sanchez

    Looking for example of GET handler with source type of PL/SQL

    Anthony Sanchez

      Hello,

      I currently have a working GET handler with a source type of QUERY running in ords 3.0.  The call returns many records (hundreds and sometimes 10's of thousands) in JSON and allows the user to page through the results.

       

      The call accepts several parameters that I simply pass into the where clause of the query behind the handler.  Data is retrieved by date range(s).

       

      To prevent runaway queries with long date ranges, I've implemented some logic in the sql statement to limit the requester.  The requester has asked for an error to be thrown instead.  There are other limits I impose on other inputs that I need to provide useful errors for as well.

       

      I'd like to modify the GET call to use PL/SQL type instead of QUERY type so that I can do validations on the inputs and pass back useful error messages if required, however I cannot seem to find any examples online.

       

      Any examples (or alternatives) would be greatly appreciated.

       

      thank you

       

      Anthony

        • 1. Re: Looking for example of GET handler with source type of PL/SQL
          Kiran Pawar

          Hi user12089208,

          user12089208 wrote:

               Please change your user handle to recognizable user name. Refer : Video tutorial how to change nickname available

          I currently have a working GET handler with a source type of QUERY running in ords 3.0.  The call returns many records (hundreds and sometimes 10's of thousands) in JSON and allows the user to page through the results.

          The call accepts several parameters that I simply pass into the where clause of the query behind the handler.  Data is retrieved by date range(s).

          To prevent runaway queries with long date ranges, I've implemented some logic in the sql statement to limit the requester.  The requester has asked for an error to be thrown instead.  There are other limits I impose on other inputs that I need to provide useful errors for as well.

          I'd like to modify the GET call to use PL/SQL type instead of QUERY type so that I can do validations on the inputs and pass back useful error messages if required, however I cannot seem to find any examples online.

          Any examples (or alternatives) would be greatly appreciated.

               Refer the following blog articles:

           

          Regards,

          Kiran

          • 2. Re: Looking for example of GET handler with source type of PL/SQL
            D.C

            Hi,

             

            You can use Pipelined Functions to generate your plsql recordset, and then just use a select statement frmo the pipelined function in the service definition.

            1 person found this helpful
            • 3. Re: Looking for example of GET handler with source type of PL/SQL
              Anthony Sanchez

              Hi Kiran,

              thank you for taking the time to reply and for the tip on changing my handle.  Sorry, first time poster.

               

              On your first link, it would be helpful if they provided the ddl for the put_dept_apex_json procedure as that's what I'm looking for.  Without that it unfortunately doesnt do much for me.

               

              The second link is a bit closer as they use htp.p to output content however I need to output many rows and columns and be output as JSON.  While it looks like it can be done it seems that it will take quite a bit of development.

               

              The third link is essentially the same as the second except they select a value from dual instead of using htp.p.

               

              While the second and third both get me closer I'm looking for an example that is a bit closer to what i'm after - multiple rows and columns returned as json.

               

              Thanks again for your reply - much appreciated.

              Anthony

              • 4. Re: Looking for example of GET handler with source type of PL/SQL
                Anthony Sanchez

                D.C - I don't know why I didn't think about this approach before.  great idea - I'll give it a shot and get back to this thread with the outcome.

                 

                thanks

                 

                Anthony

                • 5. Re: Looking for example of GET handler with source type of PL/SQL
                  Anthony Sanchez

                  Just wanted to followup with my findings after implementing DC's suggestion.  Here's a sample I put together using a pipelined function behind a rest data services GET handler so I could do validation, return useful errors, and yet still return the JSON required.  The idea is that the function will take an OWNER and return tables owned by that OWNER, however it should throw an error if the owner requested is SYS.

                   

                  --create record type

                   

                  CREATE TYPE t_all_tables_row AS OBJECT (

                    owner           varchar2(30),

                    table_name  VARCHAR2(30)

                  );

                  /

                   

                  --create table_type of record type

                  CREATE TYPE t_all_tables_tab IS TABLE OF t_all_tables_row;

                  /

                   

                  --create pipelined function

                   

                  CREATE OR REPLACE FUNCTION get_tables_pf(Powner IN varchar2) RETURN t_all_tables_tab PIPELINED AS

                   

                  excp_bad_user exception;

                   

                  BEGIN

                   

                  --dont allow users to query SYS owned objects

                  if powner='SYS' THEN

                  raise excp_bad_user;

                  end if;

                   

                    FOR i IN (select owner,table_name from all_tables where owner=Powner) LOOP

                      PIPE ROW(t_all_tables_row(i.owner,i.table_name));  

                    END LOOP;

                     

                    EXCEPTION

                     WHEN excp_bad_user THEN

                        raise_application_error (-20001,'You cannot use this for SYS owned objects.');

                   

                     WHEN OTHERS THEN

                        raise_application_error (-20002,'An error has occurred inserting an order.');

                       

                  RETURN;

                  END;

                  /

                   

                  Here's my GET HANDLER using the appropriate SQL that uses the pipelined function.  The user is allowed to input an OWNER and will get back tables owned by it.

                   

                   

                   

                  2-17-2016 9-33-02 PM.png

                   

                   

                  Here's the response from the rest endpoint when I pass in the OWNER "AMFC".  It looks as expected.

                   

                  2-17-2016 9-36-28 PM.png

                   

                  Here's what happens when I try to pass in the owner "SYS".  I get the user defined error as expected.

                   

                  2-17-2016 9-39-12 PM.png

                   

                  Thanks again D.C!