JR29792: ORACLE OCI PLUGIN: GETTING ERROR "ORA-01008: NOT ALL VARIABLES BOUND" WHEN USING CUSTOM SQL, SAME EXACT SQL WORKS AS GENERATED

APAR status

• Closed as program error.

Error description

• Oracle OCI plugin: Getting error "ORA-01008: not all variables
  bound" when using custom SQL, same exact SQL works as generated
  SQL.
  
  Information Server 8.0.1, Windows 2003, Oracle 10g server and
  client.
  
  
  
  
  When defining a lookup to a table in a job, I'm using the Oracle
  OCI stage.
  
  If I use the "Generate SELECT clause from colum list" option,
  the select is generated as follows:
  
  "SELECT A.ID_CAT_CTAS,A.CD_CAT_CTA FROM
  SIGSOR_DES.TSIG_DIM_HIST_CAT_CTAS
  A WHERE A.CD_CAT_CTA=:2"
  
  this one works allright, notice that the place holder is ":2".
  
  If we change the to the option "Enter custom SQL", but use the
  same query, we get the error "ORA-01008: not all variables
  bound", "DSP.Open GCI $DSP.Open error -100".  If we change the
  place holder from ":2" to ":1", it works.
  
  It is not just only the "view data"
  command that is not working using the place holder ":2" in the
  "Enter custom SQL", but the execution doesn't work either.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  Oracle OCI Stage
  ****************************************************************
  PROBLEM DESCRIPTION:
  Oracle OCI plugin: Getting error "ORA-01008: not all variables
        bound" when using
  custom SQL, same exact SQL works as        generated
             SQL.
  
         Information Server 8.0.1 fp1, Windows
  2003, Oracle 10g server   and
       client.
  
   The file location contains a job for recreating this error
  alongwith the structure of the table for the test.
            When defining a
  lookup to a table in a job, I'm using the  Oracle
        OCI stage.
  
           If I use the "Generate SELECT clause
  from colum list" option,
       the select is generated as follows:
            "SELECT
  A.ID_CAT_CTAS,A.CD_CAT_CTA FROM
  
  SIGSOR_DES.TSIG_DIM_HIST_CAT_CTAS
  2"
             this
  one works allright, notice that the place holder is
  ":2".
         If we change the to
  the option "Enter custom SQL", but use      the
             same query, we
  get the error "ORA-01008: not all variables
       bound", "DSP.Open GCI $DSP.Open
  error -100".  If we change  the
      place holder from ":2" to
  ":1", it works.
           at is
  not just only the "view data"
         command that is not working using the place holder
  ":2" in the      "Enter
  custom SQL", but the execution doesn't work either.
  ****************************************************************
  RECOMMENDATION:
  Install the patch:
  
  patch_JR29792_client_windows_801fp1.zip
  
  This change is included in 8.0.1 Fix Pack 2.
  ****************************************************************
  

Problem conclusion

• Problem is fixed in patch for 8.0.1 fp1
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R801 PSN

     UP