Showing full response on query on sqlcl query

Question

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

score 2 · Accepted Answer · answered Nov 17 '17 at 04:33

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>

Showing full response on query on sqlcl query

1 Answers1