Re: Checking SQL syntax

"ES" <toto_at_titi.fr> wrote in message news:<3e81cfff$0$29490$4d4eb98e_at_read.news.fr.uu.net>...
> Hello,
>
> our application build an sql query with an intermedia clause :
> the user write the word list he is seeking and we build the where clause.
>
> we need to allow the user to use the complete intermedia syntax, including
> near, fuzzy, ...
> but when an error is writen by the user, we have a really bad reaction from
> oracle 8i (core dump, instance falling down)
>
> does it exist a package that will test and agree a sql phrase without
> executing it ?
>
> thanks

Look at this
set echo off

--
-- Plsql script example that will syntax check a sql statement
--  goal - make n2 function that can be called an passes back error
--
--  20020919  Mark D Powell   Basis pulled off online board
--
declare
v_cursor     number; 
v_errloc     number;
v_stmt       varchar2(100);
--
begin
--
v_stmt  := 'select badname from smith.dual';
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor, v_stmt, dbms_sql.native);
dbms_sql.close_cursor(v_cursor);
--
exception
--
when others then
     v_errloc := dbms_sql.last_error_position;
     dbms_output.put_line(v_stmt);
     dbms_output.put_line(SQLERRM);
     if SQLCODE = -904 then
        dbms_output.put_line(substr(v_stmt, v_errloc + 1,
                                    instr(v_stmt,' ',v_errloc + 1) -
                                          v_errloc)
                             );
     end if;
dbms_sql.close_cursor(v_cursor);
end;
/

HTH -- Mark D Powell --