Re: Problem with some simple PL/SQL.

"Paul" <paul_at_see.my.sig.com> wrote in message news:lcm7e1pneoorm5d910rhhaa5rkvbdi90e3_at_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...
>

Type ? 'D;\Paul' instead of 'D:\Paul' ?

Matthias Received on Sun Jul 24 2005 - 13:46:01 CDT