1228

Глава 16

бцов в запросе. В качестве примера ее использования рассмотрим обобщенную процедуру сброса результатов запроса в файл операционной системы. Она отличается от SQL-Unloader, рассмотренной в главе 9 при создании средств выгрузки даннгх. В данном примере данные сбрасываются в файл в виде записей фиксированной длины, в которых столбцы всегда начинаются с одной и той же позиции. Для этого анализируются результаты вызова DBMS SQL.DESCRIBE COLUMNS, в которгх помимо количества выбираемых столбцов можно найти и их максимальный размер. Прежде чем рассмотреть пример полностью, давайте подробней разберемся с процедурой DESCRIBE COLUMNS. После анализа запроса с помощью этой процедуры можно обратиться к серверу за информацией о том, что можно ожидать при выборке данных по этому запросу. Эта процедура создает массив записей с информацией об именах, типах данных, максимальном размере столбцов и т.п.

Вот пример использования процедуры DESCRIBE COLUMNS. Выдаются данные, возвращаемые ею для запроса; благодаря этому можно узнать, какая информация доступна:

scott@TKYTE816> create or replace

2 procedure desc query(p query in varchar2)

3 is

4 l columnValue varchar2(4000) ;

5 l status integer;

6 l colCnt number default 0;

7 l cnt number default 0;

8 l line long; 9

10 /* На используем эту таблицу, чтобы узнать, какие данные w выбирает запрос

11 */

12 l descTbl dbms sql.desc tab; 13

15 /* Шаг 1: открыть курсор. */

16 l theCursor integer default dbms sql.open cursor;

17 begin

19 /* Шаг 2: проанализировать входной запрос, чтобы можно было w описать его результаты. */

20 dbms sql.parse(l theCursor, p query, dbms sql.native);

22 /* Шаг З: описываем результаты запроса.

23 * Переменная L COLCNT будет содержать количество выбранных в

24 * запросе столбцов. Оно будет равно L DESCTBL.COUNT;

2 5 * эта переменная содержит избыточную информацию. Переменная

26 * L DESCTBL содержит полезные сведения о выбранных столбцах.

27 */

29 dbms sql.describe columns(c => l theCursor,

30 col cnt => l colCnt,

31 desc t => l descTbl);

Динамический SQL 1229

33 for i in 1 lcolCnt

34 loop

35 dbms output.put line

36 (Colu Type........... M l descTbl(i).col type);

37 dbms output.put line

38 (Max Length............ M l descTbl(i).col max len);

39 dbms output.put line

40 (Name.................. M l descTbl(i).colname);

41 dbms output.put line

42 (Name Length........... M l descTbl(i) .col name len);

43 dbms output.put line

44 (ObjColumn Schema Name. l descTbl(i).col schema name);

45 dbms output.put line

46 (Schema Name Length.... l descTbl(i) .col schema name len) ;

47 dbms output.put line

48 (Precision............ M l descTbl(i) ,col precision) ;

49 dbms output.put line

50 (Scale................. M l descTbl(i).col scale);

51 dbms output.put line

52 (Charsetid............. M l descTbl (i). col Charsetid) ;

53 dbms output.put line

54 (Charset Form......... M l descTbl(i) .col charsetform) ;

55 if (l desctbl(i).col null ok) then

56 dbms output.put line( Nullable.............-Y);

57 else

58 dhms output.put line( Nullable..............N) ;

59 end if;

60 dbms output.put line(-) ;

61 end loop;

63 /* Шаг 9: закрыть курсор и освободить ресурсы. */

64 dbms sql.close cursor(l theCursor);

65 exception

66 when others then

67 dbms sql.close cursor(l theCursor);

68 raise;

69 end desc query;

70 /

Procedure created.

scott@TKYTE816> set serveroutput on

scott@TKYTE816> exec desc query(select rowid, ename from emp);

Column Type...........11

Max Length............16

Name..................ROWID

Name Length...........5

ObjColumn Schema Name. Schema Name Length.... 0

Precision.............0

Scale.................0

Charsetid............0

1230

Глава 16

Charset Form..........0

Nullable..............Y

Column Type...........1

Max Length............10

Name..................ENAME

Name Length...........5

ObjColumn Schema Name.

Schema Name Length.... 0

Precision.............0

Scale.................0

Charsetid.............31

Charset Form..........1

Nullable.............У

PL/SQL procedure successfully completed.

К сожалению, значение COLUMN TYPE - число, а не имя типа данных, так чю если не знать, что значение 11 соответствует типу ROWID, а значение 1 - типу VARCHAR2, расшифровать эти результаты не удастся. В руководстве Oracle Call Interface Programmers Guide представлен полный список внутренних кодов типов данных и соответствующих имен типов. Этот список воспроизведен ниже.

Теперь мы готовы рассмотреть всю подпрограмму, которая может принять практически любой запрос и сбросить результаты его выполнения в файл операционной системы (предполагается, что пакет UTL FILE настроен; эта настройка подробно описана в приложении А):