有没有办法在不执行它的情况下获取未知数据库查询的类型/名称？

I have a web application where users enter arbitrary sql queries for later batch processing. We want to validate the syntax of the query without actually executing it. Some of the queries will take a long time, which is why we don't want to execute them. I'm using Oracle's dbms_sql.parse to do this.

我有一个Web应用程序，用户可以在其中输入任意sql查询以供以后批处理。我们想验证查询的语法而不实际执行它。一些查询需要很长时间，这就是我们不想执行它们的原因。我正在使用Oracle的dbms_sql.parse来执行此操作。

However, I now have a situation where I need to know the number and type of the result set columns. Is there a way to do this without actually executing the query? That is, to have Oracle parse the query and tell me what the result datatypes/names will be returned when the query is actually executed? I'm using Oracle 10g and and it's a Java 1.5/Servlet 2.4 application.

但是，我现在需要知道结果集列的数量和类型。有没有办法在不实际执行查询的情况下执行此操作？也就是说，让Oracle解析查询并告诉我实际执行查询时返回的结果数据类型/名称是什么？我正在使用Oracle 10g，它是一个Java 1.5 / Servlet 2.4应用程序。

Edit: The users who enter the queries are already users on the database. They authenticate to my app with their database credentials and the queries are executed using those credentials. Therefore they can't put in any query that they couldn't run by just connecting with sqlplus.

编辑：输入查询的用户已经是数据库中的用户。他们使用数据库凭据对我的应用进行身份验证，并使用这些凭据执行查询。因此，只要连接sqlplus，他们就无法进行任何无法运行的查询。

2 个解决方案

#1

You should be able to prepare a SQL query to validate the syntax and get result set metadata. Preparing a query should not execute it.

您应该能够准备SQL查询以验证语法并获取结果集元数据。准备查询不应该执行它。

import java.sql.*;
. . .
Connection conn;
. . .
PreparedStatement ps = conn.prepareStatement("SELECT * FROM foo");
ResultSetMetadata rsmd = ps.getMetaData();
int numberOfColumns = rsmd.getColumnCount();

Then you can get metadata about each column of the result set.

然后，您可以获取有关结果集的每列的元数据。

#2

If you want to do this strictly through pl/sql then you could do the following:

如果你想通过pl / sql严格执行此操作，那么你可以执行以下操作：

DECLARE 
  lv_stat varchar2(100) := 'select blah blah blah';
  lv_cur INTEGER;
  lv_col_cnt INTEGER;
  lv_desc DBMS_SQL.desc_tab;
BEGIN
  DBMS_SQL.parse(lv_cur,lv_stat,DBMS_SQL.NATIVE);
  DBMS_SQL.describe_columns(lv_cur,lv_col_cnt,lv_desc);
  FOR ndx in lv_desc.FIRST .. lv_desc.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(lv_desc(ndx).col_name ||' '||lv_desc(ndx).col_type);
  END LOOP;
END;

the DBMS_SQL.desc_tab contains pretty much all that you would need to know about the columns.

DBMS_SQL.desc_tab几乎包含了有关列的所有知识。

#1