oracle.notes: ноября 2013

2013-11-27

Open/close period script Oracle EBS

Script uses concurrent GLOOAP

DECLARE
  -- какой год надо открыть
  l_year NUMBER := 2014;
  -- Сколько периодов в 1 году
  c_periods_in_year_cnt NUMBER := 5;
  l_iterations_cnt NUMBER;
  
  PROCEDURE open_period(p_sob_id gl_sets_of_books.set_of_books_id%TYPE) IS
    l_code gl_sets_of_books.short_name%TYPE;
    
    l_user_id fnd_user.user_id%TYPE;
    l_app_id fnd_responsibility_vl.APPLICATION_ID%TYPE;
    l_resp_id fnd_responsibility_vl.RESPONSIBILITY_ID%TYPE;
    
    l_req_num NUMBER;
  BEGIN
    SELECT b.short_name
    INTO l_code
    FROM gl_sets_of_books b
    WHERE b.set_of_books_id = p_sob_id;

    select user_id
    INTO l_user_id
    from fnd_user
    where user_name = 'SYSADMIN';

    select application_id,
      Responsibility_id
    INTO l_app_id, l_resp_id
    from fnd_responsibility_vl
    where responsibility_name like l_code || ' Суперпользователь ГК';

    fnd_global.APPS_INITIALIZE(user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_app_id);

    l_req_num := fnd_request.submit_request('SQLGL', 'GLOOAP',
                                           '', '', FALSE,
                                           p_sob_id,
                                           '50268', 
                                           l_app_id, 
                                           'P', 'Y',chr(0));
  -- Параметры
  -- 50268 - chart_of_accounts_id
  -- определить идентификатор можно таким запросом 
  -- select * From FND_ID_FLEX_STRUCTURES_VL  where id_flex_code='GL#';
  -- 'P' -- execution_mode = P (хз, что такое)
  -- 'Y' -- хз, что такое
    dbms_output.put_line(l_req_num);                                           
    COMMIT;
  END;
BEGIN
  FOR rec IN (
      SELECT t.set_of_books_id, t.period_year, t.period_num
      FROM (
        select t.*, row_number() OVER (PARTITION BY t.set_of_books_id ORDER BY t.period_year DESC, t.period_num DESC) rn
        from GL.GL_PERIOD_STATUSES t
        WHERE t.closing_status = 'O') t
      WHERE rn = 1  
  ) LOOP
    l_iterations_cnt := GREATEST(l_year - rec.period_year, 0) * c_periods_in_year_cnt + (c_periods_in_year_cnt - rec.period_num);
    dbms_output.put_line('sob_id = ' || rec.set_of_books_id || '; Кол-во периодов ' || l_iterations_cnt);
    FOR i IN 1 .. l_iterations_cnt LOOP
      NULL;--open_period(rec.set_of_books_id);
    END LOOP;
  END LOOP;
END;
/

Merge vs Insert/Update

Сравнение времени выполнения merge и insert/update.

Выводы: в рамках использованного в тесте распределения данных, лучше всего использовать один оператор merge для всех строк.

Для аналогичную конструкции INSERT/UPDATE в один (а вернее в два) оператора не получилось дождаться окончания запроса. Если обновление будет производиться не из запроса, а из индексированной таблицы, то вполне вероятно это решение будет так же жизнеспособно, но все равно не так удобно как один MERGE.

Если в один оператор поместиться не получается, то быстрее всего отрабатывает UPDATE/SQL%ROWCOUNT/INSERT с одним условием: из 200 000 строк обновляялась (т.е. не вызывала дальнейшей вставки) половина. Если все строки вставляются и UPDATE работает вхолостую, то производительность построчного MERGE получается выше.


SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET TIMING OFF

DROP TABLE tst PURGE;
CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100));

INSERT INTO tst
SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual
CONNECT BY LEVEL <= 500000;
COMMIT;

EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE)

SET timing ON 
BEGIN
  dbms_output.put_line('************************');
  dbms_output.put_line('Merge bulk');
  dbms_output.put_line('************************');

  MERGE INTO tst
  USING (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i
  ON (tst.a = i.rn)
  WHEN MATCHED THEN UPDATE SET b = i.b
  WHEN NOT MATCHED THEN INSERT (a, b) VALUES (i.rn, i.b);
END;
/  
SET TIMING OFF
-------------------------------------
-- Окончания этого теста дождаться не удалось
-- ПРоблема в UPDATE. 
-- Разрешится, если вместо запроса у нас будет таблица, например

--DROP TABLE tst PURGE;
--CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100));
--
--INSERT INTO tst
--SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual
--CONNECT BY LEVEL <= 500000;
--COMMIT;
--
--EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE)
--
--SET timing ON 
--BEGIN
--  dbms_output.put_line('************************');
--  dbms_output.put_line('INSERT - UPDATE bulk');
--  dbms_output.put_line('************************');
--
--  INSERT INTO tst
--  SELECT i.rn, i.b
--  FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i
--  WHERE NOT EXISTS (
--      SELECT NULL
--      FROM tst t
--      WHERE t.a = i.rn);
--      
--  UPDATE tst 
--  SET b = (SELECT i.b FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i WHERE tst.a = i.rn)
--  WHERE EXISTS (SELECT NULL FROM (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) i WHERE tst.a = i.rn);
--END;
--/  
--SET TIMING OFF
-------------------------------------

DROP TABLE tst PURGE;
CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100));

INSERT INTO tst
SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual
CONNECT BY LEVEL <= 500000;
COMMIT;

EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE)

SET timing ON
BEGIN
  dbms_output.put_line('************************');
  dbms_output.put_line('Row by row merge');
  dbms_output.put_line('************************');  
  FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP
    MERGE INTO tst
    USING (SELECT rec.rn rn, rec.b b FROM dual) i
    ON (tst.a = i.rn)
    WHEN MATCHED THEN UPDATE SET b = i.b
    WHEN NOT MATCHED THEN INSERT (a, b) VALUES (i.rn, i.b);
  END LOOP;
END;
/
SET timing OFF
-------------------------------------

DROP TABLE tst PURGE;
CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100));

INSERT INTO tst
SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual
CONNECT BY LEVEL <= 500000;
COMMIT;

EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE)

SET timing ON
BEGIN
  dbms_output.put_line('************************');
  dbms_output.put_line('Dup val on index');
  dbms_output.put_line('************************');  
  FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP
    BEGIN
      INSERT INTO tst VALUES (rec.rn, rec.b);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE tst SET b = rec.b WHERE a = rec.rn;
    END;
  END LOOP;
END;
/
SET timing OFF
-------------------------------------

DROP TABLE tst PURGE;
CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100));

INSERT INTO tst
SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual
CONNECT BY LEVEL <= 500000;
COMMIT;

EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE)

SET timing ON
DECLARE
  lCNT NUMBER;
BEGIN
  dbms_output.put_line('************************');
  dbms_output.put_line('Select - insert - update');
  dbms_output.put_line('************************');  
  FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP
      SELECT COUNT(*) INTO lCNT FROM tst WHERE a = rec.rn AND ROWNUM = 1;
      IF lCNT = 0 THEN
        INSERT INTO tst VALUES (rec.rn, rec.b);
      ELSE
        UPDATE tst SET b = rec.b WHERE a = rec.rn;
      END IF;
  END LOOP;
END;
/
SET timing OFF
-------------------------------------

DROP TABLE tst PURGE;
CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100));

INSERT INTO tst
SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual
CONNECT BY LEVEL <= 500000;
COMMIT;

EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE)

SET timing ON
DECLARE
  lCNT NUMBER;
BEGIN
  dbms_output.put_line('************************');
  dbms_output.put_line('SQL%ROWCOUNT');
  dbms_output.put_line('************************');  
  FOR rec IN (SELECT ROWNUM + 400000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP
      UPDATE tst SET b = rec.b WHERE a = rec.rn;
      IF SQL%ROWCOUNT = 0 THEN
        INSERT INTO tst VALUES (rec.rn, rec.b);
      END IF;
  END LOOP;
END;
/
SET timing OFF

-------------------------------------
DROP TABLE tst PURGE;
CREATE TABLE tst(a NUMBER PRIMARY KEY, b VARCHAR2(100));

INSERT INTO tst
SELECT ROWNUM, RPAD('x', 100, 'x') FROM dual
CONNECT BY LEVEL <= 500000;
COMMIT;

EXEC dbms_stats.gather_table_stats(USER, 'TST', cascade => TRUE)

SET timing ON
DECLARE
  lCNT NUMBER;
BEGIN
  dbms_output.put_line('************************');
  dbms_output.put_line('SQL%ROWCOUNT INSERT ONLY');
  dbms_output.put_line('************************');  
  FOR rec IN (SELECT ROWNUM + 500000 rn, RPAD('y', 100, 'y') b FROM dual CONNECT BY LEVEL <= 200000) LOOP
      UPDATE tst SET b = rec.b WHERE a = rec.rn;
      IF SQL%ROWCOUNT = 0 THEN
        INSERT INTO tst VALUES (rec.rn, rec.b);
      END IF;
  END LOOP;
END;
/
SET timing OFF


************************
Merge bulk
************************
 
Executed in 5,241 seconds
 
************************
Row by row merge
************************
 
Executed in 14,914 seconds
 
************************
Dup val on index
************************
 
Executed in 180,681 seconds
 
************************
Select - insert - update
************************
 
Executed in 20,483 seconds
 
************************
SQL%ROWCOUNT
************************
 
Executed in 14,29 seconds
 
************************
SQL%ROWCOUNT INSERT ONLY
************************
 
Executed in 17,425 seconds

2013-11-22

Оптимизация одного запроса с использованием _fix_control

После миграции OEBS на Oracle 11.2.0.4 выполнение Posting (а это одна из ключевых операций в GL) стало занимать от 5 минут до получаса.

Методом пристольного взгляда в окно сессий в PL/SQL Developer был выведен зловредный запрос:

UPDATE /*+ ORDERED
       INDEX (b, GL_BALANCES_N1) 
       USE_NL (VW_NSO_1, b) */ GL_BALANCES B
   SET (PERIOD_NET_DR,
        PERIOD_NET_CR,
        QUARTER_TO_DATE_DR,
        QUARTER_TO_DATE_CR,
        PROJECT_TO_DATE_DR,
        PROJECT_TO_DATE_CR,
        BEGIN_BALANCE_DR,
        BEGIN_BALANCE_CR,
        PERIOD_NET_DR_BEQ,
        PERIOD_NET_CR_BEQ,
        BEGIN_BALANCE_DR_BEQ,
        BEGIN_BALANCE_CR_BEQ,
        TRANSLATED_FLAG,
        LAST_UPDATE_DATE,
        LAST_UPDATED_BY) =
       (SELECT /*+ INDEX(pi1, gl_posting_interim_162343_N1) */
         NVL(B.PERIOD_NET_DR, 0) + PI1.PERIOD_NET_DR,
         NVL(B.PERIOD_NET_CR, 0) + PI1.PERIOD_NET_CR,
         NVL(B.QUARTER_TO_DATE_DR, 0) + PI1.QUARTER_TO_DATE_DR,
         NVL(B.QUARTER_TO_DATE_CR, 0) + PI1.QUARTER_TO_DATE_CR,
         NVL(B.PROJECT_TO_DATE_DR, 0) + PI1.PROJECT_TO_DATE_DR,
         NVL(B.PROJECT_TO_DATE_CR, 0) + PI1.PROJECT_TO_DATE_CR,
         NVL(B.BEGIN_BALANCE_DR, 0) + PI1.BEGIN_BALANCE_DR,
         NVL(B.BEGIN_BALANCE_CR, 0) + PI1.BEGIN_BALANCE_CR,
         NVL(B.PERIOD_NET_DR_BEQ, 0) + PI1.PERIOD_NET_DR_BEQ,
         NVL(B.PERIOD_NET_CR_BEQ, 0) + PI1.PERIOD_NET_CR_BEQ,
         NVL(B.BEGIN_BALANCE_DR_BEQ, 0) + PI1.BEGIN_BALANCE_DR_BEQ,
         NVL(B.BEGIN_BALANCE_CR_BEQ, 0) + PI1.BEGIN_BALANCE_CR_BEQ,
         PI1.TRANSLATED_FLAG,
         SYSDATE,
         :USER_ID
          FROM GL_POSTING_INTERIM_162343 PI1
         WHERE B.SET_OF_BOOKS_ID = PI1.SET_OF_BOOKS_ID
           AND B.CODE_COMBINATION_ID = PI1.CODE_COMBINATION_ID
           AND B.ACTUAL_FLAG = PI1.ACTUAL_FLAG
           AND NVL(B.ENCUMBRANCE_TYPE_ID, -1) = NVL(PI1.ENCUMBRANCE_TYPE_ID, -1)
           AND NVL(B.BUDGET_VERSION_ID, -1) = NVL(PI1.BUDGET_VERSION_ID, -1)
           AND B.PERIOD_NAME = PI1.PERIOD_NAME
           AND B.CURRENCY_CODE = PI1.CURRENCY_CODE
           AND NVL(B.TEMPLATE_ID, -1) = NVL(PI1.TEMPLATE_ID, -1)
           AND DECODE(B.TRANSLATED_FLAG,
                      '',
                      -1,
                      'Y',
                      0,
                      'N',
                      0,
                      'R',
                      1,
                      B.TRANSLATED_FLAG) =
               DECODE(PI1.TRANSLATED_FLAG,
                      '',
                      -1,
                      'Y',
                      0,
                      'N',
                      0,
                      'R',
                      1,
                      PI1.TRANSLATED_FLAG))
 WHERE (B.CODE_COMBINATION_ID, B.PERIOD_NAME, B.SET_OF_BOOKS_ID, B.CURRENCY_CODE,
        B.ACTUAL_FLAG, NVL(B.ENCUMBRANCE_TYPE_ID, -1),
        NVL(B.BUDGET_VERSION_ID, -1), NVL(B.TEMPLATE_ID, -1),
        DECODE(B.TRANSLATED_FLAG,
               '',
               -1,
               'Y',
               0,
               'N',
               0,
               'R',
               1,
               B.TRANSLATED_FLAG)) IN
       (SELECT /*+ FULL(pi2) */
         PI2.CODE_COMBINATION_ID,
         PI2.PERIOD_NAME,
         PI2.SET_OF_BOOKS_ID,
         PI2.CURRENCY_CODE,
         PI2.ACTUAL_FLAG,
         NVL(PI2.ENCUMBRANCE_TYPE_ID, -1),
         NVL(PI2.BUDGET_VERSION_ID, -1),
         NVL(PI2.TEMPLATE_ID, -1),
         DECODE(PI2.TRANSLATED_FLAG,
                '',
                -1,
                'Y',
                0,
                'N',
                0,
                'R',
                1,
                PI2.TRANSLATED_FLAG)
          FROM GL_POSTING_INTERIM_162343 PI2)

В плане запроса появился нехороший full scan по индексу GL_BALANCES_N1.
Так же было установлено, хотя это и не важно, что full scan появлялся из-за

DECODE(B.TRANSLATED_FLAG,
               '',
               -1,
               'Y',
               0,
               'N',
               0,
               'R',
               1,
               B.TRANSLATED_FLAG)

в условии IN.
На этом этапе у нас есть описание проблемы (FULL SCAN) и один запрос, поэтому тестированиие изменений при помощи EXPLAIN PLAN не занимает много времени.

Первое, что попробовал сделать -- убедился, что в прошлой версиии оптимизатора все работало нормально. Добавляем хинт /*+ optimizer_features_enable('9.2.0.8') */ и убеждаемся, что все нормально.

2. Смотрим, в какой версии оптимизатора Oracle поломал свой запрос. Допустимые значения для параметра optimizer_features_enable можно получить запросом
SELECT * FROM v$parameter_valid_values WHERE NAME LIKE '%features%' Используя хинт, убеждаемся, что план портится при переходе с версиии 11.2.0.3 на 11.2.0.4.

Тут можно остановиться, сделав
ALTER SYSTEM SET optimizer_features_enable='11.2.0.3'; Но можно пойти дальше. Найдем, что менялось в версии 11.2.0.4

SELECT *
FROM   v$system_fix_control
WHERE optimizer_feature_enable = '11.2.0.4';

Сгенерируем список хинтов

SELECT 'OPT_PARAM(''_fix_control'' ''' || bugno ||':OFF'')'
FROM   v$system_fix_control
WHERE optimizer_feature_enable = '11.2.0.4';

и добавим эти хинты в оптимизируемый запрос. При этом у нас появится хороший план.
Удаляя хинты (можно сразу кучками) и перестраивая план, найдем номер, включение которого портит план.
У меня это Bug 13704562 Suboptimal plan for a query with an =ANY predicate

Теперь можно отключить этот фикс на уровне системы
alter system set "_fix_control"='13704562:OFF'; Проверяем постинг -- все работает

Примечание: изменение скрытого параметра _fix_control не рекомендуется без прямого указания ораклового саппорта. Но для тестовой среды вполне сойдет

FORALL

Краткие итоги

Входной массив

Может быть и PL/SQL массивом и Nested Table.
Три метода перебора элементов
1.Указание интервала индексов


forall i in l_col.first .. l_col.last
forall i in 1 .. 10
-- В этом случае индексы округлятся
forall i in 1.3 .. 2.4

В этом случае нельзя работать с коллекциями с дырками (удаленными элементами)

2. INDICES OF


FORALL i IN INDICES OF l_t
FORALL i IN INDICES OF l_t BETWEEN 1 AND 1

Позволяет работать с коллекцияями с дырками. Можно ограничить интервал
3. VALUES OF


 FORALL i IN VALUES OF l_tsubscripts

В одной коллекции храним индексы (PLS_INTEGER или BINARY_INTEGER) от рабочей коллекции. Если указанного элемента в рабочей коллекции нет -- будет ошибка. Индексы могут храниться как в PL/SQL так и в Nested массиве.

Обработка ошибок

Если на одном из элементов коллекции проиисходит иисключение, то транзакцияя сохранит все предыдущие успешные операции.

Можно использовать конструкцию SAVE EXCEPTION. В этом случае операция дойдет до конца и, если при выполнении встретились ошибки, будет сгенерировано исключение ORA-24381: error(s) in array DML. Для обработки ошибок можно использовать массив SQL%BULK_EXCEPTIONS

SQL%BULK_EXCEPTIONS

1. Нумерация всегда с 1. Чаще всего для обработки будет использоваться
FOR err IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP 2. SQL%BULK_EXCEPTIONS(err).ERROR_INDEX - номер итерации. Если массив неразряженный и начинается с 1 -- то получить входные данные, вызвавшие ошибку очень просто. В остальных случаях придется яотсчитывать в цикле

3. SQL%BULK_EXCEPTIONS(err).ERROR_CODE -- код ошибки

4. SQLERRM(-SQL%BULK_EXCEPTIONS(err).ERROR_CODE) -- сообщение об ошибке

SQL%BULK_ROWCOUNT

Массив, который хранит в себе сколько строк было обработано на каждой итерации. Массив индексируется индексами от рабочего массива, т.е. если в рабочем массиве элементы со 2-го по 5-й, то и в SQL%BULK_ROWCOUNT будут 2, 3, 4, 5

Пример кода


DROP TABLE a;
CREATE TABLE a (a NUMBER CHECK (a > 0));

DECLARE
 l_cnt NUMBER;  

  TYPE t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
  l_t t;
  
  TYPE t_nest IS TABLE OF NUMBER;
  l_tn t_nest := t_nest();

  -- suscript -- только PLS_INTEGER and BINARY_INTEGER
  TYPE t_subscripts IS TABLE OF PLS_INTEGER;
  l_tsubscripts t_subscripts := t_subscripts();

  TYPE t_subscripts2 IS TABLE OF PLS_INTEGER index by pls_integer;
  l_tsubscripts2 t_subscripts2;
  
BEGIN
  l_t(1) := 1;
  l_t(3) := 3;
  -- ora-22160 -- коллекция разряженная
  BEGIN
    FORALL i IN l_t.first .. l_t.last
      INSERT INTO a VALUES (l_t(i));
    dbms_output.put_line('Should be 0');      
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;
  
  -- можно использовать indices of
  FORALL i IN INDICES OF l_t
    INSERT INTO a VALUES (l_t(i));
  dbms_output.put_line('Row count: ' || SQL%ROWCOUNT);

  -- можно использовать indices of
  FORALL i IN INDICES OF l_t BETWEEN 1 AND 1
    INSERT INTO a VALUES (l_t(i));
  dbms_output.put_line('Indices of with lower and upper bound Row count: ' || SQL%ROWCOUNT);
  
  -- Аналогично работает с nested tables
  l_tn.EXTEND(3);
  l_tn(1) := 1;
  l_tn(2) := 2;  
  l_tn(3) := 3;
  l_tn.delete(2);
  BEGIN
    FORALL i IN l_tn.first .. l_tn.last
      INSERT INTO a VALUES (l_tn(i));
      dbms_output.put_line('Should be 0');          
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Nested tables Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;

  FORALL i IN INDICES OF l_tn
    INSERT INTO a VALUES (l_tn(i));
  dbms_output.put_line('Nested Row count: ' || SQL%ROWCOUNT);

  -- values of
  l_tsubscripts.EXTEND(1);
  l_tsubscripts(1) := 3;

  -- При использованиии values of + nested table коллекция должна начинаться с 1
  BEGIN
    FORALL i IN VALUES OF l_tsubscripts
      INSERT INTO a VALUES (l_tn(i));
    dbms_output.put_line('values of + nested table Row count: ' || SQL%ROWCOUNT);
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('values of + nested table Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;

  -- При использованиии values of + pl/sql массивы коллекция может быть какая угодно
  l_tsubscripts2(1000) := 3;
  BEGIN
    FORALL i IN VALUES OF l_tsubscripts2
      INSERT INTO a VALUES (l_tn(i));
    dbms_output.put_line('Values of Row count: ' || SQL%ROWCOUNT);
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;

  -- Нет элемента -- это ошибка
  l_tsubscripts2(1000) := 3;
  l_tsubscripts2(1001) := 10;
  BEGIN
    FORALL i IN VALUES OF l_tsubscripts2
      INSERT INTO a VALUES (l_tn(i));
    dbms_output.put_line('Values of Row count: ' || SQL%ROWCOUNT);
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('No element Error: ' || SQLCODE || ' - ' || SQLERRM);
  END;
  l_t.delete;  
  l_t(1) := 1;
  l_t(2) := 2;
  l_t(3) := 3;
  -- Границы округляются до ближайшего целого
  FORALL i IN 1.3 .. 2.4
    INSERT INTO a VALUES (l_t(i));
    dbms_output.put_line('bounds rounded Row count: ' || SQL%ROWCOUNT);  


  -- SAVE EXCEPTION
  -- Если не все гладко, то рейсит ошибку ORA-24381
  -- Далее разбираемся с SQL%BULK_EXCEPTIONS
  l_t.delete;
  l_t(1) := 1;
  l_t(2) := 2;
  l_t(3) := 3;
  l_t(4) := -4;
  l_t.delete(2);
  BEGIN
    FORALL i IN 1 .. 4 SAVE EXCEPTIONS
      INSERT INTO a VALUES (l_t(i));
      dbms_output.put_line('With save exception Row count: ' || SQL%ROWCOUNT);  
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('Error: ' || SQLCODE || ' - ' || SQLERRM);
    FOR err IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      dbms_output.put_line('Row ' || err || ' iteration ' || SQL%BULK_EXCEPTIONS(err).ERROR_INDEX 
          || ' code ' || SQL%BULK_EXCEPTIONS(err).ERROR_CODE 
          || ' message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(err).ERROR_CODE));
    END LOOP;
  END;

  -- SAVE EXCEPTION для разряженных коллекций с INDICES OF
  l_t.delete;
  l_t(10) := 1;
  l_t(20) := 2;
  l_t(30) := -3;
  BEGIN
    FORALL i IN INDICES OF l_t SAVE EXCEPTIONS
      INSERT INTO a VALUES (l_t(i));
      dbms_output.put_line('With save exception indices of Row count: ' || SQL%ROWCOUNT);  
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('indices of Error: ' || SQLCODE || ' - ' || SQLERRM);
    FOR err IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      dbms_output.put_line('indices of Row ' || err || ' iteration ' || SQL%BULK_EXCEPTIONS(err).ERROR_INDEX 
          || ' code ' || SQL%BULK_EXCEPTIONS(err).ERROR_CODE 
          || ' message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(err).ERROR_CODE));
    END LOOP;
  END;
 
  ROLLBACK;
 
  -- при ошибке все предыдущие операции остаются
  l_t.delete;  
  l_t(1) := 1;
  l_t(2) := -1;
  BEGIN
    FORALL i IN 1 .. 2
      INSERT INTO a VALUES (l_t(i));
  EXCEPTION WHEN OTHERS THEN
    dbms_output.put_line('insert with error Row count: ' || SQL%ROWCOUNT);
    SELECT COUNT(*) INTO l_cnt FROM a;
    dbms_output.put_line('insert with error rows in table ' || l_cnt);
  END;

  -- использование SQL%BULK_ROWCOUNT
  ROLLBACK;
  l_t.delete;
  l_t(1) := 1;
  l_t(2) := 1;
  FORALL i IN 1 .. 2
    INSERT INTO a VALUES (l_t(i));

  -- Индекс в BULK_ROWCOUNT совпадает с тем, что мы обрабатывали
  FORALL i IN 2 .. 2
    UPDATE a SET a = l_t(i) WHERE a = l_t(i);
  dbms_output.put_line('Updated SQL%BULK_ROWCOUNT=' || SQL%BULK_ROWCOUNT(2));
  
   
END;
/

2013-11-06

Горячие клавиши в OEBS

Возникла проблема с неправильной кодировкой в окне Справка - Использование клавиатуры.

В ходе разбирательств был найден документ Doc ID 1367967.1 по которому можно корректировать не только вывод в окно, но и настраивать новые/перенастраивать старые горячие клавиши.

Настройка производится в файле $ORACLE_HOME/forms60/admin/resource/RU/fmrweb.res (для русского языка) с последующим рестартом

Исходная проблема с кракозябрами победилась перекодировкой файла в ISO8859P5 (который совпадает с NLS_LANG)

optimizer_features_enable и включение/выключение фиксов

Статья про параметр optimizer_features_enable, поиск и включение/выключение фиксов