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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
schwertnerCommented:
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
SujithData 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Enterprise Software

From novice to tech pro — start learning today.