oracle.notes

2014-01-17

Cognos crosstab обрезает строки

При выводе в колонки Crosstab обрезает строки на уровне запроса. Выглядит это примерно так:

Если смотреть результаты запроса через Run > View Tabular Data, то результаты так же обрезаются.

Обойти эту неприятность получилось следующим образом:
1. Создаем запрос, который содержит данные Crosstab (Cross)
2. Создаем запрос, который содержит данные с заголовком колонок (Category).
3. Создаем join этих запросов. Условия Join

4. В результирующий запрос Join в колонки с Категорией и именем строки выбираем элементы из запроса Category.
5. В качестве источника данных для таблицы (Crosstab - Data - Query) указываем Join_query
6. Поверяем

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, поиск и включение/выключение фиксов

2013-10-28

Способы выгрузки в Excel из XML Publisher для OEBS 11

Способ 1
При помощи http://xlspe.com/news.php

раскрашиваем наш excel-документ тегами с группами и полями
сохраняем наш excel-документ в формате Таблица XML 2003
прогоняем через программу XLS Processor Engine for BI Publisher. На выходе получаем XSL
Полученный XSL подсовываем в шаблон BI Publisher как тип XSL-XML
Печатаем отчет, который хоть и в формате XML, все равно открывается в Exce

Из минусов то, что программа преобразования триальная.

Способ 2
Делаем шаблон в rtf, output ставим как excel.
Метод имеет ограничения, описанных тут

Способ 3
Описан тут http://habrahabr.ru/post/148041/ и в документации
В документации все пошагово, но ~~пробиться через проверку Template Viewer не удалось~~ надо быть внимательнее, пропустил : после Data Contains. И, судя по всему, в OEBS 11 это не заработает

1138602.1

At this time there is limited support for Excel templates in R12.0 and 12.1 and there is no support for Excel templates in 11i. The best solution for EBS clients wanting to use Excel templates is to license and install Oracle BI Publisher Enterprise 10g or 11g, and run the reports from there

444604.1

Excel Templates are only partially supported on the Oracle E-Business Suite Release 12.0.x. There are no current plans to backport this feature to the Oracle E-Business Suite Release 11i, nor will it be fully supported in 12.1.x.

При подсовывании в XML Publisher (хотя в нем и есть тип шаблона Excel), генерирует ошибку


[10/28/13 7:16:16 PM] [UNEXPECTED] [62458:RT1060398] java.lang.ClassCastException: java.io.FileOutputStream cannot be cast to java.lang.String
 at oracle.apps.xdo.template.excel.ExcelController.processActionLanguage(ExcelController.java:356)
 at oracle.apps.xdo.template.excel.ExcelController.process(ExcelController.java:242)
 at oracle.apps.xdo.template.ExcelProcessor.process(ExcelProcessor.java:245)
 at oracle.apps.xdo.oa.schema.server.TemplateHelper.runProcessTemplate(TemplateHelper.java:6179)
 at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3459)
 at oracle.apps.xdo.oa.schema.server.TemplateHelper.processTemplate(TemplateHelper.java:3548)
 at oracle.apps.fnd.cp.opp.XMLPublisherProcessor.process(XMLPublisherProcessor.java:290)
 at oracle.apps.fnd.cp.opp.OPPRequestThread.run(OPPRequestThread.java:157)

Способ 4
Корявый. При помощи Template Viewer можно сгенерировать XSL файл (аналогично способу 1), который не содержит форматирования. Не проверялся, непонятно как будет выкидываться результат.

2013-10-11

Oracle object types

2013-09-18

Переводить Cognos можно судя по всему в этих таблицах:

SELECT * FROM ORACLE.CMOBJNAMES_BASE;
SELECT * FROM ORACLE.CMOBJPROPS3_BASE;