文章存档
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2007-01-30 15:47:05
i create a test procedure:
create or replace procedure proce_0(
ival in osm_tab_proce.col_0%type:=10,
ocnt in out number)
as
a osm_tab_proce.col_0%type;
r osm_tab_proce%rowtype;
i number := 0;
cursor c1(ct0 number) is select col_0 from osm_dml_2sp.osm_tab_proce where col_0>ct0;begin
if c1%isopen=false then
open c1(196);
end if;
loop
fetch c1 into a;
exit when c1%notfound;
select col_0,col_1,col_2 into r from osm_dml_2sp.osm_tab_proce
where col_0=a and rownum<2; -- if rownum < 2 delete then ora-01422
r.col_0 := a ival;
r.col_2 := to_char(a) || to_char(sysdate);
insert into osm_dml_2sp.osm_tab_proce values r;
i := i 1;
end loop;
close c1;
ocnt := i;
end;
error message:
error: ora-01422: exact fetch returns more than requested number of rows
cause: you tried to execute a select into statement and more than one row was returned.
action: the options to resolve this oracle error are:
1. rewrite your select into statement so that only one row is returned.
2. replace your select into statement with a cursor.
for example, if you tried to execute the following sql statement:
select supplier_id
into cnumber
from suppliers
where supplier_name = 'ibm';
and there was more than one record in the suppliers table with the supplier_name of ibm, you would receive the ora-01422 error message.
in this case, it might be more prudent to create a cursor and retrieve each row if you are unsure of how many records you might retrieve.
error message ref:
create or replace procedure proce_0(
ival in osm_tab_proce.col_0%type:=10,
ocnt in out number)
as
a osm_tab_proce.col_0%type;
r osm_tab_proce%rowtype;
i number := 0;
cursor c1(ct0 number) is select col_0 from osm_dml_2sp.osm_tab_proce where col_0>ct0;begin
if c1%isopen=false then
open c1(196);
end if;
loop
fetch c1 into a;
exit when c1%notfound;
select col_0,col_1,col_2 into r from osm_dml_2sp.osm_tab_proce
where col_0=a and rownum<2; -- if rownum < 2 delete then ora-01422
r.col_0 := a ival;
r.col_2 := to_char(a) || to_char(sysdate);
insert into osm_dml_2sp.osm_tab_proce values r;
i := i 1;
end loop;
close c1;
ocnt := i;
end;
error message:
error: ora-01422: exact fetch returns more than requested number of rows
cause: you tried to execute a select into statement and more than one row was returned.
action: the options to resolve this oracle error are:
1. rewrite your select into statement so that only one row is returned.
2. replace your select into statement with a cursor.
for example, if you tried to execute the following sql statement:
select supplier_id
into cnumber
from suppliers
where supplier_name = 'ibm';
and there was more than one record in the suppliers table with the supplier_name of ibm, you would receive the ora-01422 error message.
in this case, it might be more prudent to create a cursor and retrieve each row if you are unsure of how many records you might retrieve.
error message ref:
给主人留下些什么吧！~~