Re: Validate Query Syntax

"TerryP" <webtourist_at_gmail.com> a écrit dans le message de news: 6e528514-ad1a-4713-b132-c3ceef41ff43_at_o35g2000vbi.googlegroups.com...
| >
| > dbms_sql.parse
| >
|
| LOL good idea. you're definitely thinking outside of the box....way
| outside, LOL.
| Thou rather impractical.
|

impractical?

SQL> create or replace procedure checkSQL (p_sql varchar2)   2 authid current_user
  3 is
  4 handle int := dbms_sql.open_cursor;   5 begin

  6     dbms_sql.parse (handle, p_sql, dbms_sql.native);
  7     dbms_sql.close_cursor (handle);

Procedure created.

SQL> exec checksql('select sysdate from dual')

PL/SQL procedure successfully completed.

SQL> exec checksql('select * from nonExistent') BEGIN checksql('select * from nonExistent'); END;

*
ERROR at line 1:

ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "MICHEL.CHECKSQL", line 6
ORA-06512: at line 1

SQL> exec checksql('select * from')
BEGIN checksql('select * from'); END;

*
ERROR at line 1:

ORA-00903: invalid table name
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "MICHEL.CHECKSQL", line 6
ORA-06512: at line 1

SQL> exec checksql('select *')
BEGIN checksql('select *'); END;

*
ERROR at line 1:

ORA-00923: FROM keyword not found where expected
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "MICHEL.CHECKSQL", line 6
ORA-06512: at line 1

SQL> exec checksql('insert')
BEGIN checksql('insert'); END;

*
ERROR at line 1:

ORA-00925: missing INTO keyword
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "MICHEL.CHECKSQL", line 6
ORA-06512: at line 1

SQL> exec checkSQL('delete from system.SQLPLUS_PRODUCT_PROFILE') BEGIN checkSQL('delete from system.SQLPLUS_PRODUCT_PROFILE'); END;

*
ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 909
ORA-06512: at "SYS.DBMS_SQL", line 39
ORA-06512: at "MICHEL.CHECKSQL", line 6
ORA-06512: at line 1

And so on

Regards
Michel Received on Wed Aug 05 2009 - 12:11:46 CDT