I'm having issues running the following code: I'm trying to do a theorical exercise on the usage of UTL_FILE in which I intend to write an XML into a file.
DECLARE
XML_VAL01 XMLTYPE;
XML_VALUE CLOB;
VAR_DIRID VARCHAR(10) := '&FOLDER_NM';
VAR_DIRID_CHK NUMBER;
VAR_FILEPATH VARCHAR(200);
VAR_FILENAME VARCHAR(30) := '&SPL_NM';
VAR_FILEEXTS VARCHAR(4) := 'XML';
L_FILE UTL_FILE.FILE_TYPE;
BEGIN
-- This is my XML File --
SELECT
XMLSERIALIZE(CONTENT
XMLELEMENT("TestParameters",
XMLELEMENT("Parameter2", V1),
XMLELEMENT("Parameter3", XMLATTRIBUTES(V3"Attr1", V4"Attr4"), V2),
XMLELEMENT("Forest1", XMLFOREST('For1' AS V5, 'For2' AS V6, XMLCDATA(V7) AS "For3")),
XMLAGG(XMLELEMENT("NextNumber1", V99) ORDER BY V99) --*/
) AS CLOB INDENT)
INTO XML_VALUE
FROM (SELECT 'Value1' V1, 'Value2' V2, 'Value3' V3, 'Value4' V4, 'Value5' V5, 'Value6' V6, 'Value7' V7, LEVEL V99 FROM DUAL CONNECT BY LEVEL <= 5)
GROUP BY V1, V2, V3, V4, V5, V6, V7;
-- Here I am validating if the Directory Exists
SELECT COUNT(*) INTO VAR_DIRID_CHK FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = VAR_DIRID;
IF VAR_DIRID_CHK <= 0 THEN
DBMS_OUTPUT.PUT_LINE('No Directory');
DBMS_OUTPUT.PUT_LINE(XML_VALUE);
ELSE
-- If my directory exists, I will write the XML
SELECT DIRECTORY_PATH INTO VAR_FILEPATH FROM ALL_DIRECTORIES WHERE DIRECTORY_NAME = VAR_DIRID AND OWNER = 'SYS';
L_FILE := UTL_FILE.FOPEN (VAR_FILEPATH, VAR_FILENAME || '.' || VAR_FILEEXTS, 'W');
UTL_FILE.PUT_LINE(L_FILE, XML_VALUE);
UTL_FILE.FCLOSE(L_FILE);
END IF;
END;
/
In order to ensure that my directory is valid, I'm using ALL_DIRECTORIES. Still, I'm getting:
Error report -
ORA-29280: invalid directory object
ORA-06512: at "SYS.UTL_FILE", line 41
ORA-06512: at "SYS.UTL_FILE", line 478
ORA-06512: at line 36
29280. 00000 - "invalid directory object"
*Cause: A corresponding directory object does not exist.
*Action: Correct the directory object parameter, or create a corresponding
directory object with the CREATE DIRECTORY command.
I already verified I have access to the folder.
Thank you.
Oracle documentation states about the first parameter to utl_file.fopen
:
Directory location of file. This string is a directory object name and must be specified in upper case. Read privileges must be granted on this directory object for the UTL_FILE user to run FOPEN.
It is asking for the directory name (all_directories.directory_name
), not the file path (all_directories.directory_path
). Provide VAR_DIRID
as the parameter and it should work, assuming that network path is accessible to Oracle running on this machine and Oracle accepts that pathing syntax. If not try mapping a drive to it and assign the drive letter to the Oracle directory. Either way, by passing in the directory name, Oracle will translate the name to the real path by looking at the directory object you specified. Also ensure you have granted both read and write privs to the code owner:
grant read on directory pimtest to SCHEMANAME;
grant write on directory pimtest to SCHEMANAME;