Re: procedure - switch keys HELP, I need somebody!

Without looking at the code this can't be answered.

Also you don't mention your version.
In Oracle 8 and higher you have deferred constraints, so you at least don't need to disable the primary key.
Generally speaking though primary keys should not be updated, and the fact you are having this issue results in suspicions about bad design.

Hth,

Sybrand Bakker, Oracle DBA

"Marek Smith" <m.rezac_at_sh.cvut.cz> wrote in message news:3a5d88e2_at_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 Sat Jan 13 2001 - 03:51:36 CST