Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
Есть задача, технически выглядящая как необходимость выполнить заданный динамический блок по dblink-у. Блок в том числе может оказаться длиннее 32Кб.

Я решаю эту задачу так:

  C_REMOTE_EXECUTE constant varchar2(4000 char) := '
    declare
      l_cur integer;
      l_dummy number;
      l_accum number := 0;
      l_start number := 1;
      l_sql clob;
      t_sql dbms_sql.varchar2s@%dblink%;
      l_lob_len number;
      bind_variable_required exception;
      pragma exception_init(bind_variable_required, -1008);
    begin
      l_sql := :1;
      l_lob_len := dbms_lob.getlength(l_sql);
      while l_lob_len > l_accum
      loop
        t_sql(t_sql.count + 1) := substr(l_sql, l_accum + 1, 128);
        l_accum := l_accum + 128;
      end loop;
      l_cur := dbms_sql.open_cursor@%dblink%;
      begin
        dbms_sql.parse@%dblink%(l_cur, t_sql, t_sql.first, t_sql.last, false, dbms_sql.native);
        begin
          l_dummy := dbms_sql.execute@%dblink%(l_cur); 
        exception
          when bind_variable_required then
            dbms_sql.bind_variable@%dblink%(l_cur, ''result'', ''*'');
            l_dummy := dbms_sql.execute@%dblink%(l_cur);
            dbms_sql.variable_value@%dblink%(l_cur, ''result'', :2);
        end;
      exception
        when others then
          dbms_sql.close_cursor@%dblink%(l_cur);
          raise;
      end;
      dbms_sql.close_cursor@%dblink%(l_cur);
    exception
      when others then
        :3 := sqlcode;
        :4 := sqlerrm;
    end;';

  ...

  execute immediate replace(C_REMOTE_EXECUTE, '%dblink%', l_dblink) using ...;

  ...


Проблема заключается в том, что на отмеченной строке изредка (примерно один раз из тысячи вызовов) выпадает ошибка ORA-29471. После чего она, естественно, начинает выпадать везде.

Никакой закономерности в её выпадании я не вижу. Если прогнать один и тот же набор вызовов в одной и той же тестовой среде - три раза отработает, на четвёртый упадёт, потом (в другой сессии) ещё пять раз отработает. Команда, которую выберет, чтобы упасть - непредсказуема. Это может быть DML, это может быть DDL, такое впечатление, что она выбирается просто рандомно.

Насколько я понял, ORA-29471 возникает в случае, если в DBMS_SQL в качестве курсора передано значение, которое не числится в нём как открытый курсор (который вернул open_cursor и ещё не было close_cursor). В данном случае я не вижу, как что-то подобное может произойти.

В качестве workaround-а я пока собираюсь проверять версию удалённого сервера, и если она выше десятки, то лепить open_cursor(0). Но буду признателен ценным советам по поводу того, как вылечить это более адекватными методами.

Бил программистов по голове. Поцарапал лом.
16 фев 16, 16:29    [18824668]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
Elic
Member

Откуда:
Сообщений: 29990
softwarer
dbms_sql.open_cursor@%dblink%
dbms_sql.open_cursor@%dblink%(security_level=>0)
?
16 фев 16, 17:12    [18824996]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
Elic,

это под пунктом "в качестве workaround-а собираюсь". Уже собрался. Даёт ORA-29474, cволочь
16 фев 16, 17:29    [18825123]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
ДДЛ/parse
Guest
softwarer,

Что именно выполняется в этом курсоре? Как узнали строку, если обернуто в exception sqlcode/sqlerrm?
16 фев 16, 17:52    [18825269]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
Elic
Member

Откуда:
Сообщений: 29990
softwarer
ORA-29474
Анекдот №147
16 фев 16, 17:54    [18825290]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
ДДЛ/parse
Что именно выполняется в этом курсоре?

Всё подряд. DDL, DML, анонимные блоки...

ДДЛ/parse
Как узнали строку, если обернуто в exception sqlcode/sqlerrm?

(пожимая плечами) Расставил line := 1, line := 2, line := 3 и присоединил к sqlerrm.
16 фев 16, 18:04    [18825356]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 9273
IMHO

Создать процедуру на удаленном сервере и работать через нее.

+

Насколько я понял, ORA-29471 возникает в случае, если в DBMS_SQL в качестве курсора передано значение, которое не числится в нём как открытый курсор (который вернул open_cursor и ещё не было close_cursor). В данном случае я не вижу, как что-то подобное может произойти.


Надо читать доки, как сессия на локальной базе соотносится с сессией на удаленной базе. Вроде никто не обещал, что они соотносятся один в один.

В документации вроде описаны только распределенные транзакции и двух-фазный коммит.

Т.ч., как идею, предложу попытаться явно сделать транакцию на локальной машине, запросить (или лучше update) какие либо данные на удаленной машине (тогда, по логике, локальная машина должна стать Local Coordinator, Global Coordinator), попытаться работать с DBMS_SQL.

Пальцем в небо. Как идея. Если говорю глупость и где-то в доке описано поведение сессий при db link'е без привязки к распределенным транзакциям - прошу дать ссылку.
16 фев 16, 18:21    [18825465]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
Elic
Member

Откуда:
Сообщений: 29990
Leonid Kudryavtsev
явно сделать транакцию на локальной машине
softwarer
DDL
16 фев 16, 18:23    [18825484]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 9273
Не прав. Явно описано:
Дока
Controlling Connections Established by Database Links

When a global object name is referenced in a SQL statement or remote procedure call, database links establish a connection to a session in the remote database on behalf of the local user. The remote connection and session are only created if the connection has not already been established previously for the local user session.

The connections and sessions established to remote databases persist for the duration of the local user's session, unless the application or user explicitly terminates them....

C database link только эпизодически сталкивался.
16 фев 16, 18:43    [18825618]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
softwarer,

ну так можно вывернуть мехом внутрь - пусть из-за линка (гномик :) читает, что ему выполнить и выполняет себе (локально)
16 фев 16, 18:46    [18825640]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
orawish,

не получится. Во-первых, в некоторых местах по соображениям безопасности линк строго в одну сторону. Во-вторых, целевой сервер вообще может быть чужим. То есть, например, для интеграции даётся вход на него и право CREATE VIEW, но никаких CREATE PROCEDURE итп.
16 фев 16, 19:07    [18825769]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
Leonid Kudryavtsev
Member

Откуда:
Сообщений: 9273
Из разряда других бредовых идей:

1. dbms_job
2. DDL. А при DDL вообще нужно/можно вызывать DBMS_SQL.EXECUTE, врод же DDL выполняются при Parse ?

(This procedure parses the given statement in the given cursor. All statements are parsed immediately. In addition, DDL statements are run immediately when parsed.)
16 фев 16, 19:39    [18825922]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54395
а ошибка точно на выделенной строке, а не на 4 строки ниже?
16 фев 16, 19:56    [18825965]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
Leonid Kudryavtsev
2. DDL. А при DDL вообще нужно/можно вызывать DBMS_SQL.EXECUTE,

Не нужно, но можно. В этом случае он просто ничего не делает.

andreymx
а ошибка точно на выделенной строке, а не на 4 строки ниже?

В нескольких эпизодах, которые я промониторил, была именно на этой. Не исключаю, что может случиться и четырьмя строками ниже, но таких команд у меня мало, две на весь список, поэтому ждать, не свалится ли на такой тоже, придётся долго.
16 фев 16, 20:36    [18826091]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
P.S. Попробовал делать alter session close database link и повторять операцию. Руками - работает, из PL/SQL - нет. Скорее всего, линк грохается только по завершении работы PL/SQL.
16 фев 16, 20:47    [18826119]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54395
softwarer
P.S. Попробовал делать alter session close database link и повторять операцию. Руками - работает, из PL/SQL - нет. Скорее всего, линк грохается только по завершении работы PL/SQL.
у меня в pl-sql операция close database link работает без проблем
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE database link ' || v_database_link_name;
16 фев 16, 21:46    [18826289]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10051
Вообще-то надо:

ROLLBACK; -- или COMMIT;
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE database link ' || v_database_link_name;


SY.
16 фев 16, 22:03    [18826360]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
SY
Вообще-то надо:

ROLLBACK; -- или COMMIT;
EXECUTE IMMEDIATE 'ALTER SESSION CLOSE database link ' || v_database_link_name;



SY.

Так и делаю. Десять попыток с commit/close database link/dbms_lock.sleep(3) между ними. Не помогает. Подозреваю, помогло бы, если бы не продолжался тот же server call.
16 фев 16, 22:11    [18826388]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
Egoр
Member

Откуда:
Сообщений: 835
softwarer,

У нас была проблема с блокировкой dblnk.
Блокированный dblnk закрывался не сразу, а попозже.
И иногда это происходило в тот момент, когда его уже использовала другая сессия.
Поэтому для закрытия dblnk мы используем такую процедуру:
procedure close_DBlink(p_DB_link in varchar2) is
    c_timeout constant number := 10; -- sec
    v_upper_DB_link varchar2(64) := upper(p_DB_link);
    v_dt_start date := sysdate;
    v_chk number;
  begin
    while sysdate - v_dt_start < c_timeout / 60 / 60 / 24 loop
      select count(1) into v_chk from gv$dblink l where l.DB_LINK = v_upper_DB_link;
      if v_chk > 0 then
        commit;
        begin
          dbms_session.close_database_link(v_upper_DB_link);
        exception
          when others then
            null;
        end;
      else
        return;
      end if;
    end loop;

    commit;
  end;
Значение для c_timeout подобрано опытным путем.
17 фев 16, 10:23    [18827751]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
Дополнительная информация: в 10g в аналогичные, по-видимому, моменты, возникает ORA-01001 invalid cursor. Удалось добиться стопроцентного воспроизведения. Ошибка падает на анонимном блоке, содержащем вызов пакетной процедуры без параметров. То есть, грубо говоря, begin X.Y; end; Отладка говорит, что перед вызовом execute в проблемной строке номер курсора равен полученному в open_cursor, его is_open равен true.
18 фев 16, 19:39    [18837647]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
softwarer
Member

Откуда: 127.0.0.1
Сообщений: 64002
Блог
Ошибка уходит, если перестать вызывать в сессии dbms_utility.compile_schema. Мат опущу, поэтому ни слова.
18 фев 16, 20:41    [18837888]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
softwarer
Ошибка уходит, если перестать вызывать в сессии dbms_utility.compile_schema.
А зачем оно вызывалось изначально?
18 фев 16, 22:16    [18838189]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
orawish
Member

Откуда: Гадюкино-2 (City)
Сообщений: 15487
dbms_photoshop
softwarer
Ошибка уходит, если перестать вызывать в сессии dbms_utility.compile_schema.
А зачем оно вызывалось изначально?

поддерживаю вопрос
(в самом деле - зачем.. ?
1) пришел к залинку в гости;
2) неспросясь всех построил;
3) и ещё обижается, что бывает (1/10 раз), что такому и по морде бьют
;)
19 фев 16, 10:43    [18839531]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
andreymx
Member

Откуда: Запорожье
Сообщений: 54395
orawish
dbms_photoshop
А зачем оно вызывалось изначально?

поддерживаю вопрос
(в самом деле - зачем.. ?
1) пришел к залинку в гости;
2) неспросясь всех построил;
3) и ещё обижается, что бывает (1/10 раз), что такому и по морде бьют
;)



мне показалось, что ТС сам удивлён этим попавшим к нему наследством
softwarer
Ошибка уходит, если перестать вызывать в сессии dbms_utility.compile_schema. Мат опущу, поэтому ни слова.
19 фев 16, 11:24    [18839766]     Ответить | Цитировать Сообщить модератору
 Re: Выполнить динамический SQL на удалённом сервере  [new]
dba123
Member

Откуда:
Сообщений: 1054
softwarer
В качестве workaround-а я пока собираюсь проверять версию удалённого сервера, и если она выше десятки, то лепить open_cursor(0). Но буду признателен ценным советам по поводу того, как вылечить это более адекватными методами.
Советую дба, что по ту сторону линка, на
"необходимость выполнить заданный динамический блок" проверить и установить (для >=11g)
select n.ksppinm name,v.ksppstvl val 
 from x$ksppi n, x$ksppsv v 
  where n.indx = v.indx 
    and n.ksppinm like '%_dbms_sql_security_level%';

--как минимум 1, а лучше 2: пусть softwarer позвонит, если что

alter system set "_dbms_sql_security_level"= 1 scope=spfile;
19 фев 16, 11:33    [18839854]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Oracle Ответить