3 Replies Latest reply: Jul 26, 2011 2:29 AM by wkobargs-Oracle RSS

    Some data can't be loaded into the BLOB/CLOB column with SQL Developer

    873590
      Hi,

      When use SQL*Loader (SQL Developer version: 3.0.04) to do migration from SQL Server2008 to Oracle 11, I find a table data can't be loaded.

      The ctl file is:
      load data
      infile 'data/CDS41P2.SPSSCMOR__FILEVERSION.dat'
      "str '<EORD>'"
      into table CDS41P2.SPSSCMOR__FILEVERSION
      fields terminated by '<EOFD>'
      trailing nullcols
      (
      objid ,
      objver ,
      SQLDEVELOPER_CLOB_3 CHAR(2000000) ,
      CONTENTLENGTH ,
      CONTENTLANGUAGE "DECODE(:CONTENTLANGUAGE, CHR(00), ' ', :CONTENTLANGUAGE)",
      EXPIRATIONDATE "TO_TIMESTAMP(:EXPIRATIONDATE, 'yyyy-mm-dd HH24:mi:ss.ff3')"
      )

      When run the load script, there is some error in the log file:
      SQL*Loader-510: Physical record in data file (data/CDS41P2.SPSSCMOR__FILEVERSION.dat) is longer than the maximum(1048576)
      SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

      I tried a work around which is gotten from help Center (Migrating Third-Party Database->Migration:Backgroud Information and Guidelines->Migrating the Data->Workaround)
      But after running the load script (oracle_ctl.bat), I find the same error in the log file.

      So could you please tell me how to deal with this kind of error? Thanks.

      In SQL Server 2008, the table SPSSCMOR__FILEVERSION's DDL is:
      USE [SCDS41P2]
      GO

      /****** Object: Table [dbo].[SPSSCMOR__FILEVERSION] Script Date: 07/25/2011 01:08:21 ******/
      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      SET ANSI_PADDING ON
      GO

      CREATE TABLE [dbo].[SPSSCMOR__FILEVERSION](
           [objid] [binary](18) NOT NULL,
           [objver] [int] NOT NULL,
           [CONTENT] [image] NULL,
           [CONTENTLENGTH] [bigint] NULL,
           [CONTENTLANGUAGE] [nvarchar](128) NULL,
           [EXPIRATIONDATE] [datetime] NULL,
      PRIMARY KEY CLUSTERED
      (
           [objid] ASC,
           [objver] ASC
      )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
      ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

      GO

      SET ANSI_PADDING OFF
      GO

      In Oracle 11, the same table SPSSCMOR__CUSTOMPROPERTYDAY's DDL is:
      --------------------------------------------------------
      -- DDL for Table SPSSCMOR__FILEVERSION
      --------------------------------------------------------

      CREATE TABLE "CDS41P2"."SPSSCMOR__FILEVERSION"
      (     "OBJID" RAW(18),
           "OBJVER" NUMBER(38,0),
           "CONTENT" BLOB,
           "CONTENTLENGTH" NUMBER(38,0),
           "CONTENTLANGUAGE" NVARCHAR2(128),
           "EXPIRATIONDATE" DATE
      ) 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)
      TABLESPACE "USERS"
      LOB ("CONTENT") STORE AS BASICFILE (
      TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192
      NOCACHE LOGGING
      STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)) ;
      --------------------------------------------------------
      -- Constraints for Table SPSSCMOR__FILEVERSION
      --------------------------------------------------------

      ALTER TABLE "CDS41P2"."SPSSCMOR__FILEVERSION" MODIFY ("OBJID" NOT NULL DISABLE);

      ALTER TABLE "CDS41P2"."SPSSCMOR__FILEVERSION" MODIFY ("OBJVER" NOT NULL DISABLE);

      ALTER TABLE "CDS41P2"."SPSSCMOR__FILEVERSION" ADD CONSTRAINT "SYS_C009946" PRIMARY KEY ("OBJID", "OBJVER") DISABLE;
        • 1. Re: Some data can't be loaded into the BLOB/CLOB column with SQL Developer
          wkobargs-Oracle
          Hello,

          before you tried the workaround, did you enable the Generate Stored Procedure for Migrate Blobs Offline SQL Developer preference?

          Did you already try to increase the READSIZE for SQL*Loader in your .bat file? 1048576 is the default. Please read the following note in My Oracle Support:

          SQLLDR NOT LOADING ALL DATA IN DAT FILE : SQL*Loader-510/SQL*Loader-2026 (Doc ID 741100.1)

          Regards
          Wolfgang
          • 2. Re: Some data can't be loaded into the BLOB/CLOB column with SQL Developer
            873590
            Hi Wolfgang,

            Yes. I have enabled the Generate Stored Procedure for Migrate Blobs Offline SQL Developer preference.

            Additional, I don't know how to increase the READSIZE for SQL*Loader and which bat file I should add this in.

            Could you please give me some advice?

            I can't find the following note anywhere, too.

            So could you please tell me how to find the following note:

            SQLLDR NOT LOADING ALL DATA IN DAT FILE : SQL*Loader-510/SQL*Loader-2026 (Doc ID 741100.1).

            Thanks.
            • 3. Re: Some data can't be loaded into the BLOB/CLOB column with SQL Developer
              wkobargs-Oracle
              Hello,

              I assumed that you are using a bat file because you are using an offline migration, and I refer to the documentation here:


              Populating the Destination Database Using the Data Files

              To populate the destination database using the data files, you run the data load scripts using SQL*Loader:

              Navigate to the directory where you created the data unload scripts.

              Edit the oracle_ctl.bat (Windows systems) or oractl_ctl.sh (Linux or UNIX systems) file, to provide the appropriate user name and password strings.

              Run the SQL Load script.

              On Windows, enter:
              prompt> oracle_ctl.bat

              On Linux or UNIX, enter:
              prompt> ./oracle_ctl.sh

              About the note, I implicitly expected that everybody who is working with Oracle has a support contract and as such access to My Oracle Support (MOS). It is a MOS note, and if you are registered in MOS (with your support contract ID) then you should be able to find the note.

              Anyhow, for your convenience I am pasting the note content here:

              Symptoms

              The following symptoms are attributed to this issue :-

              +1) SQLLDR is being used to load a datafile whose contents are not considered to contain invalid records.+

              +2) Not all records get loaded and in the logs we see messages similar to :-+

              +SQL*Loader-510: Physical record in data file ([xx].[xx].[xx].dat) is longer than the maximum(1048576)+
              SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
              Specify SKIP=xx when continuing the load.

              Cause

              The root cause is not a bug itself but infact linked to the READSIZE parameter of SQLLDR being
              set too low as partially discussed under BUG:5900598 which is itself for another problem but explains that the increase of READSIZE was needed to resolve.

              Solution

              Specify READSIZE in the SQLLDR syntax e.g. :-

              sqlldr u/p control=control.ctl log=loader.log

              would become

              sqlldr u/p control=control.ctl readsize=20971520 log=loader.log

              A value for READSIZE of 20MB should be sufficient for most issues but in <=V10203
              it is possible that BLOB/CLOB data might still fail and a fix would be needed for larger
              values to be defined, this is beyond the scope of this article.

              Attempting to set READSIZE above its limit results in errors like:
              SQL*Loader-569: READSIZE parameter exceeds maximum value xxxxxxx for platform

              It should be noted that the SQL*Loader-510/SQL*Loader-2026 errors are quite generic
              so if an issue still remains and no solution can be found it would be necessary to raise a new
              SR to ORACLE Support.

              References
              BUG:5900598 - OMWB SQL*LOADER SCRIPS RESULT IN SQL*LOADER-510 ERROR

              Regards
              Wolfgang