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,360 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 27

Expert Comment

by:kretzschmar
Comment Utility
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
Comment Utility
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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:abcexpert
Comment Utility
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
Comment Utility
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
Comment Utility
I mean: probably You should use OleDbType.VarChar instead of OracleType.VarChar
0
 

Author Comment

by:abcexpert
Comment Utility
GGuzdziol,

Thanks the OleDbType.VarChar works fine ...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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

10 Experts available now in Live!

Get 1:1 Help Now