Re: Syntax problem error PLS-00103

check your utl_file_dir parameter in init.ora  if not there add it..


prefix your string by '

ie

rows_exported := dump_csv('SELECT * from USERS where SERVER_NAME =
''master''' ,' ','C:\test','test.txt');


"Fabian" <[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> platform: W2000
> product: ORACLE8
> database version: 8i
>
> I'm using the following procedure to write Oracle data into a text
> file:
>
> --------------------------------------------------------------------------
------
> Create or Replace Function dump_csv(p_query varchar2, p_separator
> varchar2
> default ',',
>                                     p_dir varchar2, p_filename
> varchar2)
> return number is
> --
> -- example usage:
> --
> --    SQL> variable number_of_lines number;
> --    SQL> begin
> --           :number_of_lines := dump_csv('select * from my_table',
> ',','/fs1/data1',  'my_table.txt');
> --         end;
> --    SQL> /
> --
>   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;
> --------------------------------------------------------------------------
------
>
> But when I try :
>
> variable rows_exported NUMBER;
> begin
> :rows_exported := dump_csv('SELECT * from USERS where SERVER_NAME =
> 'master'' ,' ','C:\test','test.txt');
> end;
> /
>
> I receive the following error message:
>
> PLS-00103: Encountered the symbol "MASTER"  when expecting one of the
> following:
> . ( ) , * @ % & | = - + < / > at in mod not range rem => ..
> <an exponent (**)> <> or != or ~= >= <= <> and or like as
> between from using is null is not || is dangling
> The symbol  ". was inserted before "MASTER" to continue.
>
>
> How should I write my request if I want to use a '=' or 'like'
> statement ?
> Does anyone has an idea ?
>
> Thanx