Varchar2 OUT param - "numeric or value error"

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

thrulogicAsked:
Who is Participating?
 
SujithConnect With a Mentor Data ArchitectCommented:
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
 
SujithData ArchitectCommented:
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
 
thrulogicAuthor Commented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
schwertnerConnect With a Mentor Commented:
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
 
SujithData ArchitectCommented:
Do you have to set the length of the out parameter ?
0
 
thrulogicAuthor Commented:
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
 
thrulogicAuthor Commented:
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
 
jamjaiCommented:
As I was looking to for solution since yesterday, this information resolved my issue. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.