Home » SQL & PL/SQL » SQL & PL/SQL » ORA-29280: Invalid directory path
ORA-29280: Invalid directory path [message #232390] Fri, 20 April 2007 06:50 Go to next message
chandrasekharbomminisidda
Messages: 21
Registered: March 2007
Location: Chennai
Junior Member
Dear Sir,


I am having function which i wrote here at below, when i tried to execute this
using

BEGIN
   DBMS_OUTPUT.put_line (   load_data ('T1'
                                     , 'x,y,z'
                                     , 'log_dir'
                                     , 'emp2.dat'
                                     , ','
                                      )
                         || ' rows loaded'
                        );
END;


It's giving me the error message


ORA-29280: Invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "OTSLGDW.LOAD_DATA", line 17
ORA-06512: at line 2

As i am using 10g.

Even though i created the directory called "log_dir" using
create or replace directory LOG_DIR
  as 'D:\chandrasekharBSFiles\oracle10gpdfs';


Even in this path 'D:\chandrasekharBSFiles\oracle10gpdfs'; i created a new file manually using right click -> new
named emp2.dat file .

Still it is giving the same message.

Why it is giving me the error message even though i created the directory.

Please give me the solution for the above said problem.
CREATE OR REPLACE FUNCTION load_data (
   p_table       IN   VARCHAR2
 , p_cnames      IN   VARCHAR2
 , p_dir         IN   VARCHAR2
 , p_filename    IN   VARCHAR2
 , p_delimiter   IN   VARCHAR2 DEFAULT '|'
)
   RETURN NUMBER
IS
   l_input       UTL_FILE.file_type;
   l_thecursor   INTEGER            DEFAULT DBMS_SQL.open_cursor;
   l_buffer      VARCHAR2 (4000);
   l_lastline    VARCHAR2 (4000);
   l_status      INTEGER;
   l_colcnt      NUMBER             DEFAULT 0;
   l_cnt         NUMBER             DEFAULT 0;
   l_sep         CHAR (1)           DEFAULT NULL;
   l_errmsg      VARCHAR2 (4000);
BEGIN
   l_input     := UTL_FILE.fopen (p_dir, p_filename, 'r');
   l_buffer    := 'insert into ' || p_table || ' values ( ';
   l_colcnt    := LENGTH (p_cnames) - LENGTH (REPLACE (p_cnames, ',', ''))
                  + 1;

   FOR i IN 1 .. l_colcnt
   LOOP
      l_buffer    := l_buffer || l_sep || ':b' || i;
      l_sep       := ',';
   END LOOP;

   l_buffer    := l_buffer || ')';
   DBMS_SQL.parse (l_thecursor, l_buffer, DBMS_SQL.native);

   LOOP
      BEGIN
         UTL_FILE.get_line (l_input, l_lastline);
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            EXIT;
      END;

      l_buffer    := l_lastline || p_delimiter;

      FOR i IN 1 .. l_colcnt
      LOOP
         DBMS_SQL.bind_variable (l_thecursor
                               , ':b' || i
                               , SUBSTR (l_buffer
                                       , 1
                                       , INSTR (l_buffer, p_delimiter) - 1
                                        )
                                );
         l_buffer    := SUBSTR (l_buffer, INSTR (l_buffer, p_delimiter) + 1);
      END LOOP;

      BEGIN
         l_status    := DBMS_SQL.EXECUTE (l_thecursor);
         l_cnt       := l_cnt + 1;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_errmsg    := SQLERRM;

            INSERT INTO badlog
                        (errm
                       , DATA
                        )
            VALUES      (l_errmsg
                       , l_lastline
                        );
      END;
   END LOOP;

   DBMS_SQL.close_cursor (l_thecursor);
   UTL_FILE.fclose (l_input);
   COMMIT;
   RETURN l_cnt;
END load_data;


[mod-edit]applied code tags. Next time, do so yourself.

[Updated on: Fri, 20 April 2007 06:53] by Moderator

Report message to a moderator

Re: ORA-29280: Invalid directory path [message #232392 is a reply to message #232390] Fri, 20 April 2007 06:56 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
use UPPERCASE for directory names (in your code)
and i dont understand why you are using DBMS_OUTPUT to execute the procedure.
DBMS_OUTPUT is just used to display to screen

[Updated on: Fri, 20 April 2007 06:58]

Report message to a moderator

ORA-29283: invalid file operation [message #232417 is a reply to message #232392] Fri, 20 April 2007 07:51 Go to previous messageGo to next message
chandrasekharbomminisidda
Messages: 21
Registered: March 2007
Location: Chennai
Junior Member
Dear Sir,


Even though i called by using "execute" STATEMENT with CAPITAL LETTERS (DIRECTORY NAME). Its's giving invalid file operation error message.

Note in my schema myself only created the directory. So no need to grant read, write on directory log_dir to my schema name.
I am right. still i am getting the error named "Invalid File operation"



EXECUTE :h:= load_data( 'chandra','x,y,z','LOG_DIR','emp2.dat',',' );

begin :h:= load_data( 'chandra','x,y,z','LOG_DIR','emp2.dat',',' ); end;

ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 475
ORA-29283: invalid file operation
ORA-06512: at "OTSLGDW.LOAD_DATA", line 17
ORA-06512: at line 1


Please give me solution for the above problem.

Thanking you Sir,

Chandrasekhar B.S.
Re: ORA-29283: invalid file operation [message #232419 is a reply to message #232417] Fri, 20 April 2007 07:55 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Quote:
Note in my schema myself only created the directory. So no need to grant read, write on directory log_dir to my schema name.

You are supposed to create directory logged in as SYS.
It is all said in documentation.
BTW, i have no idea what you are trying to do.
Goodluck!.
Previous Topic: even & odd rows
Next Topic: ref cursor restrictions
Goto Forum:
  


Current Time: Sat Dec 10 05:10:30 CST 2016

Total time taken to generate the page: 0.08661 seconds