I have some queries to find out the ddl of some objects from a schema. The result columns I am getting are truncated in the middle of the queries.
How can I increase the width of the column?
I tried with
SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 50000;
set pagesize 50000;
set long 50000;
But I'm still getting the same result.
This question is related to
sql
oracle
sqlplus
column-width
Try this
COLUMN col_name FORMAT A24
where 24 is you width.
This configuration is working for me:
set termout off
set verify off
set trimspool on
set linesize 200
set longchunksize 200000
set long 200000
set pages 0
column txt format a120
The column
format definition with the linesize
option helped to avoid the truncation at 80 chars.
What I use:
set long 50000
set linesize 130
col x format a80 word_wrapped;
select dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA') x from dual;
Or am I missing something?
On Windows you may try this:
None of these suggestions were working for me. I finally found something else I could do - dbms_output.put_line
. For example:
SET SERVEROUTPUT ON
begin
for i in (select dbms_metadata.get_ddl('INDEX', index_name, owner) as ddl from all_indexes where owner = 'MYUSER') loop
dbms_output.put_line(i.ddl);
end loop;
end;
/
Boom. It printed out everything I wanted - no truncating or anything like that. And that works straight in sqlplus
- no need to put it in a separate file or anything.
This worked like a charm for me with a CLOB column:
set long 20000000
set linesize 32767
column YOUR_COLUMN_NAME format a32767
select YOUR_COLUMN_NAME from YOUR_TABLE;
On Linux try these:
set wrap off
set trimout ON
set trimspool on
set serveroutput on
set pagesize 0
set long 20000000
set longchunksize 20000000
set linesize 4000
Additionally to setting the LINESIZE, as LordScree suggested, you could also specify to output to a file, to overcome the problem with the console width. Here's how I do it:
set linesize 15000;
spool myoutput.txt;
SELECT
...
spool off;
Actually, even that didn't work for me. When I executed "select dbms_metadata.get_ddl('TABLESPACE','TABLESPACE_NAME') from dual;" I again got only the first three lines, but this time each line was padded out to 15,000 characters. I was able to work around this with:
select substr(dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA'),80) from dual;
select substr(dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA'),160) from dual;
select substr(dbms_metadata.get_ddl('TABLESPACE','LM_THIN_DATA'),240) from dual;
It sure seemed like there ought to be an easier way, but I couldn't seem to find it.
Source: Stackoverflow.com