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.
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++.