ORA-20000

В 10 версии добавилась возможность востанавливать удаленные таблицы и индексы.

Для того что бы включить\выключить корзину:

-- Отключить корзину для сессии
ALTER SESSION SET RECYCLEBIN=OFF;
-- Отключить корзину
ALTER SYSTEM SET RECYCLEBIN=OFF SCOPE=SPFILE;

-- Включить корзину для сессии
ALTER SESSION SET RECYCLEBIN=ON;
-- Включить корзину
ALTER SYSTEM SET RECYCLEBIN=ON SCOPE=SPFILE;

По умолчанию корзина включена.

Чтобы посмотреть удаленные объеты можно воспользовать командой:

-- выбор удаленных объектов пользователя
select *
from   user_recyclebin;
-- выбор всех удаленных объектов
select *
from   dba_recyclebin;

Чтобы удалить объеты из корзины:

-- удалить все реинкарнации table1 из корзины
purge table table1; 
-- удалить table1 из корзины по системному имени
purge table "BIN$0Vq5kNlvTS6G/uyKOlzdAw==$0";
-- удалить все реинкарнации index1 из корзины
purge table table1; 
-- удалить index1 из корзины по системному имени
purge table "BIN$0Vq5kNlvSD6G/uyKOlzdAw==$0"; 
-- удалить все объекты пользователя "FK" из тайблспейса ts_fk.
purge tablespace ts_fk user fk; 
-- удалить все объекты  из тайблспейса ts_fk.
purge tablespace ts_fk; 
-- удалить все объекты из корзины текущего пользователя
purge recyclebin; 
-- удалить все объекты из корзины, включая системные объеткты
purge dba_recyclebin; 

Подробнее о команде PURGE можно посмотреть здесь: Oracle® Database SQL Reference 10g Release 2 (10.2) PURGE

Для восстановления удаленных таблиц нужно воспользоваться командами:

-- востановить последению удаленную версию table1
flashback table table1 to before drop;
-- востановить УКАЗАННУЮ удаленнную версию table1
flashback table "BIN$CxaooGNESZa4GThzDSX5SQ==$0" to before drop;

Подробнее о корзине можно почитать тут: Using Oracle's recycle bin

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

Обычно функций 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>

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