OTL problem with oracle with clause and function in it in C++

Question

I am using 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

) I found this strange issue when I tried to use OTL in C++ I have a query which uses "with" clause of oracle like below:

with 
FUNCTION
SELECT QUERY with one bind variable

When I execute this "with cluase" query in plsql developer, it executes smoothly. But when I put the same query in otl_stream and with a bind variable: It throws me an error:

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

For demostration purpose I created some temp table and written a query:

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')

When I put it in c++ code, I get the weird error mentioned above. Below is my C++ code.

#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;
}

Below is the output

]$ ./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

Is this related to some preprocessor macro that I have missed? Could anyone please help here.

score 0 · Accepted Answer · answered Jan 29 '20 at 09:00

After Changing the query from

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')

to

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

Issue is resolved in C++ using OTL. Change here is moved the last query of with clause into a sub query and added and new final select query. But please note that both the queries work through plsql developer. Not sure why the first query is not working through OTL in C++.

OTL problem with oracle with clause and function in it in C++

1 Answers1