Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    7

    Unanswered: ORA-29283: invalid file operation

    Hi Experts,

    Need your HELP and assistance to resolved error ORA-29283: invalid file operation. Done a lot of solutions in the net but still got an error. Thanks in advance for your advice.


    Please find below details of the error.

    select dump_csv('SELECT Start_Date,
    *
    ERROR at line 1:
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 488
    ORA-29283: invalid file operation
    ORA-06512: at "OPS$ORADBA.DUMP_CSV", line 17
    ORA-06512: at line 1


    SQL>


    Below are details and test done:

    1) In UNIX: /tmp folder does exist.

    aesdbb1raqx1 2% cd /tmp
    aesdbb1raqx1 3% ls -ld
    drwxrwxrwt 14 root sys 11461 Jun 8 18:39 .
    aesdbb1raqx1 4% pwd
    /tmp
    aesdbb1raqx1 5%

    2) SQL> create directory EXT_TABLES as '/tmp';
    Directory created.


    3) SQL> select * from ALL_DIRECTORIES where DIRECTORY_NAME='EXT_TABLES';

    OWNER DIRECTORY_NAME
    ------------------------------ ------------------------------
    DIRECTORY_PATH
    --------------------------------------------------------------------------------
    SYS EXT_TABLES
    /tmp

    4) Tried to execute the ff PL/SQL block and works fine:

    SQL> declare
    2 FILE_HANDLE utl_file.file_type;
    3 begin
    4 FILE_HANDLE := utl_file.fopen('EXT_TABLES','file1.txt','w');
    5 utl_file.put_line(FILE_HANDLE, 'This is sample text');
    6 utl_file.fclose(FILE_HANDLE);
    7 end;
    8 /

    PL/SQL procedure successfully completed.

    SQL> exit
    Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, Data Mining and Real Application Testing options
    aesdbb1raqx1 2% cd /tmp
    aesdbb1raqx1 3% ls file*
    file1.txt
    aesdbb1raqx1 4% cat file1.txt
    This is sample text
    aesdbb1raqx1 5%

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Folks can not debug code that can not be seen.

    CUT & PASTE the whole session so we can see exactly the whole code & how Oracle responds in context.

    I suspect that you have invoked it incorrectly.
    Last edited by anacedent; 06-10-09 at 23:15.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2009
    Posts
    7
    Below are the codes. Thx.


    select dump_csv('SELECT Start_Date,
    Num_Logs,
    to_char(Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2),''999999999'') AS Mbytes
    FROM (SELECT To_Char(Vlh.First_Time,''MM-W-YYYY'') AS Start_Date,
    COUNT(Vlh.Thread#) Num_Logs
    FROM V$log_History Vlh
    GROUP BY To_Char(Vlh.First_Time,''MM-W-YYYY'')) log_hist,
    ( select distinct bytes from V$log ) Vl
    ORDER BY Log_Hist.Start_Date',',','EXT_TABLES','redo_histor y.log')
    from dual;

  4. #4
    Join Date
    Apr 2009
    Posts
    7
    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
    With the Partitioning, Data Mining and Real Application Testing options

    SQL> select dump_csv('SELECT Start_Date,
    2 Num_Logs,
    3 to_char(Round(Num_Logs * (Vl.Bytes / (1024 * 1024)),2),''999999999'')
    4 FROM (SELECT To_Char(Vlh.First_Time,''MM-W-YYYY'') AS Start_Date,
    5 COUNT(Vlh.Thread#) Num_Logs
    6 FROM V$log_History Vlh
    7 GROUP BY To_Char(Vlh.First_Time,''MM-W-YYYY'')) log_hist,
    8 ( select distinct bytes from V$log ) Vl
    9 ORDER BY Log_Hist.Start_Date',',','EXT_TABLES','redo_histor y.log')
    10 from dual;
    select dump_csv('SELECT Start_Date,
    *
    ERROR at line 1:
    ORA-29283: invalid file operation
    ORA-06512: at "SYS.UTL_FILE", line 488
    ORA-29283: invalid file operation
    ORA-06512: at "OPS$ORADBA.DUMP_CSV", line 17
    ORA-06512: at line 1


    SQL>

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Dumping to delimited file using PLSQL
    shows:
    Code:
    create or replace procedure test_dump_csv
    as
        l_rows  number;
    begin
        l_rows := dump_csv( 'select * from all_users where rownum < 25', ',', '/tmp', 'test.dat' );
    end;
    /
    What happens for you if you invoke dump_cvs as Tom does?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •