We help IT Professionals succeed at work.

oracle sqlplus get whole column characters out

xiaoyunwu
xiaoyunwu asked
on
I'm using sqlplus to select longdesc_col from table_name, but longdesc_col gets cut-off in the middle.
what set command should I use to get all of them out?
Comment
Watch Question

Commented:
use

sqlplus > col longdesc_col for a100

then run the sql

Commented:
What is the size of longdesc_col?

If its 100 then try the following if not,

sqlplus> col longdesc_col for a<specify sizeof the column>
Top Expert 2011

Commented:

Author

Commented:
it does not seem to work


sqlplus -s $db_user/$db_passwd@$db_instance<<EOF >t.txt
col longdesc for A1000
set pages 0 flush off feed off feedback off echo off verify off
set term off head off trim on
select longdesc from table_name where rownum<5;
EOF

Author

Commented:
if I use set linesize 1000, it will work with normal column
but this column from a view and this view was xml, so I'm not sure if that affect the result.
Top Expert 2011
Commented:
- try this:

SET PAGESIZE 0 ECHO OFF;
SET LINESIZE 30000 LONG 30000 LONGCHUNKSIZE 30000 TRIMSPOOL ON;
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>but this column from a view and this view was xml

XML follows the LONG sqlplus setting.  using the example above:  set long <some large size>

Author

Commented:

>>SET PAGESIZE 0 ECHO OFF;
>>SET LINESIZE 30000 LONG 30000 LONGCHUNKSIZE 30000 TRIMSPOOL ON;

This worked.

set linesize 1000
worked with
select longdesc from table_name where rownum<5;

But it does not work with
select xmlelement(test_xml, longdesc) from table_name where rownum<5

But the above worked with both. Why?

Author

Commented:
Thank you all!
Top Expert 2011

Commented:
- glad it able to help :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.