C++中带有子句和函数的oracle OTL问题

Question

我正在使用Oracle 18C(

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jan 28 02:44:17 2020
Version 18.8.0.0.0

)当我试图在C++中使用OTL时，我发现了一个查询，它使用oracle的"with“子句，如下所示：

with 
FUNCTION
SELECT QUERY with one bind variable

当我在plsql developer中执行这个"with cluase“查询时，它执行得很顺利。但是，当我将相同的查询放在otl_stream中并使用绑定变量时:它会抛出一个错误：

ORA-00600: internal error code, arguments: [15216], [], [], [], [], [], [], [], [], [], [], []

为了演示的目的，我创建了一些临时表并编写了一个查询：

create table test_with_func
(
int_col NUMBER(9),
varchar_col varchar2(30)
);
insert into test_with_func (INT_COL, VARCHAR_COL)
values (1, 'One');
insert into test_with_func (INT_COL, VARCHAR_COL)
values (2, 'Two');
commit;
with 
function getvalue(in_varchar in varchar2) return integer is out_int NUMBER;
begin
  select int_col
    into out_int
    from test_with_func
   where varchar_col = in_varchar;
  return out_int;
end;
select varchar_col from test_with_func where int_col = getvalue('Two')

当我把它放在c++代码中时，我会得到上面提到的奇怪错误。下面是我的C++代码。

#include<iostream>
#if defined(solaris32)
#define OTL_ORA9I
#else
#define OTL_ORA12C
#define OTL_UBIGINT unsigned long long
#endif //#if defined(solaris32)
#define OTL_STL // Enable STL compatibily mode
// Now we include OTL
#include <otlv4.h>

otl_connect db; // connect object
using namespace std;

int main(int argc,char **argv)
{
 try{
  db.rlogon("user/password@dbalias"); // connect to Oracle
 }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 cout<<"Connected to DB"<<endl;
   int mindom=1;
   int maxdom=9999999;
   int minrhash=1;
   int maxrhash=9999999;                                           
 string getDateQuery = " with function getvalue(in_varchar in varchar2) return integer is out_int NUMBER;     \
                         begin                                                                                \
                           select int_col                                                                     \
                             into out_int                                                                     \
                             from test_with_func                                                              \
                            where varchar_col = in_varchar;                                                   \
                           return out_int;                                                                    \
                         end;                                                                                 \
                         select varchar_col                                                                   \
                         from test_with_func                                                                  \
                         where int_col = getvalue(:inputvarchar<char[30]>)";
 string Value;
 otl_stream *getDateStream;
try{
     string var="Two";
     getDateStream=new otl_stream(1, getDateQuery.c_str(), db);
     *getDateStream << var;

     while(!getDateStream->eof())
     {

      *(getDateStream) >> Value;

     }

   }
   catch(otl_exception &p)
   {
       cerr<<p.msg<<endl; // print out error message
       cerr<<p.stm_text<<endl; // print out SQL that caused the error
       cerr<<p.var_info<<endl; // print out the variable that caused the error
   }

  cout<<"Value is "<<Value<<endl;
  db.logoff(); // disconnect from Oracle

return 0;
}

下面是输出

]$ ./a.out
Connected to DB
ORA-00600: internal error code, arguments: [15216], [], [], [], [], [], [], [], [], [], [], []

 with function getvalue(in_varchar in varchar2) return integer is out_int NUMBER;                              begin                                                                                                           select int_col                                                                                                  into out_int                                                                                                  from test_with_func                                                                                          where varchar_col = in_varchar;                                                                              return out_int;                                                                                             end;                                                                                                          select varchar_col                                                                                            from test_with_func                                                                                           where int_col = getvalue(:inputvarchar          )

Value is

这与我错过的某个预处理器宏有关吗？这里有人能帮忙吗。

Answer 1

之后，将查询更改为

with 
function getvalue(in_varchar in varchar2) return integer is out_int NUMBER;
begin
  select int_col
    into out_int
    from test_with_func
   where varchar_col = in_varchar;
  return out_int;
end;
select varchar_col from test_with_func where int_col = getvalue('Two')

至

with 
function getvalue(in_varchar in varchar2) return integer is out_int NUMBER;
begin
  select int_col
    into out_int
    from test_with_func
   where varchar_col = in_varchar;
  return out_int;
end;
output as(
select varchar_col from test_with_func where int_col = getvalue('Two')
)
select * from output

使用OTL在C++中解决问题。这里的更改将with子句的最后一个查询移到子查询中，并添加了新的最终select查询。但是请注意，这两个查询都是通过plsql developer完成的。不确定为什么第一个查询不能通过C++中的OTL进行。