3 Replies Latest reply: Dec 14, 2012 3:30 AM by 979849 RSS

    ORA-01400 ccannot insert NULL into ("SYS"."SUMINLINE$"."TEXT")

    979849
      Hi,

      Firstly I will apologise that I cannot provide too much information as my client is off line and remote. The SQL I will discuss works on both his test and my Oracle XE solution. But on live we get the folllowing error:

      ORA-00604: error occurred at recursive SQL level 1
      ORA-01400: cannot insert NULL into ("SYS"."SUMINLINE$"."TEXT").

      I have never posted to this forum but I cannot understand what the table SUMINLINE$ even does and it's relevance. Please also excuse if there is a better way to write the SQL), anyhow any guideance would be useful.

      I thought it maybe down to how I am using rownum (this is a complete guess) but as it is materialized in the previous view I don't see how this could be)

      Anyhow if anyone is good enough to spend some of there time looking at the SQL below I would be eternally grateful.

      -----------------------------------------------------
      STEP 1: I have a base materialised view:
      (it uses the "rownum" as a primary key as a primary key is required later and this view doesn't have
      a primary key as it is a complex view)
      -------------------------------------------------------

      CREATE MATERIALIZED VIEW bo_socrates.baseCSIIdentPerPerson
      TABLESPACE bo_socrates
      PCTUSED 0
      PCTFREE 10
      INITRANS 2
      MAXTRANS 255
      STORAGE (
      INITIAL 1040K
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      PCTINCREASE 0
      BUFFER_POOL DEFAULT
      )
      NOCACHE
      NOLOGGING
      NOPARALLEL
      BUILD IMMEDIATE
      REFRESH COMPLETE
      WITH PRIMARY KEY
      AS
      SELECT rownum iid,
      csi.attendId,
      csi.sceneId,
      csi.caseId,
      csi.identDate,
      csi.identType,
      csi.subIdenttype,
      csi.suspectId,
      csi.surname,
      csi.forenames,
      csi.CRO,
      csi.ConfirmedIdent
      FROM bus_objects.v_factCSIIdentPerPerson csi
      ;

      CREATE INDEX bo_socrates.ind_baseCSIIdentPerson_Id on bo_socrates.baseCSIIdentPerPerson(id)
      TABLESPACE bo_socrates
      STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED) NOLOGGING;


      -----------------------------------------------------
      STEP 2: This materialised view (my fact table) uses the previous materilalized view
      as a point to calculate the fact date. Its aim for example the bottom "unit" is people
      but I also need to know which people arrived first, which people arrived first with blue hats, etc.
      So I use a with statements to find out these facts.
      **PLEASE NOTE IT IS THIS VIEW THAT RAISES THE ERROR WHEN THE SCRIPT IS RUN**
      If there is a simpler way to write this then please let me know
      This view basically uses the primary key (id) generated in the above view using rownum
      -------------------------------------------------------
      CREATE MATERIALIZED VIEW bo_socrates.factCSIIdent
      TABLESPACE bo_socrates
      PCTUSED 0
      PCTFREE 10
      INITRANS 2
      MAXTRANS 255
      STORAGE (
      INITIAL 1040K
      MINEXTENTS 1
      MAXEXTENTS UNLIMITED
      PCTINCREASE 0
      BUFFER_POOL DEFAULT
      )
      NOCACHE
      NOLOGGING
      NOPARALLEL
      BUILD IMMEDIATE
      REFRESH COMPLETE
      WITH PRIMARY KEY
      AS
      With
      firstAttend As (
      Select Attendid, Min(IdentDate) Identdate,
      Min(Id) Id,
      Min(Case When identtype = 0 Then Id Else Null End) Fpid,
      Min(Case When identtype = 1 Then Id Else Null End) Dnaid,
      Min(Case When identtype = 2 Then Id Else Null End) Fwid,
      Min(Case When Confirmedident = 1 Then Id Else Null End) Idconfirm,
      Min(Case When identtype = 0 And Confirmedident = 1 Then Id Else Null End) Fpidconfirm,
      Min(Case When identtype = 1 And Confirmedident = 1 Then Id Else Null End) Dnaidconfirm,
      Min(Case When identtype = 2 and confirmedident = 1 Then Id Else Null End) FwidConfirm
      From Bo_Socrates.Basecsiidentperperson
      GROUP BY Attendid),
      firstInd AS (
      Select Attendid, SuspectId, Min(IdentDate) Identdate, Min(Id) Id,
      Min(Case When identtype = 0 Then Id Else Null End) Fpid,
      Min(Case When identtype = 1 Then Id Else Null End) Dnaid,
      Min(Case When identtype = 2 Then Id Else Null End) Fwid
      From bo_socrates.BaseCSIIdentPerPerson
      Group By Attendid, Suspectid)
      Select csi.id,
      csi.attendId,
      csi.sceneId,
      csi.caseId,
      csi.identDate,
      csi.identtype,
      csi.subIdenttype,
      csi.suspectId,
      csi.surname,
      csi.forenames,
      csi.CRO,
      Csi.Confirmedident,
      Case When Ind.Id Is Null Then 0 Else 1 End Firstindident,
      Case When Ind.Fpid Is Null Then 0 Else 1 End Firstindidentfp,
      Case When Ind.DNAid Is Null Then 0 Else 1 End FirstindidentDNA,
      Case When Ind.FWid Is Null Then 0 Else 1 End FirstIndIdentFW,
      Case When Att.Id Is Null Then 0 Else 1 End Firstattident,
      Case When Attfp.Fpid Is Null Then 0 Else 1 End Firstattidentfp,
      Case When AttDNA.DNAid Is Null Then 0 Else 1 End FirstattidentDNA,
      Case When Attfw.Fwid Is Null Then 0 Else 1 End Firstattidentfw,
      Case When Att.IdConfirm Is Null Then 0 Else 1 End FirstattidentConfirm,
      Case When Attfpc.Fpidconfirm Is Null Then 0 Else 1 End Firstattidentfpconfirm,
      Case When AttDNAc.DNAidconfirm Is Null Then 0 Else 1 End FirstattidentDNAconfirm,
      Case When AttFWc.FWidconfirm Is Null Then 0 Else 1 End FirstattidentFWconfirm
      From Bo_Socrates.Basecsiidentperperson Csi
      Left Outer Join Firstind Ind On Csi.Id = Ind.Id
      Left Outer Join Firstind Indfp On Csi.Id = Indfp.Fpid
      Left Outer Join Firstind Inddna On Csi.Id = Inddna.Dnaid
      Left Outer Join Firstind IndFW On Csi.Id = IndFW.FWid
      Left Outer Join Firstattend Att On Csi.Id = Att.Id
      Left Outer Join Firstattend Attfp On Csi.Id = Attfp.Fpid
      Left Outer Join Firstattend Attdna On Csi.Id = Attdna.Dnaid
      Left Outer Join Firstattend Attfw On Csi.Id = Attfw.Fwid
      Left Outer Join Firstattend Attfpc On Csi.Id = AttFPc.Fpidconfirm
      Left Outer Join Firstattend Attdnac On Csi.Id = AttDNAc.DNAidconfirm
      Left Outer Join Firstattend Attfwc On Csi.Id = AttFWc.Fwidconfirm
      ;


      CREATE INDEX bo_socrates.ind_factCSIIdent_Id on bo_socrates.factCSIIdent(attendId)
      TABLESPACE bo_socrates
      STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED) NOLOGGING;

      CREATE INDEX bo_socrates.ind_factCSIIdent_date on bo_socrates.factCSIIdent(identDate)
      TABLESPACE bo_socrates
      STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED) NOLOGGING;

      CREATE INDEX bo_socrates.ind_factCSISubIdent_type on bo_socrates.factCSIIdent(subidentType)
      TABLESPACE bo_socrates
      STORAGE (INITIAL 64K MINEXTENTS 1 MAXEXTENTS UNLIMITED) NOLOGGING;


      COMMIT;
        • 1. Re: ORA-01400 ccannot insert NULL into ("SYS"."SUMINLINE$"."TEXT")
          Srini Chavali-Oracle
          Pl post details of OS and database versions. This is likely a bug - pl see these MOS Docs

          Error ORA-01400 When Creating MView With ANSI Join [ID 1495499.1]
          While Alter Materialized View Compile Then Error ORA-01400 Occurs [ID 1377447.1]

          As the docs state, a fix is available only in the next release of Oracle (12g) - I would suggest you open an SR with Support

          HTH
          Srini
          • 2. Re: ORA-01400 ccannot insert NULL into ("SYS"."SUMINLINE$"."TEXT")
            979849
            Thanks for you prompt answer.

            I will get the details from my client tomorrow, especially the difference between their test system and the live.

            I will then get them raise a call with Oracle through their support line, although for now do you think the best method is to re-write the SQL?

            Do you suggest re-writting it with:

            FROM X,Y WHERE X = Y(+)

            ??

            I have only started using ansii joins being an old school Oracle user :).... that will teach me.

            I will let you know how I get on.

            All the very best regards

            Matt.
            • 3. Re: ORA-01400 ccannot insert NULL into ("SYS"."SUMINLINE$"."TEXT")
              979849
              A massive thanks.

              Just for anyone else reading this thread the following information is useful:

              1) Works fine on Oracle 10g
              2) Fails on Oracle 11g.

              The fix was to change the ANSI JOINS with the standard Oracle FROM X,Y WHERE X.ID = Y.ID(+)

              I also had to move the whole select section from the last materialized view into a standard view, then reference that view in the materialized view.

              You have just saved me many hours of wasted time.

              Kiind Regards.

              Have a great Christmas.

              Thanks

              Matt.

              (PS YOu can see I am pleased, and so is my client)