On Dec 30, 12:26 am, "Binu K S" <[email protected]> wrote:
> Hi,
>
> I have a folder which contain lots of xml files .
> I want to store the xml file to a database table .
>
> For that i am using the following procedure .
>
> CREATE OR REPLACE PROCEDURE load_xml (p_dir IN VARCHAR2,
> p_filename IN VARCHAR2) AS
> l_bfile BFILE := BFILENAME(p_dir, p_filename);
> l_clob CLOB;
> BEGIN
> DBMS_LOB.createtemporary (l_clob, TRUE);
>
> DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
> DBMS_LOB.loadfromfile(l_clob, l_bfile, DBMS_LOB.getlength(l_bfile));
> DBMS_LOB.fileclose(l_bfile);
>
> INSERT INTO xml_tab (
> id,
> filename,
> xml
> )
> VALUES (
> xml_tab_seq.NEXTVAL,
> p_filename,
> XMLTYPE.createXML(l_clob)
> );
> COMMIT;
>
> DBMS_LOB.freetemporary (l_clob);
> END;
> /
>
> Then i am using the following procedure
>
> EXEC load_xml(p_dir => 'XML_DIR', p_filename => 'emp.xml');
>
> It is working fine .
>
> Now the problem .
>
> The only thing i know is the directory name where the xml files are stored .
> It is not possible to get
>
> the xml file name . To run the above procedure i need to get the file name .
> How can i get the file
>
> name inside the pl/sql . (get the files name in a specified folder ) .After
> storing the xml in the table
>
> we will move the xml file to a new location uing the UTL_FILE.FCOPY utility
> .
>
> thanks
> binu
This appears to be a situation where external procedures might be
useful. You can write this in Java or in C/C++ and call it from PL/
SQL. An overview is provided here:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14289/dcimethcj.htm#sthref250
Think of this as an interface between Oracle and the O/S layer to
return O/S level information to the database, such as a directory
listing.
The online documentation can help you write such procedures. Oracle
even provides example scripts/programs to illustrate extproc in
action:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_extern_proc.htm#sthref1763
David Fitzjarrell
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to
[email protected]
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
