qs_ia_cr_msgtyp.sql.old

oracle9i+j2ee开发aq oracle9i+j2ee开发aq oracle9i+j2ee开发aq

REM script name: qs_adm_cre.sql
REM creates the qs_adm schema
REM
REM version: 9i
REM change:  no connect required 

REM =======================================================
REM cleanup section
REM =======================================================

DROP TYPE customer_typ;
DROP TYPE order_typ;
DROP TYPE orderitemlist_vartyp;
DROP TYPE orderitem_typ;
DROP TYPE item_typ;

REM =======================================================
REM All object types are created in the qs_adm schema. All
REM application schemas that host any propagation source
REM queues are given the ENQUEUE_ANY system level privilege
REM allowing the application schemas to enqueue to the
REM destination queue.
REM =======================================================

CREATE OR REPLACE TYPE customer_typ AS OBJECT (
        custno          NUMBER,
        customer_id     varchar2(20),
        name            VARCHAR2(100),
        street          VARCHAR2(100),
        city            VARCHAR2(30),
        state           VARCHAR2(2),
        zip             NUMBER,
        country         VARCHAR2(100));
/

CREATE OR REPLACE TYPE item_typ as object (
        title           varchar2(100),
        authors         varchar2(100),
        ISBN            varchar2(20),
        price           number);
/

CREATE OR REPLACE TYPE orderitem_typ AS OBJECT (
        quantity        NUMBER,
	item		item_typ,
        subtotal        NUMBER);
/

CREATE OR REPLACE TYPE orderitemlist_vartyp AS VARRAY (20) OF ORDERITEM_TYP;
/

CREATE OR REPLACE TYPE order_typ AS OBJECT (
        orderno         NUMBER,
        status          VARCHAR2(30),
        ordertype       VARCHAR2(30),
        orderregion     VARCHAR2(30),
        customer        customer_typ,
        paymentmethod   VARCHAR2(30),
        items           orderitemlist_vartyp,
	ccnumber	VARCHAR2(20),
	order_date	VARCHAR2(30));
/


GRANT EXECUTE ON order_typ to QS;
GRANT EXECUTE ON orderitemlist_vartyp to QS;
GRANT EXECUTE ON orderitem_typ to QS;