Solved

StoredProcedure OleDb problem

Posted on 2004-04-13
13
686 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
Comment Utility
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
Comment Utility
Which line  does it crash?
0
 

Author Comment

by:igora
Comment Utility
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
 
LVL 8

Expert Comment

by:rajaloysious
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 8

Expert Comment

by:rajaloysious
Comment Utility
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
Comment Utility
:)))

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

Expert Comment

by:rajaloysious
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Comment this line
cmd.CommandType = CommandType.StoredProcedure;
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
Article by: Najam
Having new technologies does not mean they will completely replace old components.  Recently I had to create WCF that will be called by VB6 component.  Here I will describe what steps one should follow while doing so, please feel free to post any qu…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now