Oracle对象,集合建立的脚本Sample.

CREATE OR REPLACE TYPE tms.route_header_v2

AS

   OBJECT (

      bol INTEGER,

      instance_id INTEGER,

      trailer_id1 VARCHAR2 (20),

      truck_route_id INTEGER,

      dow VARCHAR2 (3),

      scheduled_departure_date DATE,

      completed_by VARCHAR2 (8)

   );

/

 

GRANT EXECUTE ON tms.route_header_v2 TO websrv;

 

CREATE OR REPLACE TYPE tms.route_stop_v2

AS

   OBJECT (

      stop_id INTEGER,

      location_name VARCHAR2 (40),

      location_id INTEGER,

      address1 VARCHAR2 (40),

      address2 VARCHAR2 (40),

      city VARCHAR2 (40),

      state VARCHAR2 (6),

      alarm_code VARCHAR2 (10),

      sched_arrival DATE,

      sched_departure DATE,

      actual_arrival DATE,

      actual_departure DATE,

      contacts VARCHAR2 (255),

      special_instructions VARCHAR2 (255),

      status VARCHAR2 (8),

      exception_code VARCHAR2 (4),

      sync_status CHAR (1),

      stop_seq INTEGER,

      enroute_dt DATE,

      exception_comments VARCHAR2 (255),

      stop_type VARCHAR (1),

      first_scan_time DATE

   );

/

 

GRANT EXECUTE ON tms.route_stop_v2 TO websrv;

 

CREATE OR REPLACE TYPE tms.stop_detail_v2

AS

   OBJECT (

      stop_detail_id INTEGER,

      bol_no VARCHAR2 (16),

      pick_up_drop_off_ind VARCHAR2 (1),

      scan_id VARCHAR2 (255),

      description VARCHAR2 (20),

      package_type VARCHAR2 (10),

      line_status VARCHAR2 (1),

      scan_ind VARCHAR2 (1),

      exception_code VARCHAR2 (4),

      uploaded_dt DATE,

      scan_timestamp DATE,

      weight INTEGER,

      weight_uom VARCHAR2 (3),

      stop_id INTEGER,

      sync_status CHAR (1),

      exception_comment VARCHAR2 (255),

      container_id VARCHAR2 (255),

      item_id VARCHAR2 (18)

   );

/

 

GRANT EXECUTE ON tms.stop_detail_v2 TO websrv;

 

CREATE OR REPLACE TYPE tms.po_v2

AS

   OBJECT (

      stop_id INTEGER,

      po_id VARCHAR (10),

      delete_flag CHAR (1),

      weight INTEGER,

      weight_uom VARCHAR (3),

      sync_status CHAR (1),

      exception_code VARCHAR2 (4),

      exception_comment VARCHAR2 (255)

   )

/

 

GRANT EXECUTE ON tms.po_v2 TO websrv;

 

CREATE OR REPLACE TYPE tms.route_stops_v2 AS TABLE OF route_stop_v2;

/

 

GRANT EXECUTE ON tms.route_stops_v2 TO websrv;

 

CREATE OR REPLACE TYPE tms.stop_details_v2 AS TABLE OF stop_detail_v2;

 

  GRANT EXECUTE ON tms.stop_details_v2 TO websrv;

 

CREATE OR REPLACE TYPE tms.pos_v2 AS TABLE OF po_v2;

/

 

GRANT EXECUTE ON tms.pos_v2 TO websrv;

 

CREATE OR REPLACE TYPE tms.route_data_v2

AS

   OBJECT (

      --   Attributes declaration

      route_header tms.route_header_v2,

      route_stops tms.route_stops_v2,

      stop_pos tms.pos_v2,

      stop_details tms.stop_details_v2,

      po_stop_details tms.po_stop_details

   --   Methods declaration

   );

/

 

GRANT EXECUTE ON tms.route_data_v2 TO websrv;