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

    Red face Unanswered: Sp does not compile in 8i but does in 9i

    Can some one say why the following SP fails to compile in 8i with error shown below ( at pipe signs ) but compiles well with 9i. I guess MOD function or pipe signs have been there in Oracle-8i too. Tried editing the file and putting back pipe signs but no luck so far. Any help is appreciated. The line it complains is:

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


    Vinnie

    This is the error:

    LINE/COL ERROR

    -------- -----------------------------------------------------------------

    77/21 PLS-00103: Encountered the symbol "SELECT" when expecting one of

    the following:

    ( - + mod not null others <an identifier>

    <a double-quoted delimited-identifier> <a bind variable> avg

    count current exists max min prior sql stddev sum variance

    execute forall time timestamp interval date

    <a string literal with character set specification>

    <a number> <a single-quoted SQL string>



    77/58 PLS-00103: Encountered the symbol "|" when expecting one of the

    following:



    LINE/COL ERROR

    -------- -----------------------------------------------------------------

    ; return returning and or



    SP Code:

    CREATE OR REPLACE PROCEDURE uspGetProductCode

    ( product IN varchar2,

    productCode IN varchar2,

    productname IN OUT varchar2

    )

    IS

    -- Declare variables.

    val varchar2(256);

    val2 varchar2(256);

    state smallint;

    key smallint;

    tempSwap smallint;

    a smallint;

    b smallint;

    N smallint;

    temp smallint;

    i smallint;

    j smallint;

    k smallint;

    cipherby smallint;

    cipher varchar2(256);

    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
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Maybe you'll have to change the code; I don't have 8i to test it, but - there were situations when code which used to work in SQL doesn't work in PL/SQL (or, as you can see, what works in a higher Oracle version doesn't necessarily have to work in lower version). So, declare a few more variables
    Code:
    l_var1 keys.state%TYPE;
    l_var2 keys.state%TYPE;
    l_var3 NUMBER;
    and use them in the procedure as
    Code:
    SELECT state INTO l_var1
      FROM keys
      WHERE id = i;
      
    SELECT state INTO l_var2
      FROM keys
      WHERE id = j;
      
    l_var3 := MOD(TO_NUMBER(l_var1 || l_var2), 256);
    
    SELECT state INTO k
    FROM keys WHERE id = l_var3;
    Not very elegant, I know, but I hope you'll get the point.

  3. #3
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    What is that supposed to do ?

    Probably the reason is that the PL/SQL and SQL engine in 8i were not in 'sync' in what respect to features.

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

    Talking Thanks that is what I was thinking

    and that more variables and assignment worked.

  5. #5
    Join Date
    Mar 2007
    Location
    Tulsa, Oklahoma
    Posts
    1
    another approach might be to insert a cast statment. Sometimes an earlier version does not do the implicit conversions the way the 9i version. You can always explicitly convert the value with a CAST statment... or use the variable approach. Whatever works!

Posting Permissions

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