CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperddl
Salva
Salva

Reputation: 113

how to get DDL (create query of a table) of a table from SQL Developer

am using Spool in SQL Developer to get the Output into a CSV file. where as i have heard of use of DDL command can b done to get the create query of a table.

my query is as below

    spool "path/output.csv" 
    @"path/query.sql";
spool of;

and sql is as below

set sqlformat loader
select * from table;

can u pls suggest how can i use the DDL command to spool the create query of the table in a .csv file.

Upvotes: 1

Views: 8571

Answers (2)

thatjeffsmith
thatjeffsmith

Reputation: 22427

DBMS_METADATA is the answer in general for Oracle. But, if you're using SQL Developer, like you tagged in your question, then you can simply use the DDL command.

What is DDL? It's a client command you can run in SQLcl and SQL Developer that constructs the dbms_metadata.get_ddl() code for you.

So let's:

  • set our DDL options, how do we want it to look
  • not include the commands in the spooled file
  • not include the '4 rows selected' in our spooled file
  • specify where we want the spooled file
  • start the spool, set the filename
  • ask for the ddl
  • ask for the table rows in CSV
  • turn the spool off

The Code:

clear screen
set ddl storage, segment_attributes off
set echo off
set feedback off
cd c:\users\jdsmith\desktop
spool regions.sql
ddl regions
select /*csv*/ * from regions;
spool off

enter image description here

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142713

How about

set sqlformat loader
spool "path/output.csv" 
select * from table;
spool off;

Based on comments you posted, it looks like you want to spool CREATE TABLE statement. If so, use DBMS_METADATA package, e.g.

SQL> set long 10000
SQL> set pagesize 0
SQL>
SQL> select dbms_metadata.get_ddl('TABLE', 'DEPT', 'SCOTT') create_table from dual;

  CREATE TABLE "SCOTT"."DEPT"
   (    "DEPTNO" NUMBER(2,0),
        "DNAME" VARCHAR2(14),
        "LOC" VARCHAR2(13),
         CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USER_DATA"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USER_DATA"


SQL>

Upvotes: 1

Related Questions