SET FMTONLY ON in Oracle queries

Question

I'd like to know if SET FMTONLY ON can be used within Oracle queries. When I'm using like this:

SET FMTONLY ON select * from department

I'm getting a message as such:

Line 1: SQLPLUS Command Skipped: SET FMTONLY ON select * from department

I'm looking for a statement in Oracle that

that returns only metadata to the client to test the format of the response without actually running the query.

EDIT

Thanks.... I want something generic for all types of queries, if the query contains any 'order by' then we cannot add this. If it is an insert query it should just validate the query, now I'm doing a roll back to just validate the query (and then execute it at run time) SET FMTONLY helps me achieve it in SQL, something similar in Oracle????.

And "CREATE TABLE FormatTest AS (SELECT ...) and then do a DESCRIBE FormatTest." I didn't get this one :(

Any help is appreciated.

Answer 1

According to this, SET FMTONLY ON:

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

No rows are processed or sent to the client because of the request when SET FMTONLY is turned ON.

Therefore, I'm guessing the outcome is that either (a) the statement succeeds, and nothing happens; or (b) the statement fails, and an exception is raised.

Therefore, you could achieve a similar effect in Oracle by running EXPLAIN PLAN FOR xxx, e.g.:

SQL> EXPLAIN PLAN FOR insert into baddml values (1);
explain plan for INSERT INTO baddml VALUES (1)
                             *
ERROR at line 1:
ORA-00942: table or view does not exist

If the statement succeeds, then the statement can be assumed to be syntactically valid.

Answer 2

It is not quite the same, but you could add WHERE rownum<=1 to your query. It would allow you to assess the format based on one row.

Its a little overkill, but you could do CREATE TABLE FormatTest AS (SELECT ...) and then do a DESCRIBE FormatTest.

EDIT:

Based on your more recent edits it sounds like you are looking for statement validation rather than layout, in which case Janek Bogucki or Jeffrey Kemp have better answers.

Answer 3

Have a look at DBMS_SQL.DESCRIBE_COLUMNS. This is the data that will be returned for each column,

This will work with any select statement afaik, for example I tried this query by modifying Example 8: Describe Columns and it worked,

DBMS_SQL.PARSE(c, 'SELECT sysdate, b.* FROM scott.bonus b', DBMS_SQL.NATIVE);

col_type = 12
col_maxlen = 7
col_name = SYSDATE
col_name_len = 7
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = true
col_type = 1
col_maxlen = 10
col_name = ENAME
col_name_len = 5
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = true
col_type = 1
col_maxlen = 9
col_name = JOB
col_name_len = 3
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = 0
col_null_ok = true
col_type = 2
col_maxlen = 22
col_name = SAL
col_name_len = 3
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = -127
col_null_ok = true
col_type = 2
col_maxlen = 22
col_name = COMM
col_name_len = 4
col_schema_name =
col_schema_name_len = 0
col_precision = 0
col_scale = -127
col_null_ok = true

PL/SQL procedure successfully completed.