generate xml file from oracle table

Question

when I ran below store procedure then it shows error. here some special char related issue ,so i cant generate xml file , here xml format same as generate XML from oracle tables

Connecting to the database DB_old.
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148
ORA-06512: at "SYS.UTL_FILE", line 889
ORA-06512: at "DBO_17FEB.EXPOR1", line 60
ORA-06512: at line 6
Process exited.
Disconnecting from the database DB_old.



-------------------



create or replace 
PROCEDURE Expor1 
(
   V_TABLE_NAME IN varchar2
  )
AS
BEGIN
         ----- Export  table data
      DECLARE
        v_file  UTL_FILE.file_type;
        qryCtx DBMS_XMLGEN.ctxHandle;
        result CLOB;
        result1 CLOB;
        v_FILENAME varchar2(30);
      BEGIN

       IF UPPER(V_TABLE_NAME) = 'PROJECT' THEN
      qryCtx :=  dbms_xmlgen.newContext ('SELECT * from '||V_TABLE_NAME ||'' );

       ELSIF UPPER(V_TABLE_NAME) = 'LOGFILE' THEN

      qryCtx :=  dbms_xmlgen.newContext ('select LOG_ID, USER_ID,RUN_DATE,PROCESS,MPOID,MODE_,trim(STATUS) as STATUS,
                                                 trim(regexp_replace(unistr(NOTES), ''[[:punct:]] '','''')) as NOTES, 
                                                 MARKDELETED from logfile where rownum<100 ' );

       ELSE
       qryCtx :=  dbms_xmlgen.newContext ('SELECT * from '||V_TABLE_NAME ||'' );
      END IF;


      v_FILENAME :=V_TABLE_NAME;
      DBMS_XMLGEN.setMaxRows(qryCtx, 5);
         v_file := UTL_FILE.fopen('MYXML', v_FILENAME || '.xml', 'W');
       UTL_FILE.put_line(v_file, '<XML><'||v_FILENAME||'></'||v_FILENAME||'> <RECORDS>');
       -- v_file := UTL_FILE.FOPEN('MYXML', v_FILENAME|| '.xml', 'R');

       LOOP
         DBMS_XMLGEN.SETNULLHANDLING(qryCtx ,null);
         DBMS_XMLGEN.setRowSetTag(qryCtx, 0);
         DBMS_XMLGEN.setRowTag(qryCtx, 'RECORD');

      -- save the XML into the CLOB field
       result :=  DBMS_XMLGEN.getXML(qryCtx);
       --result := REPLACE( result, '<?xml version="1.0"?>','<XML><'||v_FILENAME||'>'||result1 ||'</'||v_FILENAME||'>' );
         result := REPLACE( result, '<?xml version="1.0"?>',' ');
         result := REPLACE( result, '<_x0030_>',' ');
         result := REPLACE( result, '</_x0030_>',' '); 
         --result :=trim(result);
      -- UTL_FILE.put_line(v_file, '');
       EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0; 
          -- store the XML to a XML files
       UTL_FILE.put_line(v_file, result);
      --UTL_FILE.put_line(v_file, '</XML>');
       END LOOP; 
      UTL_FILE.put_line(v_file, '</RECORDS></XML>');
       UTL_FILE.FCLOSE(v_file);

       END; 


END Expor1;

i am not able to handle some special char like : & / ; :/ . etc please help

The error you've shown doesn't correspond with the problem you say you're having; I assume you're actually still getting ORA-31061, when you call this for `LOGFILE`? — Alex Poole, Feb 26 '14 at 13:25

Alex Poole · Accepted Answer · 2014-02-26T14:51:02.563

0

The ORA-29285: file write error isn't the same character-conversion error you referred to before, which was ORA-31061: XDB error: special char to escaped char conversion failed. It really isn't clear which error you're getting; if it's the file one then the character conversion isn't relevant.

When I run your version of my code against my table, it works OK for smaller tables, and gets ORA-06502: PL/SQL: numeric or value error if result is more than 32k characters; or ORA-29285 if there is more than 32k without a line break. You've lost the looping over the clob to write it to the file in chunks. To output larger values, as I did before:

position pls_integer := 1;
chars pls_integer := 32767;
...
while position < dbms_lob.getlength(result) loop
  utl_file.put(v_file, dbms_lob.substr(result, chars, position));
  utl_file.fflush(v_file);
  position := position + chars;
end loop;

Not sure why you have an inner block (declare/begin/`end') within the procedure.

If you are getting the ORA-31061 then I'm still unclear which data is causing you a problem, but assuming that the NOTES transformation you're doing solves that and you're still seeing ORA-31061 when you call this for your LOGFILE table, then that's probably because you reset qryCtx.

You're creating that at line 23 in the code you provided as:

      qryCtx :=  dbms_xmlgen.newContext ('select LOG_ID, 
        USER_ID,RUN_DATE,PROCESS,MPOID,MODE_,STATUS,
        regexp_replace(unistr(NOTES), ''[[:punct:]]'','''') as NOTES,
        MARKDELETED from logfile' );

... but then after the if/elsif/else block where you do that, you then overwrite it at line 39 with:

      qryCtx :=  dbms_xmlgen.newContext ('SELECT * from '||V_TABLE_NAME ||'' );

So when you then call getXML(qryCtx) you aren't getting the modified values for NOTES.

edited Feb 26 '14 at 14:51

answered Feb 26 '14 at 13:24

Alex Poole

183,384
11
179
318

please comment this line qryCtx := dbms_xmlgen.newContext ('SELECT * from '||V_TABLE_NAME ||'' ); and i found data like "YNC 0094: DB table Project does not have a record for that & =1" so I replace it to this " 94 CTIPS table Project does not have a record for that docyear chgoffcl1" by using function "regexp_replace(unistr()) " – hardik rawal Feb 26 '14 at 13:42
@hardikrawal - the special characters in that string would be converted to `<` etc. in the file anyway. I don't see an error with that data, either leaving it alone or with your `regexp_replace()`. Which error are you actually getting now? If you are getting `ORA-29285` then it is nothing to do with character conversion, it's the amount of data you're trying to write out at once. – Alex Poole Feb 26 '14 at 14:47
yes, how to resolve it for same store procedure currently i am facing bellow error Connecting to the database CTIPS_old. ORA-29285: file write error ORA-06512: at "SYS.UTL_FILE", line 148 ORA-06512: at "SYS.UTL_FILE", line 889 ORA-06512: at "DBO_CTIPS17FEB.EXPOR1", line 51 ORA-06512: at line 6 Process exited. Disconnecting from the database CTIPS_old. – hardik rawal Feb 26 '14 at 17:48
@hardikrawal - I've already said in this answer that you need to write the result out to the file in chunks, and I've said how to do that in a loop here, and in the previous answer. The ORA-29285 is from trying to write more then 32k in one `utl_file.put`. Do it in a loop, with a `fflush`. – Alex Poole Feb 26 '14 at 17:59
hi, thanks for your reply, but still i am not able to solve this issue, i has made one new store procedure for this please check it, how i will solve it. http://stackoverflow.com/questions/22060483/generate-xml-file-from-oracle-table-from-store-procedure – hardik rawal Feb 27 '14 at 06:15
thanks for help, i solved all special char issue but i face error ORA-29285: file write error ORA-06512: at "SYS.UTL_FILE", line 148, due to trying to write more then 32k in one utl_file.put so how i can solve this issue from fflush, if u have any example then please share. – hardik rawal Feb 28 '14 at 05:14
i am also check by useing above example but still shows same error Connecting to the database DB19FEB2014. ORA-29285: file write error ORA-06512: at "SYS.UTL_FILE", line 148 ORA-06512: at "SYS.UTL_FILE", line 403 ORA-06512: at "SYS.UTL_FILE", line 1166 ORA-06512: at "DBO_CTIPS_19FEB2014.EXPOR1", line 43 ORA-06512: at line 6 Process exited. Disconnecting from the database DB19FEB2014. – hardik rawal Feb 28 '14 at 06:24
@hardikrawal - you can't write more than 32k in one put; I've shown you twice how to loop and write in chunks, with flushing. Or are you doing that but you have a section of 32k of text with no line breaks in it? – Alex Poole Feb 28 '14 at 07:41
here data have line breaks thats why loop and write in chunks, with flushing not working ?. if yes the how to fix it ? – hardik rawal Feb 28 '14 at 08:22
@hardikrawal - I can't guess what's wrong with code I can't see. Maybe add your current code to the question. – Alex Poole Feb 28 '14 at 08:25
when i use qryCtx := dbms_xmlgen.newContext ('SELECT RUN_DATE,substr(LOG,1,1000) as LOG from '||V_TABLE_NAME ||' '); in my store procedure then its work but i need full data. – hardik rawal Feb 28 '14 at 09:34
final I has remove all special char from my database, please check my code LOOP DBMS_XMLGEN.SETNULLHANDLING(qryCtx ,null); DBMS_XMLGEN.setRowSetTag(qryCtx, 0); DBMS_XMLGEN.setRowTag(qryCtx, 'RECORD') result := DBMS_XMLGEN.getXML(qryCtx); result := REPLACE( result, '',' '); EXIT WHEN DBMS_XMLGEN.getNumRowsProcessed(qryCtx) = 0; UTL_FILE.put_line(v_file, result); END LOOP; I found error ORA-06502: PL/SQL: numeric or value error ORA-06512 – hardik rawal Feb 28 '14 at 12:58
@hardikrawal - that code is still doing a single `UTL_FILE.put_line(v_file, result);`; it has to loop and put out chunks as I showed in the answer - look at the first code block. You still seem to be confusing the error you get from `put` with >32k and the special character issue. They are not related. (Also, please don't post code in comments, it's very hard to read). – Alex Poole Feb 28 '14 at 13:12
some time i have got erro ora-30625 method dispatch on null self argument is disallowed xml ok, I am trying to solve this error from your code – hardik rawal Mar 01 '14 at 19:00

generate xml file from oracle table

1 Answers1

Linked