Solved

StoredProcedure OleDb problem

Posted on 2004-04-13
13
704 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Save json data from URL using SSIS 1 77
Need help with a query 14 55
How does this modal gets closed? 6 42
Manage big list of parameter list 8 27
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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