-
May 4th, 2012, 11:58 AM
#1
Thread Starter
PowerPoster
[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.
-
May 4th, 2012, 12:13 PM
#2
Thread Starter
PowerPoster
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.
-
May 4th, 2012, 03:20 PM
#3
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
-
May 5th, 2012, 03:22 AM
#4
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
-
May 7th, 2012, 02:49 PM
#5
Thread Starter
PowerPoster
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.
-
May 7th, 2012, 05:00 PM
#6
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
-
May 8th, 2012, 08:05 AM
#7
Thread Starter
PowerPoster
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.
-
May 8th, 2012, 08:13 AM
#8
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
-
May 8th, 2012, 01:20 PM
#9
Thread Starter
PowerPoster
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.
-
May 9th, 2012, 07:14 AM
#10
Thread Starter
PowerPoster
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.
-
May 9th, 2012, 09:06 AM
#11
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:
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.
c# Code:
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;
}
}
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
-
May 9th, 2012, 11:32 AM
#12
Thread Starter
PowerPoster
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|