Why doesn't this work - function failing?

From: Paul <paul_at_see.my.sig.com>
Date: Fri, 12 Aug 2005 17:12:44 +0100
Message-ID: <uahpf1hqc2e88smgdkjrm9h8mnskce6s3f@4ax.com>

Hi all,

I have a .csv dump routine (code at end of post) and it's not working.

When I try to run as SYS this line

DUMP_CSV('select * from scott.emp', ';', 'D:\Paul', 'billy.csv');

SQLPlus returns.

ORA-00900: invalid SQL statement

I know the SQL is valid, and I know that there is the function code. It fails if I leave the terminating semicolon in or out. It also fails if I put a terminating semicolon with the SQL statement or not.

Any ideas. TIA.

Paul...

dump_csv code ------------------------------------
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;
/

---

-- 

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 Fri Aug 12 2005 - 11:12:44 CDT