Rem Rem $Header: mgdmeta.sql 18-jul-2006.06:53:24 hgong Exp $ Rem Rem mgdmeta.sql Rem Rem Copyright (c) 2006, Oracle. All rights reserved. Rem Rem NAME Rem mgdmeta.sql - Rem Rem DESCRIPTION Rem Rem Rem NOTES Rem Rem Rem MODIFIED (MM/DD/YY) Rem hgong 07/12/06 - add version and uri info to EPC category Rem hgong 05/15/06 - move tag data translation schema and xml file Rem contents to mgdmeta.sql Rem hgong 05/12/06 - fix load metadata to work for all platforms and Rem ade Rem hgong 04/04/06 - changed xml directory Rem hgong 03/31/06 - load metadata Rem hgong 03/31/06 - load metadata Rem hgong 03/31/06 - Created Rem DECLARE amt NUMBER; buf VARCHAR2(32767); pos NUMBER; seq BINARY_INTEGER; tdt_xml CLOB; BEGIN --store tdt schema into a one column, one row table DELETE FROM mgd_id_xml_validator; INSERT INTO mgd_id_xml_validator VALUES(empty_clob()) RETURNING xsd_schema into tdt_xml; DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' Copyright ?2004 Epcglobal Inc., All Rights Reserved. EPCglobal Inc., its members, officers, directors, employees, or agents shall not be liable for any injury, loss, damages, financial or otherwise, arising from, related to, or caused by the use of this document. The use of said document shall constitute your express consent to the foregoing exculpation. Tag Data Translation (TDT) version 1.0 ]]> '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); COMMIT; --create EPC category SELECT mgd$sequence_category.nextval INTO seq FROM DUAL; INSERT INTO mgd_id_category_tab(owner, category_id, category_name, version, agency, uri) VALUES('MGDSYS', seq, 'EPC', '1.0', 'EPCGlobal', 'http://www.epcglobalinc.org'); COMMIT; --add schemes for EPC category --GID-96 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --GIAI-64 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --GIAI-96 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --GRAI-64 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --GRAI-96 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --SGLN-64 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --SGLN-96 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --SGTIN-64 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --SGTIN-96 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --SSCC-64 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --SSCC-96 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --USDOD-64 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; --USDOD-96 DBMS_LOB.CREATETEMPORARY(tdt_xml, true); DBMS_LOB.OPEN(tdt_xml, DBMS_LOB.LOB_READWRITE); buf := ' '; amt := length(buf); pos := 1; DBMS_LOB.WRITE(tdt_xml, amt, pos, buf); DBMS_LOB.CLOSE(tdt_xml); INSERT INTO mgd_id_scheme_tab(category_id, tdt_xml, owner) values(seq, tdt_xml, 'MGDSYS'); DBMS_MGD_ID_UTL.refresh_category(to_char(seq)); COMMIT; END; / SHOW ERRORS; call dbms_output.put_line('Make sure these values look OK:'); col category_name format a10; col type_name format a10; col encodings format a22; select dbms_lob.getlength(xsd_schema) as XML_VALIDATOR_CHAR_LENGTH from mgd_id_xml_validator; select category_name, category_id from mgd_id_category; select category_id, type_name, encodings, dbms_lob.getlength(tdt_xml) XML_TDTs_CHAR_LENGTH from mgd_id_scheme;