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

Оптимизация одного запроса с использованием _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; /

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

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

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

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

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

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

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

Способы выгрузки в 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), который не содержит форматирования. Не проверялся, непонятно как будет выкидываться результат.

Oracle object types

Переводить Cognos можно судя по всему в этих таблицах: SELECT * FROM ORACLE.CMOBJNAMES_BASE; SELECT * FROM ORACLE.CMOBJPROPS3_BASE;

Flashback

Flashback version query (as of) 

Работает через UNDO, не требует включения flashback в базе
SELECT * FROM par; ID TXT ---------- ---------- 1 doc 1 2 doc 2 3 today SELECT * FROM par AS OF TIMESTAMP to_date('21.08.2013 22:55:00', 'DD.MM.YYYY HH24:MI:SS'); ID TXT ---------- ---------- 1 doc 1 2 doc 2 
Вариант с датой дает 3 секундную погрешность. Есть вариант работы через scn -- Такой функцией можно узнать scn по времени с погрешностью SELECT timestamp_to_scn( to_date('21.08.2013 22:55:00', 'DD.MM.YYYY HH24:MI:SS')) FROM dual; TIMESTAMP_TO_SCN(TO_DATE('21.0 ------------------------------ 4192406 SELECT * FROM par AS OF SCN 4192406; ID TXT ---------- ---------- 1 doc 1 2 doc 2 Flashback version query (versions between) Пишет историю жизни строк в течении UNDO_RETENTION. Если нижняя граница раньше, sysdate - UNDO_RETENTION, то будет ошибка ORA-30052 INSERT INTO par VALUES (4, 'doc 4'); COMMIT; UPDATE par p SET p.txt = 'new value' WHERE ID = 4; COMMIT; SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP SYSDATE - 1/24/60 * 15 AND SYSDATE; VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID VERSIONS_OPERATION ID TXT ----------------- ------------------------------------------------- --------------- ------------------------------------------------- ---------------- ------------------ ---------- ---------- 4234414 22.08.13 13:37:30 03001B001E0F0000 U 4 new value 4234333 22.08.13 13:34:51 4234414 22.08.13 13:37:30 0A002000A20B0000 I 4 doc 4 1 doc 1 2 doc 2 3 today SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP SYSDATE - 1/24 AND SYSDATE ORA-30052: недопустимое выражение для нижней границы снимков Что бы увидеть все изменения, можно использовать конструкции MINVALUE и MAXVALUE SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, par.* FROM par VERSIONS BETWEEN TIMESTAMP MINVALUE AND MAXVALUE; Работает по UNDO, не требует включения flashback для БД flashback_transaction_query Пишет изменения из UNDO. Одна из самых полезных фич -- колонка undo_sql. SELECT * FROM flashback_transaction_query WHERE table_name = 'PAR'; XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL ---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- -------------------------------------------------------------------------------- 0100020071D50000 40244472 22.08.2013 13:4 40244474 22.08.2013 13:43 SYSTEM 1 UPDATE PAR SYSTEM AAAiW3AABAAAZr6AAA update "SYSTEM"."PAR" set "TXT" = 'doc 4' where ROWID = 'AAAiW3AABAAAZr6AAA'; 0200190091C10000 40244474 22.08.2013 13:4 40244496 22.08.2013 13:44 SYSTEM 1 INSERT PAR SYSTEM AAAiW3AABAAAZr6AAB delete from "SYSTEM"."PAR" where ROWID = 'AAAiW3AABAAAZr6AAB'; 03000B00E9E50000 40244496 22.08.2013 13:4 40244498 22.08.2013 13:44 SYSTEM 1 UPDATE PAR SYSTEM AAAiW3AABAAAZr6AAB update "SYSTEM"."PAR" set "TXT" = 'doc 1' where ROWID = 'AAAiW3AABAAAZr6AAB'; 0A0003001ECD0000 40244417 22.08.2013 13:4 40244472 22.08.2013 13:43 SYSTEM 1 INSERT PAR SYSTEM AAAiW3AABAAAZr6AAA delete from "SYSTEM"."PAR" where ROWID = 'AAAiW3AABAAAZr6AAA'; На Oracle 12C с pluggable database таблица почему-то не заполняется SQL> alter session set container=db1; Session altered SQL> create table tbl(id number); Table created SQL> insert into tbl values (1); 1 row inserted SQL> commit; Commit complete SQL> SELECT * FROM flashback_transaction_query; SELECT * FROM flashback_transaction_query ORA-01295: несоответствие DB_ID между словарем USE_ONLINE_CATALOG и файлами журналов SQL> alter session set container=CDB$ROOT; Session altered SQL> SELECT * FROM flashback_transaction_query; XID START_SCN START_TIMESTAMP COMMIT_SCN COMMIT_TIMESTAMP LOGON_USER UNDO_CHANGE# OPERATION TABLE_NAME TABLE_OWNER ROW_ID UNDO_SQL ---------------- ---------- --------------- ---------- ---------------- ------------------------------ ------------ -------------------------------- -------------------------------------------------------------------------------- -------------------------------- ------------------- -------------------------------------------------------------------------------- SQL> Flashback archive (oracle total recall) Фича позволяет обойти ограничения с UNDO для запросов Flashback version query. Создатет несколько дополнительных табличек, в которые записывает транзакции и изменения. Flashback version query после этого работает в течении интервала, заданного в retention при создании flashback archive -- Создаем архив, указываем где храним и сколько храним. -- Можно добавлять tablespace, указывать квоты, чистить SQL> create flashback archive default fa tablespace users retention 1 year; Done -- При создании таблицы указываем, что хранить для нее архив SQL> create table tbl(a number) FLASHBACK ARCHIVE; Table created SQL> insert into tbl values(1); 1 row inserted SQL> commit; Commit complete SQL> update tbl set a = 2; 1 row updated SQL> commit; Commit complete SQL> delete from tbl; 1 row deleted SQL> commit; Commit complete -- Помимо того, что можно делать SELECT ... AS OF ... еще создаются таблички в которые можно поглядеть SQL> SQL> SELECT * FROM SYS_FBA_HIST_101864; SELECT * FROM SYS_FBA_HIST_101864; RID STARTSCN ENDSCN XID OPERATION A -------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I 1 AAAY3oAABAAAa/ZAAA 30076162 30076162 08001C00D0680000 D 2 AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U 2 SQL> SELECT * FROM SYS_MFBA_NHIST_101864; SELECT * FROM SYS_MFBA_NHIST_101864; RID STARTSCN ENDSCN XID OPERATION A -------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- SQL> SELECT * FROM SYS_FBA_TCRV_101864; SELECT * FROM SYS_FBA_TCRV_101864; RID STARTSCN ENDSCN XID OP -------------------------------------------------------------------------------- ---------- ---------- ---------------- -- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U -- Попробуем изменить структуру. После него SELECT AS OF не заработал, одна созданная табличка пропала SQL> alter table tbl add b varchar2(100); Table altered SQL> insert into tbl values (1, 'txt 1'); 1 row inserted SQL> commit; Commit complete SQL> update tbl set b = 'new txt'; 1 row updated SQL> commit; Commit complete SQL> SQL> SELECT * FROM SYS_FBA_HIST_101864; SELECT * FROM SYS_FBA_HIST_101864; RID STARTSCN ENDSCN XID OPERATION A B -------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- -------------------------------------------------------------------------------- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I 1 AAAY3oAABAAAa/ZAAA 30076162 30076162 08001C00D0680000 D 2 AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U 2 SQL> SELECT * FROM SYS_MFBA_NHIST_101864; SELECT * FROM SYS_MFBA_NHIST_101864; SELECT * FROM SYS_MFBA_NHIST_101864 ORA-00942: таблица или представление пользователя не существует SQL> SELECT * FROM SYS_FBA_TCRV_101864; SELECT * FROM SYS_FBA_TCRV_101864; RID STARTSCN ENDSCN XID OP -------------------------------------------------------------------------------- ---------- ---------- ---------------- -- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U SQL> SQL> SELECT * FROM SYS_FBA_HIST_101864; SELECT * FROM SYS_FBA_HIST_101864; RID STARTSCN ENDSCN XID OPERATION A B -------------------------------------------------------------------------------- ---------- ---------- ---------------- --------- ---------- -------------------------------------------------------------------------------- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I 1 AAAY3oAABAAAa/ZAAA 30076162 30076162 08001C00D0680000 D 2 AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U 2 SQL> SELECT * FROM SYS_FBA_DDL_COLMAP_101864; SELECT * FROM SYS_FBA_DDL_COLMAP_101864; STARTSCN ENDSCN XID OPERATION COLUMN_NAME TYPE HISTORICAL_COLUMN_NAME ---------- ---------- ---------------- --------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- 30076127 A NUMBER A 30076535 B VARCHAR2(100) B SQL> SELECT * FROM SYS_FBA_TCRV_101864; SELECT * FROM SYS_FBA_TCRV_101864; RID STARTSCN ENDSCN XID OP -------------------------------------------------------------------------------- ---------- ---------- ---------------- -- AAAY3oAABAAAa/ZAAA 30076147 30076158 05000E00CC680000 I AAAY3oAABAAAa/ZAAA 30076158 30076162 0A000300094C0000 U SQL> Не работает под пользователем с привелегией DBA (system) create flashback archive default fa tablespace users retention 1 year ORA-55611: Нет полномочий для управления архивом Flashback по умолчанию Не работает для pluggable database в 12С create flashback archive default fa tablespace users retention 1 year ORA-65131: Функция Flashback Data Archive не поддерживается для подключаемой базы данных. Документация

Машина времени или "ой, я тут случайно удалила"

Если пользователь случайно попортил данные, а наша система достаточно сложная с большим количеством таблиц и восстанавливаться не охота, то помимо flashback version query (который as of timestamp или scn) можно использовать пакет DBMS_FLASHBACK

Пример использования с процедурой enable_at_time (так же есть вариант работы с scn)
-- готовим объекты CREATE TABLE par(ID NUMBER PRIMARY KEY, txt VARCHAR2(10)); CREATE TABLE chi(par_id NUMBER REFERENCES par(ID) ON DELETE CASCADE, sm NUMBER); INSERT INTO par VALUES (1, 'doc 1'); INSERT INTO par VALUES (2, 'doc 2'); INSERT INTO chi VALUES(1, 100); INSERT INTO chi VALUES(2, 200); -- Злой пользователей портит нашу жизнь DELETE FROM par WHERE ID = 2; UPDATE chi SET sm= -100 WHERE par_id = 1; -- Тут мы фиксируем время, на которое будут выполняться запросы -- В этом примере используется 1 минута назад, но BEGIN --dbms_flashback.disable; DBMS_FLASHBACK.enable_at_time(SYSDATE - 1/24/60); END; / -- Запросы без AS OF возвращают данные не испорченные пользователем SELECT * FROM par; SELECT * FROM chi;

Ну и ложка дегтя:
create table as select в этом режиме сделать невозможно
insert select в этом режиме сделать невозможно

Это делает эту фичу не сильно пригодной, для использования, хотя обходной маневр все таки есть -- пооткрывать курсоры

BEGIN dbms_flashback.disable; DBMS_FLASHBACK.enable_at_time(SYSDATE - 1/24/60); END; / SELECT * FROM par; SELECT * FROM chi; DECLARE CURSOR par_cur IS SELECT * FROM par; par_row par_cur%ROWTYPE; CURSOR chi_cur IS SELECT * FROM chi; chi_row chi_cur%ROWTYPE; BEGIN OPEN par_cur; OPEN chi_cur; -- Отключаем фичу, что позволит нам вставлять данные в таблицу dbms_flashback.disable; -- таблицы для бекапов создаем в другой сессии LOOP FETCH par_cur INTO par_row; EXIT WHEN par_cur%NOTFOUND; INSERT INTO par_bkp VALUES par_row; END LOOP; LOOP FETCH chi_cur INTO chi_row; EXIT WHEN chi_cur%NOTFOUND; INSERT INTO chi_bkp VALUES chi_row; END LOOP; COMMIT; END; / SELECT * FROM par_bkp; SELECT * FROM chi_bkp;
Но, бесспорно, ctas ... as of ... удобнее

Pluggable database

1. В V$/GV$ запросы небходимо дописывать условие con_id=NNN
или сделать так: ALTER SESSION SET container=db2;
и после этого все запросы будут выполняться только для db2

2. Открыть PDB можно командой
alter pluggable database pdb_name open;

3. Вьюхи: из dba_xxx сделали cdb_xxx (их порядка 900). Добавили новые pdb_xxx (2 штуки)
Посмотреть список pdb можно в v$pdbs

4. Создать одну базу из другой можно например так
alter pluggable database db1 close immediate; alter pluggable database db1 open read only; -- Куда кидаем файлы alter system set db_create_file_dest=/home/oracle/oradata/db2; create pluggable database db2 from db1; alter pluggable database db2 open;

5. 1 - CDB$ROOT
 2 - PDB$SEED
 3 .. - остальные PDB

clonedb

Отличное выступление Тима Холла

CloneDB утилита, которая позволяет быстро создать копию базы. На базе источнике создается копия (image copy или backup), которая используется клоном в режиме read-only.
Если клон вносит изменения в данные, то они сохраняются в Copy-on-Write location. Если измений не много, то эти файлы получаются маленькими по размеру.

Алгоритм:
1. Создаем бекап базы источника, кладем бекап в место, доступное для клона. Клонированная база будет использовать бекап как файлы данных.
2. Подготавливаем NFS Client в базе-клоне и монтируем файловую систему. На NFS будет находится COW location
3. Подготавливаем pfile для клона, заполняем на клоне переменные среды
4. Прогоняем скрипт clonedb.pl. Он генерирует два sql скрипта с созданием БД и маппингом бекап-файлов с copy-on-write файлами с использованием пакета dbms_dnfs.
Файлы БД в controlfile клона находятся в backup location
5. Прогоняем созданные скрипты. В 11 версии была ошибка по пересозданию temp tablespace.
Все, база создается за несколько минут вне зависимости от размера источника.

Такие копии хорошо делать, если надо что-то "покрутить". Не подходят, если на клоне будет много изменений и не очень валидны для тестирования производительности.

Неудобно (но достаточно терпимо), что необходимо использовать NFS-клиент
Примечание: к COW-файл системам относятся например ZFS, NetApp, btrfs.

Документация

NB: Эксперементы показывают, что поднять клонированную базу получается только из backup as copy

Использование новых типов данных в SQL из PL/SQL

В Oracle 12c появилась возможность в SQL, вызываемом из PL/SQL использовать новые типы данных:

• BOOLEAN
• табличные типы, объявленные в спецификации пакета
• ассоциативные массивы (index by)

Документация

Пример: create or replace package tst is TYPE tpt IS TABLE OF VARCHAR2(100); TYPE tpt_indexed IS TABLE OF VARCHAR2(100) INDEX BY BINARY_INTEGER; PROCEDURE p; PROCEDURE p2; PROCEDURE p3; FUNCTION f(a BOOLEAN) RETURN VARCHAR2; end tst; / create or replace package body tst is t1 tpt; t1_ind tpt_indexed; PROCEDURE p IS i NUMBER; BEGIN t1 := tpt('table type 1', 'table type 2'); FOR rec IN (SELECT VALUE(t) t FROM TABLE(t1) t) LOOP dbms_output.put_line(rec.t); END LOOP; END; PROCEDURE p2 IS i NUMBER; BEGIN t1_ind(1) := 'table index by - 1'; t1_ind(2) := 'table index by - 2'; FOR rec IN (SELECT VALUE(t) t FROM TABLE(t1_ind) t) LOOP dbms_output.put_line(rec.t); END LOOP; END; FUNCTION f(a BOOLEAN) RETURN VARCHAR2 IS BEGIN IF a THEN RETURN 'param is true'; ELSE RETURN 'param is false'; END IF; END; PROCEDURE p3 IS i1 VARCHAR2(20); i2 VARCHAR2(20); BEGIN EXECUTE IMMEDIATE 'SELECT tst.f(:p1), tst.f(:p2) FROM dual' INTO i1, i2 USING 1=1, 1=0; dbms_output.put_line(i1); dbms_output.put_line(i2); END; end tst; BEGIN tst.p; tst.p2; tst.p3; END; / Results: table type 1 table type 2 table index by - 1 table index by - 2 param is true param is false /

Комментарии

1. Определение типа должно находится в спецификации. В теле не работает
2. Что бы передать boolean необходимо использовать EXECUTE IMMEDIATE - USING

FORALL INDICES OF VALUES OF

DECLARE TYPE tbl IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER; t tbl; idx_t tbl; i PLS_INTEGER; BEGIN -- Обычный FORALL работает только с плотными коллекциями. Если мы удалили элемент, то будет ошибка begin t(1) := 1; t(10) := 10; FORALL i IN t.FIRST .. t.last INSERT INTO a VALUES (t(i)); dbms_output.put_line('Обработано строк ' || SQL%ROWCOUNT); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Ошибка ' || SQLCODE || ' ' || SQLERRM); END; -- INDICES OF позволяет работать с sparse коллекциями. Синтаксис немного изменен begin t(1) := 1; t(10) := 10; FORALL i IN INDICES OF t INSERT INTO a VALUES (t(i)); dbms_output.put_line('Обработано строк ' || SQL%ROWCOUNT); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Ошибка ' || SQLCODE || ' ' || SQLERRM); END; -- VALUES OF позволяет бегать по одной коллекции, а работать с другой. При этом коллекция, по которой мы бегаем не должна быть плотной begin t(1) := 1; t(10) := 10; idx_t(1) := 1; idx_t(3) := 10; FORALL i IN VALUES OF idx_t INSERT INTO a VALUES (t(i)); dbms_output.put_line('Обработано строк ' || SQL%ROWCOUNT); EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Ошибка ' || SQLCODE || ' ' || SQLERRM); END; END; / 

 Выводы

1. Обычный FORALL работает только с плотными коллекциями. Если мы удалили элемент, то будет ошибка
2. INDICES OF позволяет работать с sparse коллекциями. Синтаксис немного изменен
3. ALUES OF позволяет бегать по одной коллекции, а работать с другой. При этом коллекция, по которой мы бегаем не должна быть плотной 

ORA-27086: skgfglk: unable to lock file - already in use

1. remove */dbs/lkORACLE_SID file
2. You will get an error like this
ORA-00202: controlfile: '/oebs200/u01/oracle/PROD/testdata/cntrl01.dbf' ORA-27086: skgfglk: unable to lock file - already in use IBM AIX RISC System/6000 Error: 13: Permission denied on controlfiles and datafiles
3. Copy (not move) controlfile and datafiles to new path
4. Remove old files
5. Restore old files from copied on step 3.

Разное о Cognos

Чем Slicer от Detail Filter

Detail Filter может убирать строки из отчета, Slicer не убирает, а лишь изменяет данные в существующих ячейках

Параметры и фильтры

Тут показано, как делать параметры с автозапросом с multy и single выбором. На примере с выражением для slicer
[Great Outdoor Sales].[Sales Region].[Sales Region].[Country] -> ?c? -- параметр с Single выбором
set([Great Outdoor Sales].[Sales Region].[Sales Region].[Country] -> ?c?) -- параметр с Multy-выбором

Постраничный отчет в Cognos

Задача

Данные выводятся в Crosstab. Для каждого подразделения печатается своя таблица на отдельной странице. Список подразделений выбирает пользователь во время запуска отчета.

Как не получилось

Не получилось сделать Page Layers. На него получилось поместить только статичный объект, а как спрятать не выбранные пользователем страницы я так и не допер.

Как получилось сделать

Возможно, часть сделанных шагов избыточна

1. Создадим запрос и поместим на него DataItem, который будет содержать выбранные подразделения. Выражение для DataItem set([Пользовательский].[ЦПО].[ЦПО].[ЦПО] -> ?p_cpo?). Обзываем его, например, selected cpo

2. Добавляем List. Для него указываем свойства

• Query - selected cpo
• Rows per page - 1
• Column Titles - Hide

3.  Создаем связь MasterDetail между Crosstab и запросом selected cpo. Выбираем объект Crosstab и кликаем по свойству Master Detail Relationships

4. Для того, что бы в заголовке отчета можно было добавить название подразделения указываем в свойстве Page запрос selected cpo и добавляем название подразделения.

5. Помещаем Crosstab внутрь List. Проверяем

Пока не удается избавиться от серой рамки вокруг crosstab (попробовал рисовать ее белым цветом, убирать, ставить Box Type в None.

Cognos, конкатенация строк

В Cognos при конкатенации NULL операторами || и + получается пустая строка. Нормально конкатенирует только функция concat или ее аналогами

Нововведения в 12с

1. Можно индексировать набор колонок, который уже проиндексирован. Судя по всему сделали для 24х7 при перестройки индексов, например с уникальных на неуникальные.

2. Появились колонки с автоинкрементом (Identity Columns). Можно указывать, когда вставлять значения:
GENERATED ALWAYS AS IDENTITY -- колонку вообще нельзя указывать в списке полей для INSERT
GENERATED BY DEFAULT AS IDENTITY -- колонку можно использовать в списке INSERT, вставится указанное значение, если оно не NULL (в этом случае будет сообщение об ошибке) GENERATED BY DEFAULT ON NULL AS IDENTITY -- колонку можно использовать в списке INSERT. Если указан NULL, то будет выбран следующий номер Identity Columns имеют ограничение NOT NULL. Работает быстрее триггеров. Подробнее

3. Возможность задавать в таблице значения по-умолчанию из sequence.nextval и sequence.currval (для вставки master-detail). Значения по умолчанию могут вставляться в случае, если в insert просто не указана колонка (DEFAULT) и в случае NULL значения (DEFAULT ON NULL). Подробнее

Вообще появилась возможность делать значения по-умолчанию в случае, если мы явно вставляем NULL в колонку.
Старое поведение, вставили NULL - получили NULL:
DROP TABLE def; CREATE TABLE def(a VARCHAR2(50), b VARCHAR2(10) DEFAULT 'x'); insert into def(a) values('a only'); insert into def(a, b) values('a, null', NULL); SELECT * FROM def; A B -------------------------------------------------- ---------- a only x a, null
Новая возможность -- NULL вставляется и при явном указании в INSERT NULL DROP TABLE def; CREATE TABLE def(a VARCHAR2(50), b VARCHAR2(10) DEFAULT ON NULL 'x'); insert into def(a) values('a only'); insert into def(a, b) values('a, null', NULL); SELECT * FROM def; A B -------------------------------------------------- ---------- a only x a, null x


4. invisible columns. Пока применения найти не удается http://tkyte.blogspot.com/2013/07/12c-silly-little-trick-with-invisibility.html

5. Возможность посмотреть полный текст SQL запроса при помощи dbms_utility.expand_sql_text с раскрытыми представлениями, правилами VPD. Можно использовать при запросах к V$ вьюхам http://tkyte.blogspot.com/2013/07/12c-sql-text-expansion.html

6. UTL_CALL_STACK -- новое формирование стека вызова. Подробнее


7. В Oracle 11 при добавлении колонки NOT NULL с указаным значением по умолчанию DEFAULT не вызывал изменения блоков таблицы, изменяя только метаданные. В Oracle 12 при добавлении любой (т.е. и NULL колонки) с указанным DEFAULT не вызывают изменения данных и поэтому стоят очень дешево. Подробнее в разделе Metadata-Only DEFAULT Values

8. Админская штучка: в один поток ОС можно засунуть несколько сессий Oracle. Полезно, если на сервере поднята куча инстансов. Подробнее

9. В With можно писать функции и процедуры на PL/SQL. Подробнее Так же функции можно создавать с PRAGMA UDF, такие функции нельзя использовать из pl/sql, но они быстро работают в sql

10. Появилась возможность постраничной выборки в SQL, например OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY. Подробнее

11. После CTAS и INSERT ... SELECT в пустую таблицу статистика собирается автоматически

12. Для табличных партиций и сабпартиций теперь можно указывать, создавать ли local индексы. Подробнее
Для глобальных индексов в партиционированных таблицах возможно удаление партиции без удаления индекса (об этом много написано у Richard Foot).

13. В SQL из PL/SQL можно использовать новые типы данных. Подробнее

14. Теперь можно грантовать пакеты, процедуры и функции другим пакетам. Такие пакеты невозможно будет вызвать из-вне. Пример
Так же можно делать GRANT TO program_unit. Сделано для того, что бы пользователю, вызывающему пакет с invoker rights не давать лишних привелегий. Подробнее
Вообще в этой версии с привелегиями достаточно много новых штук сделали.

15. Если поставить MAX_STRING_SIZE = extended, то SQL сможет использовать VARCHAR2 размером 32767.


16. Можно использовать конструкцию LATERAL, которая позволяет использовать в последующих подзапросах FROM результаты предыдущих подзапросов
SELECT a.object_name, b.object_type FROM (SELECT * FROM All_Objects WHERE ROWNUM <= 10) a, lateral(SELECT o.object_type FROM all_objects o WHERE o.object_id = a.object_id) b I_OBJ1 INDEX CLU$ TABLE I_COL3 INDEX I_UNDO1 INDEX I_CDEF4 INDEX BOOTSTRAP$ TABLE FILE$ TABLE I_CCOL2 INDEX I_FILE#_BLOCK# INDEX C_USER# CLUSTER 

То же самое можно сделать при помощи CROSS APPLY
SELECT a.object_name, b.object_type FROM (SELECT * FROM All_Objects WHERE ROWNUM <= 10) a CROSS APPLY (SELECT o.object_type FROM all_objects o WHERE o.object_id = a.object_id) b I_OBJ1 INDEX CLU$ TABLE I_COL3 INDEX I_UNDO1 INDEX I_CDEF4 INDEX BOOTSTRAP$ TABLE FILE$ TABLE I_CCOL2 INDEX I_FILE#_BLOCK# INDEX C_USER# CLUSTER

Конструкция OUTER APPLY помогает сделать похожий OUTER JOIN
SELECT a.object_name, a.object_type, b.tablespace_name FROM (SELECT * FROM All_Objects WHERE ROWNUM <= 10) a OUTER APPLY (SELECT o.tablespace_name FROM all_tables o WHERE o.table_name = a.object_name) b CLU$ TABLE SYSTEM FILE$ TABLE SYSTEM BOOTSTRAP$ TABLE SYSTEM I_OBJ1 INDEX C_USER# CLUSTER I_UNDO1 INDEX I_FILE#_BLOCK# INDEX I_COL3 INDEX I_CDEF4 INDEX I_CCOL2 INDEX
17. Можно перемещать файлы данных не выводя их в offline

18. Не смотрел подробнее: новый вид обновления материализованных представлений synchronous refresh

19. Новый способ проверить, есть ли у пользователя роль -- sys_context SYS_SESSION_ROLES:
SELECT sys_context('SYS_SESSION_ROLES', 'DBA') FROM dual; TRUE SELECT sys_context('SYS_SESSION_ROLES', 'DBA2') FROM dual; FALSE

20. Теперь планы могут меняться во время выполнения запроса. Сравнивая статистику. используемую при построени плана с реальным выполнением оптимизатор может на лету поменять, например, NESTED LOOPS на HASH JOIN.

21. Новые виды гистограмм для колонок с более чем 254 значениями.
Top frequency histograms -- если большинство значений (99%) в колонке приходятся на несколько значений. Гистограмма строится только по этим популярным значениям, непопулярные отбрасываются
Hybrid histogram -- height-based histogram в которых популярные значения попадают в табличку и одно и тоже значение не попадает более чем 1 раз

22. Статистика для GTT своя в каждой сессии.

23. Статистика, собранная dynamic sampling и adaptive cursor plan сохраняется в словаре (в предыдущих версиях хранилась в cursor cache)

24. Клонировать RMAN можно с базы-копии.

25. В RMAN можно просто без указания конструкции SQL выполнять SQL команды и даже запросы.

26. RMAN умеет восстанавливать отдельную таблицу или группу таблиц.

27. Temporal Validity. Документация. Кайт. Штука, которая позволяет в таблице указывать колонки, в которых хранятся сроки начала и окончания действия записи. Для получения актуальной на дату записи пользоваться в запросом с конструкцией AS OF PERIOD FOR или пакетом DBMS_FLASHBACK_ARCHIVE.
Хотел протестировать эту штуку, да она не заработала. Потом нашел у Кайта  this feature currently is not supported/working with the pluggable database infrastructure. This is a temporary limitation