create or replace procedure Submit_Order (
  p_our_ref    in integer,

  p_vendor_id  in integer,
  p_scu        in integer,
  p_quantity   in integer )
is
  v_url                 varchar2(255);

  v_msg                 varchar2(32767);
  v_verified_msg        varchar2(32767);
  v_our_ref_confirmed   integer;
  v_order_date          date;
  our_ref_not_confirmed exception;


  procedure Create_Order_Message
  is
  begin
    v_msg :=
      Tags.ordr
||    Tags.customer_id || To_Char ( 1 )          || Tags.Close ( Tags.customer_id )
||    Tags.our_ref     || To_Char ( p_our_ref )  || Tags.Close ( Tags.our_ref )

||    Tags.scu         || To_Char ( p_scu )      || Tags.Close ( Tags.scu )
||    Tags.quantity    || To_Char ( p_quantity ) || Tags.Close ( Tags.quantity )
||    Tags.Close ( Tags.ordr );
  end Create_Order_Message;

  procedure Report_Exception is
  begin
    update customer_orders

    set order_date = sysdate, status = 'failed', err_msg = v_msg
    where order_ref = p_our_ref;
    commit;
    Send_Error_Mail ( p_our_ref, v_msg );
  end Report_Exception;

begin
  select url into v_url from vendors
    where vendor_id = p_vendor_id;


  Create_Order_Message;

  v_url := v_url || '?p_order=' || v_msg;

  v_msg := Utl_Http.Request (
    url   => v_url  );
--  proxy => 'www-proxy.us.oracle.com' );

  v_verified_msg := Parse_Message.Verify_Format ( v_msg, Tags.confirmation );


  v_our_ref_confirmed := To_Number (
    Parse_Message.Extract_Value ( v_verified_msg, Tags.your_ref )
    );
  if v_our_ref_confirmed <> p_our_ref
    then raise our_ref_not_confirmed; end if;

  v_order_date := To_Date (
    Parse_Message.Extract_Value ( v_verified_msg, Tags.date_received ),
    'hh:mi:ss::DD-Mon-YYYY'
    );


  update customer_orders
    set order_date = sysdate,
    status = 'submitted'
    where order_ref = p_our_ref;
  commit;
exception
  when our_ref_not_confirmed then
    v_msg :=
     'our_ref_confirmed:'
||   To_Char(v_our_ref_confirmed)
||   ' <> our_ref:'

||   To_Char(p_our_ref);
    Report_Exception;

  when Parse_Message.Bad_Format then
    Report_Exception;

  when others then
    if SQLCODE = -29273
    then
      v_msg := Utl_Http.Get_Detailed_Sqlerrm;
    else
      v_msg := SQLERRM ;
    end if;

    Report_Exception;
end Submit_Order;
/
Show Errors