We got an intermittent problem with one of our procedure. I have isolated the part that is the most problematic.
SELECT LinkedItemsSum.ikInterv_ass_casecart,
LinkedItemsSum.nQte_Prn + LinkedItemsSum.nQte_NonPrn AS nQuantite,
LinkedItemsSum.ikProduit_Cardex,
Produit_Cardex.cDesc_Produit,
Produit_Cardex.nPrix,
Produit_Cardex.cNo_Inventaire,
Produit_Cardex.cNo_Produit,
Produit_Cardex.cImputation,
Famille_Cardex.cFamille,
Produit_Cardex.cEtape,
LinkedItemsSum.lSuivre,
LinkedItemsSum.lHoraire,
LinkedItemsSum.lFacture,
LinkedItemsSum.lSurgeon_Follow,
Famille_Cardex.ikFamille_Cardex,
LinkedItemsSum.nQte_Prn,
LinkedItemsSum.nQte_NonPrn,
LinkedItemsSum.lTracable,
Produit_Cardex.cDesc_officielle,
Produit_Cardex.cNo_catalogue,
Produit_Cardex.cUnite_cons,
Produit_Cardex.ddt_modif,
Produit_Cardex.nCout_Acquisition,
Produit_Cardex.nUtilisationsPossibles,
Produit_Cardex.nCout_sterilisation,
Produit_Cardex.nCout_preparation,
Produit_Cardex.nCout_conditionnement,
Produit_Cardex.lMRP,
Unite_stockage.ikUnite_stockage,
Unite_stockage.cUnite_stockage,
StockRoom_Produit.cSection,
StockRoom_Produit.cLocalisation,
Produit_Cardex.cInstruction,
Fournisseur_Cardex.ikFournisseur_Cardex,
Fournisseur_Cardex.cNom_fournisseur,
Emplacement_CaseCart.ikEmplacement_CaseCart,
Emplacement_CaseCart.cEmplacement_CaseCart,
Produit_Cardex.iType_Produit,
Source_Interne.ikSource_Interne,
Source_Interne.cSource_interne,
Moment_cons.ikMoment_cons,
Moment_cons.cMoment_cons,
0 AS iSource_modif,
Produit_Cardex.nMark_Up,
Produit_Cardex.nPatient_Charge,
Produit_Cardex.cUpn,
Produit_Cardex.bConsigned,
Produit_Cardex.bLatex
FROM (SELECT TT_MM_PREF_CARDS.ikInterv_ass_casecart,
TT_MM_PREF_CARDS.ikInterventions_associees,
CaseCart.ikCaseCart,
CaseCart_Prod.ikProduit_cardex,
CASE WHEN SUM(CASE WHEN CaseCart_Prod.lSuivre = 0 THEN 0 ELSE 1 END) >= 1 THEN 1 ELSE 0 END as lSuivre,
CASE WHEN SUM(CASE WHEN CaseCart_Prod.lHoraire = 0 THEN 0 ELSE 1 END) >= 1 THEN 1 ELSE 0 END as lHoraire,
CASE WHEN SUM(CASE WHEN CaseCart_Prod.lSurgeon_follow = 0 THEN 0 ELSE 1 END) >= 1 THEN 1 ELSE 0 END as lSurgeon_follow,
CASE WHEN SUM(CASE WHEN CaseCart_Prod.lFacture = 0 THEN 0 ELSE 1 END) >= 1 THEN 1 ELSE 0 END as lFacture,
CASE WHEN SUM(CASE WHEN CaseCart_Prod.lTracable = 0 THEN 0 ELSE 1 END) >= 1 THEN 1 ELSE 0 END as lTracable,
SUM(CASE WHEN TT_MM_PREF_CARDS.lPrn = 1 OR CaseCart_Prod.lPrn = 1 THEN nQte ELSE 0 END) as nQte_Prn,
SUM(CASE WHEN TT_MM_PREF_CARDS.lPrn = 0 AND CaseCart_Prod.lPrn = 0 THEN nQte ELSE 0 END) as nQte_NonPrn
FROM TT_MM_PREF_CARDS
INNER JOIN CaseCart ON CaseCart.ikCaseCart = TT_MM_PREF_CARDS.ikCaseCart
INNER JOIN CaseCart CaseCart_All ON CaseCart.ikCaseCart = CaseCart_All.ikCaseCart OR
(CaseCart.ikIntervenant_chirurgien IS NOT NULL AND
CaseCart_All.ikIntervention_cardex IS NULL AND
CaseCart_All.ikGrpe_interv_cardex = CaseCart.ikGrpe_interv_cardex AND
CaseCart_All.ikLogistic_site_ref = CaseCart.ikLogistic_site_ref) OR
(CaseCart.ikIntervenant_chirurgien IS NOT NULL AND
CaseCart_All.ikIntervention_cardex IS NOT NULL AND
CaseCart_All.ikIntervenant_chirurgien IS NULL AND
CaseCart_All.ikGrpe_interv_cardex = CaseCart.ikGrpe_interv_cardex AND
CaseCart_All.ikIntervention_cardex = CaseCart.ikIntervention_cardex AND
CaseCart_All.ikLogistic_site_ref = CaseCart.ikLogistic_site_ref) OR
(CaseCart.ikIntervenant_chirurgien IS NULL AND
CaseCart.ikIntervention_cardex IS NOT NULL AND
CaseCart_All.ikIntervention_cardex IS NULL AND
CaseCart_All.ikGrpe_interv_cardex = CaseCart.ikGrpe_interv_cardex AND
CaseCart_All.ikLogistic_site_ref = CaseCart.ikLogistic_site_ref)
INNER JOIN CaseCart_Prod ON CaseCart_Prod.ikCaseCart = CaseCart_All.ikCaseCart
WHERE TT_MM_PREF_CARDS.lGenericLink = 1
GROUP BY TT_MM_PREF_CARDS.ikInterv_ass_casecart,
TT_MM_PREF_CARDS.ikInterventions_associees,
CaseCart.ikCaseCart,
CaseCart_Prod.ikProduit_cardex) LinkedItemsSum
INNER JOIN Interventions_Associees ON LinkedItemsSum.ikInterventions_associees = Interventions_Associees.ikInterventions_associees
INNER JOIN Requete ON Requete.ikRequete = Interventions_Associees.ikRequete
INNER JOIN Visite ON Visite.ikVisite = Requete.ikVisite
INNER JOIN Produit_Cardex ON LinkedItemsSum.ikProduit_cardex = Produit_cardex.ikProduit_cardex
LEFT OUTER JOIN StockRoom_Produit ON StockRoom_Produit.ikProduit_cardex = Produit_Cardex.ikProduit_cardex AND
StockRoom_Produit.bPrimary = 1 AND
StockRoom_Produit.ikUnite_traitement = Visite.ikUnite_traitement
LEFT OUTER JOIN Famille_cardex ON Produit_cardex.ikFamille_cardex = Famille_cardex.ikFamille_cardex
LEFT OUTER JOIN Unite_stockage ON StockRoom_Produit.ikUnite_stockage = Unite_stockage.ikUnite_stockage
LEFT OUTER JOIN Fournisseur_cardex ON Produit_cardex.ikFournisseur_cardex = Fournisseur_cardex.ikFournisseur_cardex
LEFT OUTER JOIN Emplacement_caseCart ON Produit_cardex.ikEmplacement_caseCart = Emplacement_caseCart.ikEmplacement_caseCart
LEFT OUTER JOIN Source_interne ON Produit_cardex.ikSource_interne = Source_interne.ikSource_interne
LEFT OUTER JOIN Moment_cons ON Produit_cardex.ikMoment_cons = Moment_cons.ikMoment_cons
WHERE LinkedItemsSum.nQte_Prn > 0 OR
LinkedItemsSum.nQte_NonPrn > 0;
Here is the script of the table that we think is problematic
CREATE TABLE "OPERA30"."PRODUIT_CARDEX"
( "IKPRODUIT_CARDEX" NUMBER(*,0) NOT NULL ENABLE,
"IKLOGISTIC_SITE_REF" NUMBER(*,0) NOT NULL ENABLE,
"IKFAMILLE_CARDEX" NUMBER(*,0),
"IKFOURNISSEUR_CARDEX" NUMBER(*,0),
"IKEMPLACEMENT_CASECART" NUMBER(*,0),
"IKSOURCE_INTERNE" NUMBER(*,0),
"IKMOMENT_CONS" NUMBER(*,0),
"CDESC_PRODUIT" VARCHAR2(80 BYTE),
"NPRIX" NUMBER(19,4),
"NQUANTITE" NUMBER(*,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"CNO_INVENTAIRE" VARCHAR2(20 BYTE),
"CNO_PRODUIT" VARCHAR2(20 BYTE),
"CIMPUTATION" VARCHAR2(40 BYTE),
"CETAPE" VARCHAR2(30 BYTE),
"LSUIVRE" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"LHORAIRE" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"LTRACABLE" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"LPRN" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"CDESC_OFFICIELLE" VARCHAR2(80 BYTE),
"CNO_CATALOGUE" VARCHAR2(20 BYTE),
"CUNITE_CONS" VARCHAR2(40 BYTE),
"DDT_MODIF" TIMESTAMP (8),
"NCOUT_ACQUISITION" NUMBER(19,4) DEFAULT ( 0 ) NOT NULL ENABLE,
"NUTILISATIONSPOSSIBLES" NUMBER(*,0) DEFAULT ( 1 ) NOT NULL ENABLE,
"NCOUT_STERILISATION" NUMBER(19,4) DEFAULT ( 0 ) NOT NULL ENABLE,
"NCOUT_PREPARATION" NUMBER(19,4) DEFAULT ( 0 ) NOT NULL ENABLE,
"NCOUT_CONDITIONNEMENT" NUMBER(19,4) DEFAULT ( 0 ) NOT NULL ENABLE,
"LMRP" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"CINSTRUCTION" VARCHAR2(100 BYTE),
"ITYPE_PRODUIT" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"LFACTURE" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"NFACT_PRIX" NUMBER(22,2) DEFAULT ( 0 ) NOT NULL ENABLE,
"NLEAD_TIME" NUMBER(*,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"NMARK_UP" NUMBER(22,2) DEFAULT ( 0 ) NOT NULL ENABLE,
"NPATIENT_CHARGE" NUMBER(19,4) DEFAULT ( 0 ) NOT NULL ENABLE,
"LSURGEON_FOLLOW" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"CUPN" VARCHAR2(30 BYTE),
"BCONSIGNED" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"BLATEX" NUMBER(1,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"INB_LINKED_CASECART" NUMBER(*,0) DEFAULT ( 0 ) NOT NULL ENABLE,
"IMGITEM_PICTURE" BLOB,
"CUSAGE_CASE" VARCHAR2(255 BYTE),
"NFACT_STOCK" NUMBER(22,2) DEFAULT ( 1 ) NOT NULL ENABLE,
CONSTRAINT "PK_PRODUIT_CARDEX" PRIMARY KEY ("IKPRODUIT_CARDEX") ENABLE,
CONSTRAINT "CK_PRODUIT_CARDEX_BCONSIGNED" CHECK (
bConsigned = 0 OR bConsigned = 1
) ENABLE,
CONSTRAINT "CK_PRODUIT_CARDEX_BLATEX" CHECK (
bLatex = 0 OR bLatex = 1
) ENABLE,
CONSTRAINT "CK_PRODUIT_CARDEX_LFACTURE" CHECK (
lFacture = 0 OR lFacture = 1
) ENABLE,
CONSTRAINT "CK_PRODUIT_CARDEX_LHORAIRE" CHECK (
lHoraire = 0 OR lHoraire = 1
) ENABLE,
CONSTRAINT "CK_PRODUIT_CARDEX_LMRP" CHECK (
lMRP = 0 OR lMRP = 1
) ENABLE,
CONSTRAINT "CK_PRODUIT_CARDEX_LPRN" CHECK (
lPRN = 0 OR lPRN = 1
) ENABLE,
CONSTRAINT "CK_PRODUIT_CARDEX_LSUIVRE" CHECK (
lSuivre = 0 OR lSuivre = 1
) ENABLE,
CONSTRAINT "CK_PRODUIT_CARDEX_LTRACABLE" CHECK (
lTracable = 0 OR lTracable = 1
) ENABLE,
CONSTRAINT "CK_PROD_CARDEX_ITYPE_PRODUIT" CHECK (
iType_produit = 2 OR (iType_produit = 1 OR iType_produit = 0)
) ENABLE,
CONSTRAINT "CK_PROD_CARDEX_LSURGEON_FOLLOW" CHECK (
lSurgeon_Follow = 0 OR lSurgeon_Follow = 1
) ENABLE,
CONSTRAINT "CK_PROD_CARDEX_NUTILSATPOSSIBL" CHECK (nUtilisationsPossibles > 0) ENABLE,
CONSTRAINT "FK_PROD_CARDX_FOURNISSEU_CARDX" FOREIGN KEY ("IKFOURNISSEUR_CARDEX")
REFERENCES "OPERA30"."FOURNISSEUR_CARDEX" ("IKFOURNISSEUR_CARDEX") ENABLE,
CONSTRAINT "FK_PROD_CARDX_FAMILLE_CARDX" FOREIGN KEY ("IKFAMILLE_CARDEX")
REFERENCES "OPERA30"."FAMILLE_CARDEX" ("IKFAMILLE_CARDEX") ENABLE,
CONSTRAINT "FK_PROD_CARDX_EMPLACEM_CASCART" FOREIGN KEY ("IKEMPLACEMENT_CASECART")
REFERENCES "OPERA30"."EMPLACEMENT_CASECART" ("IKEMPLACEMENT_CASECART") ENABLE,
CONSTRAINT "FK_PROD_CARDX_SOURCE_INTERNE" FOREIGN KEY ("IKSOURCE_INTERNE")
REFERENCES "OPERA30"."SOURCE_INTERNE" ("IKSOURCE_INTERNE") ENABLE,
CONSTRAINT "FK_PRODUIT_CARDEX_MOMENT_CONS" FOREIGN KEY ("IKMOMENT_CONS")
REFERENCES "OPERA30"."MOMENT_CONS" ("IKMOMENT_CONS") ENABLE,
CONSTRAINT "FK_PROD_CARDX_LOGISTIC_SITE_RF" FOREIGN KEY ("IKLOGISTIC_SITE_REF")
REFERENCES "OPERA30"."LOGISTIC_SITE_REF" ("IKLOGISTIC_SITE_REF") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "OPERA30_LOOKUPS"
PCTTHRESHOLD 50
LOB ("IMGITEM_PICTURE") STORE AS (
TABLESPACE "OPERA30_BLOB" DISABLE STORAGE IN ROW CHUNK 8192 PCTVERSION 0
NOCACHE LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
When i call the procedure that contain the above code from my VB.NET application, I get the following error message : ORA-03114: not connected to ORACLE. The error message doesn't help at all because this only happen when with certain parameters.
This is the part of a search procedure. If i do a search from '2007-11-01' to '2007-12-01', I won't get any error message. If i do a search from '2007-12-02' to '2007-12-03' i get this error.
For now, we don't really have a clue of what is causing this problem but here are our hypothesis:
The Produit_Cardex contains a BLOB column that is used to store images. This is the only table that contain image that we use a lot in the application. The first time we got the error, we decided to create a table space for the image and we though that it corrected the problem but recently, the problem appeared again. I don't know if it is possible that just having a BLOB field in the table could make the index of the table to corrupt. What is strange is that the image column doesn't contain any image for now.
The only hint that we found is that when we recreate the table with it's rows, the problem disappear for a while.
Does anyone have an idea of the real cause of the problem or of the solution to correct that issue.
Your help would be appreciated.