[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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

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;
0
gjutras
Asked:
gjutras
1 Solution
 
ishandoCommented:
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.
0
 
gjutrasAuthor Commented:
they do match at 500
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
gjutrasAuthor 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.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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).
0
 
gjutrasAuthor 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.
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
Vee_ModCommented:
Closed, 500 points refunded.
Vee_Mod
Community Support Moderator
0

Featured Post

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!

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