DbType.Date And Oracle date type

Topics: Data Access Application Block
Nov 27, 2013 at 9:36 AM
Edited Nov 27, 2013 at 11:35 AM
Hi,

I am using C#, Visual Studio 10, EntLib 5.0.
So I have a stored procedure "GetReport(P_DATE IN DATE)" in Oracle 8.

I try to execute the following code:

DbCommand cmd = database.GetStoredProcCommand("GetReport")
database.AddInParameter(cmd, "p_Date", DbType.Date, Convert.ToDateTime("27-nov-2013"));
database.ExecuteDataSet(cmd);

The error message displayed:

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'GetReport'

Oracle database configured for 'dd-MMM-yyyy hh24:mi:ss' format.
I suppose, than with date format 'dd/MM/yyyy hh24:mi:ss' it was OK.

how i can configure app.config for using 'dd/MM/yyyy hh24:mi:ss' date format (like alter session set nls_date_format = 'dd/MM/yyyy hh24:mi:ss')?
Nov 28, 2013 at 5:31 AM
What DbProvider are you using? Does it work if you change the representation of the date? If you pass in DBNull.Value does the error still appear?

Have you tried using a type specific to the provider instead of DbType.Date? e.g. OracleType.DateTime.

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Nov 28, 2013 at 9:09 AM
Edited Dec 11, 2013 at 3:44 PM
Hi,

I wanted to write provider-independent code. When I pass to storred procedure DBNull.Value, the error still appears.
I want to use Enterprise Library 5.0 rather than specified provider and types.

There are several strange things.
Although the database type is {Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase},
the type of command is {System.Data.OracleClient.OracleCommand} and after
database.DiscoverParameters(cmd) the type of parameter is {System.Data.OracleClient.OracleParameterCollection}

I suppose, than if date format for session was 'dd-MMM-yyyy hh24:mi:ss', than was defined for Oracle as default, then everything was fine.

How i can change date format for the session to 'dd/MM/yyyy hh24:mi:ss' (like for TOAD: alter session set nls_date_format = 'dd/MM/yyyy hh24:mi:ss')

Hire is the app.config

<?xml version="1.0"?>
<configuration>
<configSections>
<section name="dataConfiguration" type="Microsoft.Practices.EnterpriseLibrary.Data.Configuration.DatabaseSettings, Microsoft.Practices.EnterpriseLibrary.Data, Version=5.0.505.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" requirePermission="true" />
</configSections>
<dataConfiguration defaultDatabase="Connection String">
<providerMappings>
  <add databaseType="Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase, Microsoft.Practices.EnterpriseLibrary.Data" name="Oracle.DataAccess.Client"/>
</providerMappings>
</dataConfiguration>
<connectionStrings>
<add name="Connection String" connectionString="Data Source=test;User ID=tiger;Password=pass;Persist Security Info=True;"
    providerName="Oracle.DataAccess.Client" />
</connectionStrings>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0"/></startup>
</configuration>
Dec 1, 2013 at 6:33 AM
Can you post the stored procedure? By the name GetReport I'm assuming you are returning some data? Do you have a refcursor in there?
PROCEDURE GetReport(
    P_DATE   IN    DATE,
    cur_OUT OUT sys_refcursor
) AS
BEGIN
 OPEN cur_OUT FOR
 SELECT *
 FROM Reports
 WHERE ReportDate = P_DATE;
END;

~~
Randy Levy
entlib.support@live.com
Enterprise Library support engineer
Support How-to
Dec 1, 2013 at 1:09 PM
Thank you for your example. In my code I just filled data to table from cursor without returning data.

PROCEDURE GetReport(P_DATE IN DATE) AS
BEGIN
INSERT INTO Reports (SELECT SaleDate, Name FROM ReportData WHERE SaleDate = P_DATE);
END;

Below is the new code:

PROCEDURE GetReport(P_DATE IN DATE, cur_OUT OUT sys_refcursor) AS
BEGIN
INSERT INTO Reports (SELECT SaleDate, Name FROM ReportData WHERE SaleDate = P_DATE);

OPEN cur_OUT FOR SELECT * FROM Reports;
END;

and it all works now!