Toad World® Forums

Problem Testing Procedure


#1

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

I_WAREHOUSE_CODE CHAR(4);

I_PRODUCT_CODE CHAR(10);

I_OWNER_CODE CHAR(3);

I_STORE_CODE CHAR(4);

O_EXPIRY_DATE = 20090909

O_RETURN_CODE = 0

i.e procedure worked

Run via code tester and get results

O_EXPIRY_DATE = 0

O_RETURN_CODE = -1

-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

I_LOCATION_CODE CHAR(10);

Run the procedure manually and get results
AccessStylesCollection.png


#2

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 steven.feuerstein@quest.com and also it would be great to log this problem with Quest Support.

SF


#3

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


#4

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?


#5

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
)
AS
BEGIN
DBMS_OUTPUT.put_line ('WHC = ’ || LENGTH (i_warehouse_code));
END;
/

DECLARE
o_expiry_date replenishment_request.rr_expiry_date%TYPE;
o_return_code NUMBER;
BEGIN
d702a01s_replen_expiry (i_warehouse_code => ‘ABCCDDEEEEEEEEEEEEEEEE’
, i_product_code => ‘ABCCDDEEEEEEEEEEEEEEEE’
, i_owner_code => ‘ABCCDDEEEEEEEEEEEEEEEE’
, i_store_code => ‘ABCCDDEEEEEEEEEEEEEEEE’
, i_location_code => ‘ABCCDDEEEEEEEEEEEEEEEE’
, o_expiry_date => o_expiry_date
, o_return_code => o_return_code
);
END;
/

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!

SF


#6

Steven,

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.

Cheers
Chris


#7

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)…

SF