Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> procedure - switch keys HELP, I need somebody!

procedure - switch keys HELP, I need somebody!

From: Marek Smith <m.rezac_at_sh.cvut.cz>
Date: Thu, 11 Jan 2001 11:19:56 +0100
Message-ID: <3a5d88e2@news.cvut.cz>

Hello!
I automatically generate procedures for switching keys of two rows in table. The primary key restriction is during switching of course inactive. First I find the values of switched keys (are dereferenced with the input parameters). Then I store the rowid of one of them. Then comes the really switching...
Till now everything is fine....but... I compile this code twice with different results:
Oracle 7 no problem
Oracle 8 - ORA-06540: PL/SQL: compilation error

                 ORA-06553: PLS-906: compilation is not possible

I think that the error is caused with bad installed server. Am I right? Do you know any other way to switch values of key columns? Thank you for any suggestions. Marek

(All the decode stuff in the code forces that null = null.)

CREATE PROCEDURE DATA_SYNCH474747474_SWR0000004(index1 IN NUMBER, index2 IN NUMBER) AS CURSOR cursor1 IS
SELECT ENAME, JOB
FROM DATA_SYNCH474747474_DFT0000004
WHERE diffIndex = index1;

CURSOR cursor2 IS
SELECT ENAME, JOB
FROM DATA_SYNCH474747474_DFT0000004
WHERE diffIndex = index2;

row1 cursor1%ROWTYPE;
row2 cursor2%ROWTYPE;
rowID1 ROWID;

BEGIN
  open cursor1;
  open cursor2;
  FETCH cursor1 INTO row1;
  FETCH cursor2 INTO row2;

  SELECT rowid
  INTO rowID1
  FROM BONUS
  WHERE (DECODE (ENAME, null, 'DATA_SYNCH474747474_NULL', ENAME),

                  DECODE (JOB, null, 'DATA_SYNCH474747474_NULL', JOB)) =
                  (DECODE (row1.ENAME, null, 'DATA_SYNCH474747474_NULL',
row1.ENAME),
                   DECODE (row1.JOB, null, 'DATA_SYNCH474747474_NULL',
row1.JOB));

  UPDATE BONUS
  SET (ENAME, JOB) = (row1.ENAME, row1.JOB)   WHERE (DECODE (ENAME, null, 'DATA_SYNCH474747474_NULL', ENAME),

                  DECODE (JOB, null, 'DATA_SYNCH474747474_NULL', JOB)) =
                 (DECODE (row2.ENAME, null, 'DATA_SYNCH474747474_NULL',
row2.ENAME),
                  DECODE (row2.JOB, null, 'DATA_SYNCH474747474_NULL',
row2.JOB));

  UPDATE BONUS
  SET (ENAME, JOB) = (row2.ENAME, row2.JOB)   WHERE rowid = rowID1;

  close cursor1;
  close cursor2;
END; Received on Thu Jan 11 2001 - 04:19:56 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US