How to show the full result of the query in SQL Developer?

I want to see the query used to create the table DOCTORS in SQL Developer with

SELECT dbms_metadata.get_ddl('TABLE', 'DOCTORS')
FROM dual;

Then it shows only part of the query.

DBMS_METADATA.GET_DDL('TABLE','DOCTORS')                                        
--------------------------------------------------------------------------------

  CREATE TABLE "C##AKIRA"."DOCTORS" 
   (    "CODED" NUMBER(2,0),  
    "NAMED" VARCHA

I suspect this is due to some displaying setting. Could you please elaborate on how to show the full result?

-- Drop tables if they already exist
BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE works_in';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE depends';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE wards';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;
/

BEGIN
    EXECUTE IMMEDIATE 'DROP TABLE doctors';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE != -942 THEN
            RAISE;
        END IF;
END;
/

-- Create the doctors database
CREATE TABLE doctors (
    coded       NUMBER(2),
    named       VARCHAR2(15),
    speciald    NUMBER(2),
    PRIMARY KEY (coded)
);

CREATE TABLE wards (
    codew       NUMBER(2),
    namew       VARCHAR2(15),
    coded       NUMBER(2),
    PRIMARY KEY(codew),
    FOREIGN KEY (coded) REFERENCES doctors (coded)
);

CREATE TABLE works_in (
    coded       NUMBER(2),
    codew       NUMBER(2),
    datewi      DATE,
    hourcount   NUMBER(2),
    PRIMARY KEY (coded, codew, datewi),
    FOREIGN KEY (coded) REFERENCES doctors (coded),
    FOREIGN KEY (codew) REFERENCES wards (codew)
);

CREATE TABLE depends (
    codew_parent        NUMBER(2),
    codew_child         NUMBER(2),
    PRIMARY KEY (codew_parent, codew_child),
    FOREIGN KEY (codew_parent) REFERENCES wards (codew),
    FOREIGN KEY (codew_child) REFERENCES wards (codew)
);

-- Show the schema of a table
SELECT dbms_metadata.get_ddl('TABLE', 'DOCTORS')
FROM dual;

Answer

try putting

set long 1000000 longc 100000 pages 10000

before the select dbms_metadata…. It tells sqldev (and sqlplus/sqlcl also) to fetch 1MB of data and not put headers until after 10000 lines

Advertisement

Answer