Clob to varchar conversion not working in 11G

Hi I am using 11G oracle database. I have imported big history in my table using  clob datatype but now I want only 4000varchar so I can show those 4000 char, I am trying to cast that to 4000 char and want to insert in new table but its not working, How I can do that.
SudhanshumAsked:
Who is Participating?
 
SujithData ArchitectCommented:
You can package the PL/SQL logic in a function to do the insert/select.

Something like -

SQL> create or replace function my_lob_substr
  2  (
  3      p_clob  clob
  4  )
  5  return varchar2
  6  as
  7      l_str   varchar2(16000);
  8      l_str2  varchar2(4000);
  9  begin
 10      l_str   := substr(p_clob, 1, 4000);
 11      l_str2  := substrb(l_str, 1, 4000);
 12      return l_str2;
 13  end;
 14  /

Function created.

SQL> insert into tbl2 select my_lob_substr(val) from    tbl1;

3 rows created.

SQL> select lengthb(val), length(val), substr(val,1,1) first_char from tbl2 t;

LENGTHB(VAL) LENGTH(VAL) FIRS
------------ ----------- ----
        4000        4000 a
        4000        1334 +
        4000        2000 ß

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
We definitely need more information and some code snippets!
0
 
SujithData ArchitectCommented:
Use DBMS_LOB.SUBSTR
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
SujithData ArchitectCommented:
And yes - you need to be specific what is not working -

here is an example to get the substring out of a CLOB

SQL> desc tbl2;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- --------------
 VAL                                                            VARCHAR2(4000)

SQL>
SQL> insert into tbl2 select dbms_lob.substr(val, 4000) from tbl1;

1 row created.

SQL>
SQL> insert into tbl2 select substr(val, 1, 4000) from tbl1;

1 row created.

SQL>
SQL> select  length(val) from tbl2;

LENGTH(VAL)
-----------
       4000
       4000

Open in new window

0
 
SudhanshumAuthor Commented:
When I tried to import data in clob column then it worked fine but when I tried to use command like below
select CAST(DetailsAS VARCHAR(4000)) from table
Then it thrown error :
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 12475, maximum: 4000)

Then I tried to use below command but it also thrown error:
insert into HTABLE
(
DETAILS
)
SELECT dbms_lob.substr(details, 3900), FROM HTABLE2

AND GOT This error ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1

I am not sure how to fix that.
0
 
SujithData ArchitectCommented:
can you post the output of

desc HTABLE
desc HTABLE2
0
 
SudhanshumAuthor Commented:
Table1
CONTACTID      1            Y      VARCHAR2 (500 Byte)            None                        
CONTACT      2            Y      VARCHAR2 (500 Byte)            None                        
CREATIONDATE      3            Y      DATE            None                        
CREATEUSERID      4            Y      VARCHAR2 (500 Byte)            None                        
CREATEDBY      5            Y      VARCHAR2 (500 Byte)            None                        
EDITDATE      6            Y      DATE            None                        
ENDDATE      7            Y      DATE            None                        
MANAGEUSERID      8            Y      VARCHAR2 (500 Byte)            None                        
RECORDMANAGER      9            Y      VARCHAR2 (500 Byte)            None                        
REGARDING      10            Y      VARCHAR2 (500 Byte)            None                        
DETAILS      11            Y      CLOB            None                        


Table2
HISTORYID      1            Y      NUMBER            None                        
DETAILS      2            Y      VARCHAR2 (4000 Byte)            None                        
CONTACTID      3            Y      VARCHAR2 (1000 Byte)            None                        
CONTACT      4            Y      VARCHAR2 (1000 Byte)            None                        
CREATEUSERID      5            Y      VARCHAR2 (1000 Byte)            None                        
CREATEDBY      6            Y      VARCHAR2 (1000 Byte)            None                        
CREATIONDATE      7            Y      DATE            None                        
EDITDATE      8            Y      DATE            None
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
Can you please post the output of
show parameter nls

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
It is likely a multi-byte character set.

Can you change the destination table?

from:
DETAILS      2            Y      VARCHAR2 (4000 Byte)

to
DETAILS      2            Y      VARCHAR2 (4000 char)
0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
That's exactly what I wanted to confirm ;-)
NLS length semantics come into play here...
0
 
SujithData ArchitectCommented:
Yes, looks like you have multi-byte data in the CLOB.

You can try to  use PL/SQL, where the local variables doesn't have the character string buffer limitation while using sbustring function on the LOB as in SQL.

Try something like this -

declare
    l_clob  clob;
    l_str   varchar2(4000);
    cursor c1 is select  dbms_lob.substr(val,4000) from    tbl1;
begin
    open c1;

    loop
        fetch c1 into l_clob;
        exit when c1%notfound;
        l_str   := substr(l_clob, 1, 4000);
        insert into tbl2 values(l_str);
    end loop;
    
    close c1;
end;
/

Open in new window



Here is an example  -
SQL> select   cast(substr(val, 1,1) as varchar2(20)) first_char_in_col
  2          ,lengthb(cast(substr(val, 0,1) as varchar2(20))) byte_length_of_first_char
  3          ,length(val) char_length_of_col
  4  from tbl1;

FIRST_CHAR_IN_COL    BYTE_LENGTH_OF_FIRST_CHAR CHAR_LENGTH_OF_COL
-------------------- ------------------------- ------------------
a                                            1               4000
有                                            3               1334
á                                            2               2000

SQL>
SQL> declare
  2      l_clob  clob;
  3      l_str   varchar2(4000);
  4      cursor c1 is select  dbms_lob.substr(val,4000) from    tbl1;
  5  begin
  6      open c1;
  7
  8      loop
  9          fetch c1 into l_clob;
 10          exit when c1%notfound;
 11          l_str   := substr(l_clob, 1, 4000);
 12          insert into tbl2 values(l_str);
 13      end loop;
 14
 15      close c1;
 16  end;
 17  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select  substr(val, 1,1) first_char_In_target, length(val)
  2  from    tbl2;

FIRS LENGTH(VAL)
---- -----------
a           4000
有           1334
á           2000

Open in new window

0
 
Alexander Eßer [Alex140181]Software DeveloperCommented:
You can try to  use PL/SQL, where the local variables doesn't have the character string buffer limitation
l_str   varchar2(4000);

Open in new window

?!? Here, you limit l_str to the size of 4000 ?!?
Very contradictive...

Addon, nice to know/to keep in mind when dealing with varchar2 variables:
https://logicalread.com/oracle-plsql-varchar2-memory-allocation-mc08/#.WvXmdH9CSUk
0
 
SujithData ArchitectCommented:
I pumped in a bit more data with some 4 byte characters.
Here is the version I have now (There could be better/elegant ways to achieve this, but this works)

declare
    l_clob  clob;
    l_str   varchar2(16000);
    l_str2  varchar2(4000);
    cursor c1 is select  val from    tbl1;
begin
    open c1;

    loop
        fetch c1 into l_clob;
        exit when c1%notfound;
        l_str   := substr(l_clob, 1, 4000);
        l_str2  := substrb(l_str, 1, 4000);
        insert into tbl2 values(l_str2);
    end loop;

    close c1;
end;
/

Open in new window

0
 
SudhanshumAuthor Commented:
I want to use that clob in one query to bind data with grid. My query is like

SELECT H.HISTORYID,H.USERID,dbms_lob.substr(H.DETAILS,4000),H.REGARDING,H.CREATIONDATE,(CASE WHEN (H.CREATEDUSERID IS NULL OR H.CREATEDUSERID=0) THEN 'NA' ELSE U.USERNAME END)CREATEUSER,
CASE WHEN H.CREATIONDATE-H.UPDATEDDATE =0 THEN 'NA' ELSE to_char(H.UPDATEDDATE,'mm/dd/yyyy hh:mi:ss AM'  ) END  UPDATEDDATE FROM HISTORYUSERS H
LEFT OUTER JOIN MYUSERS U ON U.USERID=H.CREATEDUSERID WHERE H.USERID='####'
ORDER BY H.CREATIONDATE

When particular user has around 10K history records  then It throw this error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


 How to use your mentioned approach in my query, Please help and suggest best i should do. Also if i reduce LOB size then will it help?
Is there any way to display 4000 char no matter how many num of rows exist.
0
 
SudhanshumAuthor Commented:
Here is results :

SQL> show parameter nls

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_calendar                         string      GREGORIAN
nls_comp                             string      BINARY
nls_currency                         string      $
nls_date_format                      string      DD-MON-RR
nls_date_language                    string      AMERICAN
nls_dual_currency                    string      $
nls_iso_currency                     string      AMERICA
nls_language                         string      AMERICAN
nls_length_semantics                 string      BYTE
nls_nchar_conv_excp                  string      FALSE
nls_numeric_characters               string      .,

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      BINARY
nls_territory                        string      AMERICA
nls_time_format                      string      HH.MI.SSXFF AM
nls_time_tz_format                   string      HH.MI.SSXFF AM TZR
nls_timestamp_format                 string      DD-MON-RR HH.MI.SSXFF AM
nls_timestamp_tz_format              string      DD-MON-RR HH.MI.SSXFF AM TZR
SQL>
0
 
slightwv (䄆 Netminder) Commented:
>>SQL> show parameter nls

I don't believe that shows what was intended.  I think Alex wanted to know the character set of the database.  That is:
col parameter form a30
col value form a30
select * from nls_database_parameters;

The belief is you are running a multi-byte character set.  The CLOB has unicode or multi-btye characters in it so, a single character can be several bytes.

Your table is defined as 4000 BYTES so a 2byte characterset can only store 2000 characters.  A 3byte character set 1333.

>>Is there any way to display 4000 char no matter how many num of rows exist.

Yes.  Change the table to 4000 CHAR not BYTE.


>> bind data with grid

When I hear BIND and GRID I think .Net.  What is the actual problem you are trying to solve here?  If it is how to pull a CLOB into some GUI/website, there is likely a much better way than losing data by truncating the CLOB to 4000 characters.
0
 
SudhanshumAuthor Commented:
For now I dont want to store clob to varchar column, for Now I want to run select query and there i want to show 4000 char from clob column, It's not working properly using my query. when data is less then it works otherwise it failed. Here is my query ad error I am getting:

SELECT H.HISTORYID,H.USERID,dbms_lob.substr(H.DETAILS,4000),H.REGARDING,H.CREATIONDATE,(CASE WHEN (H.CREATEDUSERID IS NULL OR H.CREATEDUSERID=0) THEN 'NA' ELSE U.USERNAME END)CREATEUSER,
CASE WHEN H.CREATIONDATE-H.UPDATEDDATE =0 THEN 'NA' ELSE to_char(H.UPDATEDDATE,'mm/dd/yyyy hh:mi:ss AM'  ) END  UPDATEDDATE FROM HISTORYUSERS H
LEFT OUTER JOIN MYUSERS U ON U.USERID=H.CREATEDUSERID WHERE H.USERID='####'
ORDER BY H.CREATIONDATE

When particular user has around 10K history records  then It throw this error:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1
0
 
SudhanshumAuthor Commented:
when I am querying for particular User, if he has less history then above query work fine but if user has lots of history data then it get failed, what can be best way to fetch data without any Errors. I am really frustrated to get correct solution.
0
 
slightwv (䄆 Netminder) Commented:
Try normal substr:
SELECT H.HISTORYID,H.USERID,substr(H.DETAILS,1,4000),H.REGARDING,H.CREATIONDATE,(CASE WHEN (H.CREATEDUSERID IS NULL OR H.CREATEDUSERID=0) THEN 'NA' ELSE U.USERNAME END)CREATEUSER,
CASE WHEN H.CREATIONDATE-H.UPDATEDDATE =0 THEN 'NA' ELSE to_char(H.UPDATEDDATE,'mm/dd/yyyy hh:mi:ss AM'  ) END  UPDATEDDATE FROM HISTORYUSERS H
LEFT OUTER JOIN MYUSERS U ON U.USERID=H.CREATEDUSERID WHERE H.USERID='####'
ORDER BY H.CREATIONDATE

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
Here is my test setup:
drop table tab1 purge;
create table tab1(col1 clob);

declare 
	junk clob;
begin
	dbms_lob.createtemporary(junk,true);
	for i in 1..4001 loop
		dbms_lob.writeappend(junk,1,'有');
	end loop;
	insert into tab1 values(junk);
	commit;
	dbms_lob.freetemporary(junk);
end;
/

select dbms_lob.getlength(col1) from tab1;

select substr(col1,1,4000) from tab1;

Open in new window


My results:
BMS_LOB.GETLENGTH(COL1)
------------------------
                    4001


SUBSTR(COL1,1,4000)                                                             
--------------------------------------------------------------------------------
有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有有

Open in new window

Of course it doesn't show all 4000 characters
0
 
SudhanshumAuthor Commented:
Actually in select query if I just taking clob column then I guess select query works but it gets failed when multiple  columns included in select query, I dont understand what to do but I will try substr. Just to be sure that I just need to use substr nothing more. correct?
0
 
SudhanshumAuthor Commented:
I used your mentioned SUBSTR and query worked fine without error but in that column its not showing characters its just showing (HugeClob) in all rows, How I can show it properly.
0
 
slightwv (䄆 Netminder) Commented:
>> but in that column its not showing characters its just showing (HugeClob) in all rows

That is the tool you are using not the query itself.  If you use sqlplus, you will see the data.  I would look at the documentation for the tool you are using to see how to display the contents of the field.
0
 
SujithData ArchitectCommented:
You can't use SUBSTR in an SQL context to insert into the second table -

SQL> select  to_char(substr(val, 1,1)) first_char, length(val), substr(val, 4000)
  2  from    tbl1;

FIRS LENGTH(VAL) SUBSTR(VAL,4000)
---- ----------- --------------------------------------------------------------------------------
a         128000 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa
+          42688 ++ +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
ß          64000 ßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßßß

SQL> drop table tbl2;

Table dropped.

SQL>
SQL> create table tbl2(val varchar2(4000 byte));

Table created.

SQL> insert into tbl2 select  substr(val, 4000) from    tbl1;
insert into tbl2 select  substr(val, 4000) from    tbl1
                         *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 124001, maximum:
4000)


SQL> drop table tbl2;

Table dropped.

SQL>
SQL> create table tbl2(val varchar2(4000 char));

Table created.

SQL> insert into tbl2 select  substr(val, 4000) from    tbl1;
insert into tbl2 select  substr(val, 4000) from    tbl1
                         *
ERROR at line 1:
ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 124001, maximum:
4000)

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
>>insert into the second table -

They aren't really wanting to insert into a second table.  That was just a possible work-around for the problem of not being able to query it using dbms_lob.

from #a42562706
For now I dont want to store clob to varchar column, for Now I want to run select query

Anyway, the insert without pl/sql works for me:
https://livesql.oracle.com/apex/livesql/file/content_GOTCX25ELQ6SII9YUA9QVG3BR.html
0
 
SujithData ArchitectCommented:
>> Anyway, the insert without pl/sql works for me:
Because the LOB data is small. Try changing the width to 6000 or so.
0
 
slightwv (䄆 Netminder) Commented:
The select substr worked for the OP so the insert should as well.

>>Try changing the width to 6000 or so.

It works with 6000.  NOW, it does have problems when the LOB data exceeds 32K.  If they have CLOBs approaching that length, we may need a work-around.
0
 
slightwv (䄆 Netminder) Commented:
It works for me using 40,000 characters.  You had bad syntax for SUBSTR.  I just did a copy/paste without looking closely.

insert into tab2 select substr(col1, 1, 4000) from tab1;
0
 
SudhanshumAuthor Commented:
40,000 or 4000?
0
 
slightwv (䄆 Netminder) Commented:
>>40,000 or 4000?

@Sudhanshum,
Go with what I posted.

The 40,000 was a sample to show Sujith that it works for me and he had a syntax issue.
0
 
SujithData ArchitectCommented:
>>

SQL> select  to_char(substr(val, 1,1)) first_char, length(val), substr(val, 4000)
  2  from    tbl1;

FIRS LENGTH(VAL) SUBSTR(VAL,4000)
---- ----------- --------------------------------------------------------------------------------
+           6000 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

SQL> insert into tbl2 select  substr(val, 1, 4000) from    tbl1;
insert into tbl2 select  substr(val, 1, 4000) from    tbl1
                         *
ERROR at line 1:
ORA-64203: Destination buffer too small to hold CLOB data after character set conversion.

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
@Sujith

I don't wish to hijack this question.  They don't need the insert so if it works or not doesn't matter.

>>Destination buffer too small to hold CLOB data after character set conversion.

Try changing your destination table to CHAR instead of BYTE.
create table tbl2(val varchar2(4000 CHAR));

If you have a liveSQL link where it still fails, please PM me with it and we can continue this without hijacking the question.
0
 
SujithData ArchitectCommented:
Very well. If insert is not required.

But keep in mind, the substr is going to return the datata as Type CLOB. so the application has to change the binding to CLOB anyway.
0
 
slightwv (䄆 Netminder) Commented:
Then cast it?

select cast(substr(col1,1,4000) as varchar2(4000)) from tab1;
select to_char(substr(col1,1,4000)) from tab1;

That should also fix the tool not displaying the LOB.
0
 
SujithData ArchitectCommented:
Any LOB data which return more than 4000 bytes in the SUBSTR can't be cast to a VARCHAR2.
0
 
slightwv (䄆 Netminder) Commented:
Bytes or characters?  I tested what I posted and it works.  If you have a test case, prefer in LiveSQL so the environment is the same, I'll take a look at it.
0
 
SujithData ArchitectCommented:
LiveSQL is running on 18c. The limit of 4k bytes is lifted from 12c onwards.

https://docs.oracle.com/database/121/REFRN/GUID-963C79C9-9303-49FE-8F2D-C8AAF04D3095.htm#REFRN0041
0
 
SujithData ArchitectCommented:
@Sudhanshum - thanks for closing the thread.

This was quite an interactive discussion between me and slightwv, can you consider splitting the points between us if that is possible now?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.