Re: Checking SQL syntax

Thanks.

ES

"Mark D Powell" <Mark.Powell_at_eds.com> a écrit dans le message de news: 2687bb95.0303261729.522e68f4_at_posting.google.com...
> "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 --
Received on Thu Mar 27 2003 - 13:04:48 CST