Solved

ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1

Posted on 2006-11-08
7
3,412 Views
Last Modified: 2013-12-12
Hi,

I am calling an Oracle stored procedure from an ASP.Net application.

The Oracle Stored Procedure is:

----------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE simple_test (

   a_UserCode      IN varchar2,

   a_ErrorMsg      OUT varchar2) AUTHID CURRENT_USER

AS

BEGIN

 a_ErrorMsg  := a_UserCode || 'test' ;

END;
----------------------------------------------------------------------

This procedeure works fine when I run it in SQL Plus, but when I run it from ASP.Net I get the following error:

----------------------------------------------------------------------
ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1
----------------------------------------------------------------------

The ASP.Net code is :

----------------------------------------------------------------------
        OleDbConnection1.Open()

        OleDbCommand1.CommandText = "simple_test"
        OleDbCommand1.CommandType = CommandType.StoredProcedure

        OleDbCommand1.Parameters.Add("a_UserCode", OracleType.VarChar)
        OleDbCommand1.Parameters("a_UserCode").Value = "TEST"

        OleDbCommand1.Parameters.Add("a_ErrorMsg", OracleType.VarChar)
        OleDbCommand1.Parameters("a_ErrorMsg").Direction = ParameterDirection.Output

        OleDbCommand1.ExecuteNonQuery()

        Label1.Text = OleDbCommand1.Parameters("a_ErrorMsg").Value

        OleDbConnection1.Close()
----------------------------------------------------------------------

Appreciate if anyone can help. I suspect the "OracleType.VarChar" as it should be VarChar2, but this is not availabe in ASP.Net and also you can't set the size of it from ASP.Net!
0
Comment
Question by:abcexpert
[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
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17904772
well, don't know the syntax, but i miss a maxlength-parameter like

OleDbCommand1.Parameters.Add("a_ErrorMsg", OracleType.VarChar, 4000)

meikl ;-)
0
 

Author Comment

by:abcexpert
ID: 17904871
When I add the size as you suggest in this way OleDbCommand1.Parameters.Add("a_ErrorMsg", OracleType.VarChar, 4000) I get the following error:

Specified argument was out of the range of valid values. Parameter name: Invalid OleDbType enumeration value: 22

Any Idea?
0
 
LVL 27

Expert Comment

by:kretzschmar
ID: 17904940
well, i have no docs, so i may give a sample value by mistake, which is out of a limited range

i found this on the web

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=357761&SiteID=1

this shows a value of 255

meikl ;-)
0
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 

Author Comment

by:abcexpert
ID: 17904983
Meikl,

Even if I change the value to 255 I get the same error.

Anybody has another idea about the origenal request?
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 17905597
See this link: http://www.dotnet247.com/247reference/msgs/51/255394.aspx - maybe it will help You.
0
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 500 total points
ID: 17905603
I mean: probably You should use OleDbType.VarChar instead of OracleType.VarChar
0
 

Author Comment

by:abcexpert
ID: 17905916
GGuzdziol,

Thanks the OleDbType.VarChar works fine ...
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

622 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