Home » SQL & PL/SQL » SQL & PL/SQL » ORA-06502: PL/SQL: numeric or value error: character string buffer too small (Oracle 10g)
ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #402758] Tue, 12 May 2009 06:41 Go to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
declare
cursor c1 is
select
 l.TABLE_NAME ,                    
 l.COLUMN_NAME,                    
 l.DATA_TYPE ,                    
 l.DATA_LENGTH,                    
 l.DATA_PRECISION ,                    
 l.DATA_SCALE ,                    
 l.NULLABLE,                    
 l.COLUMN_ID ,                    
 l.DEFAULT_LENGTH ,                    
 l.DATA_DEFAULT  
from user_tab_columns l,ram_common_tables c
where c.table_name=l.table_name ;
TYPE rec is record (
 TABLE_NAME                   VARCHAR2(30),
 COLUMN_NAME                VARCHAR2(30),
 DATA_TYPE                      VARCHAR2(9),
 DATA_LENGTH                  NUMBER,
 DATA_PRECISION             NUMBER,
 DATA_SCALE                    NUMBER,
 NULLABLE                        VARCHAR2(1),
 COLUMN_ID                      NUMBER,
 DEFAULT_LENGTH            NUMBER,
 DATA_DEFAULT                 varchar2(2000)
);
c rec;
begin
 open c1;
 loop
   fetch c1 into c;
    exit when c1%NOTFOUND;
    insert into ram_diff_cols1 values 
    (c.table_name,c.column_name,c.data_type,c.data_length,
     c.DATA_PRECISION, c.DATA_SCALE, c.NULLABLE, c.COLUMN_ID, 
     c.DEFAULT_LENGTH, c.DATA_DEFAULT);
end loop;
end;


SQL> /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 32


Please let me know where i am missing.
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #402760 is a reply to message #402758] Tue, 12 May 2009 06:51 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
what is the structure of ram_diff_cols1.

also instead for creating a record you can just declare

c c1%rowtype;


Thanks
Trivendra
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #402761 is a reply to message #402758] Tue, 12 May 2009 06:53 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Google the error number and post back with what the error's associated action is (to show that you have the ability to at least google the error)
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #402763 is a reply to message #402758] Tue, 12 May 2009 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is "rec"?

Regards
Michel
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #402765 is a reply to message #402758] Tue, 12 May 2009 07:17 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@ramoradba,

In addition to others response,

DATA_TYPE column length might be causing this error. What made you fix the column length to VARCHAR2(9). What happens if the value from USER_TAB_COLUMNS.DATA_TYPE column holds 'TIMESTAMP(6) WITH TIME ZONE' data type. (Just an example)

Use %TYPE instead of directly giving VARCHAR2(9). Since you are using user_tab_column table, using USER_TAB_COLUMNS.column_name%TYPE will be better than declaring variables with a fixed length.

DESC USER_TAB_COLUMNS and see if the lengths you gave are matching with your record declaration.

Hope this helps.

Regards,
Jo

Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #402767 is a reply to message #402765] Tue, 12 May 2009 07:27 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
@joicejohn
Yes you are right.i modified that.
And it`s working now.
@trivendra
Your`s solution is also working fine.
Thanks for your valuble suggestions.
@Michel
rec is "type".

@pablolee
Users which are asking questions here are capable & able to find using Google,But Why there are looking here is just for the responce like @joicejohn.Where they are missing.

Thanks & Regards
Sriram.
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #402770 is a reply to message #402767] Tue, 12 May 2009 07:36 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Ahh, so you are capable, just lazy. Oh well, good luck Wink
Re: ORA-06502: PL/SQL: numeric or value error: character string buffer too small [message #402775 is a reply to message #402767] Tue, 12 May 2009 07:45 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
@Michel
rec is "type".

Which "type"?

Regards
Michel
Previous Topic: distinct values from varray
Next Topic: Custom Sort
Goto Forum:
  


Current Time: Wed Dec 07 16:29:54 CST 2016

Total time taken to generate the page: 0.05968 seconds