Problem with some simple PL/SQL.

From: Paul <paul_at_see.my.sig.com>
Date: Sun, 24 Jul 2005 19:15:33 +0100
Message-ID: <lcm7e1pneoorm5d910rhhaa5rkvbdi90e3@4ax.com>

Hi all,

From here (link wraps)  

http://asktom.oracle.com/pls/ask/f?p=4950:8:1774479186358239817:: NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:68212348056 I copied the following code - to dump the contents of a table as a .csv file.

---

create or replace function  dump_csv( p_query     in varchar2,
                                      p_separator in varchar2 
                                                    default ',',
                                      p_dir       in varchar2 ,
                                      p_filename  in varchar2 )

return number
AUTHID CURRENT_USER
is

    l_output        utl_file.file_type;
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;
    l_separator     varchar2(10) default '';
    l_cnt           number default 0;

begin

    l_output := utl_file.fopen( p_dir, p_filename, 'w' );

    dbms_sql.parse( l_theCursor, p_query, dbms_sql.native );

    for i in 1 .. 255 loop

        begin
            dbms_sql.define_column( l_theCursor, i, 
                                    l_columnValue, 2000 );
            l_colCnt := i;
        exception
            when others then
                if ( sqlcode = -1007 ) then exit;
                else
                    raise;
                end if;
        end;

    end loop;

    dbms_sql.define_column( l_theCursor, 1, l_columnValue,

                            2000 );

    l_status := dbms_sql.execute(l_theCursor);

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        l_separator := '';
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, 
                                   l_columnValue );
            utl_file.put( l_output, l_separator ||  
                                    l_columnValue );
            l_separator := p_separator;
        end loop;
        utl_file.new_line( l_output );
        l_cnt := l_cnt+1;

    end loop;
    dbms_sql.close_cursor(l_theCursor);

    utl_file.fclose( l_output );
    return l_cnt;
end dump_csv;
/

---

It ran in SQLPlus no problems, function created.

Then I input this

---

create or replace procedure test_dump_csv as

    l_rows number;
begin

    l_rows := dump_csv( 'select *

                           from all_users 
                           where rownum < 25', 
                        ',', 'D:\Paul', 'test.dat' );

end;
/

---

procedure created.

But, when I try to run

---

begin
test_dump_csv;
end;
/

---

I get

---

ERROR at line 1:

ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "SYS.DUMP_CSV", line 17
ORA-06512: at "SYS.TEST_DUMP_CSV", line 5
ORA-06512: at line 2
---------------------------------------------------

Now, I did set utl_file_dir='D;\Paul' in my init.ora.

Can anybody explain to me what's going on and why this simple code is failing?

TIA. Paul...

-- 

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2, 

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters 
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
 
Furthermore, as a courtesy to those who spend 
time analysing and attempting to help, please 
do not top post.

Received on Sun Jul 24 2005 - 13:15:33 CDT