Same code to fetch data from both ORACLE & SQLServer

Topics: Data Access Application Block
May 23, 2008 at 7:19 AM
Hi Guys,
     i am trying to do this for the past one week and could not able to make it. so seeking your help.
     thing is, i have to use the same code to fetch data from both oracle server and sql server. I read DAL supports it.
    but how can i implement it-i am using procedure in both the servers with same name (same proc/table/field names)
    oracle procedure using Ref_Curosr but there is no RefCurosr type in common-DBType class
    sql proc using simple select.
    so help me guys.
    How can i make it work?
    Thanks !
May 23, 2008 at 2:56 PM
Edited May 23, 2008 at 2:58 PM
Hi,

The OracleDatabase will add a RefCursor parameter if necessary to retrieve rows from a stored procedure. A typical Oracle stored procedure would look like this:

PROCEDURE            "GETEMPLOYEE" (vEmployeeID IN Employees.EmployeeID%TYPE,
 cur_OUT OUT PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE)
AS
BEGIN
 OPEN cur_OUT FOR
 SELECT *
 FROM Employees
 WHERE EmployeeID = vEmployeeID;
END;

And would be called like this:

   56             DbCommand command = db.GetStoredProcCommand("GetEmployee");

   57             db.AddInParameter(command, "vEmployeeID", DbType.String, "6");

   58             IDataReader dataReader = db.ExecuteReader(command);


There's a gotcha though: you can't rely on parameter discovery here you have to explicitly add the parameters.

Hope this helps,
Fernando



pravinth wrote:
Hi Guys,
     i am trying to do this for the past one week and could not able to make it. so seeking your help.
     thing is, i have to use the same code to fetch data from both oracle server and sql server. I read DAL supports it.
    but how can i implement it-i am using procedure in both the servers with same name (same proc/table/field names)
    oracle procedure using Ref_Curosr but there is no RefCurosr type in common-DBType class
    sql proc using simple select.
    so help me guys.
    How can i make it work?
    Thanks !



May 26, 2008 at 9:46 AM
Edited May 26, 2008 at 10:32 AM
Hi Fernando,
    Thanks for your reply.
     but i could not able to make it as i don't know what is this PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE
     i used Ref Cursor and getting type mismatch error at the .net side (DbType.String - code should be independent of database)
     so do i have to declare this 'PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE' type in oracle db???
     i searched for this type declaration in my EL src folder but could not able to find it..
     what to do???

       Also i can't able to specify DbType.xml in the Addoutparameter. It throws error:- invalid value 'xml' for DbType ? Any Idea ...???

 

                  DbCommand cmd = db.GetStoredProcCommand(ProcName);
                  db.AddOutParameter(cmd,
"xml", DbType
.Xml, 2000);
                 Getting this error:-  
"Value is not valid for DbType: Xml. Parameter name: dbType"
      ????
      
     Please reply..
     Thanks!

May 26, 2008 at 3:22 PM

Hi,

PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE is just a cursor type defined in a package; use your cursor type instead.

About the DbType.Xml, I'm not sure Oracle supports it; there's specific support in SQL Server for this db type. But without an error code and a stack trace it's hard to tell.

Fernando


pravinth wrote:
Hi Fernando,
    Thanks for your reply.
     but i could not able to make it as i don't know what is this PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE
     i used Ref Cursor and getting type mismatch error at the .net side (DbType.String - code should be independent of database)
     so do i have to declare this 'PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE' type in oracle db???
     i searched for this type declaration in my EL src folder but could not able to find it..
     what to do???

       Also i can't able to specify DbType.xml in the Addoutparameter. It throws error:- invalid value 'xml' for DbType ? Any Idea ...???

 

                  DbCommand cmd = db.GetStoredProcCommand(ProcName);
                  db.AddOutParameter(cmd,
"xml", DbType
.Xml, 2000);
                 Getting this error:-  
"Value is not valid for DbType: Xml. Parameter name: dbType"
      ????
      
     Please reply..
     Thanks!




May 28, 2008 at 6:52 AM
Edited May 28, 2008 at 8:44 AM

Hi Fernando,

         I tried but its not working. Please look at the below info and guide me. Thanks.

             i created a package for cursor and used that in the procedure.

             --------------  PKG SPEC:-----------------
              CREATE OR REPLACE package pkib
              as
                  type cu is REF CURSOR;
             end pkib;

              ----------PKG BODY:------------------
              create or replace package body pkib
              as
              begin
                            DBMS_OUTPUT.PUT_LINE('OUTPUT');
              end;

             ----------------PROCEDURE--------------
             create or replace procedure PRName(o OUT pkib.cu) as
             begin
                      open o for select * from tb_Name;
            end PRName;

-----------------Above all are compiled working fine at the oracle server side..----------
            But when i call this at .net side its not working... Throwing

 "ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'PRName'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"
            
                Here is my code: 
                         DbCommand
cmd = db.GetStoredProcCommand("PRName"); 
                   db.AddOutParameter(cmd,
"o", DbType
.String, 100);

                    Then i tried all the below three but nothing worked...
                               A)      db.ExecuteNonQuery(cmd);
                               B)      DataSet
ds=db.ExecuteDataSet(cmd);
                               C)      IDataReader
dr = db.ExecuteReader(cmd)

       If you know please help me.
       Thanks!

 

 

 Note:

    DataSet ds = db.ExecuteDataSet(CommandType.Text, "Select * from tb_Name"); works fine. but i want to use stored procedure.

 

 

 

 

 

May 28, 2008 at 2:54 PM
Hi,

Why would you add an out parameter of type string for the cursor?

Notice that in the sample I posted the stored procedure has two arguments but I only specified one; the cursor argument is not specified, and it's used to retrieve the elements. In your sample, you shouldn't need to add any parameter.

Fernando


pravinth wrote:

Hi Fernando,

         I tried but its not working. Please look at the below info and guide me. Thanks.

             i created a package for cursor and used that in the procedure.

             --------------  PKG SPEC:-----------------
              CREATE OR REPLACE package pkib
              as
                  type cu is REF CURSOR;
             end pkib;

              ----------PKG BODY:------------------
              create or replace package body pkib
              as
              begin
                            DBMS_OUTPUT.PUT_LINE('OUTPUT');
              end;

             ----------------PROCEDURE--------------
             create or replace procedure PRName(o OUT pkib.cu) as
             begin
                      open o for select * from tb_Name;
            end PRName;

-----------------Above all are compiled working fine at the oracle server side..----------
            But when i call this at .net side its not working... Throwing

 "ORA-06550: line 1, column 7:\nPLS-00306: wrong number or types of arguments in call to 'PRName'\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n"
            
                Here is my code: 
                         DbCommand
cmd = db.GetStoredProcCommand("PRName"); 
                   db.AddOutParameter(cmd,
"o", DbType
.String, 100);

                    Then i tried all the below three but nothing worked...
                               A)      db.ExecuteNonQuery(cmd);
                               B)      DataSet
ds=db.ExecuteDataSet(cmd);
                               C)      IDataReader
dr = db.ExecuteReader(cmd)

       If you know please help me.
       Thanks!

 

 

 Note:

 

    DataSet ds = db.ExecuteDataSet(CommandType.Text, "Select * from tb_Name"); works fine. but i want to use stored procedure.

 

 

 

 

 

 




May 29, 2008 at 6:20 AM

Hi Fernando,

    Thank you.

     I tried as you said but its still not working..

     Here is how i tried:

          DbCommand cmd = db.GetStoredProcCommand("PRName");
             IDataReader dr = db.ExecuteReader(cmd);
      
      Error Message:   wrong number or types of arguments in call to 'PRName'
      Stack Trace:                  
Message: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'PRName'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

 

   at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
   at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
   at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.DoExecuteReader(DbCommand command, CommandBehavior cmdBehavior)
   at Microsoft.Practices.EnterpriseLibrary.Data.Database.ExecuteReader(DbCommand command)
   at Microsoft.Practices.EnterpriseLibrary.Data.Oracle.OracleDatabase.ExecuteReader(DbCommand command)
  ..... .....
      Please help me.
      Its very kind of you.

    Thanks!

May 29, 2008 at 7:40 AM
Edited May 29, 2008 at 7:54 AM

Hi Fernando,

     Thank you                 Thank you                        Thank you
                                It works
        It started to work after i changed the cursor parameter name to cur_OUT in my procedure.
        I made it because of you you only.
        Thank you very much!

                             EL - DAAB           ROCKS 
  

May 29, 2008 at 10:58 AM
Hi Fernando,

I am having the same problem. I followed your instructions from this post but it still fails with the error "wrong number or types of arguments in call to 'PRName'".

Note: We are using Enterprise Library version 3.1 for our DAAB. Do we need the newer version i.e. 4.0? or is there anything else that can help.

Regards,

Abdullah
May 29, 2008 at 11:17 AM
Hi Fernando,

I am having the same problem. I followed your instructions from this post but it still fails with the error "wrong number or types of arguments in call to 'PRName'".

Note: We are using Enterprise Library version 3.1 for our DAAB. Do we need the newer version i.e. 4.0? or is there anything else that can help.

Regards,

Abdullah
May 29, 2008 at 11:54 AM
Thanks mate, fixed the problem by using the OracleDatabase object instead of Database object.
Aug 6, 2008 at 6:40 PM

Hi,

 

I am having the same problem. The details are below:

 

CREATE or REPLACE PROCEDURE usp_Reports_SelectUserReports

(

  Resultset  OUT sys_refcursor,

  UserID     IN  RAW

)

IS

 

BEGIN

 

    OPEN Resultset FOR

 

    SELECT     R.ReportID, R.Name, R.FileName

    FROM       DD_Reports R INNER JOIN

               DD_UsersReports UR ON R.ReportID = UR.ReportID

    WHERE     (UR.UserID = UserID)

    ORDER BY   R.Name;

   

END;

 

        public List<Report> GetUserReports(Guid userID)

        {

            List<Report> reports = new List<Report>();

 

            OracleDatabase db = DatabaseFactory.CreateDatabase() as OracleDatabase;

            OracleCommand dbCommand = (OracleCommand)db.GetStoredProcCommand("usp_Reports_SelectUserReports");

            dbCommand.Parameters.Add(CreateInParameter("UserID", OracleType.Raw, userID.ToByteArray()));

 

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))

            {

                while (dataReader.Read())

                {

                    reports.Add(new Report((int)dataReader["ReportID"], (string)dataReader["Name"]) { FileName = (string)dataReader["FileName"] });

                }

            }

 

            return reports;

 

        }

I get an exception when try to ExecuteReader:

ORA-06550: line 1, column 7:

PLS-00306: wrong number or types of arguments in call to 'USP_REPORTS_SELECTUSERREPORTS'

ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

 

As you can see I specify only input parameter because of the DAAB documentation:

"...You do not need to provide cursor parameters for Oracle stored procedures. The OracleDatabase object provides them. This object assumes that the cursor parameter is the first parameter in the stored procedure's parameters list."

 

Please help me.

Aug 7, 2008 at 5:49 AM
Hi Alexeyy,
    First you define your cursor in a package like:
             --------------  PKG SPEC:-----------------
              CREATE OR REPLACE package pkib
              as
                  type cu is REF CURSOR;
             end pkib;

              ----------PKG BODY:------------------
              create or replace package body pkib
              as
              begin
                            DBMS_OUTPUT.PUT_LINE('OUTPUT');
              end;
             ----------------------------------

            Then define your procedure like:

             ----------------PROCEDURE--------------
             create or replace procedure PRName(cur_OUT OUT PKIB.cu) as
             begin
                      open cur_OUT for select * from tb_Name;
            end PRName;
            ----------------------------------------------
 NOTE:  In your procedure the cursor parameter name should be "cur_OUT" if you give anyother name it won't work.. you can find this cur_OUT name soemwhere in the EL source code...
         Hope it helps!
         
Aug 7, 2008 at 9:28 AM
Hi, pravinth.

You are absolutely right - the name of the out parameter should be cur_OUT. I found it in EL source code. The following is the fragment of the EL code:

public override IDataReader ExecuteReader(DbCommand command)
{
    this.PrepareCWRefCursor(command);
    return new OracleDataReaderWrapper((OracleDataReader) base.ExecuteReader(command));
}
private void PrepareCWRefCursor(DbCommand command)
{
    if (command == null)
    {
        throw new ArgumentNullException("command");
    }
    if ((CommandType.StoredProcedure == command.CommandType) && QueryProcedureNeedsCursorParameter(command))
    {
        this.AddParameter(command as OracleCommand, "cur_OUT", OracleType.Cursor, 0, ParameterDirection.Output, true, 0, 0, string.Empty, DataRowVersion.Default, Convert.DBNull);
    }
}

 

Now it works. My solution is the following:

CREATE or REPLACE PROCEDURE usp_Reports_SelectUserReports

(

  cur_OUT  OUT sys_refcursor,

  UserID     IN  RAW

)

IS

 

BEGIN

 

    OPEN cur_OUT FOR

 

    SELECT     R.ReportID, R.Name, R.FileName

    FROM       DD_Reports R INNER JOIN

               DD_UsersReports UR ON R.ReportID = UR.ReportID

    WHERE     (UR.UserID = UserID)

    ORDER BY   R.Name;

 

   

END;

 

public List<Report> GetUserReports(Guid userID)

        {

            List<Report> reports = new List<Report>();

 

            Database db = DatabaseFactory.CreateDatabase();

            OracleCommand dbCommand = db.GetStoredProcCommand("usp_Reports_SelectUserReports") as OracleCommand;

            dbCommand.Parameters.Add(CreateInParameter("UserID", OracleType.Raw, userID.ToByteArray()));

 

            using (IDataReader dataReader = db.ExecuteReader(dbCommand))

            {

                while (dataReader.Read())

                {

                    reports.Add(new Report(Convert.ToInt32(dataReader["ReportID"]), (string)dataReader["Name"]) { FileName = (string)dataReader["FileName"] });

                }

            }

 

            return reports;

 

        }

 

private OracleParameter CreateInParameter(string paramName, OracleType dbType, object value)

        {

            OracleParameter parameter = new OracleParameter(paramName, dbType);

            parameter.Direction = ParameterDirection.Input;

            if (value == null)

            {

                parameter.IsNullable = true;

                parameter.Value = DBNull.Value;

                return parameter;

            }

            parameter.Value = value;

            return parameter;

        }

 Best Regards