|
Hello.
I have an oracle connection and I'm trying to run a stored procedure.
The code for defining the parameters in C# is:
------------------------------
cmd.commandType = CommandType.storedProcedure;
//Parameter 1:
OracleParameter prm1 = new OracleParameter("c1",OracleDbType.VARCHAR2);
prm1.Direction = ParameterDirection.Output;
prm1.Value = '1';
cmd.Parameters.add(prm1);
//Parameter 2:
OracleParameter prm2 = new OracleParameter("c2",OracleDbType.VARCHAR2, 200);
prm1.Direction = ParameterDirection.Output;
cmd.Parameters.add(prm2);
cmd.ExecutenonQuery();
--------------------------------------
The stored Procedure in Oracle (10g) is:
create or replace procedure test (c1 in varchar2, c2 out varchar2) is
Begin
c2:='1';
End test
\
--------------------------------------
The error I'm having is:
"ORA-06502: numeric or value error:charecter string buffer is too small"
I saw that error in few sites but not something like my problem?
Any idea?
Thanks.
|
|
|
|
|
You have there:
prm1 = new OracleParameter("c1",OracleDbType.VARCHAR2)
Therefore, the default length is 256 (i think).
In prm2 = new OracleParameter("c2",OracleDbType.VARCHAR2, 200) you specify a length of 200.
Sometimes, 200 < 256 
SkyWalker
|
|
|
|
|
Hi.
Thanks but thats not the problem.
I tried with size 200/256/300/100/500/10 and nothing worked.
I'm with VS2005 and I added refference to the ODP dll of oracle.
What I did manage to do is to give the parameter a value:
prm3.value ='1'
and it worked but when i gave him string.Empty or didn't give him a value (since i want him to get the value from the SP) i got the same error.
Any idea?
|
|
|
|
|
Hi,
first, in your code snippet you set parameter c1 to direction output, whereas in the sp you declared it as input. maybe that's one part of the problem.
Second, Oracle behaves ... let's say different An empty string is the same as NULL in Oracle.
Martin
|
|
|
|
|
Hi martin and thanks for the reply.
Sadly it didn't helped 
I tried use: pram3.value = null
but i got the same error.
Only if i give it a string (like: pram3.value = "str") it return the string.
can it be that the SP doesn't return a value and that way it fall?
It looks like a very simple SP.
(b.t.w The direction in my code is input for c1, I typed it wromg in here so that ok).
Do you any more ideas or a simple code for the SP and C# i can see.
I didn't find any oracle+c# on the net
Thank again.
|
|
|
|
|
Ok, next try I recognized that you are using ExecuteNonQuery. Maybe one of the other Executexxx methods will help. If not, I will try it here on our Oracle 9.0.2.
I must confess I never worked with the Oracle provider from Microsoft long. Better use Oracle's ODP.NET.
Martin
|
|
|
|
|
Arrr, It didn't do the trick.
I tried using executescaler instead and got the some error.
Also, I'm working with the ODP (I added the refference and the "using oracle.dataAccess.client") and with oracle 10g.
The problem is that I'm not even sure if the problem is from my C# code or from the oracle.
I know less about oracle so i took a very simple SP So there won't be any problem with that.
If You'll manage to get a result from a SP to a C# code I promise to add you to my holiday gift list
|
|
|
|
|
Ok, I tried it with this code and it worked here (ODP.NET 10.2, Oracle 9g):
<br />
using ( OracleConnection conn = new OracleConnection( connString ) )<br />
{<br />
conn.Open();<br />
<br />
OracleCommand cmd = conn.CreateCommand();<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
cmd.CommandText = "TEST";<br />
<br />
OracleParameter parm1 = cmd.CreateParameter();<br />
parm1.Direction = ParameterDirection.Input;<br />
parm1.OracleDbType = OracleDbType.Varchar2;<br />
parm1.ParameterName = "C1";<br />
parm1.Size = 200;<br />
cmd.Parameters.Add( parm1 );<br />
<br />
OracleParameter parm2 = cmd.CreateParameter();<br />
parm2.Direction = ParameterDirection.Output;<br />
parm2.OracleDbType = OracleDbType.Varchar2;<br />
parm2.ParameterName = "C2";<br />
parm2.Size = 200;<br />
cmd.Parameters.Add( parm2 );<br />
<br />
cmd.ExecuteNonQuery();<br />
Console.WriteLine( parm2.Value );<br />
}<br />
HTH,
Martin
|
|
|
|
|
Hi Martin ,
Thanks. That help!
I still didn't compere between my code and your but I just run yours and it WORKED!
The only diffrent I saw in a quick glance was that I didn't use the
"OracleCommand cmd = conn.CreateCommand();"
but instead I use
"OracleCommand cmd = new OracleCommand("TEST", conn);"
Thats the only thing that i saw.
Any way, The importent thing is That your code work and now i can try it!
Thanks a lot for your patient and help
Regards,
Roy,
|
|
|
|
|
I found a difference, the Size property for the first (unused) parameter. I bet this is the point.
Glad I could help you.
Martin
|
|
|
|
|
Hello Friends,
I am new in this .Net environment. Can any one give me details of
where can i get all the overview of project shown in the new project dialog
box of .Net.
Take a ex. C# Projects(windows appl., class library). I know these.
but what are others details of each one. specially of VC++.Net.
If any one can give link which give all these basic infomation, it will
be great.
Thanks in Advance.
Rahul Kulkarni.
|
|
|
|
|
One choice would be this[^]
SkyWalker
|
|
|
|
|
Someone you would know to indicate an elegant way to develop an application chronometer?
Thanks .. 
|
|
|
|
|
Perhaps this[^] comparison of .NET timers will help?
/ravi
|
|
|
|
|
Thanks. Naturally I can manage also the millesimi, just?
|
|
|
|
|
Hi,
I have a class called WriteToDoc.
And i made an object of it like this:
WriteToDoc writeToDoc = new WriteToDoc();
I was wondering how can i dispose the writeToDoc object?
Thanks in advance!
|
|
|
|
|
Hello,
You would have to Implement the IDisposable interface in your class.
This Interface includes the Dispose method.
But why do you need this?
All the best,
Martin
|
|
|
|
|
If the WriteToDoc class has a dispose method associated with it, you could do the following:
using (WriteToDoc writeToDoc = new WriteToDoc())
{
}
Dispose is automatically called when the execution block finishes.
If you don't have a Dispose method, just call writeToDoc = null to set it to a null value. Note that this isn't necessary because the garbage collector will pick it up some time after the variable goes out of scope.
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi guys,
I just implemented these two methods:
public void Dispose()
{
Dispose(true);
// This object will be cleaned up by the Dispose method.
// Therefore, you should call GC.SupressFinalize to
// take this object off the finalization queue
// and prevent finalization code for this object
// from executing a second time.
GC.SuppressFinalize(this);
}
private void Dispose(bool disposing)
{
// Check to see if Dispose has already been called.
if (!this.disposed)
{
// If disposing equals true, dispose all managed
// and unmanaged resources.
if (disposing)
{
// Dispose managed resources.
this.Dispose();
}
// Call the appropriate methods to clean up
// unmanaged resources here.
// If disposing is false,
// only the following code is executed.
//CloseHandle(handle);
//handle = IntPtr.Zero;
// Note disposing has been done.
disposed = true;
}
}
And in my Form i call the writeToDoc.Dispose() method like that.
I copied it from msdn library.
Should it work like that too?
Thanks in advance!
|
|
|
|
|
You should implement the finalizer also:
~WriteToDoc() {
Dispose(true);
}
This calls the Dispose method in case the coder fails to call it.
---
Year happy = new Year(2007);
|
|
|
|
|
Hi Guffa,
Thank you very much!
|
|
|
|
|
For a class that is this simple, you really don't need a finalizer. Writing a proper finalizer is generally a non-trivial task as there are certain rules that need to be followed. Beyond that, adding a finalizer causes the runtime to place the object in the finalization queue when it is instantiated, which causes the garbage collection system additional work.
The finalizer will cause the Dispose method to be called if the coder forgets to call it, but that safety net usually comes at a higher cost than it's worth.
For a different view on how to implement the Disposable pattern, check out this article:
http://www.codeproject.com/useritems/idisposable.asp[^]
-----------------------------
In just two days, tomorrow will be yesterday.
|
|
|
|
|
Having a Dispose method isn't enough by itself. Your class must implment IDisposable.
only two letters away from being an asset
|
|
|
|
|
Mark,
... just looking at the footer of your messages ...
Would there be any connection between KIA and "killed in action" ?
SkyWalker
|
|
|
|
|
Mark Nischalke wrote: Having a Dispose method isn't enough by itself. Your class must implment IDisposable.
Well, "must" is not really true. Adding the IDisposable interface to the list of implemented interfaces for the class doesn't really make any difference. It doesn't change the implementation of the class in any way. Using the Dispose method works just fine without the interface, only you can't use the class in a using block as that requires the IDisposable interface.
It's just that the IDisposable interface is intended for this, so if you want to add disposability to a class in a well behaved way, you should implement the IDisposable interface.
Just to be clear.
---
Year happy = new Year(2007);
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
