2

Database: Oracle 11g

Environment: Windows server. SQLPlus.

I am trying to query for all the records where the char data length in a column is larger than 10K. The column data type is LONG (which seems to be obsolete: http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1830). Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. The following post is somewhat addressing this issue: Select something that has more/less than x character I have followed the instructions without luck.

I have tried a few variations of the ‘length’ function but still getting errors:

SQL> select * from tbl_name where LEN(notes) > 1;
select * from tbl_name where LEN(notes) > 1
                                     *
ERROR at line 1:
ORA-00904: "LEN": invalid identifier


SQL> select * from tbl_name where length(notes) > 1;
select * from tbl_name where length(notes) > 1
                                            *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Thanks in advance!

Community
  • 1
  • 1
Rotem Varon
  • 1,597
  • 1
  • 15
  • 32

1 Answers1

1

I suggest you create a function that calculate length of notes,receiving as parameter your table id (I asumme tbl_name_id) like this:

CREATE OR REPLACE function get_length(val long) return number
is
res long;
begin
    select notes into res from tbl_name where val = tbl_name_id;
    return length(res);  
end;

And then you can do this:

select * from tbl_name where get_length(tbl_name_id) > 1

You can also see here http://www.techonthenet.com/oracle/questions/long_length.php

Aramillo
  • 3,176
  • 3
  • 24
  • 49
  • Unfortunately, it seems that the length function doesn't support LONG data type (which seems to be obsolete:http://docs.oracle.com/cd/B28359_01/server.111/b28318/datatype.htm#CNCPT1830). It is limited to: CHAR, NCHAR, VARCHAR2, NVARCHAR2, CLOB, NCLOB. See here: http://www.dbtalks.com/uploadfile/dkverma87/length-function-in-oracle-sql-plus/ I wonder if you know what is the equivalent 'length' function for LONG datatype.Thanks – Rotem Varon Sep 18 '14 at 19:02
  • No, i dont't know any oracle function that can do that, that's why i look for plsql solution – Aramillo Sep 18 '14 at 19:05
  • With table_name_id i refer to the tbl_name primary key – Aramillo Sep 18 '14 at 20:26