Ошибка PL / SQL при сохранении вложенного XML в таблице

Question

Ошибка PL / SQL при сохранении вложенного XML в таблице

Я пытаюсь сохранить XML в таблице. XML имеет следующий формат:

<employees>

  <employees_id> 1</employees_id>

  <employees_name>Teradata</employees_name>

     <department_id>100</department_id>

     <department_name>Techinical</department_name>

     <department_id>200</department_id>

     <department_name>Management</department_name>

     <department_id>300</department_id>

     <department_name>Telecom</department_name>

  </employees>

  <employees>

  <employees_id> 2</employees_id>

  <employees_name>AT&T</employees_name>

     <department_id>400</department_id>

     <department_name>Techinical</department_name>

     <department_id>500</department_id>

     <department_name>Management</department_name>

     <department_id>600</department_id>

     <department_name>Telecom</department_name>

  </employees>

В моей таблице есть только столбец id и столбец xmltype.

Процедура, которую я использую, выглядит следующим образом:

   DECLARE

   CTX         DBMS_XMLGEN.CTXHANDLE;

   LCLOB$XML_AS_CLOB  CLOB;

   LREC$EMPLOYEES_EMPNO NUMBER;

 BEGIN

  FOR LREC$EMPLOYEES_EMPNO IN (198, 197, 180)

   LOOP

    --Create context for XML

    CTX :=

      DBMS_XMLGEN.NEWCONTEXT (

       'SELECT employees.EMPLOYEE_ID, employees.FIRST_NAME, 

        departments.DEPARTMENT_ID, departments.DEPARTMENT_NAME

        FROM EMPLOYEES, departments

        WHERE employees.department_id = departments.department_id and 

        employees.employees_id =' || LREC$EMPLOYEES_EMPNO);

    --Get XML file of Context

     -- Set the row header to be EMPLOYEE

    DBMS_XMLGEN.setRowTag(CTX, 'EMPLOYEE');

    LCLOB$XML_AS_CLOB := DBMS_XMLGEN.GETXML (CTX);

    DBMS_XMLGEN.CLOSECONTEXT (CTX); 

    INSERT INTO HR.OFFLINE_XML (id, XML_FILE)

       VALUES ( LREC$EMPLOYEES_EMPNO, XMLTYPE (LCLOB$XML_AS_CLOB));

    END LOOP;

   COMMIT;

 END;

Сообщение об ошибке:

 ORA-06550: line 7, column 4:

PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:



   * & - + / at mod remainder rem .. <an exponent (**)> ||

   multiset year day

ORA-06550: line 18, column 16:

PLS-00103: Encountered the symbol "." when expecting one of the following:



   constant exception <an identifier>

   <a double-quoted delimited-identifier> table long double ref

   char time timestamp interval date binary national charac

ORA-06550: line 19, column 23:

PLS-00103: Encountered the symbol "=" when expecting one of the following:



   constant exception <an identifier>

   <a double-quoted delimited-identifier> table long double ref

   char time timestamp interval date binary national charac

ORA-06550: line 20, column 16:

PLS-00103: Encountered the symbol "." when expecting one of the following:



   constant exception <an identifier>

   <a double-quoted delimited-identifier> table long double ref

   char time timestamp interval date binary national charac

ORA-06550: line 21, column 5:

PLS-00103: Encountered the symbol "INSERT" when expecting one of the following:



   begin function pragma procedure subtype type <an identifier>

   <a double-quoted delimited-identifier> current cursor delete

   exists prior

The symbol "b

ORA-06550: line 24, column 4:

PLS-00103: Encountered the symbol "COMMIT" when expecting one of the following:



   end not pragma final instantiable order overriding static

   member constructor map

06550. 00000 -  "line %s, column %s:n%s"

*Cause:    Usually a PL/SQL compilation error.

*Action:

729 3

sql xml oracle plsql oracle11g

3 ответов:

Comments

Ничего не найдено.

Alex Poole · Accepted Answer · 2013-04-06 19:40:42

Как сказал Ноэль, вы не можете поставить фиксированные значения в цикл for, как это. (Все остальные ошибки в вашем отчете являются нокдаунами от этого, хотя есть и другие проблемы). Вы можете предоставить диапазон, как for i in 1..10 loop, но это не поможет вам здесь, даже для ваших выборочных данных. Поскольку это только для тестирования, вы можете использовать фиктивный курсор:
    FOR LREC$EMPLOYEES_EMPNO IN (
        SELECT 198 AS employee_id FROM dual
        UNION ALL SELECT 197 FROM dual 
        UNION ALL SELECT 180 FROM dual
    ) LOOP
(хотя на самом деле я предпочитаю массив DazzaL, но пока я буду придерживаться этого). Вам не нужно объявлять курсор и переменную number, которую вы объявили с этим именем будет заменена эта версия. И когда вы ссылаетесь на одно из значений, оно возвращает его по имени, поэтому в этом случае LREC$EMPLOYEES_EMPNO.employee_id. Persoanlly я бы выбрал более короткое имя *8 -)

Как DazzaL также упоминал, вам также не нужен промежуточный CLOB. Кроме того, кажется ненужным создавать и закрывать контекст каждый раз вокруг цикла, когда вы можете использовать переменную bind вместо этого.

Это работает и получает тот же результат, что и ваша версия (подходит с поправками), но не вывод вы предположили, что хотите в вопросе:
DECLARE
    ctx DBMS_XMLGEN.CTXHANDLE;
BEGIN
    ctx := DBMS_XMLGEN.NEWCONTEXT(
       'SELECT employees.EMPLOYEE_ID, employees.FIRST_NAME, 
        departments.DEPARTMENT_ID, departments.DEPARTMENT_NAME
        FROM EMPLOYEES, departments
        WHERE employees.department_id = departments.department_id and      
        employees.employee_id = :id');
    DBMS_XMLGEN.setRowTag(ctx, 'EMPLOYEE');
    FOR r IN (
        SELECT 198 AS employee_id FROM dual
        UNION ALL SELECT 197 FROM dual 
        UNION ALL SELECT 180 FROM dual
    ) LOOP
        DBMS_XMLGEN.SETBINDVALUE(ctx, 'ID', r.employee_id);
        INSERT INTO OFFLINE_XML (id, xml_file)
        VALUES (r.employee_id, DBMS_XMLGEN.GETXMLTYPE(ctx));
    END LOOP;
    DBMS_XMLGEN.CLOSECONTEXT(ctx); 
END;
/
Который заполняет таблицу:
        ID XML_FILE
---------- --------------------------------------------------
       198 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>198</EMPLOYEE_ID>
               <FIRST_NAME>Bob</FIRST_NAME>
               <DEPARTMENT_ID>1</DEPARTMENT_ID>
               <DEPARTMENT_NAME>First</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>

       197 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>197</EMPLOYEE_ID>
               <FIRST_NAME>Adam</FIRST_NAME>
               <DEPARTMENT_ID>1</DEPARTMENT_ID>
               <DEPARTMENT_NAME>First</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>

       180 <ROWSET>
             <EMPLOYEE>
               <EMPLOYEE_ID>180</EMPLOYEE_ID>
               <FIRST_NAME>Charlie</FIRST_NAME>
               <DEPARTMENT_ID>2</DEPARTMENT_ID>
               <DEPARTMENT_NAME>Second</DEPARTMENT_NAME>
             </EMPLOYEE>
           </ROWSET>

DazzaL · Accepted Answer · 2013-04-06 19:07:42

Объявите массив для итерации:
DECLARE
   CTX         DBMS_XMLGEN.CTXHANDLE;
   LCLOB$XML_AS_CLOB  CLOB;
   type number_tab is table of number;
   t_num number_tab := number_tab(198,197,180);
 BEGIN
  FOR idx IN t_num.first..t_num.last
   LOOP
    --Create context for XML
    CTX :=
      DBMS_XMLGEN.NEWCONTEXT (
       'SELECT employees.EMPLOYEE_ID, employees.FIRST_NAME, 
        departments.DEPARTMENT_ID, departments.DEPARTMENT_NAME
        FROM EMPLOYEES, departments
        WHERE employees.department_id = departments.department_id and 
        employees.employees_id =' || t_num(idx));
    --Get XML file of Context
     -- Set the row header to be EMPLOYEE
    DBMS_XMLGEN.setRowTag(CTX, 'EMPLOYEE');
    LCLOB$XML_AS_CLOB := DBMS_XMLGEN.GETXML (CTX);
    DBMS_XMLGEN.CLOSECONTEXT (CTX); 
    INSERT INTO HR.OFFLINE_XML (id, XML_FILE)
       VALUES ( t_num(idx), XMLTYPE (LCLOB$XML_AS_CLOB));
    END LOOP;
   COMMIT;
 END;
 /
Еще пара моментов. во-первых, с dbmx_xmlgen вы должны использовать привязки, а не сплайсинг в литерале Ther здесь:
employees.employees_id =' || LREC$EMPLOYEES_EMPNO);
Также вы можете использовать getxmltype вместо getxml в конце, чтобы сохранить получение clob и затем необходимость XMLTYPE() во вставке.

Noel · Accepted Answer · 2013-04-06 17:02:20

Я думаю, что вы не можете написать цикл FOR таким образом. Вы должны написать курсор, чтобы выбрать номера сотрудников,а затем использовать курсор в цикле FOR.
declare
    cursor cur_emp is select employee_id from employees;
begin
    for i in cur_emp
    loop
    /*your code here. Use i.employee_id to get the employee_id*/
    end loop;
end;