通过Oracle的JDBC驱动程序解析SQL [英] Parse SQL via Oracle's JDBC driver

问题描述

我想测试给定的SQL语句在语法和语义上是否有效(即没有语法错误和字段拼写错误).

I'd like to test whether given SQL statement is syntactically and semantically valid (ie. no syntax errors and no field misspellings).

对于大多数数据库，Connection.prepareStatement和PreparedStatement.getMetaData可以解决问题(无例外==好查询).不幸的是，Oracle的最新驱动程序仅像SELECT查询那样解析，而不能解析其他类型的查询.较老的司机甚至都不会这样做.

For most databases Connection.prepareStatement and PreparedStatement.getMetaData would do the trick (no exception == good query). Unfortunately Oracle's newest driver only parses like this only SELECT queries, but not other kind of queries. Older drivers don't do even that.

Oracle是否提供其他一些工具来解析SQL语句?

Is there some other facility provided by Oracle for parsing SQL statements?

推荐答案

您可以使用Oracle DBMS_SQL程序包解析字符串中包含的语句.例如:

You can use the Oracle DBMS_SQL package to parse a statement held in a string. For example:

SQL> declare
  2    c integer;
  3    l_statement varchar2(4000) := 'insert into mytable (col) values (1,2)';
  4  begin
  5    c := dbms_sql.open_cursor;
  6    dbms_sql.parse(c,l_statement,dbms_sql.native);
  7    dbms_sql.close_cursor(c);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-00913: too many values
ORA-06512: at "SYS.DBMS_SYS_SQL", line 824
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at line 6

您可以将其包装到刚刚返回的存储函数中，例如如果该语句有效，则为1；如果无效，则为0，例如:

You could wrap that up into a stored function that just returned e.g. 1 if the statement was valid, 0 if invalid, like this:

function sql_is_valid
  ( p_statement varchar2
  ) return integer
is  
  c integer;
begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse(c,p_statement,dbms_sql.native);
  dbms_sql.close_cursor(c);
  return 1;
exception
  when others then 
    return 0;
end;

然后您可以像下面的PL/SQL示例那样使用它:

You could then use it something like this PL/SQL example:

:n := sql_is_valid('insert into mytable (col) values (1,2)');

这篇关于通过Oracle的JDBC驱动程序解析SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！