Solved

StoredProcedure OleDb problem

Posted on 2004-04-13
13
695 Views
Last Modified: 2007-12-19
Hi!

I have problems with geting data from Oracle database.
I have defined the following procedure (here is just header):


PROCEDURE network_cap2 (network_id_in IN NETWRK_CAP.NETWRK_ID%TYPE,
                  , result OUT RefCursorType
                                 );
and here is my c# code:

///////V1//////////////////////////////////////////////
OleDbCommand cmd = new OleDbCommand(STRProcName,ORADBHOraHelper.GetConnection());

cmd.CommandType = CommandType.StoredProcedure;

OleDbParameter p = new OleDbParameter(
    "network_id_in",
    OleDbType.Decimal,
    22);
   
p.Direction = ParameterDirection.Input;
   
p.Value = 160002000000000;

cmd.Parameters.Add(p);

DSRows = new DataSet("DSRESULT");

OleDbDataAdapter ODAAdapter = new OleDbDataAdapter(cmd);
ODAAdapter.Fill(DSRows,"TRESULT");
//////////////////////////////////////  

but problem is that code crash when i try to execute procedure. I get the
following error:

Error: ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'NETWORK_CAP2'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

I test the procedure in plsql and it works fine.


Then I extend procedure definition and add the following paramter:

PROCEDURE network_cap2 (network_id_in IN NETWRK_CAP.NETWRK_ID%TYPE,
                      , dummy_in IN NETWRK_CAP.BANDWIDTH_CODE%TYPE := NULL
                  , result OUT RefCursorType
                                 );

I dont know why but it is important that is string parameter(VarChar)

and the following C# code:

/////////V2///////////////////////////


OleDbCommand cmd = new OleDbCommand(STRProcName,ORADBHOraHelper.GetConnection());

cmd.CommandType = CommandType.StoredProcedure;


OleDbParameter p1 = new OleDbParameter(
    "network_id_in",
    OleDbType.Decimal,
    22);
   
p1.Direction = ParameterDirection.Input;
   
p1.Value = 160002000000000;

cmd.Parameters.Add(p1);

OleDbParameter p2 = new OleDbParameter(
    "dummy_in ",
    OleDbType.VarChar,
    22);
   
p2.Direction = ParameterDirection.Input;
   
p2.Value = "";

cmd.Parameters.Add(p2);

DSRows = new DataSet("DSRESULT");

OleDbDataAdapter ODAAdapter = new OleDbDataAdapter(cmd);
ODAAdapter.Fill(DSRows,"TRESULT");
   
//////////////////////////////////////////


and what is strange to me this code WORK. But I newer use the
VarChar parameter.

If enyone could solve this please help I'm losing my mind!!!

Or if enyone know some better solution.


Igor

p.s.

I'm using MSDAORA.1 provider



0
Comment
Question by:igora
  • 7
  • 6
13 Comments
 

Author Comment

by:igora
ID: 10820764
Hmmm even more unusal is if i define in c#  in V1 code paramater as VarChar then code work.

0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 10832676
Which line  does it crash?
0
 

Author Comment

by:igora
ID: 10839759
it crash when i call ODAAdapter.Fill(DSRows,"TRESULT");



now i changed the line

OleDbParameter p = new OleDbParameter(
    "network_id_in",
    OleDbType.Decimal,
    22);
   

with

OleDbParameter p = new OleDbParameter(
    "network_id_in",
    OleDbType.VarChar,
    22);
   

and it works but i'm still not satisfied
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 8

Expert Comment

by:rajaloysious
ID: 10839985
I guess that the issue is with the width of the decimal parameter
Try
a) by not giving a width
b) check up the width of the native Oracle paramater you are using in the database and give it appropriately.

Good Luck -)

0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 10840006
Also the numeric precision and the NumericScale of a decimal has by default a particular no of digits after the period and before it.

You can change the parameter type as OleDbType.Numeric and the width as 22.
0
 

Author Comment

by:igora
ID: 10840112
No, it didn't help!

If I try A solution it is the same if I define size=0.

Regards solution B I'm not sure if I understand you.
In oracle table it is defined as Number(15), that means, length 22, precision 15 and scale 0.


I try both to define as:

length, size, precision
15,15,0
22,15,0
22,0,0
38,38,0
38,0,0

and nothing helps



0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 10840351
Guess this conversation gets a little longer, but worth it, because i think u r missing a very small issue.

Comment these 3 lines
OleDbParameter p = new OleDbParameter(
    "network_id_in",
    OleDbType.Decimal,
    22);
p1.Direction = ParameterDirection.Input;
p1.Value = 160002000000000;
          AND
Insert these two lines
cmd.Parameters.Add ("network_id_in", OleDbType.Numeric,15). Value = 160002000000000;
cmd.Parameters(0).Direction = ParameterDirection.Input;
============

Try this please -)
Your parameter datatype in Oracle is just Numeric and width 15.
0
 

Author Comment

by:igora
ID: 10840515
:)))

Still nothing, it crashes with same error.
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 10840786
ok, itried the same and it crashes here as well.
ok one point i found out is that when you return ref type cursors the  following need to be used.

You should not use the below two lines
OleDbCommand cmd = new OleDbCommand(STRProcName,ORADBHOraHelper.GetConnection());
cmd.CommandType = CommandType.StoredProcedure;

Instead you should use the escape sequence like below.
STRProcName = "{call PCK_NAME.PROCNAME({resultset 0, rset},?)}"
OleDbCommand cmd = new OleDbCommand(STRProcName,ORADBHOraHelper.GetConnection());
( Ihave assumed in the procedure the first parameter is the ref cursor and has one additional parameter)

I have been using Oracle with VB 6 and now C# all along and we use this method to return ref cursors.
I have never seen that CommandType.StoredProcedure can be used with Oracle.

If u find sufficient evidence against this, please le me know, it should be news to me..

Thanks for your patience.
At last hope this solves the issue.
0
 

Author Comment

by:igora
ID: 10841287
Could you explain this line

STRProcName = "{call PCK_NAME.PROCNAME({resultset 0, rset},?)}"

please

what ? mark represent


and reultset 0

i suppose resultset is cursor name but i can not figure what 0 mean

and rset is name of additional parameter.


I find interesting hint in documentation


Note   The .NET Framework data providers that ship with the .NET Framework version 1.0 do not verify the Precision and Scale of Decimal parameter values, which can result in truncated data being inserted at the data source. If you are using the .NET Framework version 1.0, validate the Precision and Scale of your Decimal values before setting the parameter value.
For the .NET Framework version 1.1 and later, an exception is thrown when a Decimal parameter value is set with an invalid Precision. Scale values that exceed the Decimal

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingparameterswithdataadapters.asp
0
 
LVL 8

Accepted Solution

by:
rajaloysious earned 500 total points
ID: 10841593
I give u a example:
Oracle SP with 2 parameters
procedure GetQuotes (AAAin out RecARS,bQuote in Quote.QMQueue_is%type)
.NET
myCommand.CommandText = "{call GetQuotes({resultset 0, AAA},?)}"

Oracle SP with 3 parameters
procedure GetQuotes (AAAin out RecARS,bQuote in Quote.QMQueue_is%type, nVar in varchar2)
.NET
myCommand.CommandText = "{call GetQuotes({resultset 0, AAA},?,?)}"

=======
resultset 0 is a hardcoded constant, AAA is the procedure name and ? is for the parameters
In your case
myCommand.CommandText = "{call GetQuotes(?, {resultset 0, network_cap2 })}"

The documentation, i guess has nothing to do with this. I tried out from here as well...

Ok lets see how it goes....
0
 

Author Comment

by:igora
ID: 10841957
ok here is my code
/////////V3///////////////////////////////////////

STRProcName = "{call DBApiGet.network_cap2(?,{resultset 0,network_cap2 })} " ;


OleDbCommand cmd = new OleDbCommand(STRProcName,ORADBHOraHelper.GetConnection());

cmd.CommandType = CommandType.StoredProcedure;

OleDbParameter p = new OleDbParameter(
    "network_id_in",
    OleDbType.Decimal,
    22);
   
p.Direction = ParameterDirection.Input;
   
p.Value = 160002000000000;

cmd.Parameters.Add(p);

DSRows = new DataSet("DSRESULT");

OleDbDataAdapter ODAAdapter = new OleDbDataAdapter(cmd);
ODAAdapter.Fill(DSRows,"TRESULT");

//////////////////////////////////////////////


and throws me an error

syntax error in {call...} ODBC Escape

help one more time
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 10842287
Comment this line
cmd.CommandType = CommandType.StoredProcedure;
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question