exact fetch returns more than requested number of rows 1

[alusinyants]

Hello, do you know why this SProc will raise this error : java.sql.SQLException: ORA-01422:
exact fetch returns more than requested number of rows
ORA-06512: at "PE0101.SP_WRMSABVERSION", line 60
ORA-06512: at line 1


Also do you know how the 8.1.7 treats the " " empty string (NULL ?) I had no problems with 8.1.6, now I get error can't insert NULL value....




CREATE OR REPLACE PROCEDURE "SP_WRMSABVERSION" (
rms_ver_no_input NUMBER ,
rms_yr_no_input VARCHAR2 )
AS

CURSOR get_version IS
SELECT MAX(rms_ver_no), fldProgramId, fldDivisionId,
fldQid,
rms_mo_sq_no
FROM rms_dv_mo_pln
WHERE rms_pln_tp_cd = 'Proposed'
AND fldqid IN (
SELECT DISTINCT fldqid
FROM rms_mo
WHERE rms_yr_no = rms_yr_no_input)
GROUP BY fldProgramId, fldDivisionId, fldQid, rms_mo_sq_no
ORDER BY fldProgramId , fldDivisionId , fldQid ,
rms_mo_sq_no;

v_fldprogramid VARCHAR2(16);
v_fldqid INTEGER;
v_flddivisionid INTEGER;
v_rms_mo_sq_no INTEGER;
v_rms_ver_no NUMBER(18, 0);
v_dv_mo_pln_sq_no_get NUMBER;
v_fldprogramid_get VARCHAR2(16);
v_fldqid_get INTEGER;
v_flddivisionid_get INTEGER;
v_rms_mo_sq_no_get INTEGER;
v_rms_pln_tp_cd_get VARCHAR2(20);
v_rms_ver_no_get NUMBER(18, 0);
v_dv_mo_fte_no_get NUMBER(5, 2);
v_rms_ver_no_insert NUMBER(18, 0);
v_dv_mo_pln_sq_no_pk NUMBER(18, 0);
temp_var1 VARCHAR2(255);
temp_var2 VARCHAR2(255);
CURSOR get_sow_fte IS
SELECT d.fldprogramid, d.flddivisionid, d.fldqid,
TO_NUMBER(SUM(d.dv_mo_fte_no)
/ 3)
FROM rms_dv_mo_pln d
WHERE d.rms_ver_no = rms_ver_no_input
AND d.rms_pln_tp_cd = 'SOW'
GROUP BY d.fldprogramid, d.flddivisionid, d.fldqid;

ftevalue NUMBER(5, 2);
BEGIN
OPEN get_version;

FETCH get_version INTO
v_rms_ver_no, v_fldprogramid, v_flddivisionid,
v_fldqid, v_rms_mo_sq_no;

DBMS_OUTPUT.PUT_LINE( v_rms_ver_no || '!' || v_fldprogramid
|| '!' ||
v_flddivisionid || '!' || v_fldqid || '!' ||
v_rms_mo_sq_no);
<>
WHILE (get_version%FOUND) LOOP
BEGIN
DBMS_OUTPUT.PUT_LINE( v_rms_ver_no || '!' || v_fldprogramid
|| '!' ||
v_flddivisionid || '!' || v_fldqid || '!' ||
v_rms_mo_sq_no);
BEGIN

SELECT dv_mo_pln_sq_no, fldprogramid, rms_ver_no,
flddivisionid, fldqid,
rms_mo_sq_no, dv_mo_fte_no
INTO v_dv_mo_pln_sq_no_get, v_fldprogramid_get,
v_rms_ver_no_get, v_flddivisionid_get, v_fldqid_get,
v_rms_mo_sq_no_get, v_dv_mo_fte_no_get
FROM rms_dv_mo_pln
WHERE fldprogramid = v_fldprogramid
AND rms_ver_no = v_rms_ver_no
AND fldDivisionId = v_flddivisionid
AND fldqid = v_fldqid
AND rms_mo_sq_no = v_rms_mo_sq_no
AND rms_pln_tp_cd = 'Proposed';

END;


SELECT MAX(dv_mo_pln_sq_no) + 1
INTO temp_var1
FROM rms_dv_mo_pln;

v_dv_mo_pln_sq_no_pk := temp_var1;
BEGIN

DBMS_OUTPUT.PUT_LINE('v_fldqid_get=' || v_fldqid_get || ',
v_rms_mo_sq_no_get=' || v_rms_mo_sq_no_get);

INSERT INTO rms_dv_mo_pln
(dv_mo_pln_sq_no,
fldprogramid,
rms_ver_no,
flddivisionid,
fldqid,
rms_mo_sq_no,
rms_pln_tp_cd,
dv_mo_fte_no)
VALUES (
v_dv_mo_pln_sq_no_pk,
v_fldprogramid_get,
rms_ver_no_input,
v_flddivisionid_get,
v_fldqid_get,
v_rms_mo_sq_no_get,
'App Budget',
v_dv_mo_fte_no_get);

END;
IF (rms_ver_no_input = v_rms_ver_no_get) THEN

UPDATE rms_dv_mo_pln
SET rms_pln_tp_cd = 'SOW'
WHERE dv_mo_pln_sq_no = v_dv_mo_pln_sq_no_get;
ELSE
SELECT MAX(dv_mo_pln_sq_no) + 1
INTO temp_var2
FROM rms_dv_mo_pln;

v_dv_mo_pln_sq_no_pk := temp_var2;

INSERT INTO rms_dv_mo_pln (dv_mo_pln_sq_no,
fldprogramid,
rms_ver_no,
flddivisionid,
fldqid,
rms_mo_sq_no,
rms_pln_tp_cd,
dv_mo_fte_no)
VALUES (
v_dv_mo_pln_sq_no_pk,
v_fldprogramid_get,
rms_ver_no_input,
v_flddivisionid_get,
v_fldqid_get,
v_rms_mo_sq_no_get,
'SOW',
v_dv_mo_fte_no_get);

END IF;
FETCH get_version INTO
v_rms_ver_no, v_fldprogramid, v_flddivisionid,
v_fldqid, v_rms_mo_sq_no;

END;
END LOOP;
OPEN get_sow_fte;

FETCH get_sow_fte INTO
v_fldprogramid, v_flddivisionid, v_fldqid,
ftevalue;

<>
WHILE (get_sow_fte%FOUND) LOOP

sp_RMSSaveDivision(parProgramId=>v_fldprogramid,
parQid=>v_fldqid,
parOldDivisionId=>0,
parDivisionId=>v_flddivisionid,
parDivisionCount=>ftevalue,
parDivisionIdChanged=>0);


FETCH get_sow_fte INTO
v_fldprogramid, v_flddivisionid, v_fldqid,
ftevalue;


END LOOP;
END;

-- #endsql
/


Thsnks in advance!

 

[carp]

My guess is that

SELECT dv_mo_pln_sq_no, fldprogramid, rms_ver_no,
flddivisionid, fldqid,
rms_mo_sq_no, dv_mo_fte_no
INTO v_dv_mo_pln_sq_no_get, v_fldprogramid_get,
v_rms_ver_no_get, v_flddivisionid_get, v_fldqid_get,
v_rms_mo_sq_no_get, v_dv_mo_fte_no_get
FROM rms_dv_mo_pln
WHERE fldprogramid = v_fldprogramid
AND rms_ver_no = v_rms_ver_no
AND fldDivisionId = v_flddivisionid
AND fldqid = v_fldqid
AND rms_mo_sq_no = v_rms_mo_sq_no
AND rms_pln_tp_cd = 'Proposed';

returns more than one row. To test this, change the query to

SELECT dv_mo_pln_sq_no, fldprogramid, rms_ver_no,
flddivisionid, fldqid,
rms_mo_sq_no, dv_mo_fte_no
INTO v_dv_mo_pln_sq_no_get, v_fldprogramid_get,
v_rms_ver_no_get, v_flddivisionid_get, v_fldqid_get,
v_rms_mo_sq_no_get, v_dv_mo_fte_no_get
FROM rms_dv_mo_pln
WHERE fldprogramid = v_fldprogramid
AND rms_ver_no = v_rms_ver_no
AND fldDivisionId = v_flddivisionid
AND fldqid = v_fldqid
AND rms_mo_sq_no = v_rms_mo_sq_no
AND rms_pln_tp_cd = 'Proposed'
and rownum < 2;

and see if the problem goes away.