Query is freezing

defferrari · Post by **defferrari** » Wed 13 Apr 2011 19:55

As in the topic http://www.devart.com/forums/viewtopic.php?t=20577, I have another query that is freezing, but this time the query is quite simple:

DM.QAux.SQL.Clear;
DM.QAux.SQL.Add('INSERT INTO LOTE_PARDINI_PROCEDIMENTO (CODLOTE_PARDINI, CODEXAME_PROCEDIMENTO)');
DM.QAux.SQL.Add('SELECT EP.CODEXAME_PROCEDIMENTO');
DM.QAux.SQL.Add('FROM EXAME_PROCEDIMENTO EP, PROCEDIMENTO P');
DM.QAux.SQL.Add('WHERE EP.DATA_MATERIAL_COLHIDO BETWEEN :DATAINI AND :DATAFIM');
DM.QAux.SQL.Add('AND EP.CODPROCEDIMENTO = P.CODPROCEDIMENTO');
DM.QAux.SQL.Add('AND P.CODLABORATORIO = :CODLABORATORIO');
DM.QAux.SQL.Add('AND P.CODIGO_PARDINI IS NOT NULL');
DM.QAux.SQL.Add('AND EP.CODEXAME_PROCEDIMENTO NOT IN ');
DM.QAux.SQL.Add(' (SELECT CODEXAME_PROCEDIMENTO');
DM.QAux.SQL.Add(' FROM LOTE_PARDINI_PROCEDIMENTO');
DM.QAux.SQL.Add(' WHERE CODLOTE_PARDINI = :CODLOTE_PARDINI))');
DM.QAux.ParamByName('CODLOTE_PARDINI').AsInteger := pbCodLote_Pardini;
DM.QAux.ParamByName('DATAINI').AsDateTime := DataIni.Date + StrToTime(HoraIni.Text + ':00');
DM.QAux.ParamByName('DATAFIM').AsDateTime := DataFim.Date + StrToTime(HoraIni.Text + ':00');
DM.QAux.ParamByName('CODLABORATORIO').AsInteger := pvCodLaboratorio;
DM.QAux.Open;

Obs: DM.QAux is a TUniQuery and Database is Oracle

Again, if I run that query in a SQL console, it runs quite fast (0.019 seg)
Another curiosity fact is that the procedure has a twin procedure where only changes the table LOTE_PARDINI and LOTE_PARDINI_PROCEDIMENTO by tables with exactly the same structure, and the query in that procedure runs quite fast

I also tryed changing the TUniQuery by a TUniSQL but nothing changed.
Another trying was to runs only the query, withut the "insert", and run the "insert" inside a loop, but again nothing changed, it freezed in the query.

Thanks,
Luis Enrique

AlexP · Post by **AlexP** » Thu 14 Apr 2011 10:47

hello,

Please send a script to create and fill tables to alexp*devart*com.
Also specify the TUniQuery and TUniConnection properties if they differ from the default properties, or send me a complete small sample.

defferrari · Post by **defferrari** » Thu 14 Apr 2011 17:16

I could even send you that information, but I doubt it will be helpfull to you cause, as I told you before, I have this other procedure (included below), wich the only differences are the tableS LOTE_ALVARO and LOTE_ALVARO_PROCEDIMENTO, instead of LOTE_PARDINI and LOTE_PARDINI_PROCEDIMENTO, and this procedure runs quite fast.

DM.QAux.SQL.Clear;
DM.QAux.SQL.Add('INSERT INTO LOTE_ALVARO_PROCEDIMENTO (CODLOTE_ALVARO, CODEXAME_PROCEDIMENTO)');
DM.QAux.SQL.Add('(SELECT :CODLOTE_ALVARO, EP.CODEXAME_PROCEDIMENTO');
DM.QAux.SQL.Add(' FROM EXAME_PROCEDIMENTO EP, PROCEDIMENTO P');
DM.QAux.SQL.Add(' WHERE EP.DATA_MATERIAL_COLHIDO BETWEEN :DATAINI AND :DATAFIM');
DM.QAux.SQL.Add(' AND EP.CODPROCEDIMENTO = P.CODPROCEDIMENTO');
DM.QAux.SQL.Add(' AND P.CODLABORATORIO = :CODLABORATORIO');
DM.QAux.SQL.Add(' AND P.CODIGO_ALVARO IS NOT NULL');
DM.QAux.SQL.Add(' AND EP.CODEXAME_PROCEDIMENTO NOT IN ');
DM.QAux.SQL.Add(' (SELECT CODEXAME_PROCEDIMENTO');
DM.QAux.SQL.Add(' FROM LOTE_ALVARO_PROCEDIMENTO');
DM.QAux.SQL.Add(' WHERE CODLOTE_ALVARO = :CODLOTE_ALVARO))');
DM.QAux.ParamByName('CODLOTE_ALVARO').AsInteger := pbCodLote_Alvaro;
DM.QAux.ParamByName('DATAINI').AsDateTime := DataIni.Date + StrToTime(HoraIni.Text + ':00');
DM.QAux.ParamByName('DATAFIM').AsDateTime := DataFim.Date + StrToTime(HoraFim.Text + ':59');
DM.QAux.ParamByName('CODLABORATORIO').AsInteger := pvCodLaboratorio;
DM.QAux.Execute;

object QAux: TUniQuery
Connection = Dados
SpecificOptions.Strings = (
'Oracle.FetchAll=True')
Left = 216
Top = 16
end

object Dados: TUniConnection
ProviderName = 'Oracle'
SpecificOptions.Strings = (
'Oracle.Direct=True'
'Oracle.Charset=WE8ISO8859P1'
'Oracle.PrecisionInteger=10')
Server = '172.17.1.4:1521:ORCL'
AfterConnect = DadosAfterConnect
Left = 368
Top = 64
end

defferrari · Post by **defferrari** » Thu 14 Apr 2011 17:21

the DDL of the tables envolved:

CREATE TABLE "LABEXAME"."LOTE_ALVARO"
( "CODLOTE_ALVARO" NUMBER(10,0),
"DATA_CADASTRO" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"DATA_GERACAO" DATE,
"CODUSUARIO_GERACAO" NUMBER(10,0),
CONSTRAINT "PK_LOTE_ALVARO" PRIMARY KEY ("CODLOTE_ALVARO")
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "INDICES" ENABLE,
CONSTRAINT "FK_LOTE_ALVARO_CODUSUARIO" FOREIGN KEY ("CODUSUARIO_GERACAO")
REFERENCES "LABEXAME"."USUARIO" ("CODUSUARIO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DADOS" ;

CREATE TABLE "LABEXAME"."LOTE_ALVARO_PROCEDIMENTO"
( "CODLOTE_ALVARO" NUMBER(10,0),
"CODEXAME_PROCEDIMENTO" NUMBER(10,0),
CONSTRAINT "PK_LOTE_ALVARO_PROCEDIMENTO" PRIMARY KEY ("CODLOTE_ALVARO", "CODEXAME_PROCEDIMENTO")
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 524288 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "INDICES" ENABLE,
CONSTRAINT "FK_LOTE_ALVA_PROC_CODLOTE_ALVA" FOREIGN KEY ("CODLOTE_ALVARO")
REFERENCES "LABEXAME"."LOTE_ALVARO" ("CODLOTE_ALVARO") ENABLE,
CONSTRAINT "FK_LOTE_ALVA_PROC_CODEXAM_PROC" FOREIGN KEY ("CODEXAME_PROCEDIMENTO")
REFERENCES "LABEXAME"."EXAME_PROCEDIMENTO" ("CODEXAME_PROCEDIMENTO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 393216 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DADOS" ;

CREATE TABLE "LABEXAME"."LOTE_PARDINI"
( "CODLOTE_PARDINI" NUMBER(10,0),
"DATA_CADASTRO" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"DATA_GERACAO" DATE,
"CODUSUARIO_GERACAO" NUMBER(10,0),
CONSTRAINT "PK_LOTE_PARDINI" PRIMARY KEY ("CODLOTE_PARDINI")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "INDICES" ENABLE,
CONSTRAINT "FK_LOTE_PARDINI_CODUSUARIO" FOREIGN KEY ("CODUSUARIO_GERACAO")
REFERENCES "LABEXAME"."USUARIO" ("CODUSUARIO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DADOS" ;

CREATE TABLE "LABEXAME"."LOTE_PARDINI_PROCEDIMENTO"
( "CODLOTE_PARDINI" NUMBER(10,0),
"CODEXAME_PROCEDIMENTO" NUMBER(10,0),
CONSTRAINT "PK_LOTE_PARDINI_PROCEDIMENTO" PRIMARY KEY ("CODLOTE_PARDINI", "CODEXAME_PROCEDIMENTO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "INDICES" ENABLE,
CONSTRAINT "FK_LOTE_PARD_PROC_CODLOTE_PARD" FOREIGN KEY ("CODLOTE_PARDINI")
REFERENCES "LABEXAME"."LOTE_PARDINI" ("CODLOTE_PARDINI") ENABLE,
CONSTRAINT "FK_LOTE_PARD_PROC_CODEXAM_PROC" FOREIGN KEY ("CODEXAME_PROCEDIMENTO")
REFERENCES "LABEXAME"."EXAME_PROCEDIMENTO" ("CODEXAME_PROCEDIMENTO") ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "DADOS" ;

Obs: Note that the structures of the LOTE_PARDINI and LOTE_ALVARO are the same as well as the structures of the LOTE_PARDINI_PROCEDIMENTO and LOTE_ALVARO_PROCEDIMENTO

AlexP · Post by **AlexP** » Fri 15 Apr 2011 10:18

hello,

You didn't send a script to create the following tables involved in the query:
EXAME_PROCEDIMENTO, PROCEDIMENTO

In addition, we have tested that we don't execute any additional query in this case, we only send the specified query to a server.

Please sent the missing scripts to create tables.
Also please try to reproduce the problem in the OCI mode.

defferrari · Post by **defferrari** » Fri 15 Apr 2011 12:28

CREATE TABLE EXAME_PROCEDIMENTO
(CODEXAME_PROCEDIMENTO NUMBER(10,0) NOT NULL ENABLE,
CODEXAME NUMBER(10,0) NOT NULL ENABLE,
CODPROCEDIMENTO NUMBER(10,0) NOT NULL ENABLE,
MATERIAL_COLHIDO CHAR(1 BYTE) NOT NULL ENABLE,
IMPRIMIR CHAR(1 BYTE) NOT NULL ENABLE,
FATURAR CHAR(1 BYTE) NOT NULL ENABLE,
RESULTADO VARCHAR2(4000 BYTE),
VALOR NUMBER(12,4) DEFAULT 0 NOT NULL ENABLE,
NUM_CH NUMBER(12,4) DEFAULT 0 NOT NULL ENABLE,
ORDEM NUMBER(3,0),
CODUSUARIO_CADASTRO NUMBER(10,0) NOT NULL ENABLE,
CODUSUARIO_RESULTADO NUMBER(10,0),
DATA_ENTREGA DATE,
DATA_MATERIAL_COLHIDO DATE,
MATERIAL_DESCRICAO VARCHAR2(200 BYTE),
CONVENIO1_VALOR_PAGO NUMBER(12,4) DEFAULT 0 NOT NULL ENABLE,
CONVENIO1_VALOR_GLOSA NUMBER(12,4) DEFAULT 0 NOT NULL ENABLE,
CONVENIO1_DATA_PAGO DATE,
CONVENIO1_OBSERVACAO VARCHAR2(200 BYTE),
CONVENIO2_VALOR_PAGO NUMBER(12,4) DEFAULT 0 NOT NULL ENABLE,
CONVENIO2_VALOR_GLOSA NUMBER(12,4) DEFAULT 0 NOT NULL ENABLE,
CONVENIO2_DATA_PAGO DATE,
CONVENIO2_OBSERVACAO VARCHAR2(200 BYTE),
CODUSUARIO_LIBERACAO NUMBER(10,0),
DATA_IMPRESSAO DATE,
DATA_RESULTADO DATE,
DATA_LIBERACAO DATE,
VOLUME_DIURESE NUMBER(10,2),
TEMPO_DIURESE NUMBER(5,0),
CONSTRAINT PK_EXAME_PROCEDIMENTO PRIMARY KEY (CODEXAME_PROCEDIMENTO)
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 167772160 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES ENABLE,
CONSTRAINT FK_EXAME_PROCEDIMENTO_CODPROC FOREIGN KEY (CODPROCEDIMENTO)
REFERENCES LABEXAME.PROCEDIMENTO (CODPROCEDIMENTO) ENABLE,
CONSTRAINT FK_EXAME_PROCEDIMENTO_USU_CAD FOREIGN KEY (CODUSUARIO_CADASTRO)
REFERENCES USUARIO (CODUSUARIO) ENABLE,
CONSTRAINT FK_EXAME_PROCEDIMENTO_USU_LIB FOREIGN KEY (CODUSUARIO_LIBERACAO)
REFERENCES USUARIO (CODUSUARIO) ENABLE,
CONSTRAINT FK_EXAME_PROCEDIMENTO_USU_RES FOREIGN KEY (CODUSUARIO_RESULTADO)
REFERENCES USUARIO (CODUSUARIO) ENABLE,
CONSTRAINT FK_EXAME_PROCEDIMENTO_CODEXAME FOREIGN KEY (CODEXAME)
REFERENCES EXAME (CODEXAME) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 0 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 2348810240 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE DADOS;

CREATE INDEX INDEX_EXAME_PROC_CODEXAME ON EXAME_PROCEDIMENTO (CODEXAME, CODPROCEDIMENTO)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 134217728 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES ;

CREATE INDEX INDEX_EXAME_PROC_CODEXAME_PROC ON EXAME_PROCEDIMENTO (CODEXAME_PROCEDIMENTO, CODEXAME, CODPROCEDIMENTO)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 167772160 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES ;

CREATE INDEX INDEX_EXAME_PROC_CODPROC ON EXAME_PROCEDIMENTO (CODPROCEDIMENTO, CODEXAME)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 142606336 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_EXAME_PROC_CONVENIO2DATA ON EXAME_PROCEDIMENTO (CONVENIO2_DATA_PAGO, CODEXAME)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 125829120 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_EXAME_PROC_DATA_ENTREGA ON EXAME_PROCEDIMENTO (DATA_ENTREGA, CODEXAME)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 159383552 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_EXAME_PROC_DATA_IMPRESSA ON EXAME_PROCEDIMENTO (DATA_IMPRESSAO, CODEXAME)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 184549376 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_EXAME_PROC_DATA_MATERIAL ON EXAME_PROCEDIMENTO (DATA_MATERIAL_COLHIDO, CODEXAME)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 184549376 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_EXAME_PROC_CONVENIO1DATA ON EXAME_PROCEDIMENTO (CONVENIO1_DATA_PAGO, CODEXAME)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 176160768 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_EXAME_PROC_ORDEM ON EXAME_PROCEDIMENTO (ORDEM)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 92274688 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX PK_EXAME_PROCEDIMENTO ON EXAME_PROCEDIMENTO (CODEXAME_PROCEDIMENTO)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 109051904 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_EXAME_PROC_CODEXAME_DATA ON EXAME_PROCEDIMENTO (CODEXAME, DATA_IMPRESSAO)
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 159383552 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE OR REPLACE TRIGGER GERACODEXAME_PROCEDIMENTO BEFORE INSERT ON EXAME_PROCEDIMENTO REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
declare
proximo number;
BEGIN
select SEQEXAME_PROCEDIMENTO.NEXTVAL into proximo from DUAL;
:new.CODEXAME_PROCEDIMENTO := proximo;
END;
/
ALTER TRIGGER GERACODEXAME_PROCEDIMENTO ENABLE;

CREATE TABLE PROCEDIMENTO
(CODPROCEDIMENTO NUMBER(10,0) NOT NULL ENABLE,
DESCRICAO VARCHAR2(50 BYTE) NOT NULL ENABLE,
ABREV VARCHAR2(10 BYTE) NOT NULL ENABLE,
ORDEM_LAUDO NUMBER(3,0) NOT NULL ENABLE,
TITULO_LAUDO VARCHAR2(50 BYTE) NOT NULL ENABLE,
CODGRUPO_PROCEDIMENTO NUMBER(10,0) NOT NULL ENABLE,
MASCULINO CHAR(1 BYTE) NOT NULL ENABLE,
FEMININO CHAR(1 BYTE) NOT NULL ENABLE,
FATURAR CHAR(1 BYTE) NOT NULL ENABLE,
IMPRIME_REQUISICAO CHAR(1 BYTE) NOT NULL ENABLE,
TEMPO_RESULTADO NUMBER(5,0),
OBSERVACAO VARCHAR2(500 BYTE),
TIPO_RESULTADO CHAR(1 BYTE) NOT NULL ENABLE,
FUNCAO_RESULTADO NUMBER(5,0),
TEXTO_REQUISICAO VARCHAR2(2000 BYTE),
CODLABORATORIO NUMBER(10,0),
TEMPO_JEJUM NUMBER(2,0) NOT NULL ENABLE,
MATERIAL VARCHAR2(200 BYTE),
ETIQUETA_SOZINHO CHAR(1 BYTE) NOT NULL ENABLE,
CODGRUPO_PROCEDIMENTO_GUIA NUMBER(10,0) NOT NULL ENABLE,
CODGRUPO_ETIQUETA1 NUMBER(10,0),
CODGRUPO_ETIQUETA2 NUMBER(10,0),
MATERIAL_OBRIGATORIO CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
IMPRIME_MAPA CHAR(1 BYTE) DEFAULT 'S' NOT NULL ENABLE,
REQUISICAO_SOZINHO CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
CODGRUPO_PROCEDIMENTO_MAPA NUMBER(10,0) NOT NULL ENABLE,
CODGRUPO_PROCEDIMENTO_LIBERA NUMBER(10,0) NOT NULL ENABLE,
CODHONORARIO_HR NUMBER(10,0),
CODHONORARIO_HSR NUMBER(10,0),
ALTERACAO_RESULTADO CHAR(1 BYTE) DEFAULT 'S' NOT NULL ENABLE,
IMPRESSAO_RESULTADO_RESTRITO CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
CODIGO_ALVARO VARCHAR2(10 BYTE),
MATERIAL_ALVARO NUMBER(5,0),
DIURESE_OBRIGATORIO CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
CODIGO_HEMOSYS VARCHAR2(10 BYTE),
TIPO_RESULTADO_HEMOSYS CHAR(1 BYTE) DEFAULT 'S' NOT NULL ENABLE,
VALOR_PROCEDIMENTO NUMBER(10,2) DEFAULT 0,
ENVIA_EMAIL_LIBERACAO CHAR(1 BYTE) DEFAULT 'N' NOT NULL ENABLE,
CODIGO_PARDINI VARCHAR2(10 BYTE),
MATERIAL_PARDINI VARCHAR2(10 BYTE),
CONSTRAINT PK_PROCEDIMENTO PRIMARY KEY (CODPROCEDIMENTO)
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES ENABLE,
CONSTRAINT FK_PROCEDIMENTO_CODGRUPO_ETIQ1 FOREIGN KEY (CODGRUPO_ETIQUETA1)
REFERENCES GRUPO_ETIQUETA (CODGRUPO_ETIQUETA) ENABLE,
CONSTRAINT FK_PROCEDIMENTO_CODGRUPO_ETIQ2 FOREIGN KEY (CODGRUPO_ETIQUETA2)
REFERENCES GRUPO_ETIQUETA (CODGRUPO_ETIQUETA) ENABLE,
CONSTRAINT FK_PROCEDIMENTO_CODGRUPO FOREIGN KEY (CODGRUPO_PROCEDIMENTO)
REFERENCES GRUPO_PROCEDIMENTO (CODGRUPO_PROCEDIMENTO) ENABLE,
CONSTRAINT FK_PROCEDIMENTO_CODGRUPO_GUIA FOREIGN KEY (CODGRUPO_PROCEDIMENTO_GUIA)
REFERENCES GRUPO_PROCEDIMENTO (CODGRUPO_PROCEDIMENTO) ENABLE,
CONSTRAINT FK_PROCEDIMENTO_CODGRUPO_LIBER FOREIGN KEY (CODGRUPO_PROCEDIMENTO_LIBERA)
REFERENCES GRUPO_PROCEDIMENTO (CODGRUPO_PROCEDIMENTO) ENABLE,
CONSTRAINT FK_PROCEDIMENTO_CODGRUPO_MAPA FOREIGN KEY (CODGRUPO_PROCEDIMENTO_MAPA)
REFERENCES GRUPO_PROCEDIMENTO (CODGRUPO_PROCEDIMENTO) ENABLE,
CONSTRAINT FK_PROCEDIMENTO_CODLABORATORIO FOREIGN KEY (CODLABORATORIO)
REFERENCES LABORATORIO (CODLABORATORIO) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 262144 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE DADOS;

CREATE INDEX INDEX_PROCEDIMENTO_DESCRICAO ON PROCEDIMENTO (DESCRICAO)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE UNIQUE INDEX INDEX_PROCEDIMENTO_ABREV ON PROCEDIMENTO (ABREV)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_PROCEDIMENTO_ORDEM ON PROCEDIMENTO (ORDEM_LAUDO)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE INDEX INDEX_PROCEDIMENTO_CODGRUPO ON PROCEDIMENTO (CODGRUPO_PROCEDIMENTO, CODPROCEDIMENTO)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 131072 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

CREATE UNIQUE INDEX PK_PROCEDIMENTO ON PROCEDIMENTO (CODPROCEDIMENTO)
PCTFREE 5 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE INDICES;

defferrari · Post by **defferrari** » Mon 18 Apr 2011 20:12

How I configure the UniConnection in OCI mode?. Wich parameters must I set?

AlexP · Post by **AlexP** » Tue 19 Apr 2011 07:40

Hello,

To connect in the OCI mode, you should set the Direct specific option to false like

UniConnection1.SpecificOptions.Values['Direct'] := 'false';

and use the correct TNSName from the tnsnames.ora file

UniConnection1.Server = 'TNSNAME';

also Oracle client should be installed.

defferrari · Post by **defferrari** » Tue 19 Apr 2011 11:25

I set those parameters

object UniConnection1: TUniConnection
ProviderName = 'Oracle'
Server = 'oraclent'
Left = 184
Top = 120
end

but I get the error
---------------------------
Can't initialize OCI. Error -1.
---------------------------

my tnsnames file:
# tnsnames.ora Network Configuration File: C:\Oracle\product\10.2.0\client_1\network\admin\tnsnames.ora
# Generated by Oracle configuration tools.

ORACLENT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.17.1.4)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)

defferrari · Post by **defferrari** » Tue 19 Apr 2011 14:19

I did some tests and I made a discover. The problema only happens when I use the date parameters, so I changed the sql for not use the date parameters

DM.QAux.SQL.Clear;
DM.QAux.SQL.Add('INSERT INTO LOTE_PARDINI_PROCEDIMENTO (CODLOTE_PARDINI, CODEXAME_PROCEDIMENTO)');
DM.QAux.SQL.Add('(SELECT :CODLOTE_PARDINI, EP.CODEXAME_PROCEDIMENTO');
DM.QAux.SQL.Add(' FROM EXAME_PROCEDIMENTO EP, PROCEDIMENTO P');
DM.QAux.SQL.Add(' WHERE EP.DATA_MATERIAL_COLHIDO BETWEEN to_date(''' + FormatDateTime('dd/mm/yyyy', DataIni.Date) + HoraIni.Text + ':00' + ''', ''dd/mm/yyyy hh24:mi:ss'') AND to_date(''' + FormatDateTime('dd/mm/yyyy', DataFim.Date) + HoraFim.Text + ':00' + ''', ''dd/mm/yyyy hh24:mi:ss'')');
DM.QAux.SQL.Add(' AND EP.CODPROCEDIMENTO = P.CODPROCEDIMENTO');
DM.QAux.SQL.Add(' AND P.CODLABORATORIO = :CODLABORATORIO');
DM.QAux.SQL.Add(' AND P.CODIGO_PARDINI IS NOT NULL');
DM.QAux.SQL.Add(' AND EP.CODEXAME_PROCEDIMENTO NOT IN ');
DM.QAux.SQL.Add(' (SELECT CODEXAME_PROCEDIMENTO');
DM.QAux.SQL.Add(' FROM LOTE_PARDINI_PROCEDIMENTO');
DM.QAux.SQL.Add(' WHERE CODLOTE_PARDINI = :CODLOTE_PARDINI))');
DM.QAux.ParamByName('CODLOTE_PARDINI').AsInteger := pbCodLote_Pardini;
DM.QAux.ParamByName('CODLABORATORIO').AsInteger := pvCodLaboratorio;
DM.QAux.Execute;

Still, would be good if this problem was fixed

AlexP · Post by **AlexP** » Thu 21 Apr 2011 08:55

Hello,

Please try to use the asDate property instead of the asDateTime property when assigning values to these parameters