PLSQL : How to save a dynamic SQL query result into a two dimension array?

Question

CREATE TYPE VarArray AS VARRAY(50) OF VARCHAR2(50);

CREATE TYPE VarAArray AS VARRAY(50) OF VarArray;

Declare
   myTable VarAArray VarAArray();
   tableName Varchar2(50);
Begin
  -- I want to save the data in the table of 'tableName' 
  -- into the myTable array, all the field values are 
  -- saved as varchar2 type
End;

The key requirement of doing this is to get any of the filed value by row number and column id. Please feel free to offer better design options.

I want to store all of the Table values into a well formatted XML file <TableName> <row> <FieldName> FieldValue </FieldName> ... </row> <row>..</row> ...</TableName> — Frank
– Frank, Commented Oct 17, 2012 at 18:25
There are tons of XML functions already available in Oracle, why not use them instead? If XML functions are not an option then EXECUTE IMMEDIATE 'SELECT * BULK COLLECT INTO myTable FROM table_name'; should do the work of populating the collection. — Anjan Biswas
– Anjan Biswas, Commented Oct 17, 2012 at 18:58

Nick Krasnov · Accepted Answer · 2012-11-12 20:25:54Z

If you want get an XML representation of data in your table then take a look at dbms_xmlgen package. For example, you can write a simple function like this one:

SQL> create or replace function GenXML(p_query in varchar2, p_RSetTag in varchar2)
  2  return clob
  3  is
  4    l_xmlcntx dbms_xmlgen.ctxHandle;
  5    l_resxml clob;
  6  begin
  7    l_xmlcntx := dbms_xmlgen.newContext(p_query);
  8    dbms_xmlgen.setRowSetTag(l_xmlcntx,  p_RSetTag);
  9    l_resxml := dbms_xmlgen.getXML(l_xmlcntx);
 10    dbms_xmlgen.closeContext(l_xmlcntx);
 11    return l_resxml;
 12  end;
 13  /

Function created

And then use it as follows by passing your query to a table and a rowset tag as parameters.

SQL> select genxml('select * from employees where rownum = 1','EMPLOYEES') as XmlData
  2    from dual
  3  ;

XMLDATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<EMPLOYEES>
 <ROW>
  <EMPLOYEE_ID>100</EMPLOYEE_ID>
  <FIRST_NAME>100</FIRST_NAME>
  <LAST_NAME>King</LAST_NAME>
  <EMAIL>SKING</EMAIL>
  <PHONE_NUMBER>515.123.4567</PHONE_NUMBER>
  <HIRE_DATE>17-JUN-03</HIRE_DATE>
  <JOB_ID>AD_PRES</JOB_ID>
  <SALARY>24000</SALARY>
  <DEPARTMENT_ID>90</DEPARTMENT_ID>
 </ROW>
</EMPLOYEES>

SQL>

Response to the comment

To display a master-detail type of datastructure you can use cursor. For example:

SQL> select genxml('select department_id
  2       , department_name
  3       , cursor(
  4                 select first_name
  5                   from employees t
  6                  where t.department_id = d.department_id
  7           ) employees
  8    from departments d
  9    where rownum = 1','DEPARTMENTS') xmldata
 10    from dual
 11  ;

XMLDATA
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<DEPARTMENTS>
 <ROW>
  <DEPARTMENT_ID>10</DEPARTMENT_ID>
  <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
  <EMPLOYEES>
   <EMPLOYEES_ROW>
    <FIRST_NAME>Jennifer</FIRST_NAME>
   </EMPLOYEES_ROW>
  </EMPLOYEES>
 </ROW>
</DEPARTMENTS>

UPDATE #2 In response to the comment:

To eliminate remaining opening and closing tags in the case when "detail"(the cursor) is null, lets rewrite our query as follows, for example:

SELECT XMLElement("DEPARTMENTS"
                 , XMLAgg( XMLElement( "ROW"
                                     , XMLForest( t.department_id
                                                , t.department_name
                                                )
                                     , (
                                         SELECT XMLAgg(XMLElement("EMPLOYEES"
                                                                 , XMLForest (q.first_name)
                                                                  )
                                                        )
                                           FROM employees q
                                          WHERE q.department_id = t.department_id
                                            --and 100=101
                                        )
                                     )
                          )
                 )

In order to have more control over tags. And store result of the above query in a file as follows: Before running the below code a directory have to be created: Create directory <name> as <path>. Put the name if your directory instead of XMLDIR.

declare
  l_xml clob;

begin
  SELECT XMLElement("DEPARTMENTS"
                 , XMLAgg( XMLElement( "ROW"
                                     , XMLForest( t.department_id
                                                , t.department_name
                                                )
                                     , (
                                         SELECT XMLAgg(XMLElement("EMPLOYEES"
                                                                 , XMLForest (q.first_name)
                                                                  )
                                                        )
                                           FROM employees q
                                          WHERE q.department_id = t.department_id
                                            --and 100=101
                                        )
                                     )
                          )
                 ).getclobval() into l_xml
  FROM departments t
  where rownum < 3;

  dbms_xslprocessor.clob2file(l_xml, 'XMLDIR', 'XmlFile.xml');

end;

Result#1: When sub-query returns data

 <DEPARTMENTS>
  <ROW>
    <DEPARTMENT_ID>10</DEPARTMENT_ID> 
    <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME> 
    <EMPLOYEES>
      <FIRST_NAME>Jennifer</FIRST_NAME> 
    </EMPLOYEES>
  </ROW>
  <ROW>
    <DEPARTMENT_ID>20</DEPARTMENT_ID> 
    <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME> 
    <EMPLOYEES>
      <FIRST_NAME>Michael</FIRST_NAME> 
    </EMPLOYEES>
    <EMPLOYEES>
      <FIRST_NAME>Pat</FIRST_NAME> 
    </EMPLOYEES>
  </ROW>
 </DEPARTMENTS>

Result#2: When sub-query returns no data

<DEPARTMENTS>
  <ROW>
    <DEPARTMENT_ID>10</DEPARTMENT_ID> 
    <DEPARTMENT_NAME>Administration</DEPARTMENT_NAME> 
  </ROW>
  <ROW>
    <DEPARTMENT_ID>20</DEPARTMENT_ID> 
    <DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME> 
  </ROW>
  </DEPARTMENTS>

What if I want to append another table called employeeDeatils referenced by the Employee_id with the same structure after <DEPARTMENT_ID>90</DEPARTMENT_ID> ?
One more question, if the cursor part returns null, there will still be a tag like <EMPLOYEES/> , do you know how to avoid this ? thanks ahead @Nicholas
@Frank There should be opening and closing tags <EMPLOYEES><EMPLOYEES/>
So if I don't want <EMPLOYEES><EMPLOYEES/> to be output, is it possible?Thank you @Nicholas

Collectives™ on Stack Overflow

PLSQL : How to save a dynamic SQL query result into a two dimension array?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related