Describing the schema of a query result in Oracle?

Question

Is it possible to get results similar to the Oracle DESCRIBE command for a query? E.g. I have a join among several tables with a restriction of the columns that are returned, and I want to write that to a file. I later want to restore that value from a file into its own base table in another DBMS.

I could describe all of the tables individually and manually prune the columns, but I was hoping something like DESC (select a,b from t1 join t2) as q would work but it doesn't.

Creating a view isn't going to work if I don't have create view privileges, which I don't. Is there no way to describe a query result directly?

Galiot

DBMS_SQL.DESCRIBE should do what you want: docs.oracle.com/cd/E11882_01/appdev.112/e25788/… – Bingham

Answer 1

If you plan to re-use the query, it may make sense to create a view for it.
You can comment on a database view in the same way that you can for a table:

create view TEST_VIEW as select 'TEST' COL1 from dual;
comment on table TEST_VIEW IS 'TEST ONLY';

To find comments on a view, execute this:

select * from user_tab_comments where table_name='TEST_VIEW';

References:

How to create a comment to an oracle database view

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:233014204543

NOTE: This URL states that the SQLPLUS DESCRIBE command is only supposed to be used with a "table, view or synonym" or "function or procedure". This means that the target of DESCRIBE must be an existing database object.

http://docs.oracle.com/cd/B19306_01/server.102/b14357/ch12019.htm

As an SQLPLUS command, DESCRIBE cannot dynamically parse an SQL statement. All the information returned by DESCRIBE is stored in the data dictionary.

Stratigraphy

Updated question: The reason I want to desc the query is because I can't create new tables or views in the db. I tried temporary tables either, but I don't have that permission. But the result of the join should have enough metadata to show this information (e.g. if I did it from an application like jdbc I would be able to), I'm hoping for an Oracle solution instead. – Galiot

Hi, I will research that also, and get back to you, OK? – Stratigraphy

The link above describes the limitations of DESCRIBE for ORACLE 10.2; I am checking other ORACLE versions of this command. – Stratigraphy

Answer 2

If you have a query that represents a set of data that you want to extract from one database and load into a different database, it would seem eminently sensible to create a view in the source database for that query. Once you have that view, you can describe the view or otherwise extract the information you are looking for from the various data dictionary tables.

And I'm assuming that there is a solid reason to prefer a custom file-based solution for replicating data from one database to another over any of the technologies Oracle provides to handle data replication. Materialized views, Streams, GoldenGate, etc. would all generally be a much better solution than writing your own.

If you're not allowed to create objects on the source database, you cannot use the SQL*Plus describe command. You could write an anonymous PL/SQL block that used the dbms_sql package to parse and describe a dynamic SQL statement. That's going to be quite a bit more complex than using the describe command and you'll have to figure out how you want to format the output. I'd use this describe_columns example as a starting point.

Loblolly

Updated question: The reason I want to desc the query is because I can't create new tables or views in the db. I tried temporary tables either, but I don't have that permission. But the result of the join should have enough metadata to show this information (e.g. if I did it from an application like jdbc I would be able to), I'm hoping for an Oracle solution instead. – Galiot

@Galiot - If you want an interface similar to what JDBC provides, you'd need to write an anonymous PL/SQL block that used the dbms_sql package. I linked to an example that you can use as a starting point. – Loblolly

Answer 3

I'm very late to answer, but anyway I'll add this for posterity.

In Oracle, you can use the DBMS_SQL package.

Usage:

1. Convert your SQL statement into a single line (replacing newline with space)
2. Replace single quote with two single quotes
3. Put the resulting value into STMT (in the below script).
4. Run the script.

SET SERVEROUTPUT ON; DECLARE STMT CLOB; CUR NUMBER; COLCNT NUMBER; IDX NUMBER; COLDESC DBMS_SQL.DESC_TAB2; BEGIN CUR := DBMS_SQL.OPEN_CURSOR; STMT := ''; SYS.DBMS_SQL.PARSE(CUR, STMT, DBMS_SQL.NATIVE); DBMS_SQL.DESCRIBE_COLUMNS2(CUR, COLCNT, COLDESC); DBMS_OUTPUT.PUT_LINE('Statement: ' || STMT); FOR IDX IN 1 .. COLCNT LOOP CASE COLDESC(IDX).col_type WHEN 2 THEN DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': NUMBER'); WHEN 12 THEN DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': DATE'); WHEN 180 THEN DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': TIMESTAMP'); WHEN 1 THEN DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': VARCHAR'); WHEN 9 THEN DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': VARCHAR2'); -- Insert more cases if you need them ELSE DBMS_OUTPUT.PUT_LINE('#' || TO_CHAR(IDX) || ': OTHERS (' || TO_CHAR(COLDESC(IDX).col_type) || ')'); END CASE; END LOOP; SYS.DBMS_SQL.CLOSE_CURSOR(CUR); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(SQLERRM(SQLCODE()) || ': ' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); SYS.DBMS_SQL.CLOSE_CURSOR(CUR); END;

I did not cover all possible data types in the below script, you can add more cases if you need them. You can find out about the data type values by using this SQL: select text from all_source where owner = 'SYS' and name = 'DBMS_TYPES' and type = 'PACKAGE'

Toad World has a nice list of the possible data types here: http://www.toadworld.com/platforms/oracle/w/wiki/3328.dbms-sql-describe-columns

I can't find the same list on Oracle except in this thread: https://community.oracle.com/thread/914475

Example: -- snip STMT := 'SELECT * FROM SYS.ALL_TAB_COLS'; -- snip Will give you: anonymous block completed Statement: SELECT * FROM SYS.ALL_TAB_COLS #1: VARCHAR2 (1) #2: VARCHAR2 (1) #3: VARCHAR2 (1) #4: VARCHAR2 (1) #5: VARCHAR2 (1) #6: VARCHAR2 (1) #7: NUMBER #8: NUMBER #9: NUMBER #10: VARCHAR2 (1) #11: NUMBER #12: NUMBER #13: VARCHAR2 (8) #14: NUMBER #15: VARCHAR2 (23) #16: VARCHAR2 (23) #17: NUMBER #18: NUMBER #19: NUMBER #20: DATE #21: NUMBER #22: VARCHAR2 (1) #23: NUMBER #24: VARCHAR2 (1) #25: VARCHAR2 (1) #26: NUMBER #27: NUMBER #28: VARCHAR2 (1) #29: VARCHAR2 (1) #30: VARCHAR2 (1) #31: VARCHAR2 (1) #32: VARCHAR2 (1) #33: NUMBER #34: NUMBER #35: VARCHAR2 (1) #36: VARCHAR2 (1)

Lagunas

Thanks that is useful. Although looking docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/… its actually varchar2 that seems to be type 1. Other useful items that can be obtained from the COLDESC are col_precision, col_scale and col_max_len. – Verbalize