DROP TABLE all_orders;

CREATE TABLE all_orders (
  order_id NUMBER(12) PRIMARY KEY,
  order_date DATE,
  order_mode VARCHAR2(8),
  customer_id NUMBER(6),
  order_status NUMBER(2),
  order_total NUMBER(8,2),
  sales_rep_id NUMBER(6),
  promotion_id NUMBER(6)
  );
COMMENT ON COLUMN ALL_ORDERS.ORDER_STATUS IS
  '0: Not fully entered, 1: Entered, 2: Canceled - bad credit,'
  '3: Canceled - by customer, 4: Shipped - whole order,'
  '5: Shipped - replacement items, 6: Shipped - backlog on items,'
  '7: Shipped - special delivery, 8: Shipped - billed,' 
  '9: Shipped - payment plan, 10: Shipped - paid'
COMMENT ON COLUMN ALL_ORDERS.ORDER_MODE IS
  'Mode of order : online or direct'

INSERT INTO all_orders (
  ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID, ORDER_STATUS,
  ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID )
VALUES (
  2458, '17-AUG-99', 'direct', 101, 0, 78279.6, 153, NULL
);
..
commit;

--------------------

CREATE OR REPLACE PACKAGE Article643 IS
  p_dir_name varchar2(13) := 'C:\xmldata';

  procedure OpenXmlFile(p_dir_name varchar2);
END Article643;
----------------------------

create or replace package body Article643 is

  v_FILENAME varchar2(30);
  f_XML_FILE UTL_FILE.file_type;

  procedure OpenXmlFile(p_dir_name varchar2) is
    v_record_data varchar2(4000) := null;
    v_order_id varchar2(50) := null;
    v_order_date varchar2(50) := null;
    v_order_mode varchar2(50) := null;
    v_order_total varchar2(50) := null;

    cursor orders_cursor is
      select t.order_id, t.order_date, t.order_mode, t.order_total
        from all_orders t;

  begin
    --v_FILENAME := TO_CHAR(SYSDATE, 'DDMMYYYYHH24MI') || '.xml';
    v_FILENAME := 'article643.xml';

    f_XML_FILE := UTL_FILE.fopen('DATA_FILE_DIR', v_FILENAME, 'W');

    v_RECORD_DATA := '<?xml version="1.0" encoding="UTF-8"?>';
    UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);

    UTL_FILE.put_line(f_XML_FILE, '<ORDER_DATA>');
    open orders_cursor;
    loop
      fetch orders_cursor
        into v_order_id, v_order_date, v_order_mode, v_order_total;
      EXIT WHEN orders_cursor%NOTFOUND;

      UTL_FILE.put_line(f_XML_FILE, ' <ORDER_DETAILS>');
      UTL_FILE.put_line(f_XML_FILE,
                        ' <ORDER_ID>' || v_order_id || '</ORDER_ID>');
      UTL_FILE.put_line(f_XML_FILE,
                        ' <ORDER_DATE>' || v_order_date ||
                        '</ORDER_DATE>');
      UTL_FILE.put_line(f_XML_FILE,
                        ' <ORDER_MODE>' || v_order_mode ||
                        '</ORDER_MODE>');
      UTL_FILE.put_line(f_XML_FILE,
                        ' <ORDER_TOTAL>' || v_order_total ||
                        '</ORDER_TOTAL>');
      UTL_FILE.put_line(f_XML_FILE, ' </ORDER_DETAILS>');

    end loop;
    close orders_cursor;
    UTL_FILE.put_line(f_XML_FILE, '</ORDER_DATA>');

    UTL_FILE.FCLOSE(f_XML_FILE);

  EXCEPTION
    WHEN UTL_FILE.INTERNAL_ERROR THEN
      raise_application_error(-20500,
                              'Cannot open file :' || v_FILENAME ||
                              ', internal error; code:' || sqlcode ||
                              ',message:' || sqlerrm);
    WHEN UTL_FILE.INVALID_OPERATION THEN
      raise_application_error(-20501,
                              'Cannot open file :' || v_FILENAME ||
                              ', invalid operation; code:' || sqlcode ||
                              ',message:' || sqlerrm);
    WHEN UTL_FILE.INVALID_PATH THEN
      raise_application_error(-20502,
                              'Cannot open file :' || v_FILENAME ||
                              ', invalid path; code:' || sqlcode ||
                              ',message:' || sqlerrm);
    WHEN UTL_FILE.WRITE_ERROR THEN
      raise_application_error(-20503,
                              'Cannot write to file :' || v_FILENAME ||
                              ', write error; code:' || sqlcode ||
                              ',message:' || sqlerrm);
  end;

end Article643;

------------------