Toad World® Forums

Bug: Test fails/raises ORA-06502 – Record with a Field Char(1)


#1

Hello Quest Software,

my name is Kai and at the moment i’m building severalunittests with your code tester for my company. Unfortunatly i found a bug today. Anyway i think it’s a bug, because i haveno explanation for this behaviour, but i’ll show you an example, how toreproduce it.

My examplepackagepkg_foobar contains one record with two field (you can find the code below). One is a number(6) andthe other one will be char(1). Besides this record the package will alsocontain a function. This function will have two parameters, a number(6) and achar(1). The function won’t do anything but moving the parameters into therecord and return a pls_integer (always a 0). If you build a test for this withyour code tester, the test will always fail with the error ORA-06502:PL/SQL: numerischer oder Wertefehler: character string buffer too smallpointing at the line where you move the char-parameter into the char-field ofthe record. I guess that there may not enough space for the quotes around thechar (without the quotes the created testpackage interprets the value as anundefined variable).

If you can reproduce this issue and may have a solution forit, please let me know. I hope this are enough information to get the point J

thanks in advance for your help

Kai Blankschän


Examplecode of my Package:

CREATE OR REPLACE PACKAGE pkg_foobar AS

TYPE Trec_pkg_foobar IS RECORD(
n_foo NUMBER(6),
c_bar CHAR(1));

FUNCTION a#fct_do_something(pn_foo NUMBER,
pc_bar CHAR) RETURN PLS_INTEGER;

END pkg_foobar;

CREATE OR REPLACE PACKAGE BODY pkg_foobar AS

FUNCTION a#fct_do_something(pn_foo NUMBER,
pc_bar CHAR) RETURN PLS_INTEGER IS
pi_status PLS_INTEGER := 0;
rec_foobar pkg_foobar.Trec_pkg_foobar;
BEGIN
rec_foobar.n_foo := pn_foo;
rec_foobar.c_bar := pc_bar;

  RETURN(pi_status);

END a#fct_do_something;

END pkg_foobar;


#2

Thanks, Kai, for this detailed report. I will reproduce your issue and then get it fixed for the 1.9 release (actually, it is very similar to another bug reported by our QA team, so I think it may already be fixed!).

Warm regards, SF


#3

Kai, after taking a closer look, this is a different situation and it raises a question for me.

Your program accepts a string of type CHAR. There is no limit on the size of the that fixed length string. Only inside the program do you assign it to an “internal” field of size CHAR(1).

The way Code Tester works is that we declare variables in our test code to hold the values you provide (and then pass on to the call to your program). Generally this approach works fine, but with CHAR we have an issue.

I need to declare that variable with a certain length. Currently we use 32767, largest size possible. But this causes a problem since it pads the variable with spaces - and then causes your VALUE_ERROR exception.

At this point, I am not sure how to resolve the problem. BUT - you should have a pretty straightforward work around.

Open Test Editor for your program. Drill down to Test Elements, and the CHAR argument. Change the datatype from CHAR(32767) to CHAR(1). Do the same thing for any inputs of test cases.

Then try to run your test. It should get around this issue. Let me know if this works.

Thanks, SF


#4

Hi Steven,
you’re right, if i change the size manually for the char-variable, the test will succeed. So this workaround helps me to fix the failing test, but it would be nice if i don’t need to manipulate my testcases like this. You said this is similar to a bug your QA team reported. Can you tell me if this issue is fixed in release 1.9 or will be fixed in the future?

The problem that the code tester uses the full length of a datatype made me thought of my original problem. As i said i just created this example-package to reproduce the error. The actually problem was, that the field in the record and the parameter i put in there was a %TYPE of a field declared as char(1). I don’t know how your code tester exactly works, but if i uses a %TYPE, why do you still uses all possible space for this referenced type? It’s comprehensible if you uses the maximum size if i only gave you a char - even if i can’t specify the size of a parameter in the spec of a function/procedure - but if i use a %TYPE you can get the real size from the connected table don’t you?

I rebuild my test again and tried it with VARCHAR2(1) and it causes the same problem as the CHAR(1). Don’t get me wrong, i like your software pretty much, but to manipulate my testcases (if i gave you a %TYPE) is a bit annoying and i guess that i’m not the only person who got that error. I hope you can solve this issue. In the meantime i’ll use your workaround to fix my testcases.

thanks for your quick and helpful support. I’m looking forward to the further releases of code tester.

kind regards
Kai

PS: I’m sorry for the missing spaces in my first post. I copied the text from and editor into the inputfield :frowning:


#5

Kai,

First, the bug that I fixed re: CHAR is not the same as the situation you have.

Second, you mention %TYPE, but that was not in the code that you sent me. Did you make this change and try again to see how Code Tester handled it? I just want to make sure we are “on the same page” as we work though this issue.

Generally, though, I can tell you that Code Tester does not (yet) look inside your program and make any attempt to understand how it works, what it is trying to do. You define a record in the package spec, but the only use of that record is inside the body of your function. There is no way to know, looking at the parameter list, what a limit on your CHAR inputs should be. Does this make sense?

Third, I don’t see what you mean by you get the same problem even if it is VARCHAR2(1). If I change the record and parameter type to VARCHAR2, then I only get that value_error exception when I provide an input value with more than one character. But of course this will raise V_E! That’s the way your code is designed. So you would simply define an outcome to check for this exception (assuming this is expected behavior).

Regards, SF


#6

Hi Steven,
First, thanks for your fast answer (if I’m right, it’s like middle of the night at your location?).

I guess you are misunderstanding me, so I hope I can help you to get my point clear :slight_smile:

The problem I mentioned in my first post - string buffer error for CHAR(1) - is referenced to my original problem, which I told you in my second post. In my “real” testcase and package I’ve got a record with a %TYPE of a field declared as CHAR(1). So I broke this problem down until I could reproduce it and make this easy understanding codesample (see my first post). As a solution you told me the workaround to limit the size of the variable in my testcase, which works pretty well for this issue. The problem I see here is, that I use the %TYPE-Expression in my “real” test (sorry that I haven’t mentioned that before) and so i could not limit the size of this %TYPE. Therefore i changed my tests from %TYPE to CHAR(1) and my tests works fine.

This brought me to the question, why Code Tester does not interpret %TYPE correctly in this case or why do you reserve the full size (which causes the problem, as you explained before) if you could get the exact maximum size from the referenced table?

The changed case with VARCHAR2(1) instead of CHAR(1) was just a little test, if you got the same problem with this datatype. I just want to improve your Code Tester :wink:

Kind regards,
Kai


#7

Kai,

Thanks for persisting in making sure I understand the problem.

So as I understand it now, it your parameter is of type:

table.column%type

If this is the case, then I can confirm that we were not handling this situation properly, and I have fixed the code so that the value_error exception will only be raised (by your program) if you pass in a value that is too large, which is correct behavior, so far as I can tell.

This fix will be available in the next 1.9 beta, which should be on the community website by end of week.

If I still am missing your point, please clarify.

Regards, SF


#8

Steven,

Finally I could explain my problem to you.

It’s good to hear that there will be a fix in release 1.9 for the value_error (the V_E caused by too large values is correct, that’s right). Will this solve both problems: table.column%TYPE and normal datatypes like char()? If this is the case, then my testcase will work with the new release.

Thanks for helpful postings and the upcoming release 1.9

Regards,
Kai

PS: Sorry that I didn’t mentioned “table.column.%TYPE” before, I thought it was clear that I meant it :wink:


#9

Kai,

What do you mean by “normal datatypes like char()”? When you pass in a CHAR type parameter, you do not specify a length. In this case, you will still need to manually change the max length if your usage of this parameter inside your program is restricted to a certain size. I don’t see a way around that one, for now.

SF


#10

Steven,

Of cource you can not know the real size of a normal datatype instead of a %TYPE, sorry for this confusion. So there will be a fix with release 1.9 which solve the problem with table.column%TYPE references to a CHAR(1)-Field in a table, am I right? I just want to get this clear for me :wink:

Regards,
Kai


#11

No worries. Yes, 1.9 will fix the problem with %TYPE.


#12

That are good news.
Thank you a lot for your support.

Regards,
Kai