[RESOLVED] I've never used Oracle before

[MMock]

I have to run a stored procedure in Oracle. I will be on our client's network. I am writing an ASP.NET page in C# to do this.

I've never used Oracle before, only Microsoft data sources. So, this article is a little bit confusing to me: http://www.codeguru.com/csharp/cshar...p-Tutorial.htm

How do I know if I want to connect to Oracle using System.Data.OracleClient Data Provider or connect to Oracle using Oracle.DataAccess.Client Data Provider?

What the client has given me is: the database name, an id and password, a package name (what's that?) and a "package procedure definition" (I'm assuming that's like a stored procedure in SQL Server) and its second parameter is curData out T_CURSOR - what does that mean I am getting back? What do I pass in?

The hard part about this development is I have visual studio on my PC, but I have to run my page on a laptop they have sent me which connects to their network. So I can't quickly make code changes or see what I am getting back. I don't have the tools to make an Oracle DB locally for testing. I have to build a DLL, run the page, and just write something out to let me know if it's working. Any ideas how to best do this?

Thanks if you can help. I will keep googling for tutorials...

[MMock]

Hold off answering this for now - I just went to talk to my boss, who seems to think the client has pulled a fast one, as this is the first he has heard of the DB not being SQL Server!

[techgnome]

"think the client has pulled a fast one" - NO! Say it isn't so! that NEVER happens.... ;0)

-tg

[abhijit]

You need to download ODP .NET and install it. That is a .NET library written by Oracle. That's what you need to program with Oracle. If you want simple examples like calling a stored procedure, selecting records etc, I have several of them.

You do not need an Oracle DB running locally. You need connectivity to an Oracle Db. If you install ODP .NEt you don't need to install anything else for developing your applications. You need to modify a tnsnames.ora file, but that is optional. There are ways which will allow your app to connect to the database without a TNSNAMES.ora file being configured.

[MMock]

Yes, if you have examples, I would like to see them if you are offering .

As I said, I have to deploy the code, then test it, so debugging will be difficult. So anything you can supply that can get me started will be a big help.

As for the client pulled a fast one, oh well. We are billing another few hours for my learning curve .

[abhijit]

For connecting without a TNSnames.ora configuration, you can refer to this post.

[MMock]

I need to back up to the beginning and ask some questions.

What is the difference between ODP.NET and Oracle_DataAccess.dll? You said I need ODP.NET, and so did the client. He put Oracle_DataAccess.dll on his server in a folder called ODP.NET. So is it really the DLL I need to reference in my code?

[techgnome]

ODP.NET is Oracle Data Provider for .NET... when I googled "ODP.NET and Oracle_DataAccess.dll" .... the results seem to suggest that the assembly is Oracle.DataAccess.dll .... so I wonder if they simply re-named the dll (some systems don't like multiple dots in the name ... so I'd say go ahead and reference it and see what happens.

-tg

[MMock]

Okay, I am referencing it. Now I need to execute the package and a procedure within the package. Can't find any examples of this. But he gave me a package name and a procedure definition which looks analogous to a SQL stored procedure, e.g. GetEmployeeData. So I just have to put these pieces together. I'm finding stuff from 2003 when I google.

[MMock]

Does anyone have any expertise in this area? The client isn't being very helpful. He gave me the name of a package and the name of a procedure. I thought I would be using both. But when I googled "difference between package and stored procedure", it sounds like you use one or the other.

[abhijit]

A stored procedure could be stand alone or be a part of a package. It all depends on how the developers chose to implement it in the database. In either case the methods to call the stored procedure remains the same.

1) you establish a connection to the database.
2) make sure your account (the login that you use) has privileges to execute the stored procedure.
3) create a ODP .NEt command object
4) create parameters for the command object based on the parameters the stored proc takes.
5) execute the stored procedure
6) retrieve results.

Here's a sample code that works.

This code uses an insert query with parameters to insert a record in an oracle table.

public static void InsertProspect(OracleConnection oracleConnection, int partyId, string accountId)
        {
            //ReadCommitted is the default in oracle.
            var transaction = oracleConnection.BeginTransaction(IsolationLevel.ReadCommitted);
            try
            {
                const string sqlStr = "INSERT INTO party_Table \n" +
                                      "(partyId, account_Id) \n" +
                                      "VALUES(:partyId, :accountId)";
 
                var oracleCommand = new OracleCommand
                {
                    Connection = oracleConnection,
                    CommandText = sqlStr,
                    CommandType = CommandType.Text
                };
 
                var prmcontractId = new OracleParameter
                {
                    ParameterName = "partyId",
                    Direction = ParameterDirection.Input,
                    OracleDbType = OracleDbType.Int32,
                    Size = 8,
                    Value = partyId
                };
                oracleCommand.Parameters.Add(prmcontractId);
 
                var prmaccountId = new OracleParameter
                {
                    ParameterName = "account_Id",
                    Direction = ParameterDirection.Input,
                    OracleDbType = OracleDbType.Varchar2,
                    Size = 100,
                    Value = accountId
                };
                oracleCommand.Parameters.Add(prmaccountId);
                var retVal = oracleCommand.ExecuteNonQuery();
                transaction.Commit();
            }catch(Exception exception)
            {
                transaction.Rollback();
                Console.WriteLine(string.Format("InsertParty:\t{0}",exception.Message));
                throw;
            }
        }

This stored procedure doesn't have any input parameters and it returns a recordset from the database.

private static OracleDataReader FireMyFunc(OracleConnection oc)
        {
            try
            {
                var cmd = new OracleCommand
                            {
                                Connection = oc,
                                CommandText = "sendmearefcursor",
                                CommandType = CommandType.StoredProcedure
                            };
                var prm = cmd.CreateParameter();
                prm.OracleDbType = OracleDbType.RefCursor;
                prm.ParameterName = "returncurse";
                prm.Direction = ParameterDirection.ReturnValue;
                cmd.Parameters.Add(prm);
                cmd.ExecuteNonQuery();
                var rc = (OracleRefCursor) prm.Value;
                return rc.GetDataReader();
            }
            catch (Exception ex)
            {
               Console.WriteLine(String.Format("FireMyFunc: {0}",ex));
                throw;
            }
        }

[MMock]

Thanks. The customer finally chimed in and gave me this:

Public Function testProcedureCall() As DataSet 
Const SQL As String = "dbo.pkgname.procname"
Try
Using conn As OracleConnection = GetConnectionOracle(), cmd As OracleCommand = CreateSQLCommand(SQL, conn, CommandType.StoredProcedure)

cmd.Parameters.Add("1stParam", "xxxx").Direction  = ParameterDirection.Input
cmd.Parameters.Add("outParam", OracleDbType.RefCursor).Direction = ParameterDirection.Output
Return ExecuteStoredProc(cmd,)
End Using
Catch ex As Exception
End Try
End Function

which I modified into

    public DataSet getUserProfileFromOracle(StorefrontProcessor sfp)
    {
        sfp.writeLog(string.Format("getUserProfileFromOracle() begin"));

        try
        {
            // Using cmd As New OracleCommand(procName, conn)
            OracleConnection conn = connectToDatabase();
            sfp.writeLog(string.Format("getUserProfileFromOracle() connected"));

            OracleCommand cmd = new OracleCommand("dbo.pkgname.procname", conn);
            cmd.CommandType = CommandType.StoredProcedure;
            sfp.writeLog(string.Format("getUserProfileFromOracle() stored procedure set"));

            DataSet ds = new DataSet();

            OracleParameter param = new OracleParameter("1stParam", OracleDbType.Varchar2);
            param.Value = "xxxx";
            cmd.Parameters.Add(param).Direction = ParameterDirection.Input;
            sfp.writeLog(string.Format("getUserProfileFromOracle() input parameter created"));

            cmd.Parameters.Add("outParam", OracleDbType.RefCursor).Direction = ParameterDirection.Output;
            sfp.writeLog(string.Format("getUserProfileFromOracle() output parameter created"));
            
            conn.Open();
            sfp.writeLog(string.Format("getUserProfileFromOracle() connection opened"));

            OracleDataReader reader = cmd.ExecuteReader();
            sfp.writeLog(string.Format("getUserProfileFromOracle() executed"));

            DataTable dt = new DataTable();
            dt.Load(reader);
            ds.Tables.Add(dt);
            sfp.writeLog(string.Format("getUserProfileFromOracle() created datatable"));

            return ds;
        }
        catch (Exception ex)
        {
            sfp.writeLog(string.Format("getUserProfileFromOracle() called but exceptioned: {0}", ex.Message));
            return null;
        }
        
    }

    private OracleConnection connectToDatabase()
    {
        String connString = "User Id=***;Password=***;Data Source=dbServerName";
        return new OracleConnection(connString);
    }