Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.tools -> procedure - switch keys HELP, I need somebody!
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
![]() |
![]() |