Criação das tabelas de processamento do ColdSearch em Oracle

O script abaixo deve ser executado na instância/usuário criada(o) para o processamento da consulta ao e-Cold (COLDSEARCH). O usuário deve possuir as seguintes permissões:

•CREATE TABLE;

•CREATE VIEW;

•CREATE SEQUENCE;

•CREATE PROCEDURE;

•CREATE TRIGGER;

•CREATE MATERIALIZED VIEW;

•CREATE TYPE;

•CREATE OPERATOR;

•CREATE INDEXTYPE;

•CREATE JOB.

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

-- DDL for Type NDD_COMPOSEVALUEOBJECT

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

CREATE OR REPLACE TYPE "NDD_COMPOSEVALUEOBJECT" AS OBJECT

(

doccnpj NUMBER(14,0), docnumber NUMBER(10,0), docserie NUMBER(3,0)

);

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

-- DDL for Type NDD_COMPOSEVALUEOBJECT_TABLE

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

CREATE OR REPLACE TYPE "NDD_COMPOSEVALUEOBJECT_TABLE" AS TABLE OF NDD_COMPOSEVALUEOBJECT;

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

-- DDL for Type NDD_KEYTABLEOBJECT

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

CREATE OR REPLACE TYPE "NDD_KEYTABLEOBJECT" AS OBJECT

(

dockey varchar(50),

docprotocol NUMBER(19,0),

dckey01 NUMBER(19,0),

dckey02 NUMBER(19,0),

dckey03 NUMBER(19,0)

);

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

-- DDL for Type NDD_KEYTABLEOBJECT_TABLE

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

CREATE OR REPLACE TYPE "NDD_KEYTABLEOBJECT_TABLE" AS TABLE OF NDD_KEYTABLEOBJECT;

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

-- DDL for Type NDD_NUMBEROBJECT

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

CREATE OR REPLACE TYPE "NDD_NUMBEROBJECT" AS OBJECT

(VNUMBER NUMBER(19,0));

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

-- DDL for Type NDD_NUMBEROBJECT_TABLE

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

CREATE OR REPLACE TYPE "NDD_NUMBEROBJECT_TABLE" AS TABLE OF ndd_numberobject;

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

-- DDL for Sequence TBCOLDSEARCHRESULT_SEQ

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

CREATE SEQUENCE "TBCOLDSEARCHRESULT_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 161 CACHE 20 NOORDER NOCYCLE ;

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

-- DDL for Sequence TBCOLDSEARCH_SEQ

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

CREATE SEQUENCE "TBCOLDSEARCH_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 21 CACHE 20 NOORDER NOCYCLE ;

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

-- DDL for Table TBCOLDSEARCH

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

CREATE TABLE "TBCOLDSEARCH" (

"COLDSEARCHID" NUMBER(19,0) NOT NULL,

"INSERTDATE" TIMESTAMP(6) NOT NULL,

"TOTALSEARCHED" NUMBER(10,0) NULL,

"TOTALPROCESSED" NUMBER(10,0) NULL,

"LASTPROCESSDATE" TIMESTAMP(6) NULL,

"SEARCHSTATUS" NUMBER(2,0) NOT NULL,

"XMLSEARCH" BLOB NOT NULL,

"TYPESEARCH" VARCHAR2(1 BYTE) NOT NULL)

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

-- DDL for Table TBCOLDSEARCHRESULT

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

CREATE TABLE "TBCOLDSEARCHRESULT"

( "RESULTID" NUMBER(19,0),

"COLDSEARCHID" NUMBER(19,0),

"SEQUENCIALID" NUMBER(19,0),

"XMLDATA" BLOB,

"PDFDATA" BLOB,

"DOCUMENTKEY1" NUMBER(19,0),

"DOCUMENTKEY2" NUMBER(19,0),

"DOCUMENTKEY3" NUMBER(19,0)

) ;

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

-- Constraints for Table TBCOLDSEARCH

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

ALTER TABLE "TBCOLDSEARCH" ADD CONSTRAINT "TBCOLDSEARCH_PK" PRIMARY KEY ("COLDSEARCHID") ENABLE;

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

-- Constraints for Table TBCOLDSEARCHRESULT

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

ALTER TABLE "TBCOLDSEARCHRESULT" MODIFY ("RESULTID" NOT NULL ENABLE);

ALTER TABLE "TBCOLDSEARCHRESULT" MODIFY ("COLDSEARCHID" NOT NULL ENABLE);

ALTER TABLE "TBCOLDSEARCHRESULT" MODIFY ("SEQUENCIALID" NOT NULL ENABLE);

ALTER TABLE "TBCOLDSEARCHRESULT" MODIFY ("DOCUMENTKEY1" NOT NULL ENABLE);

ALTER TABLE "TBCOLDSEARCHRESULT" MODIFY ("DOCUMENTKEY2" NOT NULL ENABLE);

ALTER TABLE "TBCOLDSEARCHRESULT" MODIFY ("DOCUMENTKEY3" NOT NULL ENABLE);

ALTER TABLE "TBCOLDSEARCHRESULT" ADD CONSTRAINT "TBCOLDSEARCHRESULT_PK" PRIMARY KEY ("RESULTID") ENABLE;

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

-- DDL for Index TBCOLDSEARCHRESULT_INDEX1

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

CREATE INDEX "TBCOLDSEARCHRESULT_INDEX1" ON "TBCOLDSEARCHRESULT" ("COLDSEARCHID", "SEQUENCIALID")

;

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

-- Ref Constraints for Table TBCOLDSEARCHRESULT

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

ALTER TABLE "TBCOLDSEARCHRESULT" ADD CONSTRAINT "TBCOLDSEARCHRESULT_TBCOLD_FK1" FOREIGN KEY ("COLDSEARCHID")

REFERENCES "TBCOLDSEARCH" ("COLDSEARCHID") ON DELETE SET NULL ENABLE;

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

-- DDL for Trigger TBCOLDSEARCHRESULT_TRG

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

CREATE OR REPLACE TRIGGER "TBCOLDSEARCHRESULT_TRG" BEFORE

INSERT ON TBCOLDSEARCHRESULT FOR EACH ROW BEGIN

SELECT TBCOLDSEARCHRESULT_SEQ.NEXTVAL INTO :NEW.RESULTID FROM DUAL;

END;

ALTER TRIGGER "TBCOLDSEARCHRESULT_TRG" ENABLE;

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

-- DDL for Trigger TBCOLDSEARCH_TRG

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

CREATE OR REPLACE TRIGGER "TBCOLDSEARCH_TRG" BEFORE

INSERT ON TBCOLDSEARCH FOR EACH ROW BEGIN

SELECT TBCOLDSEARCH_SEQ.NEXTVAL INTO :NEW.COLDSEARCHID FROM DUAL;

END;

ALTER TRIGGER "TBCOLDSEARCH_TRG" ENABLE;

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

-- DDL for Function CREATECOMPOSEVALUES

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

CREATE OR REPLACE FUNCTION "CREATECOMPOSEVALUES" (plist CLOB)

RETURN NDD_COMPOSEVALUEOBJECT_TABLE PIPELINED AS

v_pos number(19,0);

v_nextpos number(19,0);

v_valuelen number(19,0);

v_docnumber number(10,0);

v_doccnpj NUMBER(14,0);

v_docserie NUMBER(3,0);

v_document VARCHAR2(100);

BEGIN

v_pos := 0;

v_nextpos := 1;

v_docnumber := 0;

v_docserie := 0;

v_doccnpj := 0;

WHILE v_nextpos > 0

LOOP

v_nextpos := INSTR(plist, ',' , v_pos + 1);

v_valuelen := (CASE WHEN v_nextpos > 0 THEN v_nextpos

ELSE LENGTH(plist) + 1

END) - v_pos - 1;

v_document := SUBSTR(plist, v_pos + 1, v_valuelen);

v_doccnpj := TO_NUMBER(SUBSTR(v_document, 1, 14));

v_docnumber := TO_NUMBER(SUBSTR(v_document, 15, 9));

v_docserie := TO_NUMBER(SUBSTR(v_document, 24, 3));

PIPE ROW(ndd_composevalueobject(v_doccnpj,v_docnumber, v_docserie));

V_pos := V_nextpos;

END LOOP

RETURN;

END CREATECOMPOSEVALUES;

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

-- DDL for Function CREATEKEYTABLE

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

CREATE OR REPLACE FUNCTION "CREATEKEYTABLE" (plist CLOB) RETURN NDD_KEYTABLEOBJECT_TABLE PIPELINED AS

v_pos number(19,0);

v_nextpos number(19,0);

v_valuelen number(19,0);

v_documentkey VARCHAR2(100);

v_documenttype varchar2(2);

v_protocol number(19,0);

v_dockey01 number(19,0);

v_dockey02 number(19,0);

v_dockey03 number(19,0);

BEGIN

v_pos := 0;

v_nextpos := 1;

v_documentkey := '';

v_documenttype := '';

v_protocol := 0;

v_dockey01 := 0;

v_dockey02 := 0;

v_dockey03 := 0;

WHILE v_nextpos > 0

LOOP

v_nextpos := INSTR(plist, ',' , v_pos + 1);

v_valuelen := (CASE WHEN v_nextpos > 0 THEN v_nextpos

ELSE LENGTH(plist) + 1

END) - v_pos - 1;

v_documentkey := SUBSTR(plist, v_pos + 1, v_valuelen);

if length(v_documentkey) > 44 then

v_protocol := to_number(substr(v_documentkey, 45, length(v_documentkey)));

v_documentkey := substr(v_documentkey, 1, 44);

else

v_protocol := 0;

end if;

v_documenttype := substr(v_documentkey, 21, 2);

if v_documenttype = '55' then

v_documentkey := 'NFe' || v_documentkey;

else

v_documentkey := 'CTe' || v_documentkey;

end if;

v_dockey01 := TO_NUMBER(SUBSTR(v_documentkey, 4, 15));

v_dockey02 := TO_NUMBER(SUBSTR(v_documentkey, 19, 15));

v_dockey03 := TO_NUMBER(SUBSTR(v_documentkey, 34, 14));

PIPE ROW(ndd_keytableobject(substr(v_documentkey,1,length(v_documentkey)), v_protocol, v_dockey01, v_dockey02, v_dockey03));

V_pos := V_nextpos;

END LOOP

RETURN;

END;

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

-- DDL for Function CREATELISTTABLE

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

CREATE OR REPLACE FUNCTION "CREATELISTTABLE" (plist CLOB)

RETURN ndd_numberobject_table PIPELINED AS

v_pos number(19,0);

v_nextpos number(19,0);

v_valuelen number(19,0);

v_numberval number(19,0);

BEGIN

v_pos := 0;

v_nextpos := 1;

v_numberval := 0;

WHILE v_nextpos > 0

LOOP

v_nextpos := INSTR(plist, ',' , v_pos + 1);

v_valuelen := (CASE WHEN v_nextpos > 0 THEN v_nextpos

ELSE LENGTH(plist) + 1

END) - v_pos - 1;

v_numberval := TO_NUMBER(SUBSTR(plist, v_pos + 1, v_valuelen));

PIPE ROW(ndd_numberobject(v_numberval));

V_pos := V_nextpos;

END LOOP

RETURN;

END;