Is there a way to get types/names of an unknown db query without executing it?

Question

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.

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.

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.

Answer 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.

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.

Answer 2

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

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.