Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 733
  • Last Modified:

StoredProcedure OleDb problem

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
igora
Asked:
igora
  • 7
  • 6
1 Solution
 
igoraAuthor Commented:
Hmmm even more unusal is if i define in c#  in V1 code paramater as VarChar then code work.

0
 
rajaloysiousCommented:
Which line  does it crash?
0
 
igoraAuthor Commented:
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
Technology Partners: 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!

 
rajaloysiousCommented:
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
 
rajaloysiousCommented:
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
 
igoraAuthor Commented:
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
 
rajaloysiousCommented:
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
 
igoraAuthor Commented:
:)))

Still nothing, it crashes with same error.
0
 
rajaloysiousCommented:
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
 
igoraAuthor Commented:
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
 
rajaloysiousCommented:
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
 
igoraAuthor Commented:
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
 
rajaloysiousCommented:
Comment this line
cmd.CommandType = CommandType.StoredProcedure;
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now