Segue abaixo o script que deve ser executado junto à base de dados de processamento do e-Forms:
-------------------------------------------------------- -- DDL for Type NDD_COMPOSEVALUEOBJECT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_COMPOSEVALUEOBJECT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TYPE "NDD_COMPOSEVALUEOBJECT" AS OBJECT (doccnpj NUMBER(14,0), docnumber NUMBER(10,0), docserie NUMBER(3,0));'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_COMPOSEVALUEOBJECT_TABLE --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_COMPOSEVALUEOBJECT_TABLE'; IF( lcnt = 0 ) THEN execute immediate ' CREATE OR REPLACE TYPE "NDD_COMPOSEVALUEOBJECT_TABLE" AS TABLE OF NDD_COMPOSEVALUEOBJECT;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_KEYTABLEOBJECT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_KEYTABLEOBJECT'; IF( lcnt = 0 ) THEN execute immediate '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));'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_KEYTABLEOBJECT_TABLE --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_KEYTABLEOBJECT_TABLE'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TYPE "NDD_KEYTABLEOBJECT_TABLE" AS TABLE OF NDD_KEYTABLEOBJECT;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_NUMBEROBJECT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_NUMBEROBJECT'; IF( lcnt = 0 ) THEN execute immediate ' CREATE OR REPLACE TYPE "NDD_NUMBEROBJECT" AS OBJECT (VNUMBER NUMBER(19,0));'; END IF; END;
/
-------------------------------------------------------- -- DDL for Type NDD_NUMBEROBJECT_TABLE --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(type_name) INTO lcnt FROM user_types WHERE type_name = 'NDD_NUMBEROBJECT_TABLE'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TYPE "NDD_NUMBEROBJECT_TABLE" AS TABLE OF ndd_numberobject;'; END IF; END;
/
--================================================================================================================================================--
-------------------------------------------------------- -- DDL for Table TBWEBSERVICEINPUT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEINPUT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE "TBWEBSERVICEINPUT" ( "SERVICEINPUTID" NUMBER(19,0), "HEADER" CLOB, "DOCUMENTDATA" BLOB, "INSERTDATE" TIMESTAMP (6), "SINGLEPROCESS" SMALLINT)'; END IF; END;
/ -------------------------------------------------------- -- ADICIONA NOVA COLUNA singleProcess A TABELA TBWEBSERVICEINPUT caso não exista --------------------------------------------------------
DECLARE column_exists number := 0; BEGIN Select count(*) into column_exists from user_tab_cols where column_name = 'SINGLEPROCESS' and table_name = 'TBWEBSERVICEINPUT';
if (column_exists = 0) then execute immediate 'ALTER TABLE TBWEBSERVICEINPUT ADD (SINGLEPROCESS NUMBER(3))'; end if; END;
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICEINPUT -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEINPUT'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICEINPUT" MODIFY ("SERVICEINPUTID" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEINPUT" MODIFY ("HEADER" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEINPUT" MODIFY ("DOCUMENTDATA" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEINPUT" ADD CONSTRAINT "TBWEBSERVICEINPUT_PK" PRIMARY KEY ("SERVICEINPUTID") ENABLE;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Sequence TBWEBSERVICEINPUT_SEQ --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(*) INTO lcnt FROM user_sequences WHERE sequence_name = 'TBWEBSERVICEINPUT_SEQ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE SEQUENCE "TBWEBSERVICEINPUT_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
/
-------------------------------------------------------- -- DDL for Index TBWEBSERVICEINPUT_PK --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (index_name) INTO lcnt FROM user_indexes where index_name= 'TBWEBSERVICEINPUT_PK'; IF( lcnt = 0 ) THEN execute immediate 'CREATE UNIQUE INDEX "TBWEBSERVICEINPUT_PK" ON "TBWEBSERVICEINPUT" ("SERVICEINPUTID")'; END IF; END;
/
-------------------------------------------------------- -- DDL for Trigger BI_TBWEBSERVICEINPUT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (trigger_name) INTO lcnt FROM user_triggers where trigger_name= 'BI_TBWEBSERVICEINPUT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TRIGGER "BI_TBWEBSERVICEINPUT" before INSERT ON "TBWEBSERVICEINPUT" FOR EACH row WHEN (new.SERVICEINPUTID IS NULL) BEGIN SELECT "TBWEBSERVICEINPUT_SEQ".nextval INTO :NEW.SERVICEINPUTID FROM dual; END;'; END IF; END;
/ ALTER TRIGGER "BI_TBWEBSERVICEINPUT" ENABLE;
--============================================================================================================ -------------------------------------------------------- -- DDL for Table TBWEBSERVICEDOCUMENT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCUMENT'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE "TBWEBSERVICEDOCUMENT" ("PROTOCOLID" NUMBER(19,0), "INSERTDATE" TIMESTAMP (6) DEFAULT SYSDATE, "DOCUMENTDATA" CLOB, "SERVICEKIND" NUMBER(2,0))'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICEDOCUMENT --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCUMENT'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICEDOCUMENT" MODIFY ("PROTOCOLID" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENT" MODIFY ("INSERTDATE" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENT" MODIFY ("DOCUMENTDATA" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENT" MODIFY ("SERVICEKIND" NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENT" ADD CONSTRAINT "TBWEBSERVICEDOCUMENT_PK" PRIMARY KEY ("PROTOCOLID") ENABLE;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Index TBWEBSERVICEDOCUMENT_PK --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (index_name) INTO lcnt FROM user_indexes where index_name= 'TBWEBSERVICEDOCUMENT_PK'; IF( lcnt = 0 ) THEN execute immediate 'CREATE UNIQUE INDEX "TBWEBSERVICEDOCUMENT_PK" ON "TBWEBSERVICEDOCUMENT" ("PROTOCOLID")'; END IF; END;
/
--=========================================================================================================== -------------------------------------------------------- -- DDL for Table TBWEBSERVICEDOCUMENTCONTROL -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCUMENTCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE "TBWEBSERVICEDOCUMENTCONTROL" ("PROTOCOLID" NUMBER(19,0), "DOCCNPJ" NUMBER(15,0), "DOCNUMBER" NUMBER(10,0), "DOCSERIE" NUMBER(3,0), "INSERTDATE" TIMESTAMP (6) DEFAULT SYSDATE)'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICEDOCUMENTCONTROL --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCUMENTCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("PROTOCOLID" NUMBER(19,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("DOCCNPJ" NUMBER(15,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("DOCNUMBER" NUMBER(10,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("DOCSERIE" NUMBER(3,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" MODIFY ("INSERTDATE" TIMESTAMP(6) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCUMENTCONTROL" ADD CONSTRAINT "TBWEBSERVICEDOCUMENTCONTR_PK" PRIMARY KEY ("DOCCNPJ", "DOCNUMBER", "DOCSERIE") ENABLE;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Index TBWEBSERVICEDOCUMENTCONTR_PK -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT (index_name) INTO lcnt FROM user_indexes where index_name= 'TBWEBSERVICEDOCUMENTCONTR_PK'; IF( lcnt = 0 ) THEN execute immediate 'CREATE UNIQUE INDEX "TBWEBSERVICEDOCUMENTCONTR_PK" ON "TBWEBSERVICEDOCUMENTCONTROL" ("DOCCNPJ", "DOCNUMBER", "DOCSERIE")'; END IF; END;
/ --=========================================================================================================== -------------------------------------------------------- -- DDL for Table TBDOCSEARCHPROCESS -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBDOCSEARCHPROCESS'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE "TBDOCSEARCHPROCESS" ("PROCESSID" NUMBER(20,0), "DOCKEY1" NUMBER(20,0), "DOCKEY2" NUMBER(20,0), "DOCKEY3" NUMBER(20,0), "PROTOCOL" VARCHAR2(20))'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBDOCSEARCHPROCESS --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBDOCSEARCHPROCESS'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("PROCESSID" NUMBER(20,0) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY1" NUMBER(20,0) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY2" NUMBER(20,0) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("DOCKEY3" NUMBER(20,0) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" MODIFY ("PROTOCOL" VARCHAR2(20) NOT NULL ENABLE); ALTER TABLE "TBDOCSEARCHPROCESS" ADD CONSTRAINT "TBDOCSEARCHPROCESS_PK" PRIMARY KEY ("PROCESSID") ENABLE;'; END IF; END;
/ -------------------------------------------------------- -- DDL for Sequence TBDOCSEARCHPROCESS_SEQ --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(*) INTO lcnt FROM user_sequences WHERE sequence_name = 'TBDOCSEARCHPROCESS_SEQ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE SEQUENCE "TBDOCSEARCHPROCESS_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
/
-------------------------------------------------------- -- DDL for Trigger TBDOCSEARCHPROCESS_PK_TRG --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (trigger_name) INTO lcnt FROM user_triggers where trigger_name= 'TBDOCSEARCHPROCESS_PK_TRG'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TRIGGER TBDOCSEARCHPROCESS_PK_TRG BEFORE INSERT ON TBDOCSEARCHPROCESS FOR EACH ROW WHEN (new.PROCESSID IS NULL) BEGIN SELECT TBDOCSEARCHPROCESS_SEQ.NEXTVAL INTO :new.PROCESSID FROM dual;END;'; END IF; END;
/ ALTER TRIGGER "TBDOCSEARCHPROCESS_PK_TRG" ENABLE;
--============================================================================================================ -------------------------------------------------------- -- DDL for Table TBWEBSERVICENSUCONTROL -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICENSUCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICENSUCONTROL ("OID" NUMBER(10,0), "CNPJ" VARCHAR2(14), "JOB" VARCHAR2(30), "UF" VARCHAR2(2), "ULTNSU" NUMBER(18,0))'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICENSUCONTROL --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICENSUCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("OID" NUMBER(10,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("CNPJ" VARCHAR2(14) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("JOB" VARCHAR2(30) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("UF" VARCHAR2(2) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICENSUCONTROL" MODIFY ("ULTNSU" NUMBER(18,0) NOT NULL); ALTER TABLE "TBWEBSERVICENSUCONTROL" ADD CONSTRAINT "TBWEBSERVICENSUCONTROL_PK" PRIMARY KEY ("OID") ENABLE;'; END IF; END;
/
-------------------------------------------------------- -- DDL for Sequence TBWEBSERVICENSUCONTROL_SEQ --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(*) INTO lcnt FROM user_sequences WHERE sequence_name = 'TBWEBSERVICENSUCONTROL_SEQ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE SEQUENCE "TBWEBSERVICENSUCONTROL_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
/
-------------------------------------------------------- -- DDL for Trigger TBWEBSERVICENSUCONTROL_PK_TRG --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (trigger_name) INTO lcnt FROM user_triggers where trigger_name= 'TBWEBSERVICENSUCONTROL_PK_TRG'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TRIGGER TBWEBSERVICENSUCONTROL_PK_TRG BEFORE INSERT ON TBWEBSERVICENSUCONTROL FOR EACH ROW WHEN (new."OID" IS NULL) BEGIN SELECT TBWEBSERVICENSUCONTROL_SEQ.NEXTVAL INTO :new."OID" FROM dual; END;'; END IF; END;
/ ALTER TRIGGER "TBWEBSERVICENSUCONTROL_PK_TRG" ENABLE;
--============================================================================================================ -------------------------------------------------------- -- DDL for Table TBWEBSERVICEDOCSITUATION -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCSITUATION'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICEDOCSITUATION ("ID" NUMBER(18,0), "SEARCHIN" CLOB, "SEARCHOUT" CLOB, "STATUS" NUMBER(5,0))'; END IF; END;
/
-------------------------------------------------------- -- Constraints for Table TBWEBSERVICEDOCSITUATION --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICEDOCSITUATION'; IF( lcnt = 0 ) THEN execute immediate 'ALTER TABLE "TBWEBSERVICEDOCSITUATION" MODIFY ("ID" NUMBER(18,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCSITUATION" MODIFY ("SEARCHIN" CLOB NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCSITUATION" MODIFY ("SEARCHOUT" CLOB NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCSITUATION" MODIFY ("STATUS" NUMBER(5,0) NOT NULL ENABLE); ALTER TABLE "TBWEBSERVICEDOCSITUATION" ADD CONSTRAINT "TBWEBSERVICEDOCSIT_PK" PRIMARY KEY ("ID") ENABLE;'; END IF; END;
/ -------------------------------------------------------- -- DDL for Table TBWEBSERVICECOLDCONTROL -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICECOLDCONTROL'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICECOLDCONTROL ("COLDID" NUMBER(10,0), "DOCQTD" NUMBER(10,0))'; END IF; END; / -------------------------------------------------------- -- DDL for Table TBWEBSERVICECOLDCONTROLCNPJ -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICECOLDCONTROLCNPJ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICECOLDCONTROLCNPJ ("CNPJ" NUMBER(14,0), "STATUS" NUMBER(3,0), "BLOCKDATE" TIMESTAMP(6))'; END IF; END; / -------------------------------------------------------- -- DDL for Table TBWEBSERVICECOLDNSU -------------------------------------------------------- declare lcnt number; BEGIN SELECT COUNT(table_name) INTO lcnt FROM user_tables where table_name= 'TBWEBSERVICECOLDNSU'; IF( lcnt = 0 ) THEN execute immediate 'CREATE TABLE TBWEBSERVICECOLDNSU ("COLDID" NUMBER(10,0), "CNPJ" VARCHAR2(30), "NSU" NUMBER(18,0))'; END IF; END; /
-------------------------------------------------------- -- DDL for Sequence TBWEBSERVICEDOCSITUATION_SEQ --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT(*) INTO lcnt FROM user_sequences WHERE sequence_name = 'TBWEBSERVICEDOCSITUATION_SEQ'; IF( lcnt = 0 ) THEN execute immediate 'CREATE SEQUENCE "TBWEBSERVICEDOCSITUATION_SEQ" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE'; END IF; END;
/
-------------------------------------------------------- -- DDL for Trigger TBWEBSERVICEDOCSIT_PK_TRG --------------------------------------------------------
declare lcnt number; BEGIN SELECT COUNT (trigger_name) INTO lcnt FROM user_triggers where trigger_name= 'TBWEBSERVICEDOCSIT_PK_TRG'; IF( lcnt = 0 ) THEN execute immediate 'CREATE OR REPLACE TRIGGER TBWEBSERVICEDOCSIT_PK_TRG BEFORE INSERT ON TBWEBSERVICEDOCSITUATION FOR EACH ROW WHEN (new."ID" IS NULL) BEGIN SELECT TBWEBSERVICEDOCSITUATION_SEQ.NEXTVAL INTO :new."ID" FROM dual;END;'; END IF; END;
/ ALTER TRIGGER "TBWEBSERVICEDOCSIT_PK_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, 18)); v_dockey02 := TO_NUMBER(SUBSTR(v_documentkey, 22, 18)); v_dockey03 := TO_NUMBER(SUBSTR(v_documentkey, 40, 8)); 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;
/ -------------------------------------------------------- -- Altera o tamanho da coluna Job da tabela TBWEBSERVICENSUCONTROL de 30 para 60 --------------------------------------------------------
DECLARE CONTADOR NUMBER; BEGIN SELECT DATA_LENGTH INTO CONTADOR FROM USER_TAB_COLUMNS WHERE table_name = 'TBWEBSERVICENSUCONTROL' AND COLUMN_NAME = 'JOB';
IF CONTADOR = 30 THEN EXECUTE IMMEDIATE 'ALTER TABLE TBWEBSERVICENSUCONTROL MODIFY JOB VARCHAR2(60)'; END IF; END;
/ |