search

DIPSAS / Dapper.Oracle

Oracle support for Dapper Micro ORM.
MIT License
113 stars 43 forks source link

Oracle.ManagedDataAccess.Client.OracleException (0x80004005): ORA-00902: #35

Closed yuessir closed 4 years ago

yuessir commented 4 years ago

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.

epaulsen commented 4 years ago

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();
yuessir commented 4 years ago

I'm not sure what the problem is,so I decide to close the issue. Thanks for the good project.