We help IT Professionals succeed at work.

Oracle call of stored procedure from ODP.NET throws ORA-06502: PL/SQL: numeric or value error exception

Medium Priority
2,211 Views
Last Modified: 2013-12-19
I only get the error the the product_code is longer than 5 characters.  It works fine no matter what in toad/sqlplus.  But has the character length issue of .net.
My stored proc is:
create or replace package  wsTRAIN_cursor as
  procedure get_prod_info_by_prodcode(
    product_code in varchar2,
    products_ref_cursor out sys_refcursor
);
end;
/
create or replace package body wsTRAIN_cursor as
procedure get_prod_info_by_prodcode(
    product_code in varchar2,
    products_ref_cursor out sys_refcursor
    ) is
    begin
        open products_ref_cursor for
        select
               pf.id "ProductFamilyID",
            p.id "ProductID",
            P.NAME "ProductCode",
            pf.identification_code "FamilyCode",
            pf.NAME "FamilyName",
            dt.NAME "Division",
            ef.text2 "BusinessUnit",
            ef.text3 "ClassofTrade",
            P.KEYWORDS "ProductDescription",
            P.ALTERNATE_PRODUCT_DESCRIPTION "AltProductDescription",
            pst.name "ProductStatus",
            p.STATUS_DATE "ProductStatusDate",
            ECT.NAME "EUClass",
            ECR.NAME "EUClassRule",
            P.NATIONAL_DRUG_CODE "NationalDrugCode",
            P.SHELF_LIFE "ShelfLife",
            P.CE_MARKED_FLAG "CEMarked",
            P.CE_DATE "CEDate",
            ef2.text12 "CountryofOrigin"
        from
            ods.product_family pf,
            ods.extra_fields ef,
            ods.division_type dt,
            ods.product p,
            ods.extra_fields ef2,
            ods.prod_status_type pst,
            ods.eu_class_type ect,
            ods.eu_class_rule_TYPE ecr
        where
            pf.EXTRA_FIELDS_ID = ef.id
          AND pf.division_type_id = dt.id
          AND PF.ID = P.PRODUCT_FAMILY_ID
          AND P.EXTRA_FIELDS_ID = EF2.ID(+)
          AND p.STATUS_ID = pst.id(+)
          AND P.EU_CLASS_TYPE_ID = ECT.ID(+)
          AND P.EU_CLASS_RULE_TYPE_ID = ECR.ID(+)
          and p.NAME = product_code;        
    end;
end;
/

My .net code is:
        // create connection
        OracleConnection conn = new OracleConnection("Data Source="+datasource+";User Id="+userid+";Password="+password+";");

        // create the command object and set attributes
        OracleCommand cmd = new OracleCommand("wsTRAIN_cursor.get_prod_info_by_prodcode", conn);
        cmd.CommandType = CommandType.StoredProcedure;

        // create parameter objects for the product_code
        OracleParameter product_code1 = new OracleParameter();
        product_code1.ParameterName = "product_code";
        // set type and size
        product_code1.OracleDbType = OracleDbType.Varchar2;
        product_code1.Size = 500;
        // this is an input parameter so we must indicate that fact
        product_code1.Direction = ParameterDirection.Input;
        // assign the value to be passed in
        product_code1.Value = productcode;
        // add the parameter to the collection
        cmd.Parameters.Add(product_code1);

        // create parameter objects for the products cursor
        OracleParameter products_ref_cursor = new OracleParameter();
        products_ref_cursor.ParameterName = "products_ref_cursor";
        // this is vital to set when using ref cursors
        products_ref_cursor.OracleDbType = OracleDbType.RefCursor;
        // this is an output parameter so we must indicate that fact
        products_ref_cursor.Direction = ParameterDirection.Output;
        // add the parameter to the collection
        cmd.Parameters.Add(products_ref_cursor);
       

        conn.Open();
        cmd.Prepare();
        // create a data adapter to use with the data set
        OracleDataAdapter da = new OracleDataAdapter(cmd);

        // create the data set
        DataSet ds = new DataSet();

        // fill the data set
        try
        {
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            throw;
        }
        finally
        {
            // clean up our objects release resources
            conn.Close();
            da.Dispose();
            product_code1.Dispose();
            products_ref_cursor.Dispose();
            cmd.Dispose();
            conn.Dispose();
        }
        return ds;
Comment
Watch Question

Commented:
do the sizes of all the column in the cursor you define in the .net code match the size of the columns in the database?
As you say it only happens if the product_code is longer than 5 characters, it would suggest that the corresponding field in the .net defined cursor is only 5 characters long.

Author

Commented:
they do match at 500
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
I've never worked with .NET applications, but an ORA-06502 error is almost always something internal to Oracle that is independent of the calling client application.  This is usually caused by something trying to force more characters into a CHAR or VARCHAR2 column or variable than what it was declared to handle.  I would not expect that in a procedure that has only a ref cursor (but then I've never worked with ref cursors either, since I don't need them for the Oracle client tools I use).

I do see three  things in your ref cursor that I don't like.  I don't think they contribute to the ORA-06502 error, but they could easily contribute to poor performance, either now or later, or unexpected results.  They are:
1. the "in" variable named: "product_code"
2. the outer joins
3. having the "known" values on the left side of the "=" sign in the "where" clauses

Explanations:

1. Be careful in PL\SQL to make sure that "in" paremeters and PL\SQL variables *NEVER* have the same name as a column of a table that is used in the procedure!  I would suggest a variable name like: "v_product_code" instead.  If the product table actually has a column named "product_code" and your procedure has a variable or "in" parameter with the same name, it will compile just fine.  But at run time, the results may (and likely will) surprise and confuse you and/or the users!

2. Outer joins can be *HUGE* performance-killers.  The actual performance impact will vary based on lots of factors (your version of Oracle, the SGA size, the initialization parameters, the numbers of records in the tables, etc.).  I would avoid them in Oracle9 and higher by using "nested selects" instaed of outer joins, like this:
        select
            pf.id "ProductFamilyID",
            p.id "ProductID",
            P.NAME "ProductCode",
            pf.identification_code "FamilyCode",
            pf.NAME "FamilyName",
            dt.NAME "Division",
            ef.text2 "BusinessUnit",
            ef.text3 "ClassofTrade",
            P.KEYWORDS "ProductDescription",
            P.ALTERNATE_PRODUCT_DESCRIPTION "AltProductDescription",
            (select pst.name from ods.prod_status_type pst
                       where pst.id = p.STATUS_ID) "ProductStatus",
            p.STATUS_DATE "ProductStatusDate",
            (select ECT.NAME from ods.eu_class_type ect
                       where ECT.ID = P.EU_CLASS_TYPE_ID) "EUClass",
            (select ECR.NAME from ods.eu_class_rule_TYPE ecr
                       where ECR.ID = P.EU_CLASS_RULE_TYPE_ID) "EUClassRule",
            P.NATIONAL_DRUG_CODE "NationalDrugCode",
            P.SHELF_LIFE "ShelfLife",
            P.CE_MARKED_FLAG "CEMarked",
            P.CE_DATE "CEDate",
            (select ef2.text12 from ods.extra_fields ef2
                       where ef2.id = P.EXTRA_FIELDS_ID) "CountryofOrigin"
        from
            ods.product p,
            ods.product_family pf,
            ods.extra_fields ef,
            ods.division_type dt
        where
            ef.id = pf.EXTRA_FIELDS_ID
          AND dt.id = pf.division_type_id
          AND PF.ID = P.PRODUCT_FAMILY_ID
          and p.NAME = product_code;

3. Note how I re-wrote the "from" and "where" clauses of your cursor to put the "driving' table first in the "from" clause (based on my assumption that your database uses Oracle's cost-based optimizer, not the older rule-based optimizer that was common in Oracle7 and 8) and to put the "known" values on the left side of the "=" sign.

Author

Commented:
It turns out there's a bug in 9.2.0.6 which is what I was using that has to do with varchar2 lengths getting messed up when called from odp.net (and other outside programs, I assume).  Oracle's solution is to upgrade the db to 9.2.0.7 or higher.  Which becuase of the size of the company I work in and validated/audited systems and change control isn't feasible.  How ever we are getting a nifty linux cluster and 10.0.2 installed in 2 months and we're moving our schema to it.  I already tested the same code against a 10.0.2 test db and it works fine there.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
To giutras:

I thought I might hear back from you after my previous posting.  I realize that comment addressed issues other than what you asked about, but they seem significant (to me at least,  in Oracle applications that I've worked with).

Author

Commented:
What you answered, honestly isn't something I'll use.  The code works and it's not a high volume system.  I won't be examining performance.
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
OK, I guess I should be grateful that there are programmers like you out there who don't care about performance.  That's what gave me the opportunity some years ago to get into Oracle programming and DBA work: by trying to improve the performance of systems others had developed.
Commented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.