使用C和嵌入式SQL在Oracle中选择CLOB [英] Selecting CLOB in Oracle using C and embedded SQL

问题描述

也许这里有人可以帮助我；我的想法用光了.

Perhaps somebody here can help me; I've run out of ideas.

我尝试在Oracle中包含CLOB的表上实现select语句.这是表的定义:

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

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

我的SELECT语句看起来是这样的:

My SELECT-Statement looks this way:

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->pque_id),
               :(pque_ptr->pga_id),
               :(pque_ptr->ws_code),
               :(pque_ptr->operation),
               :(pque_ptr->rf_nr),
               :(pque_ptr->status),
               :(pque_ptr->sender),
               :(pque_ptr->empfaenger),
               :(pque_ptr->start_zeit),
               :clob
        FROM   PQUE
       WHERE   PQUE.ID = :(pque_ptr->pque_id) ;
   *sql_ptr = sqlca.sqlcode ;

   if (sqlca.sqlcode == 0)
   {
      ds_trim(pque_ptr->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->daten == NULL)
               pque_ptr->daten = calloc(new_size, sizeof(char)) ;
            else
               pque_ptr->daten = realloc(pque_ptr->daten,               
                                         new_size*sizeof(char)) ;
            memcpy(pque_ptr->daten+old_size-1, buffer, bufsize) ;
            pque_ptr->daten[new_size-1] = '\0' ;
         }
      }
      while (sqlca.sqlcode == 0) ;
   }
   EXEC SQL FREE :clob ;

   *sql_ptr = sqlca.sqlcode ;

   return ;
}

使用Pro-C-Precompiler，我得到了错误:

Using the Pro-C-Precompiler, I get the error:

第59行第67列，文件pap_pque_sel.pc中的语义错误: EXEC SQL LOB读取:bufsize FROM:clob AT:start INTO:buffer; ................................................... ................ 1 PCC-S-02428，缓冲区类型与LOB类型不兼容

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

任何人都有一个主意，为什么char [...]应该与CLOB不兼容?我在数据库中与其他CLOB一起使用了相同的构造，并且运行良好.

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.

预先感谢您 最好的祝福 约尔格(Jörg)

Thank you in advance Best regards Jörg

推荐答案

我终于找到了问题所在.所有其他PRO-C模块都使用选项CHAR_SET = STRING进行编译.这不适用于LOB.删除该模块的选项时，我可以对其进行编译，并且效果很好.

I finally found the problem myself. All the other PRO-C modules are compiled using the option CHAR_SET=STRING. This does not work with LOB. When removing the option for this module, I could compile it, and it works fine.

最诚挚的问候 约尔格(Jörg)

Best regards Jörg

这篇关于使用C和嵌入式SQL在Oracle中选择CLOB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！