Home » Developer & Programmer » JDeveloper, Java & XML » Writing XML to Oracle directory (Oracle 11g)
Writing XML to Oracle directory [message #583004] Wed, 24 April 2013 12:38 Go to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Hi,

I have the following tables & data ..
 
create table identity (IDENTITY_ID varchar2(100));

Insert into identity values ('100');
Insert into identity values ('101');



create table CM_ENCOUNTER (IDENTITY_ID varchar2(100), CSN varchar2(100));

Insert into CM_ENCOUNTER values ('100','3a1');
Insert into CM_ENCOUNTER values ('101','1a2');

create table CM_PATIENT (IDENTITY_ID varchar2(100), GENDER varchar2(100), race varchar2(100));

Insert into CM_PATIENT values ('100','F','AA');
Insert into CM_PATIENT values ('101','M','HA');
Insert into CM_PATIENT values ('101','F','EA');



create table CM_ENCOUNTER_RFV
 (IDENTITY_ID varchar2(100), CSN_ID varchar2(100), LINE varchar2(100), ENC_REASON_NAME  varchar2(100));
 

Insert into  CM_ENCOUNTER_RFV values ('100','23vx','2', 'phone');

commmit;



The output needed is :


 <EvaluatePatient>
    <PatientInformation>
      <Patient>

          <mrn>100</mrn>
          <CSN>3a1</CSN>
          <Gender>F</Gender>
          <race>AA</race>
          <ID>23vx2</ID>
          <Name>phone</Name>
       </Patient>
     <Orders>   <!-- XML from orders here, if applicable --> </Orders>
     <Immunizations>    <!-- XML from orders here, if applicable --> </Immunizations>
     <ProblemList></ProblemList>
   </PatientInformation>

   <PatientInformation>
     <Patient> 
       <mrn>101</mrn>
       <CSN>1a2</CSN>
       <Gender>M</Gender>
       <race>HA</race>
       <ID></ID>
       <Name></Name>
      </Patient>
     <Orders>   <!-- XML from orders here, if applicable --> </Orders>
     <Immunizations>    <!-- XML from orders here, if applicable --> </Immunizations>
     <ProblemList></ProblemList>
   </PatientInformation>
</EvaluatePatient>




Based on the above, I created a table (which is of xml) ...

 Create table  temp_xml
as
  Select                     XMLELEMENT("PatientInformation",(XMLELEMENT("Patient", 
                              XMLELEMENT("mrn", s.identity_id),
                              XMLELEMENT("CSN", m.csn),
                              XMLELEMENT("Gender", p.gender),
                              XMLELEMENT("race", p.race),
                              XMLELEMENT("ID", r.csn_id || r.line),
                              XMLELEMENT("Name", r.enc_reason_name))),
                              XMLELEMENT ("Orders", NULL),
                              XMLELEMENT ("Immunizations", NULL),
                              XMLELEMENT ("ProblemList", NULL)) cola
        

  from identity s
  join CM_ENCOUNTER m
  on m.identity_id = s.identity_id
  join CM_PATIENT   p
    on m.identity_id = p.identity_id
  left outer join CM_ENCOUNTER_RFV   r
    on m.identity_id = r.identity_id;



Now, I need to write each record into xml file..
So, I used the below to write into file..but it gives me an error...wrong number of arguments to utl_file at line 14.

DECLARE
  CURSOR c_data IS
SELECT * from temp_xml;

  v_file  UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN(location     => 'PLSQL_DIR', -- DUMP_DIR
                           filename     => 'sample_xml.csv',
                           open_mode    => 'w',
                           max_linesize => 32767);
  utl_file.PUT_LINE( v_file, '<?xml version="1.0" encoding="utf-8" ?>' );
  utl_file.PUT_LINE( v_file, '<EvaluatePatient>' );
  FOR cur_rec IN c_data LOOP
    UTL_FILE.PUT_LINE(v_file, cur_rec.cola );  -- error here
  END LOOP;
  utl_file.PUT_LINE( v_file, '</EvaluatePatient>' );
  UTL_FILE.FCLOSE(v_file);
END;  
  



Any help is appreciated.

Thanks,
Raj
Re: Writing XML to Oracle directory [message #583005 is a reply to message #583004] Wed, 24 April 2013 13:13 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member
Hi rajivn786 ,

Quote:
UTL_FILE.PUT_LINE(v_file, cur_rec.cola ); -- error here


Better you do first conversion in second parameter of UTL_FILE.PUT_LINE, because XMLELEMENT you used in SQL statement
will return only XMLType. and UTL_FILE.PUT_LINE accept scalar data types.
Re: Writing XML to Oracle directory [message #583006 is a reply to message #583005] Wed, 24 April 2013 13:53 Go to previous messageGo to next message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
I have the following code..but right now, i have very few rows..SO, this works fine..
Just thinking about the future, where there will be millions of rows..possibly this code might lead to memory issues especially XMLAGG part...

Do you have any other solution apart from this?
or do we encounter any memory issues at all??
DECLARE

   -- Data Variables
   v_xml              XMLTYPE;
   v_blob             BLOB;
   v_data_length      NUMBER; 

   -- Loop Control Variables
   v_offset           NUMBER             DEFAULT 1;
   v_chunk   CONSTANT NUMBER             DEFAULT 4000;

   -- UTL_FILE variables
   fh                 UTL_FILE.file_type;

BEGIN
   -- A big query, about 27k on my PC
   SELECT XMLELEMENT ("EvaluatePatient", XMLAGG (cola))
     INTO v_xml
     FROM temp_xml;

   -- Turn the XML into a BLOB to bypass any 4k and 32k issues
   v_blob := v_xml.getblobval (1);
   v_data_length := DBMS_LOB.getlength (v_blob);
   
   -- Open the file
   fh := UTL_FILE.fopen ('PLSQL_DIR', 'myxml.xml', 'wb', v_chunk);

   -- Da loop de loop
   LOOP
      -- Exit when our file offset is bigger than our file
      EXIT WHEN v_offset > v_data_length;

      -- Write the output chunk by chunk
      UTL_FILE.put_raw (fh, DBMS_LOB.SUBSTR (v_blob, v_chunk, v_offset),
                        TRUE);

      -- Increment the offset by the amount written
      v_offset := v_offset + v_chunk;
   END LOOP;

   -- Close the file and say byebye
   UTL_FILE.fclose (fh);

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      -- We won't write any data, or even open the file,
      -- if the query return no rows
      NULL;
END;



Thanks,
Raj
Re: Writing XML to Oracle directory [message #583056 is a reply to message #583006] Thu, 25 April 2013 03:59 Go to previous messageGo to next message
ashwani0301
Messages: 44
Registered: March 2013
Location: Karnatka, Bangalore
Member

Quote:
Just thinking about the future where there will be millions of rows..possibly this code might lead to memory issues especially XMLAGG part...


In future when your date will grow you also have to increase the memory.
well I don't think there is any other alternative solution of XMLAGG which consume less memory.

Let's see in future what we have better than XMLAGG
Re: Writing XML to Oracle directory [message #583321 is a reply to message #583056] Mon, 29 April 2013 09:56 Go to previous message
rajivn786
Messages: 136
Registered: January 2010
Senior Member
Thanks...I wish!!!
Previous Topic: Using java.lang.Math in Oracle
Next Topic: Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor
Goto Forum:
  


Current Time: Fri Jul 11 14:21:17 CDT 2014

Total time taken to generate the page: 0.92543 seconds