[RESOLVED] I've never used Oracle before-VBForums
Results 1 to 12 of 12

Thread: [RESOLVED] I've never used Oracle before

  1. #1

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room, trying to forget the past
    Posts
    3,412

    Resolved [RESOLVED] I've never used Oracle before

    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...
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  2. #2

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room, trying to forget the past
    Posts
    3,412

    Re: I've never used Oracle before

    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!
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  3. #3
    PowerPoster
    Join Date
    May 2002
    Posts
    25,485

    Re: I've never used Oracle before

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

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  4. #4
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,208

    Re: I've never used Oracle before

    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.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  5. #5

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room, trying to forget the past
    Posts
    3,412

    Re: I've never used Oracle before

    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 .
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  6. #6
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,208

    Re: I've never used Oracle before

    For connecting without a TNSnames.ora configuration, you can refer to this post.
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  7. #7

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room, trying to forget the past
    Posts
    3,412

    Re: I've never used Oracle before

    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?
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  8. #8
    PowerPoster
    Join Date
    May 2002
    Posts
    25,485

    Re: I've never used Oracle before

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room, trying to forget the past
    Posts
    3,412

    Re: I've never used Oracle before

    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.
    Last edited by MMock; May 9th, 2012 at 07:14 AM. Reason: typo
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  10. #10

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room, trying to forget the past
    Posts
    3,412

    Re: I've never used Oracle before

    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.
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

  11. #11
    PowerPoster abhijit's Avatar
    Join Date
    Jun 1999
    Location
    Chit Chat Forum.
    Posts
    3,208

    Re: I've never used Oracle before

    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.
    c# Code:
    1. public static void InsertProspect(OracleConnection oracleConnection, int partyId, string accountId)
    2.         {
    3.             //ReadCommitted is the default in oracle.
    4.             var transaction = oracleConnection.BeginTransaction(IsolationLevel.ReadCommitted);
    5.             try
    6.             {
    7.                 const string sqlStr = "INSERT INTO party_Table \n" +
    8.                                       "(partyId, account_Id) \n" +
    9.                                       "VALUES(:partyId, :accountId)";
    10.  
    11.                 var oracleCommand = new OracleCommand
    12.                 {
    13.                     Connection = oracleConnection,
    14.                     CommandText = sqlStr,
    15.                     CommandType = CommandType.Text
    16.                 };
    17.  
    18.                 var prmcontractId = new OracleParameter
    19.                 {
    20.                     ParameterName = "partyId",
    21.                     Direction = ParameterDirection.Input,
    22.                     OracleDbType = OracleDbType.Int32,
    23.                     Size = 8,
    24.                     Value = partyId
    25.                 };
    26.                 oracleCommand.Parameters.Add(prmcontractId);
    27.  
    28.                 var prmaccountId = new OracleParameter
    29.                 {
    30.                     ParameterName = "account_Id",
    31.                     Direction = ParameterDirection.Input,
    32.                     OracleDbType = OracleDbType.Varchar2,
    33.                     Size = 100,
    34.                     Value = accountId
    35.                 };
    36.                 oracleCommand.Parameters.Add(prmaccountId);
    37.                 var retVal = oracleCommand.ExecuteNonQuery();
    38.                 transaction.Commit();
    39.             }catch(Exception exception)
    40.             {
    41.                 transaction.Rollback();
    42.                 Console.WriteLine(string.Format("InsertParty:\t{0}",exception.Message));
    43.                 throw;
    44.             }
    45.         }

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

    c# Code:
    1. private static OracleDataReader FireMyFunc(OracleConnection oc)
    2.         {
    3.             try
    4.             {
    5.                 var cmd = new OracleCommand
    6.                             {
    7.                                 Connection = oc,
    8.                                 CommandText = "sendmearefcursor",
    9.                                 CommandType = CommandType.StoredProcedure
    10.                             };
    11.                 var prm = cmd.CreateParameter();
    12.                 prm.OracleDbType = OracleDbType.RefCursor;
    13.                 prm.ParameterName = "returncurse";
    14.                 prm.Direction = ParameterDirection.ReturnValue;
    15.                 cmd.Parameters.Add(prm);
    16.                 cmd.ExecuteNonQuery();
    17.                 var rc = (OracleRefCursor) prm.Value;
    18.                 return rc.GetDataReader();
    19.             }
    20.             catch (Exception ex)
    21.             {
    22.                Console.WriteLine(String.Format("FireMyFunc: {0}",ex));
    23.                 throw;
    24.             }
    25.         }
    Everything that has a computer in will fail. Everything in your life, from a watch to a car to, you know, a radio, to an iPhone, it will fail if it has a computer in it. They should kill the people who made those things.- 'Woz'
    save a blobFileStreamDataTable To Text Filemy blog

  12. #12

    Thread Starter
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Sitting in an empty room, trying to forget the past
    Posts
    3,412

    Re: I've never used Oracle before

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

    Code:
    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
    Code:
        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;
            }
            
        }
    Code:
        private OracleConnection connectToDatabase()
        {
            String connString = "User Id=***;Password=***;Data Source=dbServerName";
            return new OracleConnection(connString);
        }
    There are 10 kinds of people in this world. Those who understand binary, and those who don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.