You Asked

Hi Tom,
I need to pull total no of rows from table: abc_tbl where column xyz_clm size in > 1000 bytes. xyz_clm has datatype Long, it's content mainly message text.

I tried with length function it did not work. Could you please hemp me?

and Connor said...

Here's an example - first we show how it fails (TEXT is a 'long' in DBA_VIEWS)

SQL> select length(text)
  2  from   dba_views
  3  where  view_name = 'DBA_OBJECTS';
select length(text)
              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected CHAR got LONG

For a one-off requirement, we can simply clone the table converting to LOB

SQL> create table DUMMY as
  2  select to_lob(text) c
  3  from   dba_views
  4  where  view_name = 'DBA_OBJECTS';

Table created.

SQL> select length(c) from dummy;

 LENGTH(C)
----------
      3244

If that is not possible or appropriate, then there's a trick I learned from a friend Adrian Billington, to extract the data as XML, then use that

SQL> WITH xml AS (
  2          SELECT XMLTYPE(
  3                    DBMS_XMLGEN.GETXML(q'{SELECT * FROM dba_views where  view_name = 'DBA_OBJECTS'}')
  4                    ) AS xml
  5          FROM   dual
  6          )
  7  ,    parsed_xml AS (
  8          SELECT extractValue(xs.object_value, '/ROW/TEXT')             AS text_as_string
  9          FROM   xml x
 10          ,      TABLE(XMLSEQUENCE(EXTRACT(x.xml, '/ROWSET/ROW'))) xs
 11          )
 12  SELECT length(text_as_string)
 13  FROM   parsed_xml
 14  /

LENGTH(TEXT_AS_STRING)
----------------------
                  3244

Hope this helps.

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

more than 4000 characters?

Tom, October 09, 2015 - 2:01 pm UTC

Wouldn't your solution have a problem with long values more than 4000 characters? I think this would work better:

WITH xml AS (
        SELECT XMLTYPE(
                  DBMS_XMLGEN.GETXML(q'{SELECT * FROM dba_views where  view_name = 'DBA_OBJECTS'}')
                  ) AS xml
        FROM   dual
        )
,    parsed_xml AS (
        SELECT text_as_clob
        FROM   xml x
        ,      XMLTABLE('/ROWSET/ROW' passing x.xml columns text_as_clob clob path 'TEXT') xs
        )
SELECT length(text_as_clob)
FROM   parsed_xml
/

October 09, 2015 - 11:31 pm UTC

Thanks for the contribution.

really more than 4000 chars?

Rajeshwaran, Jeyabal, October 12, 2015 - 2:51 pm UTC

Please see below and correct me if i am wrong.

I am on 11.2.0.4

rajesh@ORA11G> create table t as
  2  select to_lob(text) x
  3  from user_views;

Table created.

rajesh@ORA11G> select length(x) from t;

 LENGTH(X)
----------
     41009

1 row selected.

rajesh@ORA11G> WITH xml AS (
  2          SELECT XMLTYPE(
  3                    DBMS_XMLGEN.GETXML(q'{select text from user_views}')
  4                    ) AS xml
  5          FROM   dual
  6          )
  7  ,    parsed_xml AS (
  8          SELECT text_as_clob
  9          FROM   xml x
 10          ,      XMLTABLE('/ROWSET/ROW' passing x.xml columns text_as_clob clob path 'TEXT') xs
 11          )
 12  select length(text_as_clob)
 13  FROM   parsed_xml
 14  /

LENGTH(TEXT_AS_CLOB)
--------------------
                4000

1 row selected.

rajesh@ORA11G>

How ever its is clearly documented, that both XMLSEQUENCE and XMLTABLE are constrained by 4000 bytes, even the column datatype is provided as CLOB. To work on more than 4K bytes, I think we should still rely on dbms_sql for piece wise access to data or migrate to LOB (one time activity) using TO_LOB().

http://www.oracle-developer.net/display.php?id=430

October 13, 2015 - 1:26 am UTC

Agreed.

You can extend the limit up to 32767 by fetching it into a plsql variable, otherwise its back to dbms_sql, or oci.

Hope this helps.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

more than 4000 characters?

really more than 4000 chars?

More to Explore

DBMS_XMLGEN

How to determine size of a field having LONG datatype?

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

more than 4000 characters?

really more than 4000 chars?

More to Explore

DBMS_XMLGEN