ORA-29280: invalid directory path [message #608019] |
Fri, 14 February 2014 05:43 |
meteo
Messages: 89 Registered: April 2010 Location: beirut
|
Member |
|
|
form isqlplus i create the following directory and procedure:
1)
CREATE DIRECTORY log_dir AS 'C:\Climate_File'
GRANT READ ON DIRECTORY log_dir TO PUBLIC
Commit;
2)
P92_CLOB_TO_FILE_TRIVAL ( p_fileName IN VARCHAR2, p_dir IN VARCHAR2, p_clob IN CLOB )
AS
c_amount CONSTANT BINARY_INTEGER := 32767;
l_buffer VARCHAR2(32767);
l_chr10 PLS_INTEGER;
l_clobLen PLS_INTEGER;
l_fHandler UTL_FILE.FILE_TYPE;
l_pos PLS_INTEGER := 1;
BEGIN
l_clobLen := DBMS_LOB.GETLENGTH(p_clob);
l_fHandler := UTL_FILE.FOPEN(p_dir, p_fileName,'W',c_amount);
WHILE l_pos < l_clobLen LOOP
l_buffer := DBMS_LOB.SUBSTR(p_clob, c_amount, l_pos);
EXIT WHEN l_buffer IS NULL;
l_chr10 := INSTR(l_buffer,CHR(10),-1);
IF l_chr10 != 0 THEN
l_buffer := SUBSTR(l_buffer,1,l_chr10-1);
END IF;
UTL_FILE.PUT_LINE(l_fHandler, l_buffer,TRUE);
l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c_amount);
END LOOP;
UTL_FILE.FCLOSE(l_fHandler);
EXCEPTION
WHEN OTHERS THEN
IF UTL_FILE.IS_OPEN(l_fHandler) THEN
UTL_FILE.FCLOSE(l_fHandler);
END IF;
RAISE;
END;
3)
from isqlplus i also write the following code to call the above procedure:
set serveroutout on;
DECLARE
file clob;
BEGIN
P4_FIRE_HTML_TRIGGER_ARG ('OLBA+LCLK+LFPO+OEJN', file); --this procedure generate the messages OLBA LCLK... airports into file that have
a CLOB type
P92_CLOB_TO_FILE_TRIVAL ( 'log_dir', 'doc.txt', file ); -- this is the called procedure
END;
always i am getting the following error:
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "METEO.P92_CLOB_TO_FILE_TRIVAL", line 43
ORA-06512: at line 4
Any suggestion or solution may solve this error???
thank you
|
|
|
|
|
|
Re: ORA-29280: invalid directory path [message #608040 is a reply to message #608031] |
Fri, 14 February 2014 08:21 |
meteo
Messages: 89 Registered: April 2010 Location: beirut
|
Member |
|
|
Hi Mr and thank you for your follow
this is what I have made to put the directory in the DB
from sql
connect as meteo/passsword user
CREATE DIRECTORY log_dir AS 'C:\Climate_File'
grant read, write on directory log_dir TO PUBLIC
commit;
any thing else I must do???
thank you again
|
|
|
|
|
|
|
|
Re: ORA-29280: invalid directory path [message #608057 is a reply to message #608055] |
Fri, 14 February 2014 09:36 |
meteo
Messages: 89 Registered: April 2010 Location: beirut
|
Member |
|
|
yes also i changed the path for log_dir directory as follow
CREATE or replace DIRECTORY log_dir AS 'C:\Home9i\database'; -- where i put txt file
GRANT READ, WRITE ON DIRECTORY log_dir TO PUBLIC;
commit;
still the same error!
|
|
|
|