Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00902:
yuessir opened this issue · 2 comments
My platform is .Net core 3.1
and using ODP.NET CORE library.
I created a function on Oracle 11c and work fine.
Function like :
CREATE OR REPLACE FUNCTION Get_Clob_Content(IDs IN varchar2)
return TBL_OBJ_COLL
as
v_tbl TBL_OBJ_COLL:=TBL_OBJ_COLL();
v_sql varchar2(3000);
create type TBL_OBJ is object(CONTENT VARCHAR2(4000),OFFSET int,MOD int,FILE_CONTENT_LENGTH int,INTERATION_COUNT int);
create type TBL_OBJ_COLL is table of TBL_OBJ;
When I try to use Dapper.net(v2.0.35) to get the entity from the function. And got the error(s)
Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00902:Invalid datatype error
`
at OracleInternal.ServiceObjects.OracleConnectionImpl.VerifyExecution(Int32& cursorId, Boolean bThrowArrayBindRelatedErrors, SqlStatementType sqlStatementType, Int32 arrayBindCount, OracleException& exceptionForArrayBindDML, Boolean& hasMoreRowsInDB, Boolean bFirstIterationDone)
at OracleInternal.ServiceObjects.OracleCommandImpl.ExecuteReader(String commandText, OracleParameterCollection paramColl, CommandType commandType, OracleConnectionImpl connectionImpl, OracleDataReaderImpl& rdrImpl, Int32 longFetchSize, Int64 clientInitialLOBFS, OracleDependencyImpl orclDependencyImpl, Int64[] scnForExecution, Int64[]& scnFromExecution, OracleParameterCollection& bindByPositionParamColl, Boolean& bBindParamPresent, Int64& internalInitialLOBFS, OracleException& exceptionForArrayBindDML, OracleConnection connection, OracleLogicalTransaction& oracleLogicalTransaction, IEnumerable`1 adrianParsedStmt, Boolean isDescribeOnly, Boolean isFromEF)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
at Oracle.ManagedDataAccess.Client.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
--- End of stack trace from previous location where exception was thrown ---
at Dapper.SqlMapper.QueryAsync[T](IDbConnection cnn, Type effectiveType, CommandDefinition command) in /_/Dapper/SqlMapper.Async.cs:line 419
plain SQL text is working!
select CONTENT from table( CAST(Get_Clob_Content('20010044_01') AS TBL_OBJ_COLL))
C# code:
string sql=@$"select CONTENT as CONTENT from table( CAST(Get_Clob_Content('{tableName}','{colName}','{condition}','{id}') AS TBL_OBJ_COLL)) ";
Can Table() function map to entity?
Is it a bug from ODO.NET Core?Any ideas? Thank you in advance.
Sorry for the late response. Are you sure this is a problem with Dapper and Dapper.Oracle?
What do you get as a result when you execute this directly on a command object and return a datareader, e.g(pseudokode):
var command = new OracleCommand(_myconnection, "select CONTENT from table( CAST(Get_Clob_Content('20010044_01') AS TBL_OBJ_COLL))");
var reader = command.ExecuteReader();
I'm not sure what the problem is,so I decide to close the issue.
Thanks for the good project.