procedure - switch keys HELP, I need somebody!

procedure - switch keys HELP, I need somebody!

Post by Marek Smit » Fri, 12 Jan 2001 19:19:56



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;

 
 
 

procedure - switch keys HELP, I need somebody!

Post by Sybrand Bakke » Sun, 14 Jan 2001 18:51:36


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


Quote:> 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;


 
 
 

1. HELP, I need Somebody, HELP :)) - SQL

Hi there.

I usually find out how to solve my problems by my own. But I got no
time anymore to find that answer.

I got this SQL in my VB5 Application.

SELECT * FROM CLIENTS WHERE MY_FUNCTION_IN_VB( Field ) = TRUE.

Why is it not  working ?
It keeps answer me that error

Can't not find the user-defined function ; or something like that.

I've already put  PUBLIC  FUNCTION  MY_FUNCTION_IN_VB.
I've put that function in a module of my  DATABASE TOO.

Thanks to everybody

2. System delay problem

3. HELP : Changing (switching) keys of a keyboard

4. DBA position available - Atlanta GA

5. "Help, I need somebody..."

6. United Way Software

7. Need somebody to help: A proposal of computerised system

8. Dates

9. Switching to 32 bit, need help converting database.

10. Help, can somebody help me ?

11. Switching FOREIGN KEY constraints off???

12. Query Analyzer switching windows hot-keys

13. Data switch for VGA, Mouse and Key board