Menu
I have a table with one column of CLOB type data, they are all very short no more than 20 bytes, however I cannot see the actual string in the CLOB data.
For example if I use SELECT *, under the CLOB type every data is like:
CLOB, 8 Bytes
CLOB, 15 Bytes
CLOB, 9 Bytes
But I just want to see the content of the CLOB data.
I tried:
SELECT DBMS_LOB.SUBSTR(ClobColumnName, 20 ,1)
And it doesn't work, error is:
Error Code: 4121, SQL State: S1000
Cannot find either column "DBMS_LOB" or the user-defined function or aggregate "DBMS_LOB.SUBSTR", or the name is ambiguous.
So can I ask what's the syntax for direct display a CLOB data in a query?
I'm using SQL Server with dbVisualizer.
529
You can read CLOB value (character stream data) from a table using getCharacterStream() or getClob() methods of the ResultSet interface. These methods accept an integer value representing the index of the required column (or, a String value representing its name) and, reads CLOB data from it.
CLOB values can be used with SQL Server 2005 (9. x) (or later) large-value data types. Specifically, CLOB types can be used with the varchar(max) and nvarchar(max) data types, BLOB types can be used with varbinary(max) and image data types, and NCLOB types can be used with ntext and nvarchar(max).
CLOBs require that you use the DBMS_LOB package to perform substr/instr type searches. In order to use do any kind of searching within the column, you must first get the locator for the CLOB column, the use the DBMS_LOB. SUBSTR or DBMS_LOB. INSTR function to search and/or pull part of the text from the string.
I figured out one solution. There should be better ways, please show more possible solutions in the comments.
SELECT CAST(ClobColumnName AS VARCHAR(50)) AS ClobColumnName ;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With