ORA-20000

Обычно функций xmlelement, xmlagg, xmlattributes и других подобных хватает на все случаи жизни. Но тут понадобилось создать иерархический xml на основе существующий иерархии.

Возьмем для примера существующую иерархию из схемы SCOTT:

select emp.empno, emp.ename, emp.job
from   emp
start with emp.mgr is null
connect by prior emp.empno = emp.mgr;

Для построения иерархии воспользуеся пакетом DBMS_XMLGEN. Примеры использования можно посмотреть здесь: Generating XML Using DBMS_XMLGEN. Для этого создадим функцию.

create or replace package body xml_api is

  function get_xml_hierarchy( ps_sql varchar2
                            ) return xmltype
  is
    qryctx  dbms_xmlgen.ctxhandle;
    vclob   clob;
    vresult xmltype;
  begin
    qryctx := dbms_xmlgen.newcontextFromHierarchy(ps_sql);
    vclob := dbms_xmlgen.getxml(qryctx);
    vresult := xmltype(vclob);
    dbms_xmlgen.closecontext(qryctx);
    return vresult;
  end get_xml_hierarchy;      
  
end xml_api;

Функция использует функцию dbms_xmlgen.newcontextFromHierarchy, которая принимает один параметр, это иерархический запрос. Для этого запроса есть несколько условий:

1. еще раз, это должен быть иерархический запрос;


2. результат запроса должен состоять из 2 колонок;


3. первая колонка должна быть уровнем, LEVEL


4. вторая колонка должна быть типа xmltype;

Строим xml:

select xml_api.get_xml_hierarchy(
'
select level,
       xmlelement(employee,
         xmlattributes(emp.empno, emp.ename, emp.job)) as xmlelem 
from   emp 
start with emp.mgr is null 
connect by prior emp.empno = emp.mgr
')
from   dual;

И смотрим результат:

<?xml version="1.0"?>
<EMPLOYEE EMPNO="7839" ENAME="KING" JOB="PRESIDENT">
  <EMPLOYEE EMPNO="7566" ENAME="JONES" JOB="MANAGER">
    <EMPLOYEE EMPNO="7788" ENAME="SCOTT" JOB="ANALYST">
      <EMPLOYEE EMPNO="7876" ENAME="ADAMS" JOB="CLERK"/>
    </EMPLOYEE>
    <EMPLOYEE EMPNO="7902" ENAME="FORD" JOB="ANALYST">
      <EMPLOYEE EMPNO="7369" ENAME="SMITH" JOB="CLERK"/>
    </EMPLOYEE>
  </EMPLOYEE>
  <EMPLOYEE EMPNO="7698" ENAME="BLAKE" JOB="MANAGER">
    <EMPLOYEE EMPNO="7499" ENAME="ALLEN" JOB="SALESMAN"/>
    <EMPLOYEE EMPNO="7521" ENAME="WARD" JOB="SALESMAN"/>
    <EMPLOYEE EMPNO="7654" ENAME="MARTIN" JOB="SALESMAN"/>
    <EMPLOYEE EMPNO="7844" ENAME="TURNER" JOB="SALESMAN"/>
    <EMPLOYEE EMPNO="7900" ENAME="JAMES" JOB="CLERK"/>
  </EMPLOYEE>
  <EMPLOYEE EMPNO="7782" ENAME="CLARK" JOB="MANAGER">
    <EMPLOYEE EMPNO="7934" ENAME="MILLER" JOB="CLERK"/>
  </EMPLOYEE>
</EMPLOYEE>

Читать полностью

Столкнулся с проблемой, что в ORACLE DB нет "правильной" функции для формирования xml заголовка. Кажется в 10.2 появилась функция XMLRoot , но в фукции нельзя указать кодировку xml, а хотелось бы добавить заголовок подобного типа.

<?xml version="1.0" encoding="windows-1251"?>

Заголовок можно конкатенировать к xml, но существуют ограничения по длине в 32K.
Поэтому пришлось написать свою функцию для добавления заголовка.

create or replace package body xml_api is

  function add_xml_root( ps_root varchar2
                        ,pcl_body clob
                       ) return clob
  is
    vcl_temp clob;
  begin
    dbms_lob.createtemporary(vcl_temp, true, dbms_lob.session);
    
    dbms_lob.open(vcl_temp, DBMS_LOB.LOB_READWRITE);
    dbms_lob.writeappend(vcl_temp, length(ps_root), ps_root);
    dbms_lob.append(vcl_temp, pcl_body);
    dbms_lob.close(vcl_temp);
    
    return vcl_temp;
  end add_xml_root;      
  
end xml_api;

Посмотрим результат на данных их схемы SCOTT.

select xml_api.add_xml_root('<?xml version="1.0" encoding="windows-1251"?>',
         xmlelement(employee_list,
           xmlagg(xmlelement(employee,
             xmlattributes(emp.empno, emp.ename)))).getclobval())
from   emp;

<?xml version="1.0" encoding="windows-1251"?>
<EMPLOYEE_LIST>
  <EMPLOYEE EMPNO="7369" ENAME="SMITH"/>
  <EMPLOYEE EMPNO="7499" ENAME="ALLEN"/>
  <EMPLOYEE EMPNO="7521" ENAME="WARD"/>
  <EMPLOYEE EMPNO="7566" ENAME="JONES"/>
  <EMPLOYEE EMPNO="7654" ENAME="MARTIN"/>
  <EMPLOYEE EMPNO="7698" ENAME="BLAKE"/>
  <EMPLOYEE EMPNO="7782" ENAME="CLARK"/>
  <EMPLOYEE EMPNO="7788" ENAME="SCOTT"/>
  <EMPLOYEE EMPNO="7839" ENAME="KING"/>
  <EMPLOYEE EMPNO="7844" ENAME="TURNER"/>
  <EMPLOYEE EMPNO="7876" ENAME="ADAMS"/>
  <EMPLOYEE EMPNO="7900" ENAME="JAMES"/>
  <EMPLOYEE EMPNO="7902" ENAME="FORD"/>
  <EMPLOYEE EMPNO="7934" ENAME="MILLER"/>
</EMPLOYEE_LIST>

Читать полностью

В 10.2 наконец-то появилось логирование ошибок при выполнение dml команд insert, update, delete и merge.

Почитать хорошую статью можно тут: DML Error Logging in Oracle 10g Database Release 2
В документации пример можно посмотреть здесь: Inserting Data with DML Error Logging

Единственно что нужно, это уточнить пару моментов:

1. Команда REJECT LIMIT указывает на максимально количество ошибок, которое может произойти, прежде чем statement "отвалится". Представим, что у нас при выполнении statement произойдет 2 ошибки, что будет происходить, если при разных значениях REJECT LIMIT.



REJECT LIMIT	Ошибка?	Логирование	Транзакция
0	отрайзится ошибка	в логе будет 1 строка	произойдет автоматический rollback
1	отрайзится ошибка	в логе будет 2 строки	произойдет автоматический rollback
2, больше и UNLIMITED	ошибки не будет!	в логе будет 2 записи	нужно будет подтвердить или откатить транзацию.


Строки в табличке для логирования появятся в любом случае, так как они добавляются в автономной транзакции.

2. Можно использовать 'simple_expression' для последующей выборки из таблицы логирования по колонке ORA_ERR_TAG$. Особенно это полезно, при параллельном выполнении команд.
3. Логирование не работает в следующих случаях:


• есть отложенное ограничение;

Читать полностью