-1279 in stored procedure
If anyone can tell me why I get the -1279 (value exceeds string column
length) in the stored procedure it would help. This is an ANSI DB. I
know it's big but it might be something obvious.
-- Warning! Any existing privileges will be lost. You must grant
privileges to the object again.
-- Dropping procedure
drop procedure "informix".inf2ora_extreffv7;
-- Creating procedure
create procedure "informix".inf2ora_extreffv7(dbname_f char(30))
define cowner_l like sysconstraints.owner;
define owner_l char(12);
define bowner_l like sysconstraints.owner;
define constrnm_l like sysconstraints.constrname;
define idxname_l like sysconstraints.owner;
define bidxname_l like sysconstraints.owner;
define part1_l like sysindexes.part1;
define bpart1_l like sysindexes.part1;
define part2_l like sysindexes.part2;
define bpart2_l like sysindexes.part2;
define part3_l like sysindexes.part3;
define bpart3_l like sysindexes.part3;
define part4_l like sysindexes.part4;
define bpart4_l like sysindexes.part4;
define part5_l like sysindexes.part5;
define bpart5_l like sysindexes.part5;
define part6_l like sysindexes.part6;
define bpart6_l like sysindexes.part6;
define part7_l like sysindexes.part7;
define bpart7_l like sysindexes.part7;
define part8_l like sysindexes.part8;
define bpart8_l like sysindexes.part8;
define part9_l like sysindexes.part9;
define bpart9_l like sysindexes.part9;
define part10_l like sysindexes.part10;
define bpart10_l like sysindexes.part10;
define part11_l like sysindexes.part11;
define bpart11_l like sysindexes.part11;
define part12_l like sysindexes.part12;
define bpart12_l like sysindexes.part12;
define part13_l like sysindexes.part13;
define bpart13_l like sysindexes.part13;
define part14_l like sysindexes.part14;
define bpart14_l like sysindexes.part14;
define part15_l like sysindexes.part15;
define bpart15_l like sysindexes.part15;
define part16_l like sysindexes.part16;
define bpart16_l like sysindexes.part16;
define colname_l like syscolumns.colname;
define bcolname_l like syscolumns.colname;
define tabid_l,btabid_l,constrid_l,colorder_l smallint;
define delrule_l char(1);
define delrule_t char(1);
begin
set debug file to 'd:\\infref.txt';
trace on;
end;
create table inf2ora_fnky(
table_id smallint not null,
constraint_name char(30) not null,
ref_owner char(30) not null,
key_col_order smallint not null,
key_col_name char(30) not null,
ref_table_id smallint not null,
ref_key_col_name char(30) not null,
delete_rule char(1) not null);
delete from inf2ora_fnky;
--All the referential constraints and associated index, tables
--and columns are selected.
foreach
select c1.constrid,c1.owner,c1.tabid,c1.constrname,c1.idxname,
c2.owner,c2.tabid,c2.idxname, r.delrule
into constrid_l,cowner_l,tabid_l,constrnm_l,idxname_l,
bowner_l,btabid_l,bidxname_l,delrule_t
from sysconstraints c1,sysreferences r,sysconstraints c2
where c1.constrtype="R"
and c1.constrid=r.constrid
and r.primary=c2.constrid
if delrule_t = "C" then
let delrule_l="Y";
end if;
if delrule_t = "R" then
let delrule_l="N";
end if;
let owner_l="ops$"||cowner_l;
let colorder_l=0;
--get all the associated columns of referenced table.
select
part1,part2,part3,part4,part5,part6,part7,part8,part9,
part10,part11,part12,part13,part14,part15,part16
into
part1_l,part2_l,part3_l,part4_l,part5_l,part6_l,part7_l,part8_l,
part9_l,part10_l,part11_l,part12_l,part13_l,part14_l,part15_l,
part16_l
from sysindexes
where idxname = idxname_l
and owner = cowner_l
and tabid = tabid_l;
--get all the associated columns of referencing table.
select
part1,part2,part3,part4,part5,part6,part7,part8,part9,
part10,part11,part12,part13,part14,part15,part16
into
bpart1_l,bpart2_l,bpart3_l,bpart4_l,bpart5_l,bpart6_l,
bpart7_l,bpart8_l,bpart9_l,bpart10_l,bpart11_l,bpart12_l,
bpart13_l,bpart14_l,bpart15_l,bpart16_l
from sysindexes
where idxname = bidxname_l
and owner = bowner_l
and tabid = btabid_l;
if part1_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part1_l;
select colname into bcolname_l
from syscolumns
where tabid = btabid_l
and colno = bpart1_l;
insert into inf2ora_fnky values
(tabid_l,constrnm_l,owner_l,
colorder_l,colname_l,btabid_l,
bcolname_l,delrule_l);
end if;
if part2_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part2_l;
select colname into bcolname_l
from syscolumns
where tabid = btabid_l
and colno = bpart2_l;
insert into inf2ora_fnky values
(tabid_l,constrnm_l,owner_l,
colorder_l,colname_l,btabid_l,
bcolname_l,delrule_l);
end if;
if part3_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part3_l;
select colname into bcolname_l
from syscolumns
where tabid = btabid_l
and colno = bpart3_l;
insert into inf2ora_fnky values
(tabid_l,constrnm_l,owner_l,
colorder_l,colname_l,btabid_l,
bcolname_l,delrule_l);
end if;
if part4_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part4_l;
select colname into bcolname_l
from syscolumns
where tabid = btabid_l
and colno = bpart4_l;
insert into inf2ora_fnky values
(tabid_l,constrnm_l,owner_l,
colorder_l,colname_l,btabid_l,
bcolname_l,delrule_l);
end if;
if part5_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part5_l;
select colname into bcolname_l
from syscolumns
where tabid = btabid_l
and colno = bpart5_l;
insert into inf2ora_fnky values
(tabid_l,constrnm_l,owner_l,
colorder_l,colname_l,btabid_l,
bcolname_l,delrule_l);
end if;
if part6_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part6_l;
select colname into bcolname_l
from syscolumns
where tabid = btabid_l
and colno = bpart6_l;
insert into inf2ora_fnky values
(tabid_l,constrnm_l,owner_l,
colorder_l,colname_l,btabid_l,
bcolname_l,delrule_l);
end if;
if part7_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l