|
Replies:
8
-
Pages:
1
-
Last Post:
Jan 14, 2005 8:08 AM
Last Post By: franmiguel
|
|
|
Posts:
8
Registered:
11/28/00
|
|
|
|
Error getting return value from function
Posted:
Jan 11, 2005 3:48 AM
|
|
|
|
Hello
I'm getting a error calling a function with ODP.NET from C#
Code:
OracleCommand oraCom = new OracleCommand("NORMALIZACION.nif",oraCon);
oraCom.CommandType = CommandType.StoredProcedure;
OracleParameter param1 = new OracleParameter("numnif",OracleDbType.Varchar2);
param1.Value= "73667866A";
param1.Direction = ParameterDirection.Input;
OracleParameter param2 = new OracleParameter("nif",OracleDbType.Varchar2);
param2.Size = 10; //FIXME line
param2.Direction = ParameterDirection.ReturnValue;
oraCom.Parameters.Add(param1);
oraCom.Parameters.Add(param2);
oraCom.ExecuteNonQuery();
nif_norm = oraCom.Parameters["nif"].Value.ToString();
if i write the FIXME line i get a error (ORA-06502) complaining about the size, no matter the value i wrote.
If i don't write the FIXME line, it works but nif_norm is always empty, although the function i call is a single return 'Hello';
Where am I wrong??
Any help, examples with varchar2 as return value???
BTW: the same code with the MS provider for Oracle works fine.
|
|
|
Posts:
1,346
Registered:
04/21/98
|
|
|
|
Re: Error getting return value from function
Posted:
Jan 11, 2005 1:20 PM
in response to: franmiguel
|
|
|
|
I have not tested your code, but (from what I see here) you are using bind by position (rather than by name) and you are binding the return value after the input parameter. Have you tried binding the return value first? ODP.NET will generate an anonymous block that will look something like:
begin :1 := function(:2); end;
The return value is the first parameter.
Hope that helps a bit...
- Mark
|
|
|
Posts:
8
Registered:
11/28/00
|
|
|
|
Re: Error getting return value from function
Posted:
Jan 12, 2005 12:09 AM
in response to: franmiguel
|
|
|
|
Ok, now it works, thank you very much.
It seems that you must to add the parameters in the right order, and you must define the size in Varchar2 variables, otherwise it will fail.
Geezz!!! If only ODP.NET documentation was better...
|
|
|
Posts:
291
Registered:
09/26/98
|
|
|
|
Re: Error getting return value from function
Posted:
Jan 12, 2005 2:48 AM
in response to: franmiguel
|
|
|
|
It seems that you must to add the parameters in the right order
If you want to bind parameters by name you can, you just need to do OracleCommand.BindByName, but the docs say this only applies to CommandType.Text.
|
|
|
Posts:
1,022
Registered:
10/05/98
|
|
|
|
Re: Error getting return value from function
Posted:
Jan 12, 2005 8:53 AM
in response to: franmiguel
|
|
|
If only ODP.NET documentation was better...
Amen to that. |
|
|
Posts:
1,346
Registered:
04/21/98
|
|
|
|
Re: Error getting return value from function
Posted:
Jan 12, 2005 10:19 AM
in response to: David Aldridge
|
|
|
|
You can always submit suggestions, corrections, etc. in regard to documentation through various channels...
Email: ntdoc_us@oracle.com
FAX: (650) 506-7365 Attn: Oracle Database for Windows Documentation
Traditional Mail:
Oracle Corporation
Oracle Database for Windows Documentation Manager
500 Oracle Parkway, Mailstop 1op6
Redwood Shores, CA 94065
USA
- Mark
|
|
|
Posts:
1,022
Registered:
10/05/98
|
|
|
|
Re: Error getting return value from function
Posted:
Jan 12, 2005 10:48 AM
in response to: Mark Williams
|
|
|
|
Good point -- i shall do so.
What I think I'm missing, in my quest for ODP.NET competence, is a solid set of example code. I've searched around and found various fragements here and there, but when it comes to data access from .NET there must surely be some finitie set of possibilities (if we can discount bad practices like building dynamic SQL statements without bind variables).
For example, possibly in increasing order of complexity ...
* Read a single value from a SQL statement ... "select emp_name from emp where rownum < 2"
* Read a single value by passing in a parameter ... "select emp_name from emp where user_id = :?"
* read multiple values ... "select emp_name from emp where user_id in (:?,:?)"
* execute a stored procedure with no in or out parameters
* retreive a value from a function with no parameters
* pass a parameter to a stored procedure
* read an out parameter from a stored procedure
Then work with in and out ref cursors, blobs, whatever.
Thoughts?
|
|
|
Posts:
1,346
Registered:
04/21/98
|
|
|
|
Re: Error getting return value from function
Posted:
Jan 12, 2005 11:07 AM
in response to: David Aldridge
|
|
|
I'm not sure how helpful it would be, but I have two sample chapters from my book on my website. One of them is Chapter 2 - Data Retrieval. I don't deal with using stored procedures, functions, or ref cursors in that chapter - I do that in Chapter 5 though. The other sample chapter is on advanced connections and authentication.
I can appreciate what you are saying - I prefer to have a decent set of example code to work through in order to get a solid foundation. I assume you have looked at the samples on OTN, though here is a link if you have not:
http://www.oracle.com/technology/sample_code/tech/windows/odpnet/index.html
If you want to look at the sample chapter(s) they are in PDF format. My personal site is listed in my profile which you should see by clicking on my name. It is very basic at the moment so don't set your expectations too high!
- Mark |
|
|
Posts:
8
Registered:
11/28/00
|
|
|
|
Re: Error getting return value from function
Posted:
Jan 14, 2005 8:08 AM
in response to: franmiguel
|
|
|
I think that i will ask my boss to buy your book 
It seems pretty good, and i really need some good reference (with examples) about ODP.NET |
|
|
|
Legend
|
|
Guru : 2500
- 1000000
pts
|
|
Expert : 1000
- 2499
pts
|
|
Pro : 500
- 999
pts
|
|
Journeyman : 200
- 499
pts
|
|
Newbie : 0
- 199
pts
|
|
Oracle ACE Director
|
|
Oracle ACE Member
|
|
Oracle Employee ACE
|
|
Helpful Answer
(5 pts)
|
|
Correct Answer
(10 pts)
|
|
