SQLPLUS displaying of tables with CLOB types

Question

I have a table which has 3 columns. I have a NUMBER column, CLOB column, and BLOB column. how can i use some sort of SELECT * statement in order to display what I have entered into this table, not just a partial piece of the long character strings i have in there. The only way I know of displaying a long string form a CLOB would be using the DBMS_LOB.substr technique. My BLOB column is currently all NULL so not too worried about displaying that section, Just the number column with its associated CLOB. Thanks!

What is the problem with `select number_col, clob_col from your_table`? Maybe your `long` setting is just not large enough and you need to `set long xxxxx` where `xxxxx` is a number of bytes that is larger than the largest CLOB you want to print out? — Justin Cave, Jul 26 '15 at 23:14

score 0 · Answer 1 · edited May 23 '17 at 12:06

See here How to query a CLOB column in Oracle

When getting the substring of a CLOB column and using a query tool that has size/buffer restrictions sometimes you would need to set the BUFFER to a larger size.
For example while using SQL Plus use the SET BUFFER 10000 to set it to 10000 as the default is 4000.

Running the DMBS_LOB.substr command you can also specify the amount of characters you want to return and the offset from which.
So using DMBS_LOB.substr(column, 3000) might restrict it to a small enough amount for the buffer.

See oracle documentation for more info on the substr command

DBMS_LOB.SUBSTR (
   lob_loc     IN    CLOB   CHARACTER SET ANY_CS,
   amount      IN    INTEGER := 32767,
   offset      IN    INTEGER := 1)
  RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET;

SQLPLUS displaying of tables with CLOB types

1 Answers1