Jul 03 2011

XML file export with stored procedure in Oracle

Category: ProcedureFatih Acar @ 19:50

This example provides export xml file. You must create a directory before create procedure. After, you must give grant for user. Finally, You can create procedure and execute.

Create Directory and Grant

You must be sys user and can execute this queries;

CREATE DIRECTORY XMLDUMP AS ‘C:\XmlDirectoryFileName’;
SELECT * FROM DBA_DIRECTORIES;
GRANT READ,WRITE ON DIRECTORY XMLDUMP TO HR;

Create Procedure

CREATE OR REPLACE PROCEDURE PR_XMLEXPORT
IS
CURSOR CRS IS
SELECT first_name,last_name,salary FROM employees;

REC CRS%ROWTYPE;
XML_CONTENT VARCHAR2(10000) := ”;
FILENAME varchar2(100) := ’employees_salary.xml’;
FILEWRITE UTL_FILE.FILE_TYPE;

BEGIN

OPEN CRS;

XML_CONTENT := XML_CONTENT || ‘< ?xml version="1.0" encoding="ISO-8859-9" ? >‘;

LOOP
FETCH CRS INTO REC;
EXIT WHEN CRS%NOTFOUND;

XML_CONTENT := XML_CONTENT || ‘< employee>‘;
XML_CONTENT := XML_CONTENT || ‘< first_name>‘||TO_CHAR(REC.FIRST_NAME)||’< /first_name>‘;
XML_CONTENT := XML_CONTENT || ‘< last_name>‘||TO_CHAR(REC.LAST_NAME)||’< /last_name>‘;
XML_CONTENT := XML_CONTENT || ‘< salary>‘||TO_CHAR(REC.SALARY)||’< /salary>‘;
XML_CONTENT := XML_CONTENT || ‘< /employee>‘;

END LOOP;

XML_CONTENT := XML_CONTENT || ‘< /employees >‘;

FILEWRITE := UTL_FILE.FOPEN(‘XMLDUMP’,’sehirleregorenes.xml’,’w’,32767);
UTL_FILE.PUTF(FILEWRITE,XML_CONTENT);
UTL_FILE.FCLOSE(FILEWRITE);

COMMIT;

CLOSE CRS;

END;

28,951 total views, 25 views today

Tags: Oracle, SQL, Stored Procedure

facebook comments:

One Response to “XML file export with stored procedure in Oracle”

  1. Lucia says:

    You share interesting things here. I think that your blog can go
    viral easily, but you must give it initial boost and i know how to do
    it, just search in google for – wcnu traffic increase

Leave a Reply

Time limit is exhausted. Please reload CAPTCHA.