Toad World® Forums

Problem Testing Procedure


CREATE OR REPLACE procedure MDLDB.d702a01s_replen_expiry

( i_warehouse_code IN replenishment_request.rr_warehouse_code%TYPE,

i_product_code IN replenishment_request.rr_product_code%TYPE,

i_owner_code IN replenishment_request.rr_owner_code%TYPE,

i_store_code IN replenishment_request.rr_store_code%TYPE,

i_location_code IN replenishment_request.rr_location_code%TYPE,

o_expiry_date OUT replenishment_request.rr_expiry_date%TYPE,

o_return_code OUT number





O_EXPIRY_DATE = 20090909


i.e procedure worked

Run via code tester and get results



-1 is an exception code

Running the procedure manually and in code tester are giving different results.

I think the problem is to do with the input params being a set size and the sql select statements expecting specific sized input variables.

The procedure starts as follows :


the definitions as follows


Run the procedure manually and get results


Ah, CHAR…we had some issues with that type and I thought I had fixed it. Maybe not. Could you please prepare a support bundle (off the Help menu) that includes the program you show? I will see about reproducing it. Send the bundle to me at and also it would be great to log this problem with Quest Support.



Thanks for your reply Steven, i have emailed you the Support Bundle and have raised a Quest Support call “Case ID: 622017”.


Thanks, Chris. I have checked out the test definition we generate. I believe the problem has to do with how we are declaring the local variables passed to your program. Currently it looks like this:

     i_i_warehouse_code   CHAR (32767);
     i_i_product_code     CHAR (32767);
     i_i_owner_code       CHAR (32767);
     i_i_store_code       CHAR (32767);
     i_i_location_code    CHAR (32767);

Now, clearly, with CHAR, you end up with lots of padded blanks and that is likely the cause of the problem. So why don’t I use %TYPE to declare these, as they are defined in your parameter list?

Here’s MY problem: if I use %TYPE to declare these variables, then we can end up with this complication:

Oracle does NOT apply the %TYPE constraint on length to your actual parameter values. In other words, if an argument is defined %TYPE and the column is CHAR(4), you can STILL pass “ABCDEF” as an actual parameter.

So we moved away from that to this kind of hard-coded declaration. Works fine with VARCHAR2, but not with CHAR.

I am not at this moment sure about what to do with this conflict.

I generally DO need to declare a local variable to support more complex scenarios for an IN argument.

So let’s see if we can work around this issue.

Could you do me this favor: Open Test Editor for this program, and drill down into the list of Test Elements. Change the “Datatype as Declaration” value to CHAR(N) (N = actual length of column) for each of your CHAR arguments. Then try to run the test again. Does that fix the problem?


Here’s a demonstration of what I mean about Oracle NOT checking the constraint on size:

DROP TABLE replenishment_request
CREATE TABLE replenishment_request (
rr_warehouse_code CHAR(4),
rr_product_code CHAR(10),
rr_owner_code CHAR(3),
rr_store_code CHAR(4),
rr_location_code CHAR(10),rr_expiry_date NUMBER)

CREATE OR REPLACE PROCEDURE d702a01s_replen_expiry (
i_warehouse_code IN replenishment_request.rr_warehouse_code%TYPE
, i_product_code IN replenishment_request.rr_product_code%TYPE
, i_owner_code IN replenishment_request.rr_owner_code%TYPE
, i_store_code IN replenishment_request.rr_store_code%TYPE
, i_location_code IN replenishment_request.rr_location_code%TYPE
, o_expiry_date OUT replenishment_request.rr_expiry_date%TYPE
, o_return_code OUT NUMBER
DBMS_OUTPUT.put_line ('WHC = ’ || LENGTH (i_warehouse_code));

o_expiry_date replenishment_request.rr_expiry_date%TYPE;
o_return_code NUMBER;
d702a01s_replen_expiry (i_warehouse_code => ‘ABCCDDEEEEEEEEEEEEEEEE’
, i_product_code => ‘ABCCDDEEEEEEEEEEEEEEEE’
, i_location_code => ‘ABCCDDEEEEEEEEEEEEEEEE’
, o_expiry_date => o_expiry_date
, o_return_code => o_return_code

No problems calling this program like that. But INSIDE your program, this will be treated as CHAR, and that is where you run into the problem in your code (inside a SQL where clause specifically).

So Oracle’s ambiguity on this makes it tough on us…poor us!




I have made the changes in your workaround and they work fine.

Thanks for your time.

What to you want me to do about the Quest Support Call.



I am posting the workaround info there. You should tell Quest Support when they contact you that consider it closed (if you do; I really do not know how I can resolve this conflict - and it won’t be available for several months)…