6 Replies Latest reply on May 18, 2016 9:24 AM by david.pye

    Stored Procedure with ORDS

    suds123

      Hi,

      I'm trying to call stored procedures using ORDS. I successfully managed to call a stored procedure with no parameters or return.

      But I couldn't find any where in ORDS documentation how to pass parameter to stored procedures and return results in JSON format.

      If any one has used stored procs with ORDS with parameters and resturning results, please provide some pointers.

       

      Thanks

        • 1. Re: Stored Procedure with ORDS
          Mike Kutz

          I don't know if ORDS has been enhanced so that you can easily map functions/procedures.

           

          pre12c - I find making XML easier then JSON.

          But, the concept is the same.

           

          Let's say you have an endpoint that looks like this:

          /ords/myworkspace/this_procedure/{p1_int}/{p2_date}

           

          The PL/SQL you'd use something like:

           

          declare
            p_1 int;
            p_2 date;
            l_answer varchar2(30);
            l_json clob;
          begin
           -- always explicitly convert strings
            p_1 := to_number( :P1_int, '999999999' );
            p_2 := to_date( :P2_date, 'dd-mon-yyyy' );
          
            l_answer := some_function( p_1, p_2 );
          
            -- build JSON here
            l_json := '{ answer="' || l_answer || '", the_number="' || p_1 || '", the_date="' || to_char(p_2,'dd-mon-yyyy') || '" }';
          
            -- create HTTP Response here
            -- ( I have to look it up, but it follows a similar format for rendering PDF page in APEX )
           -- create HEADER  - ensure you set MIME-Type !!!
           -- send l_json eg   htp.p( l_json );  
           -- close http.
          
          end;
          

           

          MK

          • 2. Re: Stored Procedure with ORDS
            david.pye

            Hi,

             

            I hope this helps with what you're trying.

             

            Example GET and POST definitions of the sort we are using would be:

             

                ords.define_module(

                    p_module_name    => 'my_module',

                    p_base_path      => '/my_ords_test/',

                    p_items_per_page => 25,

                    p_status         => 'PUBLISHED',

                    p_comments       => 'Module for handling customers');

             

              ords.define_template(

                    p_module_name => 'my_module',

                    p_pattern     => 'customer/',

                    p_priority    => 0,

                    p_etag_type   => 'HASH',

                    p_comments    => 'Template for handling customers');

             

                ords.define_handler(

                    p_module_name    => 'my_module',

                    p_pattern     => 'customer/:customer_id',

                    p_method         => 'GET',

                    p_source_type    => ords.source_type_collection_item,

                    p_source         => 'select

                                            id as customer_id,

                                            title_code,

                                            first_name,

                                            last_name

                                        from customer

                                        where id = :customer_id',

                    p_items_per_page => 10,

                    p_comments       => 'GET handler to retrieve a specific customer');

             

                ords.define_handler(

                    p_module_name => 'my_module',

                    p_pattern     => 'customer/',

                    p_method      => 'POST',

                    p_source_type => ords.source_type_plsql,

                    p_source      => '

                                    begin

                                        customers.customer_pkg.post_customer

                                        (

                                            p_json           => :body,

                                            p_status_code    => :status_code,

                                            p_forward_url    => :forward_url

                                        );

                                    end;',

                    p_comments    => 'POST handler to add a new customer');

             

               ords.define_parameter(

                    p_module_name        => 'my_module',

                    p_pattern            => 'customer/',

                    p_method             => 'POST',

                    p_name               => 'X-APEX-STATUS-CODE',

                    p_bind_variable_name => 'status_code',

                    p_source_type        => 'HEADER',

                    p_param_type         => 'INT',

                    p_access_method      => 'OUT');

             

                ords.define_parameter(

                    p_module_name        => 'my_module',

                    p_pattern            => 'customer/',

                    p_method             => 'POST',

                    p_name               => 'X-APEX-FORWARD',

                    p_bind_variable_name => 'forward_url',

                    p_source_type        => 'HEADER',

                    p_param_type         => 'STRING',

                    p_access_method      => 'OUT');

             

            We're passing in all the required fields for inserting a new customer in a JSON object. For example, something like:

             

            {

              "customer_reference": "ABC123",

              "title_code": 1,

              "first_name: "John",

              "last_name": "Smith"

            }

             

            This needs to be in the body of the request and will be passed to the customers.customer_pkg.post_customer procedure in the p_json parameter. Within the PL/SQL we're using the json_table table functionality in Oracle to treat the JSON relational rows.

             

            The PL/SQL procedure sets p_status_code in order to return the required http response code.

             

            To also return JSON in the body of the response we use 2 approaches. For a successfully added customer, we set p_forward_url to the primary key id of the newly inserted customer. The ORDS forwarding functionality then automatically forwards to the get handler for customers we have defined in this ORDS module - and so returns JSON containing the customer details.

             

            If an error occurs, we create a string in the PL/SQL holding a JSON object containing any error messages and we then pass this to htp.prn. This appears in the body of the response.

             

            It would be possible to use also htp.prn to return the successfully added customer details but it isn't worth the bother of coding that as we already have a get handler for returning customer information

             

            David

            • 3. Re: Stored Procedure with ORDS
              1000222

              Hi David,

              I am working on a POC for ORDS with Stored procedure,

              Can you please share the customers.customer_pkg.post_customer procedure contents.

               

              Thanks

              • 4. Re: Stored Procedure with ORDS
                david.pye

                Hi,

                 

                This is what we'd have as the post_customer procedure:

                 

                procedure post_customer

                (

                    p_json                in blob,

                    p_status_code   out number,

                    p_forward           out varchar2

                )

                is

                 

                    v_json clob;

                   

                    cursor json_cur is

                        select

                            json.*

                        from json_table

                        (v_json, '$'

                        columns (

                                    row_number for ordinality,

                                    title_code       path '$.title_code',

                                    first_name      path '$.first_name',

                                    last_name      path '$.last_name'

                                )

                        ) as json;

                 

                    e_user_defined      exception;

                    v_customer_id       customers.id%type;

                    v_json_string         clob;

                       

                begin

                 

                    -- Convert the BLOB body into a CLOB   

                    convert_blob_to_clob(p_json, v_json);

                 

                    for json_rec in json_cur

                    loop       

                 

                        -- Example validation

                        if json_rec.last_name is null then

                            v_error_message = 'No last name specified';

                            raise e_user_defined;

                        end if;

                   

                        insert into customers

                        (

                            title_code,

                            first_name,

                            last_name

                        )

                        values

                        (

                 

                            json_rec.title_code,

                            json_rec.first_name,

                            json_rec.last_name

                        )

                        returning id into v_customer_id;

                 

                    end loop;

                 

                    p_status_code := 200;

                   

                    -- URI that was used for the post will be re-triggered with the value in p_forward appended to it

                    -- i.e. a get request returning the newly inserted customer in the body

                    p_forward := to_char(v_customer_id);

                 

                exception

                    when e_user_defined then

                        rollback;

                        p_status_code := 400;

                       

                        -- This procedure builds our custom json error object in a string

                        v_json_string := error_notification_to_json(v_error_message);

                       

                        -- This ensures that the json error object is returned in the response body

                        htp.prn(v_json_string);

                 

                    when others then

                        rollback;

                        p_status_code := 400;

                        v_json_string := error_notification_to_json ('Unexpected error');

                        htp.prn(v_json_string);

                         

                end post_customer;

                • 5. Re: Stored Procedure with ORDS
                  Mike Kutz

                  Within your WHEN OTHERS block, you should be logging the SQL error somewhere (eg apex_debug.message() )

                   

                  MK

                  • 6. Re: Stored Procedure with ORDS
                    david.pye

                    Hi Mike,

                     

                    We do log the SQL error - the actual code we're running has much more error handling than the example I posted. I can't post any of our actual real code, the example is just a simplified version of the structure and type of thing we're doing

                     

                    David