-1

How to export query of "DESCRIBE table" to excel file?

For example :

I have query like that "DESCRIBE Mst_Fi_Bond_Issuers;" and i have result like that : enter image description here

I want export that automatically to excel table. Please help me. Thanks..

kumala nindya
  • 69
  • 1
  • 1
  • 10
  • Please show us what you've tried already, how it failed and we might be able to help. – Nitish Apr 12 '16 at 09:57
  • Describe is a client command; maybe you need a query that mimics it ([here's an example](http://stackoverflow.com/a/28581465/266304)). You haven't said how you're connecting Excel and Oracle, if you want a CSV dump or via a linked query, etc. 'Export' is a bit vague. – Alex Poole Apr 12 '16 at 09:59

2 Answers2

0

You could use an SQL block like this: [please edit as you see fit]

DECLARE
  createdir VARCHAR2(2000);
  directory NUMBER;
  filen VARCHAR2(50);
  dirn VARCHAR2(50);
  filedat UTL_FILE.file_type;

BEGIN    
  dirn := 'DESC';
  filen := 'yourfilename.csv';

  createdir := q'{create directory DESC as '[your directory]'}';
  execute immediate (createdir);

  filedat := UTL_FILE.fopen(dirn, filen, 'W', 32767);
  UTL_FILE.put_line (filedat, 'NAME;TYPE');

  for rowdat in (select column_name || ';' || data_type currow from dba_tab_columns)
  LOOP
    UTL_FILE.put_line (filedat, rowdat.currow);
  END LOOP;
  UTL_FILE.fclose (filedat);    
END;
/
jera
  • 302
  • 4
  • 30
0

As jera already said, it´s much better to do a query and write results to a file, because you have much more flexibility in gathering informations and formatting output.

But maybe due to restrictions, your not able to access the dba_... Objects. Instead of that, have a look on the USER_... - Views, which are always available to a oracle-user

Everything you want to know can be found there (f.e. like USER_TABLES. USER_TAB_COLS, USER_PROCEDURES, etc.).

oratom
  • 271
  • 1
  • 5