oracle-databasesqlcl

Showing full response on query on sqlcl query


I'm trying to use sqlcl on a remote server to retrieve the table definition of the tables in given oracle database.
Since I have to use the cli there, I can't simply use SQLDeveloper and get the table definitions out of there (can't connect to DB from outside of the server).

The idea is to use

select dbms_metadata.get_ddl('TABLE', table_name)
from user_tables where table_name = 'RESULTS';

in order to get the table DDL for the table I need.
However, when I run this query in the sqlcl tool, the output looks like this:

 CREATE TABLE "AP29_QUDB"."RESULTS" 
 (  "LOCATION_RESULT_UID" RAW(16) NOT NU

That's it, no more information.

Does someone know how I get the full result of that query to be displayed?
(Or alternatively simply pipe it to a file for me to copy it out of there)

Thank you very much
- Tim


Solution

  • Looks like sqlcl copied the LONG setting from SQL*Plus. By default it only shows the first 80 characters of a CLOB. That size can be changed with set long [some large number]'.

    SQL> select dbms_metadata.get_ddl('TABLE', 'DUAL', 'SYS') from dual;
    
    DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS')
    --------------------------------------------------------------------------------
    
      CREATE TABLE "SYS"."DUAL" SHARING=METADATA
       (    "DUMMY" VARCHAR2(1)
       ) PCT
    
    
    SQL> set long 10000000
    SQL> /
    
    DBMS_METADATA.GET_DDL('TABLE','DUAL','SYS')
    --------------------------------------------------------------------------------
    
      CREATE TABLE "SYS"."DUAL" SHARING=METADATA
       (    "DUMMY" VARCHAR2(1)
       ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     NOCOMPRESS LOGGING
      STORAGE(INITIAL 16384 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
      PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
      BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
      TABLESPACE "SYSTEM"
    
    
    SQL>