Home » SQL & PL/SQL » SQL & PL/SQL » Inserting Data from Clob to Varchar2.
icon4.gif  Inserting Data from Clob to Varchar2. [message #188716] Mon, 21 August 2006 05:19 Go to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

Dear All,

I have to Insert data from one table to another table.
First table contains some clob datatype columns and second table have carchar2 datatype.
Below are the structures of both tables;

SQL> DESC TBL_MAIN				SQL> DESC TBL_MAIN2                           
Name                          Type        	Name                          Type            
----------------------------- ------------	----------------------------- --------------  
TXT_FEIN_NUMBER               VARCHAR2(9) 	TXT_FEIN_NUMBER               VARCHAR2(9)     
TXT_QUOTE_NUMBER              VARCHAR2(12)	TXT_QUOTE_NUMBER              VARCHAR2(12)    
TXT_POLICY_NUMBER             VARCHAR2(12)	TXT_POLICY_NUMBER             VARCHAR2(12)    
TXT_AGENT_CODE                VARCHAR2(10)	TXT_AGENT_CODE                VARCHAR2(10)    
TXT_USER_CODE                 VARCHAR2(3) 	TXT_USER_CODE                 VARCHAR2(3)     
TXT_INSURED_NAME              VARCHAR2(60)	TXT_INSURED_NAME              VARCHAR2(60)    
TXT_DBA                       VARCHAR2(50)	TXT_DBA                       VARCHAR2(50)    
TXT_ADDRESS1                  VARCHAR2(50)	TXT_ADDRESS1                  VARCHAR2(50)    
TXT_ADDRESS2                  VARCHAR2(50)	TXT_ADDRESS2                  VARCHAR2(50)    
TXT_CITY                      VARCHAR2(20)	TXT_CITY                      VARCHAR2(20)    
TXT_STATE                     VARCHAR2(2) 	TXT_STATE                     VARCHAR2(2)     
TXT_ZIP                       VARCHAR2(10)	TXT_ZIP                       VARCHAR2(10)    
DAT_SUBMISSION_DATE           DATE        	DAT_SUBMISSION_DATE           DATE            
DAT_COMPLETE_SUBMISSION       DATE        	DAT_COMPLETE_SUBMISSION       DATE            
DAT_EFFECTIVE_DATE            DATE        	DAT_EFFECTIVE_DATE            DATE            
DAT_EXPIRATION_DATE           DATE        	DAT_EXPIRATION_DATE           DATE            
TXT_GOV_CLASS                 VARCHAR2(20)	TXT_GOV_CLASS                 VARCHAR2(20)    
NUM_DEPOSIT                   FLOAT       	NUM_DEPOSIT                   FLOAT           
NUM_XMOD                      FLOAT       	NUM_XMOD                      FLOAT           
NUM_SCHEDULE_CREDIT           FLOAT       	NUM_SCHEDULE_CREDIT           FLOAT           
TXT_ASSOCIATION_CODE          VARCHAR2(50)	TXT_ASSOCIATION_CODE          VARCHAR2(50)    
TXT_POLICY_TYPE               VARCHAR2(50)	TXT_POLICY_TYPE               VARCHAR2(50)    
NUM_RETRO_MIN                 FLOAT       	NUM_RETRO_MIN                 FLOAT           
MEM_DESC_OF_OPS               CLOB        	MEM_DESC_OF_OPS               CLOB            
MEM_XMOD_DESC                 CLOB        	MEM_XMOD_DESC                 CLOB            
MEM_LOSS_CONTROL_DESC         CLOB        	MEM_LOSS_CONTROL_DESC         VARCHAR2(4000)  
MEM_CLAIMS_DESC               CLOB        	MEM_CLAIMS_DESC               VARCHAR2(4000)  
MEM_SPECIAL_CONCERNS          CLOB        	MEM_SPECIAL_CONCERNS          VARCHAR2(4000)  
MEM_RECOMMENDATIONS           CLOB        	MEM_RECOMMENDATIONS           VARCHAR2(4000)  



The Table contains 5000 records. Please any one give me soluiton about how to convert CLOB to VARCHAR2.

I used DBMS_LOB.SUBSTR but I received BUFFER TO SMALL ERROR.

Thanks in advance,
Prathamesh.
Re: Inserting Data from Clob to Varchar2. [message #188717 is a reply to message #188716] Mon, 21 August 2006 05:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you could actually show us what you've tried, and what the problems were it would help.

In general, it sounds like your approach is correct. Of course, if theres more then 4000 bytes in any of the CLOBS then they won't all fit into the VARCHAR2 field.
Re: Inserting Data from Clob to Varchar2. [message #188722 is a reply to message #188717] Mon, 21 August 2006 05:37 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

I want to transfer only first 4000 bytes.

Here are the coe that I am doing.

create or replace procedure prc_trancefer_data
as
cursor c1 is
select txt_fein_number, txt_quote_number, txt_policy_number, txt_agent_code, txt_user_code, txt_insured_name, 
txt_dba, txt_address1, txt_address2, txt_city, txt_state, txt_zip, dat_submission_date, dat_complete_submission, 
dat_effective_date, dat_expiration_date, txt_gov_class, num_deposit, num_xmod, num_schedule_credit, txt_association_code, 
txt_policy_type, num_retro_min, substr(mem_desc_of_ops,1,4000) mem_desc_of_ops, substr(mem_xmod_desc,1,4000) mem_xmod_desc, 
substr(mem_loss_control_desc,1,4000) mem_loss_control_desc, substr(mem_claims_desc,1,4000) mem_claims_desc, 
substr(mem_special_concerns,1,4000) mem_special_concerns, substr(mem_recommendations,1,4000) mem_recommendations
from tbl_main
order by txt_fein_number;

begin
for i in c1 loop
insert into tbl_main2
values (i.txt_fein_number, 
i.txt_quote_number, i.txt_policy_number, i.txt_agent_code, i.txt_user_code, i.txt_insured_name, i.txt_dba, 
i.txt_address1, i.txt_address2, i.txt_city, i.txt_state, i.txt_zip, i.dat_submission_date, i.dat_complete_submission, 
i.dat_effective_date, i.dat_expiration_date, i.txt_gov_class, i.num_deposit, i.num_xmod, i.num_schedule_credit, 
i.txt_association_code, i.txt_policy_type, i.num_retro_min, i.mem_desc_of_ops, i.mem_xmod_desc,i.mem_loss_control_desc,
i.mem_claims_desc, i.mem_special_concerns, i.mem_recommendations);
end loop;
commit;
end;




Re: Inserting Data from Clob to Varchar2. [message #188724 is a reply to message #188722] Mon, 21 August 2006 05:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What is the error you are getting?

My guess would be that if you're using SUBSTR as opposed to DBMS_LOB.SUBSTR, then some of your CLOBS have more than 32K of data in them. That would probably cause problems with SUBSTR.
Re: Inserting Data from Clob to Varchar2. [message #188725 is a reply to message #188724] Mon, 21 August 2006 05:43 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

when this procedure executed then I received following error;


ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion (actual: 8000, maximum: 4000)



And when I used DBMS_LOB.SUBSTR the I received
numeric value error: Buffer too small error
Re: Inserting Data from Clob to Varchar2. [message #188733 is a reply to message #188725] Mon, 21 August 2006 06:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Would you be using a multibyte character set by any chance?
If so, each chr takes up 2 (or more) bytes of the string, and you'lll need to drop the amount you take over to 2000 bytes.
Re: Inserting Data from Clob to Varchar2. [message #188734 is a reply to message #188733] Mon, 21 August 2006 06:27 Go to previous messageGo to next message
loveoracle
Messages: 41
Registered: February 2006
Location: Mumbai
Member

Here are the NLS parameter values;

PARAMETER                      VALUE
------------------------------ ---------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
[COLOR=red]NLS_CHARACTERSET               AL32UTF8[/COLOR]
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
[COLOR=red]NLS_LENGTH_SEMANTICS           BYTE[/COLOR]
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0
Re: Inserting Data from Clob to Varchar2. [message #188755 is a reply to message #188734] Mon, 21 August 2006 08:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Doesn't look like a NLS multi byte problem then.

Can you check the ORA error you're getting - I can't find any references to an ORA-22835
Re: Inserting Data from Clob to Varchar2. [message #188787 is a reply to message #188725] Mon, 21 August 2006 13:15 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
How are you using dbms_lob.substr? It is a common error not to realize that the parameters are the reverse of those in substr. So, instead of "substr(column_name,1,4000)" you would use "dbms_lob.substr(column_name,4000,1)".
Re: Inserting Data from Clob to Varchar2. [message #188848 is a reply to message #188787] Tue, 22 August 2006 02:04 Go to previous messageGo to next message
gbarbisan
Messages: 67
Registered: August 2006
Location: Treviso - Italy
Member
Think Barbara is right.
Why don't you try:

CREATE OR REPLACE PROCEDURE prc_trancefer_data AS
BEGIN

  INSERT INTO TBL_MAIN2
    (SELECT TXT_FEIN_NUMBER, TXT_QUOTE_NUMBER, TXT_POLICY_NUMBER, TXT_AGENT_CODE, TXT_USER_CODE,
            TXT_INSURED_NAME, TXT_DBA, TXT_ADDRESS1, TXT_ADDRESS2, TXT_CITY, TXT_STATE, TXT_ZIP,
            DAT_SUBMISSION_DATE, DAT_COMPLETE_SUBMISSION, DAT_EFFECTIVE_DATE, DAT_EXPIRATION_DATE,
            TXT_GOV_CLASS, NUM_DEPOSIT, NUM_XMOD, NUM_SCHEDULE_CREDIT, TXT_ASSOCIATION_CODE,
            TXT_POLICY_TYPE, NUM_RETRO_MIN, SUBSTR(MEM_DESC_OF_OPS, 1, 4000) MEM_DESC_OF_OPS,
            SUBSTR(MEM_XMOD_DESC, 1, 4000) MEM_XMOD_DESC,
            DBMS_LOB.SUBSTR(MEM_LOSS_CONTROL_DESC, 4000, 1) MEM_LOSS_CONTROL_DESC,
            DBMS_LOB.SUBSTR(MEM_CLAIMS_DESC, 4000, 1) MEM_CLAIMS_DESC,
            DBMS_LOB.SUBSTR(MEM_SPECIAL_CONCERNS, 4000, 1) MEM_SPECIAL_CONCERNS,
            DBMS_LOB.SUBSTR(MEM_RECOMMENDATIONS, 4000, 1) MEM_RECOMMENDATIONS
       FROM TBL_MAIN)
  
  COMMIT;
END;
Re: Inserting Data from Clob to Varchar2. [message #482859 is a reply to message #188848] Tue, 16 November 2010 07:54 Go to previous message
brookheather
Messages: 1
Registered: November 2010
Location: London
Junior Member
The AL32UTF8 character set is a UNICODE (UTF8) character set and uses two bytes per character which is why you are getting the error as 2000 characters are using 4000 bytes. The solution is to either change the character set to a standard 8 bit one or reduce the chunking to 2000 characters.

- Simon.
Previous Topic: Question on SQL using union
Next Topic: View
Goto Forum:
  


Current Time: Sun Dec 04 02:47:24 CST 2016

Total time taken to generate the page: 0.11322 seconds