Февраль/Март 2003
Профессионалу администратору
Том Кайт
О создании XML-документов, трансляции дат и работе с переменными привязки
(On Creating, Dating, and Binding, By Tom Kyte)
Источник: журнал Oracle Magazine, May/June 2002
(/oramag/oracle/02-may/o32asktom.html).
Наш эксперт создает XML-документы, транслирует даты и работает с переменными привязки.
Я хочу создать файл (с выгрузкой данных из таблицы) в формате XML. Как лучше всего сделать это? Есть ли в СУБД Oracle средства для создания XML-документов?
Я попросил Шона Дилона (Sean Dillon), нашего местного гуру по XML, ответить на этот вопрос. Ниже приведен его ответ.
Загрузите и инсталлируйте последнюю версию инструментария разработчика XML (XDK, Oracle XML Developer's Kit), доступного по адресу: otn.oracle.com/tech/xml/index.html. С помощью XDK вы сможете относительно просто создавать XML-документы, используя для этого такие языки, как PL/SQL, Java, C или C++. Инсталлировав Java XDK, вы можете, например, создать XML-документ, содержащий строки таблицы SCOTT.EMP:
$ java OracleXML getXML
-user "scott/tiger" "select * from emp" >emp.xml
$ cat emp.xml
<?xml version = '1.0'?>
<ROWSET>
<ROW num="1">
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
...
<ROW num="14">
<EMPNO>7566</EMPNO>
<ENAME>JONES</ENAME>
<JOB>MANAGER</JOB>
<MGR>7839</MGR>
<HIREDATE>4/2/1981 0:0:0</HIREDATE>
<SAL>2975</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
</ROWSET>
В этом классе Java OracleXML есть также различные опции; вы сможете ознакомиться с ними, введя:
$ java OracleXML
Будет выдан список всех команд с описанием их функций. Данное решение на языке Java является внешним по отношению к СУБД, тем не менее оно может вас не удовлетворить. Альтернативный вариант: вы можете использовать PL/SQL и создавать XML-документы с помощью пакета DBMS_XMLGEN, как это показано в листинге 1.
|
ЛИСТИНГ 1: использование пакета DBMS_XMLGEN для создания XML-документов |
SQL> var my_xml clob
SQL> set autoprint on
SQL> declare
2 l_ctx dbms_xmlgen.ctxHandle;
3 l_sql varchar2(400);
4 begin
5 l_sql := 'select * from emp';
6 l_ctx := dbms_xmlgen.newContext(l_sql);
7 :my_xml := dbms_xmlgen.getXml(l_ctx);
8 end;
9 /
PL/SQL procedure successfully completed.
MY_XML
-----------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
...
</ROW>
</ROWSET>
SQL> set autoprint off |
В пакете DBMS_XMLGEN есть функция getXml(), которая по задаваемому запросу SQL возвращает объект CLOB без создания контекста и накладных расходов. Но я рекомендую в блоке PL/SQL использовать контекстный указатель. Он позволит вам воспользоваться огромным количеством функциональных возможностей (большинство из которых безусловно необходимы в промышленных приложениях), например переменные привязки в вашем запросе, создание определений типов документов (DTD) или схем для ваших XML-документов, модификация имен наборов строк (ROWSET) и элементов строк (ROW), ограничение количества возвращаемых строк.
Функцию dbms_xmlgen.newContext() можно использовать для непосредственного создания XML-документов, но параметром функции может быть только простой статический оператор SQL. Из предыдущих статей колонки "Спросите Тома" (и вопросов в форуме asktom.oracle.com) вы все знаете, что приложения со статическими операторами SQL без переменных привязки будут работать медленно и будет отсутствовать их масштабируемость. Итак, есть ли способ создания XML-документа с помощью оператора SELECT, которому как аргумент передается курсор? Как выяснилось, есть пара способов реализации этого. К счастью, в пакете DBMS_XMLQUERY имеется процедура SETBINDVALUE, поэтому можно создать функцию, как это показано в листинге 2. Листинг показывает, как написать запрос с переменными привязки (:DEPTNO - переменная привязки). После этого можно вызывать процедуру dbms_xmlquery.setbindvalue для присваивания значений переменной привязки. Это позволяет эффективно использовать разделяемый (совместно используемый) SQL (план выполнения запроса используется многократно независимо от значений в переменной привязки :DEPTNO).
|
ЛИСТИНГ 2: использование пакета DBMS_XMLGEN с процедурой SETBINDVALUE |
SQL> create or replace function
2 get_emps_in_dept( p_deptno in number )
3 return clob
4 is
5 l_ctx dbms_xmlquery.ctxType;
6 l_sql varchar2(100);
7 l_xml clob;
8 begin
9 l_sql := 'select * from emp
10 where deptno = :deptno';
11 dbms_lob.createtemporary
(l_xml,true,dbms_lob.session);
12 l_ctx := dbms_xmlquery.newContext(l_sql);
13 dbms_xmlquery.setbindvalue
(l_ctx,'deptno',p_deptno);
14 dbms_xmlquery.setRaiseNoRowsException
(l_ctx,false);
15
16 - get the xml fm the context
17 l_xml := dbms_xmlquery.getXML(l_ctx);
18
19 dbms_xmlquery.closeContext(l_ctx);
20 return l_xml;
21 exception
22 when others then
23 dbms_xmlquery.closeContext(l_ctx);
24 raise;
25 end get_emps_in_dept;
26 /
Function created. |
Если ваш интерфейс не позволяет использовать переменные привязки, вы можете использовать разделение курсоров, доступное в СУБД Oracle8i Release 2 (8.1.6) и выше, установив:
execute immediate 'alter session set
cursor_sharing = force';
Затем выполните нужные действия, не используя переменные привязки. После этого для возврата в исходное состояние выполните:
execute immediate 'alter session set
cursor_sharing = exact';
Функции обработки дат и национальные языки
Как мне в функции [next_day] (следующий день недели) использовать одно и то же значение параметра [name_of_day] (название дня недели) независимо от установленного значения параметра NLS_LANGUAGE? Моя функция должна работать в разных системах баз данных с разными национальными языками, а в функции [next_day] нужно задавать название дня недели. Как это сделать?
Показываю используемый мною прием. Я создаю символьную строку, в которую по умолчанию заносится название понедельника в текущем национальном языке. Я выбираю какую-то дату, которая, как я знаю, приходится на понедельник, и заставляю СУБД Oracle научить меня правописанию этого дня недели в текущем национальном языке, как это показано в листинге 3.
|
ЛИСТИНГ 3: правописание "понедельника" |
SQL> alter session set nls_language = english;
SQL> declare
2 l_monday varchar2(255) default
to_char( to_date('20011231','yyyymmdd'),'day');
3 l_until date;
4 begin
5 l_until := next_day( sysdate, l_monday );
6 dbms_output.put_line(l_until||' '||l_monday);
7 end;
8 /
14-JAN-02 Monday
PL/SQL procedure successfully completed.
SQL> alter session set nls_language = french;
Session altered.
SQL> declare
2 l_monday varchar2(255) default
to_char(to_date('20011231','yyyymmdd'),'day');
3 l_until date;
4 begin
5 l_until := next_day( sysdate, l_monday );
6 dbms_output.put_line(l_until||' '||l_monday);
7 end;
8 /
14-JAN-02 lundi
PL/SQL procedure successfully completed. |
В примере листинга 3 я выбрал известную мне дату, которая приходится на понедельник: 20011231, понедельник 31 декабря 2001 года, и задал ее хранение в формате yyyymmdd. Затем я "попросил" СУБД Oracle "написать" название понедельника в том языке, который установлен в данное время.
Селективное предоставление привилегий
У меня системой баз данных Oracle7 Release 7.3 работает несколько пользователей. Как мне выдать всем пользователям привилегию для выполнения оператора
alter tablespace имя_таб_пространства coalesce ?
Я хочу разрешать только дефрагментацию табличных пространств и не выдавать никаких других привилегий.
Это как раз то, для чего хорошо подходят хранимые процедуры. Они по умолчанию работают с привилегиями владельца, поэтому с их помощью легко и безопасно решать такие задачи.
В листинге 4 показано создание процедуры, в которой привилегия ALTER TABLESPACE (изменить табличное пространство) предоставляется только для дефрагментации табличных пространств). После этого нужно только всем, кому можно выполнять эту процедуру, выдать соответствующую привилегию EXECUTE.
|
ЛИСТИНГ 4: предоставление привилегий в процедуре |
create or replace procedure
coalesce_tablespace(p_tablespace in varchar2)
is
cursor_name pls_integer default
dbms_sql.open_cursor;
ignore pls_integer;
BEGIN
dbms_sql.parse(cursor_name,
'alter tablespace '||p_tablespace||' coalesce',
dbms_sql.native);
ignore := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
END; |
| 
