Hi Shalex,
We changed to use 9.6.558.0 as you told. and now another issue.
Our test environment is using Oracle 12c, user side 11g.
With the same code as pasted before in my reply, when calling procedure, user side report and issue, "ORA-04043: object PUBLIC does not exist". If I use user.ProcedureName, the message will be "ORA-04043: object PUBLIC.ProcedureName does not exist". but actually, we did not call or have a object named as 'PUBLIC'.
Meanwhile, pure SQL(CommandType.Text) works ok.
And in my dev environment with 12c, both versions are always ok.
When we revert to 9.4.280.0 and connect to user's database, the issue disappeared.
Exception Trace:
at Devart.Data.Oracle.d7.e()
at Devart.Data.Oracle.f.a()
at Devart.Data.Oracle.cs.a(String A_0, Int32 A_1, OracleConnection A_2)
at Devart.Data.Oracle.OracleCommand.DescribeProcedure(String name)
at Devart.Data.Oracle.OracleCommand.CreateStoredProcSql(String procName)
at Devart.Common.DbCommandBase.CreateSql()
at Devart.Common.DbCommandBase.get_Sql()
at Devart.Data.Oracle.OracleCommand.InternalPrepare(Boolean implicitPrepare, Int32 startRecord, Int32 maxRecords)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior, Boolean nonQuery)
at Devart.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at Devart.Data.Oracle.OracleCommand.ExecuteReader(CommandBehavior behavior)
at DevartPerformanceTest.Program.TestDirectConn(String connName) in C:\Users\Michytang\Documents\Visual Studio 2015\Projects\DevartPerformanceTest\Program.cs:line 68
below is the sample of the procedure.
Procedure:
create or replace procedure "ProcedureName"(strCond IN VARCHAR2, oCursor OUT UTILITIES.cur_OUT) is
sSQLStatement VARCHAR2(2000);
begin
sSQLStatement := 'SELECT * FROM SampleTableName';
IF strCond IS NULL OR strCond = ' ' THEN
sSQLStatement := sSQLStatement||' WHERE 1=1 ORDER BY colomn';
ELSE
sSQLStatement := sSQLStatement||' WHERE '||strCond || 'ORDER BY colomn';
END IF;
OPEN oCursor FOR sSQLStatement;
end ProcedureName;
Package:
CREATE OR REPLACE PACKAGE Utilities IS
TYPE cur_OUT IS REF CURSOR;
END Utilities;
Package Body:
Nothing related.
Table:
CREATE TABLE "SampleTableNameFTI"
( "SampleTableNameFLTY" NUMBER(9,0),
"SampleTableNameINO" NUMBER(9,0),
"SampleTableNameDESC" VARCHAR2(15),
"SampleTableNameFINM" VARCHAR2(15),
"SampleTableNameDUP" VARCHAR2(1),
"SampleTableNameFLEN" NUMBER(9,0),
"SampleTableNameIS" VARCHAR2(1),
"SampleTableNameX1" NUMBER(9,0),
"SampleTableNameY1" NUMBER(9,0),
"SampleTableNameX2" NUMBER(9,0),
"SampleTableNameY2" NUMBER(9,0),
"SampleTableNameUSCR" VARCHAR2(8),
"SampleTableNameDTCR" DATE,
"SampleTableNameUSAM" VARCHAR2(8),
"SampleTableNameDTAM" DATE,
"SampleTableNameRQVL" VARCHAR2(1),
"SampleTableNameVDRL" VARCHAR2(4)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_SAMPLE" ;
CREATE UNIQUE INDEX "SampleTableNameFT1" ON "SampleTableNameFTI" ("SampleTableNameFLTY", "SampleTableNameINO")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOLOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TS_SAMPLE" ;
Also, i get the Json of the OracleCommand. it's same for 9.4.280.0 and 9.6.558.0,you can check the parameters.
{
"CommandText": "ProcedueName",
"CommandType": 4,
"Connection": {
"AutoCommit": true,
"UserId": "xxx",
"Password": "xxx",
"Server": "10.129.xxx.xxx",
"Home": "",
"ClientId": "",
"Direct": true,
"Port": 1521,
"Sid": "xxx",
"ServiceName": "",
"ConnectMode": 0,
"Unicode": false,
"ConnectionString": "direct=true;trim fixed char=false;data source=10.129.xxx.xxx;port=1521;sid=xxx;user id=xxx;",
"State": 1,
"ClientVersion": "9.2.0.0",
"ServerVersion": "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production\nWith the Partitioning, OLAP, Data Mining and Real Application Testing options",
"ShortServerVersion": {
"Major": 11,
"Minor": 2,
"Build": 0,
"Revision": 4,
"MajorRevision": 0,
"MinorRevision": 4
},
"ServerType": 1,
"ConnectionTimeout": 15,
"Database": "",
"TrimFixedChar": false,
"PassParametersByName": false,
"NumberMappings": [ ],
"Name": "",
"Owner": null,
"LogicalTransactionId": null,
"OracleLogicalTransaction": null,
"ModuleName": null,
"ActionName": null,
"ClientInfo": null,
"DataSource": "10.129.xxx.xxx",
"DesignTimeVisible": true,
"InDistributedTransaction": false,
"Site": null,
"Container": null
},
"Parameters": [ "strCond" ],
"Transaction": null,
"Cached": true,
"FetchSize": 0,
"PassParametersByName": false,
"CommandTimeout": 0,
"Notification": null,
"TableValuedResultType": null,
"IsTableValuedFunction": null,
"ImplicitRefCursors": true,
"InitialLobFetchSize": 0,
"DesignTimeVisible": true,
"UpdatedRowSource": 3,
"ParameterCheck": true,
"Name": "",
"Owner": null,
"Site": null,
"Container": null
}