Hi , Thanks 4 the help . I will try
Regards Binu On Tue, Dec 30, 2008 at 7:26 PM, ddf <[email protected]> wrote: > > > > 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 -~----------~----~----~----~------~----~------~--~---
