Selecting CLOB in Oracle using C and embedded SQL
<p>Perhaps somebody here can help me; I've run out of ideas.</p>

<p>I try to implement an select statement on a table containing a CLOB in Oracle. This is the definition of the table:</p>

<pre><code>PQUE
 ID                    NOT NULL NUMBER(13)
 PGA_ID                NUMBER(13)
 WS_CODE               NUMBER(5)
 OPERATION             VARCHAR2(30)
 RF_NR                 NUMBER(13)
 STATUS                NUMBER(5)
 SENDER                NUMBER(5)
 EMPFAENGER            NUMBER(5)
 START_ZEIT            TIMESTAMP(6)
 DATEN                 CLOB
</code></pre>

<p>My SELECT-Statement looks this way:</p>

<pre><code>void pap_pque_select(long        *sql_ptr,
                     PQUE_RECORD *pque_ptr)
{
   OCIClobLocator *clob=NULL ;
   long           bufsize=4294967295, start=1, i=0 ;
   long           old_size, new_size=1 ;
   char           buffer[2048] ;

   EXEC SQL ALLOCATE :clob ;
   EXEC SQL
      SELECT   PQUE.ID,
               PQUE.PGA_ID,
               PQUE.WS_CODE,
               NVL(PQUE.OPERATION, ' '),
               PQUE.RF_NR,
               PQUE.STATUS,
               PQUE.SENDER,
               PQUE.EMPFAENGER,
               TO_CHAR(PQUE.START_ZEIT, 'YYYYMMDDHH24MISSFF2'),
               NVL(PQUE.DATEN, EMPTY_CLOB())
        INTO   :(pque_ptr-&gt;pque_id),
               :(pque_ptr-&gt;pga_id),
               :(pque_ptr-&gt;ws_code),
               :(pque_ptr-&gt;operation),
               :(pque_ptr-&gt;rf_nr),
               :(pque_ptr-&gt;status),
               :(pque_ptr-&gt;sender),
               :(pque_ptr-&gt;empfaenger),
               :(pque_ptr-&gt;start_zeit),
               :clob
        FROM   PQUE
       WHERE   PQUE.ID = :(pque_ptr-&gt;pque_id) ;
   *sql_ptr = sqlca.sqlcode ;

   if (sqlca.sqlcode == 0)
   {
      ds_trim(pque_ptr-&gt;operation) ;
      do
      {
         if (i++ == 0)
            EXEC SQL LOB READ :bufsize FROM :clob AT :start INTO :buffer ;
         else
            EXEC SQL LOB READ :bufsize FROM :clob INTO :buffer ;
         if (sqlca.sqlcode == 0 || sqlca.sqlcode == 1403)
         {
            old_size = new_size ;
            new_size += bufsize ;
            if (pque_ptr-&gt;daten == NULL)
               pque_ptr-&gt;daten = calloc(new_size, sizeof(char)) ;
            else
               pque_ptr-&gt;daten = realloc(pque_ptr-&gt;daten,               
                                         new_size*sizeof(char)) ;
            memcpy(pque_ptr-&gt;daten+old_size-1, buffer, bufsize) ;
            pque_ptr-&gt;daten[new_size-1] = '\0' ;
         }
      }
      while (sqlca.sqlcode == 0) ;
   }
   EXEC SQL FREE :clob ;

   *sql_ptr = sqlca.sqlcode ;

   return ;
}
</code></pre>

<p>Using the Pro-C-Precompiler, I get the error:</p>

<blockquote>
  <p>Semantic error at line 59, column 67, file pap_pque_sel.pc:
      EXEC SQL LOB READ :bufsize FROM :clob AT :start INTO :buffer ;
      ..................................................................1
      PCC-S-02428, Buffer type is incompatible with LOB type</p>
</blockquote>

<p>Anybody got an idea, why char[...] should be incompatible with CLOB? I sed the same construct with other CLOBs in my database, and it worked fine.</p>

<p>Thank you in advance
Best regards
Jörg</p>