Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22285: non-existent directory or file for FILEOPEN operation (10g, XP)
ORA-22285: non-existent directory or file for FILEOPEN operation [message #395433] Wed, 01 April 2009 07:52 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello to all Gurus,

I am facing problem in uploading file. And your help is required to solve that.
I have created two packages SEO_FTP and SEO_DIR to get connected with file server using FTP and to create directory respectively.
After getting connected to server, I create one directory on my local database server.
While I try to read that file, it gives me error.
Please help.


  1  declare
  2      conn UTL_TCP.CONNECTION;
  3      num number(1);
  4      l_bfile   BFILE;
  5      l_data    CLOB;
  6  begin
  7      conn := SEO_FTP.LOGIN('198.168.101.25','21','dev','eloper','/',null);
  8      num := SEO_DIR.CREATE_OR_REPLACE_DIRECTORY('ss','d:\ftp_dir','XLNTSEO',0);
  9      l_bfile := BFILENAME('ss', 'file1.txt');
 10      dbms_output.put_line('bfilename executed');
 11      DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);
 12      dbms_output.put_line('File opened');
 13      DBMS_LOB.loadfromfile(l_data, l_bfile, DBMS_LOB.getlength(l_bfile));
 14      DBMS_LOB.fileclose(l_bfile);
 15      SEO_FTP.LOGOUT(conn,false);
 16  exception
 17      when others then
 18          SEO_FTP.LOGOUT(conn,false);
 19      raise;
 20* end;
 21  /
Login Successful
CREATE OR REPLACE DIRECTORY ss AS 'd:\ftp_dir'
Directory created successfully
bfilename executed
Logout successful
declare
*
ERROR at line 1:
ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at line 19


regards,
Delna
Re: ORA-22285: non-existent directory or file for FILEOPEN operation [message #395436 is a reply to message #395433] Wed, 01 April 2009 07:57 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Does the d:/ftp_dir exists ?
"CREATE DIRECTORY" won't create a OS directory

[Updated on: Wed, 01 April 2009 07:59]

Report message to a moderator

Re: ORA-22285: non-existent directory or file for FILEOPEN operation [message #395441 is a reply to message #395433] Wed, 01 April 2009 08:02 Go to previous messageGo to next message
joy_division
Messages: 4642
Registered: February 2005
Location: East Coast USA
Senior Member
I have to admit I gave never seen SEO_DIR, but what does DBA_DIRECTORIES show as valid directories? And to my knowledge, directory objects must be identified in upper case.
Re: ORA-22285: non-existent directory or file for FILEOPEN operation [message #395442 is a reply to message #395436] Wed, 01 April 2009 08:04 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Yes, that directory exist.
Output also shows that directory is created, BFILENAME function is also executed successfully.

regards,
Delna
Re: ORA-22285: non-existent directory or file for FILEOPEN operation [message #395444 is a reply to message #395442] Wed, 01 April 2009 08:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Go with joy_division's advice: use the directoryname in UPPERCASE in your code. ('SS' instead of 'ss')
Re: ORA-22285: non-existent directory or file for FILEOPEN operation [message #395445 is a reply to message #395441] Wed, 01 April 2009 08:12 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
hello joy_division,
I think you are right.
By changing the case of letters(making it Upper case),
DBMS_LOB.fileopen
statement executed well.
But there is another error now. I am trying to solve. Till that bye.
And by the way, SEO_DIR is a user defined package.

regards,
Delna
Re: ORA-22285: non-existent directory or file for FILEOPEN operation [message #395710 is a reply to message #395445] Thu, 02 April 2009 06:40 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hello friends,

I am back with another error.
Be helpful in the same.
I am using package FTP available on internet.
following is the code.

  1  declare
  2      conn UTL_TCP.CONNECTION;
  3      dat clob;
  4      num number(1);
  5      l_bfile   BFILE;
  6      l_data    CLOB := empty_clob();
  7  begin
  8      conn :=XLNTSEO_FTP.LOGIN('198.168.101.25','21','dev','eloper','/',null);
  9      num := XLNTSEO_FTP.UPLOAD_ONEFILE(conn,'d:\ftp_dir','file1.txt','file1.txt');
 10      xlntSEO_FTP.LOGOUT(conn);
 11  exception
 12      when others then
 13          xlntSEO_FTP.LOGOUT(conn);
 14      raise;
 15* end;
 16  /
Login Successful
Directory => D_FTP_DIR Path => d:\ftp_dir

...

In put_remote_ascii_data
198.168.101.25--->4129
connected
Logout successful
declare
*
ERROR at line 1:
ORA-20000: 550 Permission denied
ORA-06512: at line 14


and I am getting error in the following function.

PROCEDURE put_remote_ascii_data (p_conn  IN OUT NOCOPY  UTL_TCP.connection,
                                 p_file  IN             VARCHAR2,
                                 p_data  IN             CLOB) IS
-- --------------------------------------------------------------------------
  l_conn      UTL_TCP.connection;
  l_result    PLS_INTEGER;
  l_buffer    VARCHAR2(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_clob_len  INTEGER;
BEGIN
    dbms_output.put_line('In put_remote_ascii_data');
  l_conn := get_passive(p_conn);
    dbms_output.put_line('connected');
  send_command(p_conn, 'STOR ' || p_file, TRUE);
    dbms_output.put_line('after send_command');
  l_clob_len := DBMS_LOB.getlength(p_data);

  WHILE l_pos <= l_clob_len LOOP
    dbms_output.put_line('in loop');
    DBMS_LOB.READ (p_data, l_amount, l_pos, l_buffer);
    dbms_output.put_line('Data read frmo lob');
    IF g_convert_crlf THEN
      l_buffer := REPLACE(l_buffer, CHR(13), NULL);
    END IF;
    l_result := UTL_TCP.write_text(l_conn, l_buffer, LENGTH(l_buffer));
    dbms_output.put_line('written on channel');
    UTL_TCP.flush(l_conn);
    dbms_output.put_line('Data flushed');
    l_pos := l_pos + l_amount;
  END LOOP;
    dbms_output.put_line('Loop complete');
  -- The following line allows some people to make multiple calls from one connection.
  -- It causes the operation to hang for me, hence it is commented out by default.
  -- get_reply(p_conn);
    UTL_TCP.close_connection(l_conn);
    dbms_output.put_line('Data written in put_remote_ascii_data');
EXCEPTION
  WHEN OTHERS THEN
    UTL_TCP.close_connection(l_conn);
    RAISE;
END;


and the called procedure

PROCEDURE send_command (p_conn     IN OUT NOCOPY  UTL_TCP.connection,
                        p_command  IN             VARCHAR2,
                        p_reply    IN             BOOLEAN := TRUE) IS
-- --------------------------------------------------------------------------
  l_result  PLS_INTEGER;
BEGIN
  l_result := UTL_TCP.write_line(p_conn, p_command);
  -- If you get ORA-29260 after the PASV call, replace the above line with the following line.
  -- l_result := UTL_TCP.write_text(p_conn, p_command || utl_tcp.crlf, length(p_command || utl_tcp.crlf));
  
  IF p_reply THEN
    get_reply(p_conn);
  END IF;
END;


as output shows, I am getting error at UTL_TCP.WRITE_LINE()

Any help to solve this problem?

regards,
Delna
Previous Topic: How to wait for trigger to complete (merged)
Next Topic: How to use Substitution Variables in Pl/SQL
Goto Forum:
  


Current Time: Thu Dec 08 00:19:50 CST 2016

Total time taken to generate the page: 0.09933 seconds