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

Question

I have a web application in which users enter arbitrary sql queries for subsequent batch processing. We want to check the syntax of the request without actually executing it. Some of the requests will take a lot of time, so we don’t want to execute them. I am using Oracle dbms_sql.parse for this.

However, now I have a situation where I need to know the number and type of columns of the result set. Is there a way to do this without actually executing the request? That is, for Oracle to analyze the query and tell you what result the data types / names will return when the query is actually executed? I am using Oracle 10g and this is a Java 1.5 / Servlet 2.4 application.

Edit: Users who enter queries are already users in the database. They authenticate my application with the credentials of their database, and requests are made using these credentials. Therefore, they cannot embed any queries that they could not fulfill simply by connecting to sqlplus.

Answer 1

You should be able to prepare an SQL query to check the syntax and retrieve the result set metadata. Request preparation should not be performed.

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 in the result set.

Answer 2

If you want to do this strictly through pl / sql, you can 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; 

DBMS_SQL.desc_tab contains almost everything you need to know about columns.