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

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.

CodePudding user response：

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>

CodePudding user response：

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