XML вытянуть произвольную таблицу

Стало задание хранить результаты выполнения запросов.

Например, какие-то джобы периодически выполняют различные запросы и их результаты ( таблицы с разным числом столбцов и строк ) складывают , например, в clob ( XML ).

Существует ли возможность с этого clob-a сделать select и получить ту таблицу , которая хранится в ( XML ).

Думал динамически генерироварь селект, а потом выполнить в execute immediate, но как тогда вытянуть произвольное кол-во результатов ( столбцов ) .

Может будут какие идеи?

hover82

Думал динамически генерироварь селект, а потом выполнить в execute immediate, но как тогда вытянуть произвольное кол-во результатов ( столбцов ) .

А куда Вы этот результат дальше отправлять будете?

посмотрите в сторону
DBMS_XMLDOM.GETATTRIBUTES
dbms_xmldom.getLength
DBMS_XMLDOM.getnodename
DBMS_XMLDOM.getnodevalue

приблизительно как-то так

  for rec5 in 0..dbms_xmldom.getLength(l_nodeColumnInfo) - 1 loop

      l_nColumnDetails := dbms_xmldom.item(l_nodeColumnInfo, rec5);
      v_nodeName_TX := dbms_xmldom.getnodename(l_nColumnDetails);

      if dbms_xmldom.getnodetype(l_nColumnDetails) = dbms_xmldom.ELEMENT_NODE then
          v_nodeValue_TX := dbms_xmldom.getnodevalue(dbms_xmldom.item(dbms_xmldom.getchildnodes(l_nColumnDetails), 0));
      elsif dbms_xmldom.getnodetype(l_nColumnDetails) = dbms_xmldom.TEXT_NODE then
          v_nodeValue_TX := dbms_xmldom.getnodevalue(l_nColumnDetails);
      end if;
  end loop;

Я наверное неправильн осформулировал проблему.
Парсить XML я умею, я неумею строить select с заранее неизвестным количеством столбцов.

Ладно, http://www.sql.ru/forum/actualthread.aspx?tid=714751

hover82,

ещё раз - куда дальше отправлять собираетесь результаты запроса? Если в клиентский грид, то частенько гриды умеют и с XML работать.

hover82

select с заранее неизвестным количеством столбцов

DBMS_SQL.DESC_TAB
DBMS_SQL.DESCRIBE_COLUMNS

и так далее

Примерно такая же задача
вот мое решение
осталось научиться парсить XML и вставить в код

FUNCTION GENERATE_XML (
   tab   IN   VARCHAR2,
   whr   IN   VARCHAR2 := NULL,
   col   IN   VARCHAR2 := '*'
)
   RETURN CLOB AUTHID CURRENT_USER
IS
   TYPE cv_type IS REF CURSOR;

   CV            cv_type;
   column_name   VARCHAR2 (255);
   data_type     VARCHAR2 (255);
   val           CLOB;
   RESULT        CLOB;
   res XMLType;   
BEGIN
   OPEN CV FOR    'select COLUMN_NAME , DATA_TYPE from ALL_TAB_COLUMNS '
               || ' WHERE DATA_TYPE in (''NUMBER'',''VARCHAR2'',''DATE'') and upper(trim(TABLE_NAME))=upper(trim('''
               || tab
               || '''))';

   LOOP
      FETCH CV
       INTO column_name, data_type;

      EXIT WHEN CV%NOTFOUND;

      IF CV%ROWCOUNT <> 1
      THEN
         RESULT := RESULT || ';';
      END IF;

      EXECUTE IMMEDIATE    'SELECT to_char('
                        || COLUMN_NAME
                        || ')  FROM '
                        || tab
                        || ' WHERE rownum=1 and '
                        || NVL (whr, '1=1')
                        into val;

      RESULT := RESULT || COLUMN_NAME||':'|| val;
   END LOOP;

   CLOSE CV;

   RETURN RESULT;
END;

^{Креативу нет предела ;-)}

Вот окончательное решение с генерацией XML для однострочного запроса
возвращает XML произвольного запроса
вида

select generate_xml('dic','*','n=98') from dual

результат

<?xml version="1.0"?>
<dic><N value="98" datatype="NUMBER"/><UP value="0" datatype="NUMBER"/><TYP value="1" datatype="NUMBER"/><NAME value="Test data" datatype="VARCHAR2"/><FD value="29-Jan-2003" datatype="DATE"/><TD value="" datatype="DATE"/>
</dic>

CREATE OR REPLACE 
FUNCTION generate_xml (
   tab   IN   VARCHAR2,
   col   IN   VARCHAR2 := '*',
   whr   IN   VARCHAR2 := NULL
   
)
   RETURN XMLType AUTHID CURRENT_USER
IS
   TYPE cv_type IS REF CURSOR;

   CV            cv_type;
   column_name   VARCHAR2 (255);
   data_type     VARCHAR2 (255);
   val           CLOB;
   res XMLType; 

BEGIN
   
    res:=xmltype.CreateXML('<?xml version="1.0"?> <'||tab||'>  </'||tab||'>' );

   OPEN CV FOR    'select COLUMN_NAME , DATA_TYPE from ALL_TAB_COLUMNS '
               || ' WHERE DATA_TYPE in (''NUMBER'',''VARCHAR2'',''DATE'') and upper(trim(TABLE_NAME))=upper(trim('''
               || tab
               || '''))';

   LOOP
      FETCH CV
       INTO column_name, data_type;

      EXIT WHEN CV%NOTFOUND;

      IF CV%ROWCOUNT <> 1
      THEN
        null;-- RESULT := RESULT || ';';
      END IF;

      EXECUTE IMMEDIATE    'SELECT to_char('
                        || COLUMN_NAME
                        || ')  FROM '
                        || tab
                        || ' WHERE rownum=1 and '
                        || NVL (whr, '1=1')
                        into val;

      select insertchildxml( res, '/'||tab, COLUMN_NAME, xmltype( '<'||COLUMN_NAME||' value="'||val||'" datatype="'||data_type||'"/>') ) into res from dual ;

   END LOOP;

   CLOSE CV;

   RETURN res;
END;
/

^{Креативу нет предела ;-)}

подобную задачу решил недавно таким образом:
можно хранить в справочнике для каждого такого исходного запроса парный запрос для выборки результатов из XML. Запросы к XML можно сформировать либо ручками, либо автоматически на основе парсинга исходных запросов через DBMS_SQL (уже указали выше). В CLOB записывать результат и в другом поле заполнять код из справочника соответствий обычных запросов- XML-запросам.
Получение XML было однотипным - через xmltype(cursor(select...)).