Ora 01779 ошибка

A DML table expression clause is only useful when you need columns from more than one table. In your case, you can use a regular update with an EXISTS:

update web_userrole
set role = replace(role, 'FULL', 'READ')
where read_only <> 'Y'
    and exists
    (
        select 1/0
        from web_userdatasource
        where datasource = p_datasource
            and username = web_userrole.username
    );

If you really do need to use columns from both tables you have three options:

1. repeat the join in the SET and the WHERE clause. This is easy to build but not optimal.
2. DML table expression. This should work, if you have the correct indexes.

3. MERGE, below is an example.

   merge into web_userrole
   using
   (
       select distinct username
       from web_userdatasource
       where datasource = p_datasource
   ) web_userdatasource on
   (
       web_userrole.username = web_userdatasource.username
       and web_userrole.read_only <> 'Y'
   )
   when matched then update
   set role = replace(role, 'FULL', 'READ');
   

This does not directly answer your question, but instead provides some work-arounds. I can’t reproduce the error you’re getting. I’d need a full test case to look into it further.

Generic advice for updatable views

One of the main problems with updatable views is the large number of restrictions on the queries they can contain. The query or view must not contain a lot of features, such as DISTINCT, GROUP BY, certain expressions, etc. Queries with those features may raise the exception «ORA-01732: data manipulation operation not legal on this view».

The updatable view query must unambiguously return each row of the modified table only one time. The query must be “key preserved”, which means Oracle must be able to use a primary key or unique constraint to ensure that each row is only modified once.

To demonstrate why key preserved is important, the below code creates an ambiguous update statement. It creates two tables, the first table has one row and the second table has two rows. The tables join by the column A, and try to update the column B in the first table. In this case it’s good that Oracle prevents the update, otherwise the value would be non-deterministic. Sometimes the value would be set to «1», sometimes it would be set to «2».

--Create table to update, with one row.
create table test1 as
select 1 a, 1 b from dual;

--Create table to join two, with two rows that match the other table's one row.
create table test2 as
select 1 a, 1 b from dual union all
select 1 a, 2 b from dual;

--Simple view that joins the two tables.
create or replace view test_view as
select test1.a, test1.b b_1, test2.b b_2
from test1
join test2 on test1.a = test2.a;

--Note how there's one value of B_1, but two values for B_2.
select *
from test_view;

A  B_1  B_2
-  ---  ---
1    1    1
1    1    2

--If we try to update the view it fails with this error:
--ORA-01779: cannot modify a column which maps to a non key-preserved table
update test_view
set b_1 = b_2;

--Using a subquery also fails with the same error.
update
(
    select test1.a, test1.b b_1, test2.b b_2
    from test1
    join test2 on test1.a = test2.a
)
set b_1 = b_2;

The MERGE statement does not have the same restrictions. The MERGE statement appears to try to detect ambiguity at run time, instead of compile time.

Unfortunately MERGE doesn’t always do a good job of detecting ambiguity. On Oracle 12.2, the below statement will occasionally work, and then fail. Making small changes to the query may make it work or fail, but I can’t find a specific pattern.

--The equivalent MERGE may work and changes "2" rows, even though there's only one.
--But if you re-run, or uncomment out the "order by 2 desc" it might raise:
--  ORA-30926: unable to get a stable set of rows in the source tables
merge into test1
using
(
    select test1.a, test1.b b_1, test2.b b_2
    from test1
    join test2 on test1.a = test2.a
    --order by 2 desc
) new_rows
    on (test1.a = new_rows.a)
when matched then update set test1.b = new_rows.b_2;

UPDATE fails at compile time if it is theoretically possible to have duplicates. Some statements that should work won’t run.

MERGE fails if the database detects unstable rows at run time. Some statements that shouldn’t work will still run.

I’m getting «ORA-01779: cannot modify a column which maps to a non key-preserved table» when I try to update a join. I’ve searched around the site and found a lot of advice on what key-preserved means and why it is necessary… but as near as I can tell I’m complying with that advice, and still getting the error.

I have two tables:

PG_LABLOCATION has, among other things, the columns:
"LABLOCID" NUMBER,
"DNSNAME" VARCHAR2(200 BYTE)

LABLOCID is the primary key, DNSNAME has a unique constraint

PG_MACHINE has, among other things, the columns:
"MACHINEID" NUMBER, 
"LABLOCID" NUMBER, 
"IN_USE" NUMBER(1,0) DEFAULT 0, 
"UPDATE_TIME" TIMESTAMP (6) DEFAULT '01-JAN-1970'

MACHINEID is a primary key
LABLOCID is a foreign key into LABLOCID in PG_LABLOCATION (its primary key)

The update I’m running is:

update 
  (select mac.in_use, mac.update_time
     from pg_machine mac 
     inner join pg_lablocation loc
       on mac.lablocid = loc.lablocid
     where loc.dnsname = 'value'
       and '02-JAN-2013' > mac.update_time
  )
set in_use = 1 - MOD( 101, 2 ), update_time = '02-JAN-2013';

I’m only updating values in one table (PG_MACHINE) and the join column in the other table is the primary key, which should make it key-preserved by my reading. I’m concerned that the where clause is causing the problem, but I tried removing the filter on mac.update_time and got the same error, and loc.dnsname has a unique constraint.

What’s even odder is that we have, like many folks, a dev and a prod environment. We did a complete schema and data migration from prod to dev. I’ve looked them both over and they have identical indexes and constraints. The query works in dev, but generates the above error in prod.

So two questions:

1) Can you see what’s wrong with my query?
2) Can you suggest what might be different between my dev and prod environment (e.g. server settings) that could cause this error in one but not the other?

У меня такая процедура:

create or replace procedure changePermissionsToRead( datasource in varchar2 ) IS begin update ( select * from WEB_USERROLE ur , WEB_USERDATASOURCE ds where ur.username = ds.username and ds.datasource = datasource and ur.READ_ONLY <> 'Y' ) r set r.role = replace(r.role, 'FULL', 'READ'); end; 

и я получаю следующую ошибку:

 ORA-01779 

но если выну обновление и напишу:

 update ( select * from WEB_USERROLE ur , WEB_USERDATASOURCE ds where ur.username = ds.username and ds.datasource = 'PIPPO' and ur.READ_ONLY <> 'Y' ) r set r.role = replace(r.role, 'FULL', 'READ'); 

тогда это хорошо работает. Вы можете сказать мне, что происходит?

• 1 Интересная проблема. Как откровенно предлагалось, вы должны сначала устранить двусмысленность datasource. Это одна из причин, чтобы всегда начинать имена параметров с p_. Я не могу легко воспроизвести проблему, было бы полезно, если бы вы могли добавить все необходимые DDL и DML воспроизвести это. Или просто используйте MERGE и избегайте проблемы.
• вы можете показать мне, как использовать слияние в этом случае?
• Отвечает ли это на ваш вопрос? Oracle — обновление соединения — таблица без сохранения ключа

Предложение табличного выражения DML полезно только тогда, когда вам нужны столбцы из более чем одной таблицы. В вашем случае вы можете использовать обычное обновление с EXISTS:

update web_userrole set role = replace(role, 'FULL', 'READ') where read_only <> 'Y' and exists ( select 1/0 from web_userdatasource where datasource = p_datasource and username = web_userrole.username ); 

Если вам действительно нужно использовать столбцы из обеих таблиц, у вас есть три варианта:

1. повторите объединение в предложении SET и WHERE. Это легко построить, но не оптимально.
2. Табличное выражение DML. Эта должен работать, если у вас есть правильные индексы.

3. ORA-01779 0, ниже приведен пример.

    ORA-01779 1

Это не дает прямого ответа на ваш вопрос, но вместо этого предоставляет некоторые обходные пути. Я не могу воспроизвести ошибку, которую вы получаете. Мне нужен полный тестовый пример, чтобы разобраться в этом дальше.

Общий совет для обновляемых представлений

Одна из основных проблем с обновляемыми представлениями — большое количество ограничений на запросы, которые они могут содержать. Запрос или представление не должны содержать много функций, таких как DISTINCT, GROUP BY, определенные выражения и т. Д. Запросы с этими функциями могут вызвать исключение «ORA-01732: операция манипулирования данными недопустима для этого представления».

Запрос обновляемого представления должен однозначно возвращать каждую строку измененной таблицы только один раз. Запрос должен иметь «сохраненный ключ», что означает, что Oracle должна иметь возможность использовать первичный ключ или уникальное ограничение, чтобы гарантировать, что каждая строка изменяется только один раз.

Чтобы продемонстрировать важность сохранения ключа, приведенный ниже код создает неоднозначный оператор обновления. Он создает две таблицы, первая таблица имеет одну строку, а вторая таблица — две строки. Таблицы соединяются по столбцу ORA-01779 2 и пытаются обновить столбец ORA-01779 3 в первой таблице. В этом случае хорошо, что Oracle предотвращает обновление, иначе значение будет недетерминированным. Иногда значение устанавливается на «1», иногда на «2».

 ORA-01779 4

Оператор ORA-01779 5 не имеет таких ограничений. Оператор ORA-01779 6, похоже, пытается обнаружить неоднозначность во время выполнения, а не во время компиляции.

К сожалению, ORA-01779 7 не всегда хорошо справляется с обнаружением двусмысленности. В Oracle 12.2 приведенный ниже оператор иногда срабатывает, а затем дает сбой. Внесение небольших изменений в запрос может привести к его работе или сбою, но я не могу найти конкретный шаблон.

 ORA-01779 8

ORA-01779 9 завершается ошибкой во время компиляции, если теоретически возможно наличие дубликатов. Некоторые заявления, которые должен работа не пойдет.

update ( select * from WEB_USERROLE ur , WEB_USERDATASOURCE ds where ur.username = ds.username and ds.datasource = 'PIPPO' and ur.READ_ONLY <> 'Y' ) r set r.role = replace(r.role, 'FULL', 'READ'); 0 завершается ошибкой, если база данных обнаруживает нестабильные строки во время выполнения. Некоторые заявления, которые не должен работа по-прежнему будет работать.