0 Replies Latest reply on Mar 29, 2018 6:43 PM by 3295629

    apex

    3295629

      test

        • 1. Re: re: APEX REST webservice
          Alli Pierre Yotti

          Hi,

          Please give details how you write tthe service. We can't debug something that we don't see.

           

          404 means the client can communicate with the server, but the server has not data for the request

           

           

          https://en.wikipedia.org/wiki/HTTP_404

          • 2. Re: re: APEX REST webservice
            3295629

            please see the document uploaded.

             

            It's a pl/sql block.

             

            I tested with sample sql query select * from oe_order_headers_all where order_number = :id

             

            When passing a non existing order_number ( ie :id) I get a null json response , not 404 Not found.

             

            So wondering 404 where this error comes from. How do I attach a document?

             

             

            thanks

            • 3. Re: re: APEX REST webservice
              Alli Pierre Yotti

              Just paste your code here.

               

               

              You can handle the no_data_found like so

              ......

              Exception

              When no_data_found then

              : output_parameter :=' no data for this id';

              • 4. Re: re: APEX REST webservice
                3295629

                Hi,

                But when I use a simple service with  select from a table  where id = :id   and testing this with invalid :ID it does not give 404 even though no data was found.

                Instead I get a JSON response with no items.

                 

                What is the difference between these two  pl/sql block and sql with no data.

                 

                when i run this code in toad/sql developer no error comes.

                 

                 

                declare

                 

                --    v_orgid       number := :A2;

                --    v_PkgId       varchar2(3);

                      v_LotCtrl_Code  varchar2(3);

                      v_SO           varchar2(12);

                      v_SO_Line      varchar2(12);

                      v_SO_Ship      varchar2(12);

                      v_SeqNbr      number;

                      v_E9          varchar2(40);    -- Item

                      v_E7          varchar2(12);    -- Revision

                      v_G1          number;          -- qty

                      v_F0          varchar2(3);      -- UOM

                      v_E3          varchar2(20);      -- Roll Nbr

                      v_E8          number;            -- Inventory_item_id

                   

                begin

                    v_LotCtrl_Code := null;

                    begin

                      select hpl.Item

                ,            hpl.Item_Revision

                ,            hpl.Container_Qty

                ,            hpl.Container_UOM

                ,            hpl.Roll_Nbr

                ,            mtl.Inventory_item_id

                ,            mtl.lot_control_code

                ,            hpl.SO_OrderNbr

                ,            hpl.SO_LINENBR

                ,            hpl.SO_SHIPNBR

                        into v_E9, v_E7, v_G1, v_F0, v_E3, v_E8, v_LotCtrl_Code, v_SO, v_SO_Line, v_SO_Ship

                        from hope_printed_labels hpl, mtl_system_items_b mtl

                       where hpl.org_ID = :A2  -- P_OrgID

                         and HPL.PACKAGE_ID = :P_PkgID   

                         and mtl.organization_id = :A2    -- P_OrgId

                         and mtl.segment1 = hpl.Item;

                     exception

                         when others then

                             v_E9 := Null;

                             v_E7 := Null;

                             v_G1 := Null;

                             v_F0 := Null;

                             v_E3 := Null;

                             v_LotCtrl_Code := '?';

                    end;

                 

                -- Not Lot Controlled....

                    If v_LotCtrl_Code <> '2' and v_LotCtrl_Code <> '?' then

                       INSERT INTO mtl_transactions_interface

                            (transaction_uom,

                             transaction_date,

                             source_code,

                             source_line_id,

                             source_header_id,

                             process_flag,

                             transaction_mode,

                             lock_flag,

                             last_update_date,

                             last_updated_by,

                             creation_date,

                             created_by,

                             inventory_item,

                             subinventory_code,

                             transfer_organization,

                             transaction_quantity,

                             primary_quantity,

                             transaction_type_id,

                             transfer_subinventory,

                             LOC_SEGMENT1,

                             LOC_SEGMENT2,

                             LOC_SEGMENT3,

                             XFER_LOC_SEGMENT1,

                             XFER_LOC_SEGMENT2,

                             XFER_LOC_SEGMENT3,

                             Inventory_ITEM_ID,

                             Item_Segment1,

                             Revision,

                             ORGANIZATION_ID,

                             VALIDATION_REQUIRED,

                             ATTRIBUTE9,

                             ATTRIBUTE14,

                             ATTRIBUTE15)

                      VALUES (v_F0,         -- UOM

                             SYSDATE,

                             'SubInventory Transfer',        -- :F1, Source_Code          --

                             99,

                             99,

                             1,

                             3,

                             2,

                             SYSDATE,

                             '4082',     -- Last_updated_by   :A1,

                             SYSDATE,

                             '4082',     -- Last_updated_by   :A1, 

                             v_E9,        -- Item

                             :F9,        -- from SubInventory

                             :A2,        --:A2, OrgID

                             v_G1,        -- Container_Qty

                             v_G1,        --Container_Qty 

                             2,

                             :G4,         -- To SubInventory

                             :G7,           -- From SubInv segment1  :G7,

                             :G8,           -- From SubInv segment2  :G8,

                             :G9,           -- From SubInv segment3  :G9,

                             :H0,           -- To SubInv segment1 :H0,

                             :H1,           -- To SubInv segment2 :H1,

                             :H2,           -- To SubInv segment3 :H2,

                             v_E8,          -- Item ID

                             v_E9,          -- Item 

                             v_E7,          -- Revision

                             :A2,          -- :A2,   Org

                             '2',          -- :H7 Transfer Validation

                             :P_Attr9,       --(v_SO || '|' || v_SO_Line || '|' || v_SO_Ship),  --  Attribute9 SO/Line/Ship ...

                             'Data Collection',   --:K0 Badge)

                             :P_Badge);

                   

                        commit;

                   

                    end if;

                   

                -- Lot Controlled....

                    If v_LotCtrl_Code = '2' and v_LotCtrl_Code <> '?' then

                   

                 

                       SELECT mtl_material_transactions_S.NEXTVAL into v_SeqNbr from dual;

                 

                 

                       INSERT INTO mtl_transaction_lots_interface

                            (transaction_interface_id,

                             lot_number,

                             transaction_quantity,

                             last_update_date,

                             last_updated_by,

                             creation_date,

                             created_by,

                             ATTRIBUTE15)

                       VALUES (v_SeqNbr,

                             V_E3,

                             v_G1,

                             SYSDATE,

                              '4082',     -- Last_updated_by   :A1,

                             SYSDATE,

                             '4082',     -- Last_updated_by   :A1,

                             :P_Badge);  --:K0 Badge)

                        commit;

                            

                      INSERT INTO mtl_transactions_interface

                            (transaction_interface_id,

                             transaction_uom,

                             transaction_date,

                             source_code,

                             source_line_id,

                             source_header_id,

                             process_flag,

                             transaction_mode,

                             lock_flag,

                             last_update_date,

                             last_updated_by,

                             creation_date,

                             created_by,

                             inventory_item,

                             subinventory_code,

                             transfer_organization,

                             transaction_quantity,

                             primary_quantity,

                             transaction_type_id,

                             transfer_subinventory,

                             LOC_SEGMENT1,

                             LOC_SEGMENT2,

                             LOC_SEGMENT3,

                             XFER_LOC_SEGMENT1,

                             XFER_LOC_SEGMENT2,

                             XFER_LOC_SEGMENT3,

                             Inventory_ITEM_ID,

                             Item_Segment1,

                             Revision,

                             ORGANIZATION_ID,

                             VALIDATION_REQUIRED,

                             ATTRIBUTE9,

                             ATTRIBUTE14,

                             ATTRIBUTE15)

                      VALUES (v_SeqNbr,

                             v_F0,

                             SYSDATE,

                             'SubInventory Transfer',        -- :F1, Source_Code    

                             99,

                             99,

                             1,

                             3,

                             2,

                             SYSDATE,

                             '4082',     -- Last_updated_by   :A1,

                             SYSDATE,

                             '4082',     -- Last_updated_by   :A1,

                             v_E9,

                             :F9,

                             :A2,

                             v_G1,

                             v_G1,

                             2,

                             :G4,

                             :G7,

                             :G8,

                             :G9,

                             :H0,

                             :H1,

                             :H2,

                             v_E8,

                             v_E9,

                             v_E7,

                             :A2,

                             '2',          -- :H7 Transfer Validation

                             :P_Attr9,       --(v_SO || '|' || v_SO_Line || '|' || v_SO_Ship),  --  Attribute9 SO/Line/Ship ...

                             'Data Collection',

                             :P_Badge);  --:K0 Badge)

                            

                             commit;

                    end if;

                 

                 

                   

                    apex_json.open_object();

                    apex_json.write('status', 'true');

                    apex_json.write('PkgID', :P_PkgID );

                    apex_json.write('date', to_char(localtimestamp, 'dd-mm-yyyy'));

                    apex_json.write('from', :A2);

                    apex_json.close_object();

                   

                    exception

                    when others then

                   

                    apex_json.open_object();

                    apex_json.write('status', 'false');

                    apex_json.write('PkgID', :P_PkgID );

                    apex_json.write('date', to_char(localtimestamp, 'dd-mm-yyyy'));

                    apex_json.write('from', :A2);

                    apex_json.close_object();

                 

                 

                end;

                 

                ===================================================================

                 

                How can I attached word document. so the service setup can be viewed. it was setup as GET method even though the PL/SQL code is doing the INSERT and the service was suppose to be POST method.  For quick testing we kept it GET with values getting passed via URL.

                 

                thanks

                • 5. Re: re: APEX REST webservice
                  3295629

                  Attached document showing the REST service setup and the pl/sql code.

                  thanks

                  • 6. Re: apex
                    3295629

                    converted the post method and it resolved the issue.

                    • 7. Re: apex
                      Alli Pierre Yotti

                      Converted what?