Oracle Procedure VARCHAR2 Parameter Size limit

Question

Good Morning.

I am facing a very strange problem. I have an Oracle stored procedure which will accept a VARCHAR2 parameter.

From my ASP.NET application, I am calling this stored procedure and passing a comma separated string to this parameter. The length of the comma separated string is 700 characters (including commas).

Now the problem is Oracle procedure is returning the error "3421: Application uses a value of the wrong type for the current operation".

If I delete some content of the input string and send it to procedure, then it is running successfully.

I read that VARCHAR2 in Oracle accepts a maximum of 4000 characters. I am sending only 700 characters. Still it is not taking it.

I tried to give the size to the procedure parameter. But it is giving compilation error and I read that we can't specify the size to oracle procedure prameter.

Can you please help me in resolving this issue.

Thanks Srinivas

Input String that is sent to VAR33 in SP_TESTPROC procedure:

    195083,195320,195815,196311,196247,196866,197002,197271,197294,197998,198107,
195322,195501,196537,196887,198194,198964,195125,196153,196454,196587,197158,
197082,197277,197482,198110,198312,196586,196403,196428,196510,196694,197075,
197543,198111,198351,195215,196399,197697,198200,198338,196267,195851,196069,
196435,197154,196411,196883,196898,196384,196892,196826,197138,198123,198590,
197384,196383,196438,197044,197831,198917,195787,198199,198120,195874,197155,
197262,197264,197447,198193,195730,196886,196891,196899,197076,198502,196232,
196538,196603,196857,196263,195380,196660,198041,198763,195616,195720,196430,
197559,197836,197844,198015,198666,195777,195951,196433,196795,195843,196398,
196448 




create or replace PROCEDURE   SP_TESTPROC 
     (VAR33  IN VARCHAR2 DEFAULT NULL
      RC1 IN OUT OMWB_EMULATION.GLOBALPKG.RCT1)
AS

BEGIN

        OPEN RC1 FOR
          SELECT *
          FROM   TABLE1 A
            INNER JOIN TABLE2 B ON A.ID = B.TABLE1ID
          WHERE A.ID IN (select TO_NUMBER(regexp_substr(SPCWKF_CASEASSIGNMASTERQUERY.VAR33,'[^,]+', 1, level)) from dual
                        connect by regexp_substr(SPCWKF_CASEASSIGNMASTERQUERY.VAR33, '[^,]+', 1, level) is not null);

END SP_TESTPROC;

A bit of code might help to analyze your problem... it might also help to know database version, driver version etc. — Erich Kitzmueller
Message clearly states wrong type, not anything about size. Is that the full literal error message? No ORA-... prefix? — Álvaro González
There is no ORA prefix to it. The Parameter type is VARCHAR2. I am passing string to it. The problem is coming only when the input string is more than 104 characters. — Srinivas
3421: Application uses a value of the wrong type for the current operation is not an Oracle message. Are you invoking this procedure through some sort of API, which perhaps believes that there's a limit on how long the string can be? Also, the VAR33 parameter is not used in the procedure, so what's the point of having it there in the first place? — Bob Jarvis - Reinstate Monica

krokodilko krokodilko · Accepted Answer · 2015-06-18T17:05:30

Try:

create or replace PROCEDURE   SP_TESTPROC 
     (VAR33  IN VARCHAR2 DEFAULT NULL
      RC1 IN OUT OMWB_EMULATION.GLOBALPKG.RCT1)
AS

BEGIN
   VAR33 := regexp_replace(VAR33, '[^,0-9]', '');

    OPEN RC1 FOR
      SELECT *
      ..........
      ..........
      ..........
END SP_TESTPROC;

the above will remove all unwanted and "hidden" characters from the string.

For example, if you are calling the procedure in this way in your code:

proc_name('1234,2345,
           2345,5432,
           4545,7777', ..... )

then the string contains a few hidden line feed characters and some spaces, and these characters are obvioulsy 'not number' - but the procedure is trying to convert them into the number using to_number.

Take a look at this demo:http://sqlfiddle.com/#!4/f270bc/1

CREATE TABLE t(
  x varchar(4000)
);

INSERT INTO t VALUES(
       '1234,2345,
        2345,5432,
        4545,7777'
);
select x, dump(x)
from t;

and you will see that a dump of the output contains some 'hidden' charachers (20-space, 10-line feed):
Typ=1 Len=47: 49,50,51,52,44,50,51,52,53,44,10,32,32,32,32,32,32,32,32,50,51,52,53,44,
53,52,51,50,44,10,32,32,32,32,32,32,32,32,52,53,52,53,44,55,55,55,55

Oracle Procedure VARCHAR2 Parameter Size limit

1 Answers