This is the PL/SQL procedure which I am trying to run . The issue I am facing an invalid directory path. I am running Oracle 11g On windows server. I have a client configured on a different machine which is a linux machine.I tried to check in the init.ora file to set the directory path,but seems like futil package is not supported in 11g Windows oracle version

How can I do import of a single column from a table where its a XML_clob(XMLs stored in a bunch),column type is CLOB in this case and dump to a file. Appreciate if a better PL/SQL procedure is available for converting the whole column to a single file and store in the directory through the client machine

Appreciate responses for the guidance towards my problem

UPDATE

CREATE or REPLACE PROCEDURE
process_all_rows
IS TYPE clob_aat IS TABLE OF minf100k300wclob%xml_clob INDEX BY PLS_INTEGER; l_XML clob_aat;
BEGIN select XMLElement(xml_clob).getClobVal() BULK COLLECT INTO l_XML FROM minf100k300wclob;
FOR indx IN 1 .. L_XML.COUNT LOOP dbms_lob.append(L_XML, 'XML_EDI_FILES_1', 'file1.xml', nls_charset_id('AL32UTF8')); END LOOP;
END process_all_rows;

I am unable to get this working as I am getting the error as shown below

Errors for PROCEDURE PROCESS_ALL_ROWS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
4/4 PL/SQL: Item ignored
5/33 PLS-00208: identifier 'XML_CLOB' is not a legal cursor attribute
12/22 PLS-00382: expression is of wrong type
18/7 PL/SQL: Statement ignored
18/7 PLS-00306: wrong number or types of arguments in call to 'APPEND'
SQL>
enter code here

1 Answer

1) Well, to use clob2file or most of the functions that rely on UTL_FILE, you must create a directory object in oracle database first. The syntax is:

CREATE DIRECTORY XML_EDI_FILES AS 'C:\path\on\your\server\';

This adds specified directory on your server to a special oracle table with the name you specify, in that case XML_EDI_FILES. When you call file-related procedures later, you will pass that name as an argument. See docs for more info. Granted, oracle process on your server must have write permissions to that dir.

2) Wait, through client machine? Use sqlplus spool, for example. Rough syntax is

set long 4000;
spool output_file.txt
select xml_clob from minf100k300wc

Though I can almost guarantee this will not work as desired at first, since sql-plus tends to also output column names, insert line breaks etc. You'll have to look up sqlplus formatting options. I'd probably go with a little bit of Java code instead, Clobs are nicely mapped to strings IIRC.

3) It's not clear what you're asking.

Answer to updated question:

...but I even described what you had to do in my comment. Here you go:

CREATE or REPLACE PROCEDURE process_all_rows IS tmp_clob clob; cursor cur_clob is select XMLElement(xml_clob).getClobVal() val FROM minf100k300wclob;
begin dbms_lob.createtemporary(tmp_clob,true); for c in cur_clob loop dbms_lob.append(tmp_clob, c.val); end loop; dbms_xslprocessor.clob2file(tmp_clob, 'XML_EDI_FILES_1', 'file1.xml', nls_charset_id('AL32UTF8')); dbms_lob.freetemporary(tmp_clob);
end;