понедельник, 17 декабря 2012 г.

Регистрация формы в ОЕБС

Для регистрации формы, разработанной в 6 версии Oracle Forms используем:


f60gen module=<FORM_NAME> userid=APPS/apps Module_Type=FORM Output_File=$<PRODUCT>_TOP/forms/US/<FORM_NAME>.fmx


Для регистрации формы, разработанной в 10 версии Oracle Forms используем:

frmcmp_batch module=$AU_TOP/forms/RU/XX_CITY.fmb userid=apps/apps module_type=FORM Compile_All=YES output_file=$AU_TOP/forms/RU/XX_CITY.fmx

четверг, 6 декабря 2012 г.

Трассировка сессии

Трассировка сессии Oracle помогает найти проблемные запросы и понять в чем именно причина.

Что именно будет записываться в трассировочный файл, определяется номером события (event).
Стандартный номер события 10046. Именно такой уровень устанавливается при выполнении команды1 alter session set sql_trace=true;


Список данных событий и для чего они предназначены можно посмотреть здесь EventReference



Событие трассировки 10046 / Trace event 10046

Включить трассировку в текущей сессии1 alter session set events '10046 trace name context forever, level 1';


выключить трассировку в текущей сессии1 alter session set events '10046 trace name context off';


Уровни:
0 – трассировка отключена
1 – стандартная трассировка
4 – добавлены значения связываемых (binds) переменных
8 – добавлены события ожидания (waits)
12 – связываемые переменные + события ожидания

Для включения трассировки в другой сессии можно воспользоваться командой1 sys.dbms_system.set_ev(sid, serial#, 10046, 12, '');


Путь к директории, где появится файл трассировки1 select value from v$parameter where name = 'user_dump_dest';


Задать имя для trace файла1 alter session set tracefile_identifier = 'mytrace1';




Понимание трасировочного файла


Выполняем следующий код, чтобы создать trace файл:1
2
3
4
5
6
7
8
9
10
11
12
13
14
15 SET SERVEROUTPUT ON
alter session set tracefile_identifier = 'mytrace2'
/
alter session set events '10046 trace name context forever, level 12'
/
declare
l_row_num number;
l_owner varchar2(10) := 'SYS';
begin
select count(1) into l_row_num from all_tables t where t.owner = l_owner;
dbms_output.put_line('num='||l_row_num);
end;
/
alter session set events '10046 trace name context forever, level 0'
/


Заголовок файла – содержит информацию об инстансе и сессии для которой создан1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 Trace file .../"instance_name"_ora_12591_mytrace2.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = ...
System name: ...
Node name: ...
Release: ...
Version: ...
Machine: ...
Instance name: "instance_name"
Redo thread mounted by this instance: 1
Oracle process number: 545
Unix process pid: 12591, image: ...

*** 2012-09-19 12:07:49.268
*** SESSION ID:(669.6321) 2012-09-19 12:07:49.268
*** CLIENT ID:() 2012-09-19 12:07:49.268
*** SERVICE NAME:(...) 2012-09-19 12:07:49.268
*** MODULE NAME:(PL/SQL Developer) 2012-09-19 12:07:49.268
*** ACTION NAME:(Command Window - New) 2012-09-19 12:07:49.268


Разбор курсора1
2
3 PARSING IN CURSOR #11529215044981500944 len=54 dep=1 uid=44 oct=3 lid=44 tim=10192202222475 hv=1751201178 ad='c0000005d60dcaf0' sqlid='1f79yzdn62dcu'
SELECT COUNT(1) FROM ALL_TABLES T WHERE T.OWNER = :B1
END OF STMT


cинтаксис1
2
3 PARSING IN CURSOR #[CURSOR] len=X dep=X uid=X oct=X lid=X tim=X hv=X ad='X' sqlid='X'
[statement]
END OF STMT

[CURSOR] – номер курсора
len – длина SQL запроса
dep – рекурсивная глубина SQL запроса
uid – id пользователя (user) под которым был разобран запрос 1 select * from all_users where user_id = :uid;

oct – ID команды oracle. Можно увидеть в V$SQL.COMMAND_TYPE и V$SESSION.COMMAND
lid – ID схемы в которой был разобран запрос. Может отличаться от uid
tim – timestamp, в микросекундах
hv – hash значение, V$SQL.HASH_VALUE
ad – адрес. V$SQL.ADDRESS, V$SQLTEXT.ADDRESS
sqlid – ID запроса, V$SQL.SQL_ID

Основные операции1
2
3 PARSE #11529215044981500944:c=0,e=370,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=10192202222474
EXEC #11529215044981500944:c=270000,e=276923,p=0,cr=3,cu=0,mis=1,r=0,dep=1,og=1,plh=242196440,tim=10192202499569
FETCH #11529215044981500944:c=840000,e=1466871,p=2776,cr=71124,cu=0,mis=0,r=1,dep=1,og=1,plh=242196440,tim=10192203966668


PARSE – разбор курсора, EXEC – выполнение курсора, FETCH – получение данных
cинтаксис1
2
3 PARSE #[CURSOR]:c=X,e=X,p=X,cr=X,cu=X,mis=X,r=X,dep=X,og=X,plh=X,tim=X
EXEC #[CURSOR]:c=X,e=X,p=X,cr=X,cu=X,mis=X,r=X,dep=X,og=X,plh=X,tim=X
FETCH #[CURSOR]:c=X,e=X,p=X,cr=X,cu=X,mis=X,r=X,dep=X,og=X,plh=X,tim=X

[CURSOR] – номер курсора
c – время в микросекундах затраченное процессором
e – общее затраченное время в микросекундах
p – число физических чтений (physical reads)
cr – количество блоков полученных в режиме согласованного чтения (retrieved for CR reads)
cu – количество блоков полученных в текущем режиме (retrieved in current mode)
mis – отсутствие курсора в кеше, выполняется hard parse
r – количество возвращаемых строк
dep – рекурсивная глубина запроса
og – Цель оптимизатора: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose
plh – Hash значение для выполняемого плана. V$SQL_PLAN.PLAN_HASH_VALUE
tim – timestamp, в микросекундах

Связываемые переменные / binds1
2
3
4
5
6 BINDS #11529215044981500944:
Bind#0
oacdty=01 mxl=32(10) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=171 siz=32 off=0
kxsbbbfp=9fffffffbf3b37b8 bln=32 avl=03 flg=09
value="SYS"

[CURSOR] – номер курсора
bind#N – номер переменной
oacdty – тип данных
mxl- maximum length of the bind variable value (private maximum length in parentheses)
mxlc – ?
mal – длина массива
scl – масштаб (scale)
pre – точность (precision)
oacflg – special flag indicating bind options
fl2 – second part of oacflg
frm – ?
csi – идентификатор кодировки
siz – объем выделяемой памяти
off – offset into the chunk of the bind buffer
kxsbbbfp – адрес переменной
bln – длина буфера для переменной
avl – актуальная длина переменной
flg – статус переменной
value- значение переменной

Ожидания / wait1
2 WAIT #11529215044981500944: nam='Disk file operations I/O' ela= 285 FileOperation=2 fileno=4 filetype=2 obj#=397424 tim=10192202526355
WAIT #11529215044981500944: nam='db file sequential read' ela= 583 file#=4 block#=7927 blocks=1 obj#=397424 tim=10192202526996

[CURSOR] – номер курсора
nam- тип ожидания
ela – затраченное время в микросекундах
obj#- номер объекта
tim – timestamp, в микросекундах

Остальные параметры зависят от типа ожидания. Увидеть данные параметры можно с помощью запроса:1 select * from v$event_name where name = :waitname;


План выполнения1
2
3
4
5
6
7
8 STAT #11529215044981500944 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=71124 pr=2776 pw=0 time=1466915 us)'
STAT #11529215044981500944 id=2 cnt=1047 pid=1 pos=1 obj=0 op='FILTER (cr=71124 pr=2776 pw=0 time=1123290 us)'
STAT #11529215044981500944 id=3 cnt=1047 pid=2 pos=1 obj=0 op='NESTED LOOPS OUTER (cr=68261 pr=2735 pw=0 time=1080842 us cost=3024 size=14145 card=115)'
STAT #11529215044981500944 id=4 cnt=1047 pid=3 pos=1 obj=0 op='HASH JOIN RIGHT OUTER (cr=64291 pr=2289 pw=0 time=954642 us cost=2820 size=11322 card=102)'
...
STAT #11529215044981500944 id=9 cnt=28 pid=8 pos=1 obj=16 op='TABLE ACCESS FULL TS$ (cr=33 pr=0 pw=0 time=1609 us cost=8 size=84 card=28)'
STAT #11529215044981500944 id=10 cnt=1047 pid=8 pos=2 obj=0 op='NESTED LOOPS (cr=62544 pr=2252 pw=0 time=561698 us cost=2620 size=8976 card=102)'
...

[CURSOR] – номер курсора
id – номер строки плана
cnt – количество обработанных строк
pid – идентификатор родителя
pos – позиция относительно родителя
obj – ID объекта. ALL_OBJECTS.OBJECT_ID и V$SQL_PLAN.OBJECT#.
op – операция
cr – согласованное чтение (Consistent read)
pr – физическое чтение (Physical reads)
pw – физическая запись (Physical writes)
time – timestamp, в микросекундах
cost – оценка выполнения запроса (CBO)
size – объем данных в байтах
card – число возвращаемых строк

Дополнительно можно почитать ноту Interpreting Raw SQL_TRACE output [ID 39817.1]

среда, 5 декабря 2012 г.

API добавления полномочия для пользователя

С помощью данного API можно добавить полномочие пользователю1

 begin
-- Call the procedure
fnd_user_pkg.addresp(username => :username,
resp_app => :resp_app,
resp_key => :resp_key,
security_group => :security_group, -- в основном 'STANDARD'
description => :description, -- любой коммент
start_date => :start_date,
end_date => :end_date);
commit;
dbms_output.put_line('Responsibility Added Successfully');
exception
when others then
dbms_output.put_line(' Responsibility is not added due to '
|| substr(SQLERRM, 1, 100));
rollback;
end;



И весьма полезный скрипт, c помощью которого можно быстро перетянуть полномочия с одного инстанса на другой.

declare
l_user_name varchar2(100) := 'USER_NAME';
begin
dbms_output.put_line('BEGIN');
-- цикл по списку полномочий пользователя
for i in (select 'fnd_user_pkg.addresp('''||u.user_name||''', '''
|| a.application_short_name ||''', '''
|| r.responsibility_key ||''', '''
|| 'STANDARD' ||''', '''
|| 'Add by script' ||''', '
|| 'sysdate' ||', '
|| 'null' ||');'
as script
, r.responsibility_name
from fnd_user u
,fnd_user_resp_groups g
,fnd_application_vl a
,fnd_responsibility_vl r
where g.user_id(+) = u.user_id
and g.responsibility_application_id = a.application_id
and a.application_id = r.application_id
and g.responsibility_id = r.responsibility_id
AND u.user_name = l_user_name
)
loop
dbms_output.put_line(' dbms_output.put_line('''||i.responsibility_name||''');');
dbms_output.put_line(' '||i.script);
dbms_output.put_line('');
end loop;
dbms_output.put_line(' commit;');
dbms_output.put_line(' dbms_output.put_line('''');');
dbms_output.put_line(' dbms_output.put_line(''Responsibility Added Successfully'');');
dbms_output.put_line('EXCEPTION');
dbms_output.put_line(' when others then');
dbms_output.put_line(' dbms_output.put_line('' Responsibility is not added due to '' || substr(SQLERRM, 1, 100));');
dbms_output.put_line(' rollback;');
dbms_output.put_line('END;');
end;

вторник, 27 ноября 2012 г.

О соединении данных столбца в одну строку с помощью SQL-запроса

Постановка задачи: в некоей таблице имеется столбец с данными. Необходимо эти данные склеить в строку с помощью одного SQL-запроса.

Часть I. Аналитика и иерархия.

Простой способ решения задачи подсказал Т.Кайт в статье On Ignoring, Locking, and Parsing. Этот способ основывается на использовании аналитических функций и функции SYS_CONNECT_BY_PATH для иерархических запросов и применим начиная с Oracle9i Database Release 1. Идея заключается в следующем.
  1. Выбираемые данные необходимо разделить на подгруппы, для которых будут склеиваться строки и в каждой подгруппе пронумеровать элементы в порядке их склеивания.
      select
        deptno,
        ename,
        row_number() over (partition by deptno order by ename) rn
      from emp
  2. С помощью иерархического запроса все данные подгруппы соединяются в одну ветку дерева. Для каждого узла с помощью функции SYS_CONNECT_BY_PATH вычисляется строка со всеми предшествующими значениями поля, разделенными заданным разделителем.
    select
      deptno,
      sys_connect_by_path(ename, ' ' ) scbp
    from
      (select
         deptno,
         ename,
         row_number() over (partition by deptno order by ename) rn
       from emp
      )
    start with rn = 1
    connect by prior rn = rn-1
    and prior deptno = deptno
  3. Далее остается сгруппировать полученные данные по идентификатору подгруппы и выбрать для каждой значение строки максимальной длинны.
    select
      deptno,
      max(sys_connect_by_path(ename, ' ' )) scbp
    from
      (select
         deptno,
         ename,
         row_number() over (partition by deptno order by ename) rn
       from emp
      )
    start with rn = 1
    connect by prior rn = rn-1
    and prior deptno = deptno
    group by deptno
    order by deptno
В результате получаем список сотрудников по отделам.
   DEPTNO         SCBP
---------         ----------------------------------
       10         CLARK KING MILLER
       20         ADAMS FORD JONES SCOTT ...
       30         ALLEN BLAKE JAMES MARTIN ...
Этот способ прост и удобен, но имеет один недостаток. Функция SYS_CONNECT_BY_PATH работает с переменными VARCHAR2, которые, как известно, ограничиваются размером 4000 символов. При большом объеме склеиваемых данных, неизбежно возникает ошибка ORA-01489: result of string concatenation is too long. Эту проблему решает второй способ решения задачи.

Часть II. XML и CLOB.

Второй вариант предусматривает использование аггрегатной функции XMLAGG для получения склеенной строки в виде XML-объекта, который может быть преобразован в CLOB.
select
  deptno,
  XMLAGG(XMLELEMENT("node", ename)).getCLOBVal() scbp
from emp
group by deptno
Для упорядочивания элементов в склеиваемой строке функции XMLAGG можно добавить сортировку.
select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getCLOBVal() scbp
from emp
group by deptno
А если возникнет необходимость отсортировать полученные склеенные строки, можно воспользоваться преобразованием типов cast.
select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getCLOBVal() scbp
from emp
group by deptno
order by cast( substr(scbp, 1, 4000) as varchar2(4000))
В результате получится следующее:
   DEPTNO         SCBP
---------         ----------------------------------
       20         <node>ADAMS</node><node>FORD</node> ...
       30         <node>ALLEN</node><node>BLAKE</node> ...
       10         <node>CLARK</node><node>KING</node>
Недостаток данного способа – наличие тэгов, которые необходимо обрабатывать в зависимости от целей поставленной задачи. Хотя для случая, ставшего причиной поиска описанного решения, тэги оказались скорее плюсом.
Этот же метод можно применить и для более коротких строк, где не требуется использование CLOB-данных. XML-объект можно преобразовать в varchar2 с помощью функции getStringVal().
select
  deptno,
  XMLAGG(XMLELEMENT("node", ename) order by ename).getStringVal() scbp
from emp
group by deptno
order by scbp

среда, 14 ноября 2012 г.

Окно - Исходный формат отчета отличается от конечного

Итак, если создан отчет с шаблоном в формате RTF,  а вывод в виде Excel, и выпадает окошко с сообщением, что исходный формат отчета отличается от конечного, а нам это окошко надо убрать с глаз долой, тогда правим:

В регистре:
HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Excel\Security
новый параметр (если нету) с типом DWORD , называем ExtensionHardening - значение 0

вторник, 6 ноября 2012 г.

Использование Oracle SQL*Loader для загрузки данных в таблицы

Использование Oracle SQL*Loader для загрузки данных в таблицы

Данный минифак не претендует на полноту отражения всех особенностей работы с SQL*Loader, а служит для того, чтобы быстро войти в тему
SQL*Loader — специальная утилита Oracle сервера для загрузки данных в БД из локальных файлов с данными. Для того чтобы воспользоваться ее функционалом создадим несколько файлов, которые будем использлвать в работе, и положим их в папку C:\oracle\ora90\BIN\load (в общем случае):

  • data.dat или data.txt — собственно файл с данными. Пусть он будет представлять строки данных разделенных сиволом перевода строки.
  • load.ctl — контрольный файл — содержит инструкции по загрузке данных в БД, в нашем случае будет иметь вид:
    LOAD DATA -- Загружаем данные
    INFILE 'data.txt'  "str '\n'" -- из файла data.txt, разделитель строк str '\n' - символ перевода строки
    INTO TABLE load_data -- в таблицу load_data
    FIELDS terminated BY ',' OPTIONALLY ENCLOSED BY '"'
    (DATA char) -- название столбца и тип данных в таблице load_data
    То что мы не используем, но встречается часто: fields terminated by ‘,’ означает, что границы данных определены по символу «,», а optionally enclosed by ‘»‘ определяет, что данные могут содержать символ обрамления.
  • load.bat — пакетный файл (англ. batch file) выполняющий запуск sqlldr.exe имеет вид:
    SET nls_lang=russian_cis.ru8pc866 -- устанавливаем нужную кодировку
    sqlldr.exe userid=user_name/user_pass@test control=LOAD.ctl errors=100 bad=DATA.bad -- test — строка связи*, load.ctl — контрольный файл, data.bad — файл плохих записей
    Строка связи — это та строка, которая должна быть прописана в ORA Network Configuration File: D:\oracle\ora90\network\admin\tnsnames.ora (в общем случае). Errors=100 — количество допустимых ошибок (по умолчанию 50).
  • Далее находим нашу папку C:\oracle\ora90\BIN\load, находим файл load.bat, запускаем его и любуемся на результат:
    SELECT * FROM load_data;
    Пока все, пишите об ошибках и неточностях.
Пара полезных ссылок по теме:
Примечание:
Интересно, что при сохранении *.xslx в «.csv с разделителем запятой» разделителем на самом деле становится точка с запятой.

Разбор/парсинг значения поля (строки) по разделителям на строки

Источник:  http://plsqlbook.ru/category/question-answer/


Простой пример того, о чем речь:
SELECT regexp_substr(str, '[^#]+', 1, LEVEL)
  FROM (SELECT 'one#two#tree' str FROM dual)
CONNECT BY instr(str, '#', 1, LEVEL-1) > 0
напоминалка:
  • ‘[^#]+’ такая маска найдет все символы кроме решетки
  • instr(str, ‘#’, 1, LEVEL-1) > 0 поиск позиции разделителя начиная с первой позиции и, видимо, с LEVEL = 2 (возвратит 0 если позиция при заданных параметрах не найдена)
  • CONNECT BY условие подсказывает ораклу как долго продолжать цикл
Результат:
1 one
2 two
3 tree
 
 
Вариант 2
 
select 
   ltrim(rtrim(
    substr(s_element,
         instr(s_element,',',1,iter.pos)+1,
         instr(s_element,',',1,iter.pos+1)-
         instr(s_element,',',1,iter.pos)),',')) s_element,iter.pos
from 
     (select ','||s_in||',' s_element from dual) csv,
     (select rownum pos from ra_customer_trx_all) iter
where 
     iter.pos <= ((length(csv.s_element)-length(replace(csv.s_element,',')))/length(','))-1 

где  s_in  - исходная строка