0 Replies Latest reply: Nov 26, 2013 6:02 PM by user8948418 RSS

    Missing blob value for one row from a join.

    user8948418

      The issue is wrong data returned by the simple query below. The issue only happen when the query is run via the Oracle OLEDB driver. In that case, in just one row out of several hundred, a null value is returned for a blob field that has an actual value. All other values seem to be correct. The same query when run from Toad or SQLPlus returns the correct bytes for all rows.

       

      The query is:

       

      select MF_LIBRARY_WORKFLOW.id, stepID,functionID,evaluationorder,parameters from MF_LIBRARY_WORKFLOW INNER JOIN MF_LIBRARY_STEP ON MF_LIBRARY_WORKFLOW.StepID=MF_LIBRARY_STEP.ID where MF_LIBRARY_WORKFLOW.taskid is null and MF_LIBRARY_STEP.taskid=21

       

      The query returns about 300 rows. The issue is with one row. In my db the row number is 2784.  This row has 111 bytes in the “Parameters” column. However the returned value from the above query is null.

       

       

      If I change the query by requesting only the data for this particular row. I.E:

       

      select MF_LIBRARY_WORKFLOW.id, stepID,functionID,evaluationorder,parameters from MF_LIBRARY_WORKFLOW INNER JOIN MF_LIBRARY_STEP ON MF_LIBRARY_WORKFLOW.StepID=MF_LIBRARY_STEP.ID where MF_LIBRARY_WORKFLOW.taskid is null and MF_LIBRARY_STEP.taskid=21 and MF_LIBRARY_WORKFLOW.id = 2784

       

      I now get back one row of data and the PARAMETERS column has the correct blob bytes.

       

      If I change the query to return both the PARAMETERS blob and the length of the blob I get the interesting result of null with length of 111. See the attached file.

       

      The issue is not with the bytes of the blob, because when I replaced this blob with blob from a “good” row (One that was being returned properly.) the issue was still there.

       

      I have produce this same error in 2 version of Oracle\OLEDB drivers. One is Oracle Database 10g Express Edition Release 10.2.0.1.0. The version of the OLEDB driver is 11.1.0.6

       

      The other is Oracle 11. The version of the OLEDB driver is 11.2.0.

       

      The OLEDB connections string I use is:  Provider=OraOLEDB.Oracle;Data Source=[SERVER];User Id=[USER];Password=[PASSWORD];OLEDB.NET=True

      The version of .Net I use is 2.0.

       

      A backup of a XE database that manifest the error is available.

       

      The definitions of the two tables used in the query are:

       

      CREATE TABLE "MF_LIBRARY_WORKFLOW"

      (    "ID" NUMBER(38,0) NOT NULL ENABLE,

      "TASKID" NUMBER(38,0),

      "STEPID" NUMBER(38,0),

      "WORKFLOWTYPE" NUMBER(38,0),

      "EVALUATIONORDER" NUMBER(10,0),

      "FUNCTIONID" NVARCHAR2(40),

      "PARAMETERS" BLOB,

             CONSTRAINT "MF_LIBRARY_WORKFLOW_PK" PRIMARY KEY ("ID")

        )

           

        CREATE TABLE "SYSTEM"."MF_LIBRARY_STEP"

         (    "ID" NUMBER(38,0) NOT NULL ENABLE,

      "NAME" NVARCHAR2(255),

      "TASKID" NUMBER(38,0),

      "ORDINAL" NUMBER(10,0),

      "INPUTTYPE" NUMBER(10,0),

      "INPUTMECHANISM" NUMBER(10,0),

      "DEFAULTVALUE" NVARCHAR2(255),

      "REPORTCOLUMNNAME" NVARCHAR2(80),

              "ATTRIBUTES" BLOB,

               CONSTRAINT "MF_LIBRARY_STEP_PK" PRIMARY KEY ("ID")

          )

       

      Any idea what this issue is?

       

      Thanks.