Ask TEM Dba

create or replace procedure
getXML(newContext_qry varchar2,
       rowSettag varchar2,
       rowTag varchar2,filename varchar2)
 is
-- Input query string

-- Input rowsetTag , the root tag
-- Input row level tag
-- Input file name
-- Note make sure that before running this procedure,
-- it is required that UTL_FILE_DIR must
-- be set in init.ora file In this code
-- it has been set to d:\oracle9i. 

begin
declare
 qryCtx dbms_xmlgen.ctxHandle ;
 result clob;
 lob_length     integer;
 read_amount    integer;
 read_offset    integer;

 buffer         varchar2(100);
 loc            varchar2(100) := 'usr_dir';
 f_hand         utl_file.file_type;

Begin
        -- Setting up offset and no. of chars to be read in
        -- in one go from clob datatype.
        read_offset := 1;

        read_amount := 75;

        dbms_output.put_line('opening');
        --Opening file
        f_hand := Utl_File.Fopen(location =>'d:\oracle9i',
                                 filename =>filename,
                                 open_mode =>'w',
                                 max_linesize => 32767);
        dbms_output.put_line('file open');

        -- Creating new context
        qryCtx := dbms_xmlgen.newContext(newContext_qry);

        -- Defining Rowsettag
        DBMS_XMLGEN.setRowSetTag(qryCtx,rowSettag);

        -- Defining Rowtag
        DBMS_XMLGEN.setRowTag(qryCtx,rowTag);

        -- Generating XML and storing in an clob datatype
        result :=DBMS_XMLGEN.getXML(qryCtx);

        dbms_output.put_line('xml generated');

        -- Getting the length of the data stored in Clob
        lob_length := dbms_lob.getlength(result);

        -- Reading data from clob variable and writng into file.
        while (lob_length > 0) loop
        dbms_lob.read(result,read_amount,read_offset,buffer);

        dbms_output.put_line('writing in file');
        utl_file.put(f_hand,buffer);
        dbms_output.put_line('written');
        read_offset := read_offset+read_amount;
        lob_length := lob_length-read_amount;
        if lob_length < read_amount then
           read_amount := lob_length;
        end if;
        end loop;
        utl_file.fclose(f_hand);

        EXCEPTION WHEN UTL_FILE.INVALID_PATH THEN
        RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

        WHEN UTL_FILE.INVALID_MODE THEN
        RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

        WHEN UTL_FILE.INVALID_OPERATION then
        RAISE_APPLICATION_ERROR(-20102,'Invalid Operation');

        WHEN UTL_FILE.INVALID_FILEHANDLE then
        RAISE_APPLICATION_ERROR(-20103,'Invalid Filehandle');

        WHEN UTL_FILE.WRITE_ERROR then
        RAISE_APPLICATION_ERROR(-20104,'Write Error');

        WHEN UTL_FILE.READ_ERROR then
        RAISE_APPLICATION_ERROR(-20105,'Read Error');

        WHEN UTL_FILE.INTERNAL_ERROR then
        RAISE_APPLICATION_ERROR(-20106,'Internal Error');

        WHEN OTHERS THEN
        UTL_FILE.FCLOSE(f_hand);

end;

end;
/