I am getting a 'ORA-06502: PL/SQL: numeric or value error: character string buffer too small error', trying to execute a stored procedure that has an output parameter with all 50 US states and the abbreviations.
Any suggestions on what I am doing wrong. Oracle database version is 11.2.0.1.0. I am also searching to see if I can find anything online, but thought of asking here in case someone had this problem in the past that was addressed.
CREATE TABLE STATE_CDS (STATE VARCHAR2(2) NOT NULL, STATE_NAME VARCHAR2(25 BYTE));
insert into state_cds values('AL', 'ALABAMA');
insert into state_cds values('AK', 'ALASKA');
insert into state_cds values('AZ', 'ARIZONA');
insert into state_cds values('AR', 'ARKANSAS');
insert into state_cds values('CA', 'CALIFORNIA');
insert into state_cds values('CO', 'COLORADO');
insert into state_cds values('CT', 'CONNECTICUT');
insert into state_cds values('DE', 'DELAWARE');
insert into state_cds values('DC', 'DISTRICT OF COLUMBIA');
insert into state_cds values('FL', 'FLORIDA');
insert into state_cds values('GA', 'GEORGIA');
insert into state_cds values('HI', 'HAWAII');
insert into state_cds values('ID', 'IDAHO');
insert into state_cds values('IL', 'ILLINOIS');
insert into state_cds values('IN', 'INDIANA');
insert into state_cds values('IA', 'IOWA');
insert into state_cds values('KS', 'KANSAS');
insert into state_cds values('KY', 'KENTUCKY');
insert into state_cds values('LA', 'LOUISIANA');
insert into state_cds values('ME', 'MAINE');
insert into state_cds values('MD', 'MARYLAND');
insert into state_cds values('MA', 'MASSACHUSETTS');
insert into state_cds values('MI', 'MICHIGAN');
insert into state_cds values('MN', 'MINNESOTA');
insert into state_cds values('MS', 'MISSISSIPPI');
insert into state_cds values('MO', 'MISSOURI');
insert into state_cds values('MT', 'MONTANA');
insert into state_cds values('NE', 'NEBRASKA');
insert into state_cds values('NV', 'NEVADA');
insert into state_cds values('NH', 'NEW HAMPSHIRE');
insert into state_cds values('NJ', 'NEW JERSEY');
insert into state_cds values('NM', 'NEW MEXICO');
insert into state_cds values('NY', 'NEW YORK');
insert into state_cds values('NC', 'NORTH CAROLINA');
insert into state_cds values('ND', 'NORTH DAKOTA');
insert into state_cds values('OH', 'OHIO');
insert into state_cds values('OK', 'OKLAHOMA');
insert into state_cds values('OR', 'OREGON');
insert into state_cds values('OU', 'OUT OF COUNTRY');
insert into state_cds values('PA', 'PENNSYLVANIA');
insert into state_cds values('RI', 'RHODE ISLAND');
insert into state_cds values('SC', 'SOUTH CAROLINA');
insert into state_cds values('SD', 'SOUTH DAKOTA');
insert into state_cds values('TN', 'TENNESSEE');
insert into state_cds values('TX', 'TEXAS');
insert into state_cds values('UT', 'UTAH');
insert into state_cds values('VT', 'VERMONT');
insert into state_cds values('VA', 'VIRGINIA');
insert into state_cds values('WA', 'WASHINGTON');
insert into state_cds values('WV', 'WEST VIRGINIA');
insert into state_cds values('WI', 'WISCONSIN');
insert into state_cds values('WY', 'WYOMING');
create or replace procedure P_state_CODES
(cd_type in varchar2,
cd_desc out varchar2) is
cursor states is
select state, state_name
from state_cds
order by state_name desc;
begin
if cd_type = 'STATES' then
for recs in states loop
cd_desc := recs.state||';'||recs.state_name||'+'||cd_desc;
end loop;
end if;
end;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "KBNIS.P_STATE_CODES", line 11
ORA-06512: at "KBNIS.P_STATE_CODES", line 11
ORA-06512: at line 7
Thank you for the help.
Sandeep.