-284 in stored procedure. help.
Any know offhand why I'm getting -284 (a subquery has returned not
exactly one row) ? I know it's big but if it's obvious thing it would
help.
Warning! Any existing privileges will be lost. You must grant
privileges to the object again.
-- Dropping procedure
drop procedure "informix".inf2ora_extindf;
-- Creating procedure
create procedure "informix".inf2ora_extindf(dbname_f char(30))
define idxname_l like sysindexes.idxname;
define owner_l like sysindexes.owner;
define tabid_l like sysindexes.tabid;
define prtabid_l like sysindexes.tabid;
define idxtype_l like sysindexes.idxtype;
define part1_l like sysindexes.part1;
define part2_l like sysindexes.part2;
define part3_l like sysindexes.part3;
define part4_l like sysindexes.part4;
define part5_l like sysindexes.part5;
define part6_l like sysindexes.part6;
define part7_l like sysindexes.part7;
define part8_l like sysindexes.part8;
define part9_l like sysindexes.part9;
define part10_l like sysindexes.part10;
define part11_l like sysindexes.part11;
define part12_l like sysindexes.part12;
define part13_l like sysindexes.part13;
define part14_l like sysindexes.part14;
define part15_l like sysindexes.part15;
define part16_l like sysindexes.part16;
define colname_l like syscolumns.colname;
define constrtype_l like sysconstraints.constrtype;
define constrname_l like sysconstraints.constrname;
define colorder_l,srcindno_l,count_l,cnt_l smallint;
define uniqueflag_l char(10);
define vsnno_l integer;
define osvsn_l char(8);
create table inf2ora_indx(
table_id smallint not null,
src_indno smallint not null,
src_indnm char(30) not null,
index_id smallint not null,
ora_indnm char(30),
unique_flag char(10));
delete from inf2ora_indx;
create table inf2ora_idcl(
table_id smallint not null,
src_indno smallint not null,
key_col_id smallint not null,
key_col_name char(30) not null);
delete from inf2ora_idcl;
let prtabid_l=0;
let cnt_l=0;
begin
set debug file to 'd:\\infind.txt';
trace on;
end;
--all the indexes and colums involved in the indexes are
selected.
foreach
select
i.idxname,i.owner,i.tabid,i.idxtype,i.part1,i.part2,i.part3,
i.part4,i.part5,i.part6,i.part7,i.part8,i.part9,i.part10,i.part11,
i.part12,i.part13,i.part14,i.part15,i.part16
into
idxname_l,owner_l,tabid_l,idxtype_l,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 i
where i.tabid > 99
order by i.tabid,i.owner,i.idxname
let cnt_l=cnt_l+1;
if prtabid_l != tabid_l then
let srcindno_l=0;
end if;
let constrtype_l=null;
let constrname_l=idxname_l;
--check whether index is created to enforce Primary key
or unique
--or referential constraint.
select count(*) into count_l
from sysconstraints
where idxname = idxname_l
and owner = owner_l
and tabid = tabid_l; if count_l > 0 then
select constrtype,constrname into
constrtype_l,constrname_l
from sysconstraints
where idxname = idxname_l
and owner = owner_l
and tabid = tabid_l; if constrtype_l in ("C","R","P") then
--Primary key constraints, check
constraints and
--referential constraints are not taken
care of in
--indexes information.
let prtabid_l=tabid_l;
continue foreach;
end if;
end if;
let srcindno_l=srcindno_l+1;
if idxtype_l="U" then
let uniqueflag_l="unique";
else
let uniqueflag_l=null;
end if;
insert into inf2ora_indx values
(tabid_l,srcindno_l,constrname_l,
cnt_l,constrname_l,uniqueflag_l);
let colorder_l=0;
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;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_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;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_l);
else
let prtabid_l=tabid_l;
continue foreach;
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;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_l);
else
let prtabid_l=tabid_l;
continue foreach;
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;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_l);
else
let prtabid_l=tabid_l;
continue foreach;
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;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_l);
else
let prtabid_l=tabid_l;
continue foreach;
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;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_l);
else
let prtabid_l=tabid_l;
continue foreach;
end if;
if part7_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part7_l;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_l);
else
let prtabid_l=tabid_l;
continue foreach;
end if;
if part8_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part8_l;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_l);
else
let prtabid_l=tabid_l;
continue foreach;
end if;
if part9_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno = part9_l;
insert into inf2ora_idcl values
(tabid_l,srcindno_l,colorder_l,
colname_l);
else
let prtabid_l=tabid_l;
continue foreach;
end if;
if part10_l>0 then
let colorder_l=colorder_l+1;
select colname into colname_l
from syscolumns
where tabid = tabid_l
and colno =