Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Red face Unanswered: Need help in this SP code error fix !!!!!

    The following are the two stored procedures I am calling one from the other. When I execute the "uspgetproductcode" procedure through Pl/SQL with parameters it works fine. When I call it from the "edproductcodes" and did debug and the procedure sends the same Pl/SQL working parameters it fails with "ORA-06502" and "ORA-06512" Buffer too small error stack at line:
    productname := cipher ( last but line in "uspgetproductcode" ). Can some one say how to fix this? I tried all that I know of ( I am not a good Pl/SQL guy ).

    Many thanks for the help and any help is appreciated.

    Thanks, Vinnie

    The stored procedure codes:

    The call in the following procedure is sending parameters to call is: uspGetProductCode ('234A345B876' , 1234 , productname ) .

    This works fine if in Pl/SQL when I execute at Pl/SQL command prompt but fails when I execute in Pl/SQL the procedure call: edproductcodes.


    CREATE OR REPLACE procedure edproductcodes ( devalue in varchar2 default 'E' )
    IS
    pkey smallint;
    productname varchar2(255);
    mpid varchar2(30);
    pval char(11);
    id int;

    cursor V_ed is
    select val1, val2
    from mytable1
    where val2 is not null ;
    CursorVal V_ed %ROWTYPE;

    BEGIN
    select max(value) into pkey from table2 ;
    open V_ed ;
    LOOP
    fetch V_ed into mpid, pval;

    if V_ed %NOTFOUND then
    --No more rows to process
    EXIT ;
    end if;

    uspGetProductCode (pval, pkey, productname ) ;

    if productname is not null then
    update table1 set val2 = productname where val1=mpid ;
    end if;
    end loop ;
    close V_ed ;
    END ;



    CREATE OR REPLACE PROCEDURE uspGetProductCode
    ( product IN varchar2,
    productCode IN varchar2,
    productname IN OUT varchar2
    )



    IS
    -- Declare variables.
    val varchar2(255);
    val2 varchar2(255);
    state smallint;
    key smallint;
    tempSwap smallint;
    a smallint;
    b smallint;
    N smallint;
    temp smallint;
    i smallint;
    j smallint;
    k smallint;
    cipherby smallint;
    cipher varchar2(255);
    code varchar(64);

    -- The RC4 algorithm works in two phases, key setup and ciphering.
    -- Key setup is the first and most difficult phase of this algorithm.
    -- During a N-bit key setup (N being your key length),
    -- the encryption key is used to generate an encrypting variable using two arrays,
    -- state and key, and N-number of mixing operations.
    -- These mixing operations consist of swapping bytes,
    -- modulo operations, and other formulas.
    -- A modulo operation is the process of yielding a remainder from division.
    -- This implementation of RC4 uses temporary tables in place of arrays.

    BEGIN
    -- Initialize variable values.
    code := productcode || ',./;''p[\`0-=Z';

    N := Length(code);
    a := 0 ;

    -- Initialize temp table with key and state values.
    WHILE a < 256
    loop
    BEGIN

    key := Ascii(Substr(code, MOD(a,N) + 1, 1));
    state := a ;
    INSERT INTO Keys(ID, state, intKey) VALUES(a, state, key) ;
    a := a + 1 ;
    END;
    END LOOP;

    -- Initialize variable values.

    b := 0;
    a := 0 ;

    -- The state array now undergoes 256 mixing operations.
    WHILE a < 256 LOOP
    BEGIN
    SELECT mod ( (b + state + intKey) , 256) , state
    INTO b, tempSwap
    FROM Keys
    WHERE ID = a ;

    UPDATE Keys
    SET state = (SELECT state FROM Keys WHERE ID = b)
    WHERE ID = a ;

    UPDATE Keys
    SET state = tempSwap
    WHERE ID = b ;
    a := a + 1 ;
    END;
    END LOOP;

    -- Initialize variable values.
    SELECT 0, 0, 1, '', 0
    INTO i, j, a, cipher, cipherby
    from dual;

    -- Once the encrypting variable is produced from the key setup,
    -- it enters the ciphering phase,
    -- where it is XORed with the plain text message to create and encrypted message.
    -- XOR is the logical operation of comparing two binary bits.
    -- If the bits are different, the result is 1.
    -- If the bits are the same, the result is 0.
    -- The string is decrypted by XORing the encrypted message with the same encrypting key.
    WHILE a < Length(product) + 1 LOOP
    BEGIN

    SELECT
    MOD ( (i + 1) , 256),
    MOD ( (j + state) , 256),
    state
    INTO i, j, temp
    FROM Keys
    WHERE ID = i;

    UPDATE Keys
    SET state = (SELECT state FROM Keys WHERE ID = j)
    WHERE ID = i ;

    UPDATE Keys
    SET state = temp
    WHERE ID = j ;

    SELECT state
    INTO k
    FROM keys
    WHERE ID = MOD (((SELECT state FROM Keys WHERE ID = i) || (SELECT state FROM Keys WHERE ID = j)) , 256) ;


    val2 := substr(product, a, 1);
    val := ascii(val2);

    -- cipherby := POWER (Ascii (Substr(product, a, 1) ) , k ) ;

    select ((val + k) - bitand(val, k)*2) into cipherby from dual ; -- Exclusive BIT OR operation
    cipher := cipher || CHR (cipherby) ;
    a := a + 1 ;
    END;
    END LOOP;

    -- Clean up.
    delete from Keys ;
    -- Set ouput variable.
    productName := cipher ;

    END;

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    you have pkey defined as a smallint in the first procedure and the parameter productCode as a varchar2, which is it?
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Jan 2003
    Location
    Atlanta
    Posts
    134

    Question Thanks I tried changing pkey def to varchar2(10)

    in the calling procedure "edproductcodes" and recompiled and it failed again at the same line in "uspgetproductcode" on the line
    productname := cipher ;

    with ORA-06502 "Character string buffer too small" error. The pkey value is passing like '12345'.

    Any thoughts?

    Vinnie

  4. #4
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    without debugging all your code, your varchar2's like productname and cipher are a little small - 255 - when at least one of your loops are for 256

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •