?
Solved

Varchar2 OUT param - "numeric or value error"

Posted on 2008-11-19
8
Medium Priority
?
3,592 Views
Last Modified: 2012-05-05
Hi,
I am getting an error "numeric or value error" when calling a Proc with a varchar2 OUT param, using ODP.NET.  

Thanks in advnace..

create or replace
PROCEDURE p_batch_heartbeat
(
  v_admin_location IN CHAR DEFAULT NULL,
  v_poller_identity IN VARCHAR2 DEFAULT NULL,
  v_last_poller_identity OUT VARCHAR2,
  v_suspend_batch OUT NUMBER
)
AS
  v_running NUMBER(1,0);   
BEGIN
...
 
            OracleCommand runBatchCommand = new OracleCommand("p_batch_heartbeat", connection);
            runBatchCommand.BindByName = true;
            runBatchCommand.CommandType = CommandType.StoredProcedure;
            runBatchCommand.Parameters.Add("v_admin_location", location);
            runBatchCommand.Parameters.Add("v_poller_identity", pollerIdentity);
            runBatchCommand.Parameters.Add("v_last_poller_identity", OracleDbType.Varchar2,ParameterDirection.Output);
            runBatchCommand.Parameters.Add("v_suspend_batch", OracleDbType.Decimal, ParameterDirection.Output);

Open in new window

0
Comment
Question by:thrulogic
8 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 22993276
Why is v_admin_location defined as CHAR?

Try to run the procedure standalone from sqlplus, with the same value of arguments passed from the .NET code.
It will help you to narrow down the error.
Post the procedure code, may be we can find out something from there.
0
 

Author Comment

by:thrulogic
ID: 22993906
Hi,  yes I have narrowed it down to the varchar2 out param.  It works when running in Oracle SQL Developer but doesn't when calling it in c# using ODP.NET.  

I've attached a simlpified version of the code which gives the following error (in c#)::

ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SMITBRE.P_TEST_VARCHAR_OUT", line 10
ORA-06512: at line 1
create or replace
PROCEDURE p_test_varchar_out
(
  v_in_string  IN VARCHAR2,
  v_out_string OUT VARCHAR2
)
AS
BEGIN
 
  SELECT v_in_string
  INTO v_out_string
  FROM DUAL;
  
  DBMS_OUTPUT.PUT_LINE('V_OUT_STRING = ' || V_OUT_STRING);
  
END;
 
----------------------
Client Code:
 
        [Test]
        public void TestVarChar2Out()
        {
            OracleConnection connection = DBServices.GetPalmEngineConnection();
            connection.Open();
 
            OracleCommand runBatchCommand = new OracleCommand("p_test_varchar_out", connection);
            runBatchCommand.BindByName = true;
            runBatchCommand.CommandType = CommandType.StoredProcedure;
            runBatchCommand.Parameters.Add("v_in_string", "CanYouHearMe");
            runBatchCommand.Parameters.Add("v_out_string", OracleDbType.Varchar2, ParameterDirection.Output);
 
            runBatchCommand.ExecuteNonQuery();
 
            string outString = (string) runBatchCommand.Parameters["v_out_string"].Value;
            System.Diagnostics.Debug.WriteLine(outString);
            
            connection.Close();
 
        }

Open in new window

0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 300 total points
ID: 22994569
You have also to define P_TEST_VARCHAR_IN

  runBatchCommand.Parameters.Add("v_in_string", "CanYouHearMe");

using

OracleDbType.Varchar2

Also instead

SELECT v_in_string
  INTO v_out_string
  FROM DUAL;

use

 
 v_out_string := v_in_string;
 

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 27

Expert Comment

by:sujith80
ID: 22995045
Do you have to set the length of the out parameter ?
0
 

Author Comment

by:thrulogic
ID: 22995408
Thanks, I have made both your suggested changes and the issue has 'progressed'  to a different one:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SMITBRE.P_TEST_VARCHAR_OUT", line 9
ORA-06512: at line 1

So I have tried adding length as well as follows:
runBatchCommand.Parameters.Add("v_in_string", OracleDbType.Varchar2,50,"CanYouHearMe", ParameterDirection.Input);
 runBatchCommand.Parameters.Add("v_out_string", OracleDbType.Varchar2,50, ParameterDirection.Output);

But am still getting the same error.  Dio UI have to specify the length of the params in procedure declaration.  I have tried this as well but the procedure doesn't compile in sql developer..


0
 
LVL 27

Accepted Solution

by:
sujith80 earned 1200 total points
ID: 22995639
wait
>> runBatchCommand.Parameters.Add("v_out_string", OracleDbType.Varchar2, ParameterDirection.Output)

I dont think this is the right way of doing it. For input parameters it seems ok. But for output parameters you need to pass a locally declared string type variable. You need to bind a local variable with the output parameter from the stored procedure. ( I am not a .NET person to show you an example)
0
 

Author Comment

by:thrulogic
ID: 23003744
Sujith,
Yes, it seems that you do need to pass in an empty variable to get it to work (a bit quirky if you ask me - as it expects you to call it as if it was an input variable, and doesn't use or fill the variabel you pass in?)

working code is attached.  Thanks for both of your help

Note: I also had to cast the output from OracleString.TosString() to extract out value.

Cheers,
Brendon.

        [Test] 
        public void TestVarChar2Out()
        {
            OracleConnection connection = DBServices.GetPalmEngineConnection();
            connection.Open();
 
            string outString = "";
            OracleCommand runBatchCommand = new OracleCommand("p_test_varchar_out", connection);
            runBatchCommand.BindByName = true;
            runBatchCommand.CommandType = CommandType.StoredProcedure;
            runBatchCommand.Parameters.Add("v_in_string", OracleDbType.Varchar2,  50, "CanYouHearMe", ParameterDirection.Input);
            runBatchCommand.Parameters.Add("v_out_string", OracleDbType.Varchar2, 50, outString, ParameterDirection.Output);
 
            runBatchCommand.ExecuteScalar();
            //string outString = (string) runBatchCommand.Parameters["v_out_string"].Value;
            outString = runBatchCommand.Parameters["v_out_string"].Value.ToString();
            System.Diagnostics.Debug.WriteLine(outString);
            
            connection.Close();
 
        }

Open in new window

0
 

Expert Comment

by:jamjai
ID: 23213545
As I was looking to for solution since yesterday, this information resolved my issue. Thanks
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In our personal lives, we have well-designed consumer apps to delight us and make even the most complex transactions simple. Many enterprise applications, however, are a bit behind the times. For an enterprise app to be successful in today's tech wo…
Messaging apps are amazing tools with the power to do a lot of good, but the truth is the process of collaborating with coworkers requires relationships established through meaningful communication - the kind of communication that only happens face-…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

864 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