Exact fetch ERROR on an INSERT statement ??? 2

[speial]

Hello, In an SQL procedure I am getting an SQL error >>> ORA-01422: 'exact fetch returns more than requested number of rows' on an table insert statement.

I am puzzled to say the least. Anyone seen this before ?

==============
BEGIN
INSERT INTO sysadm.ps_cu_sp_eq_stg
(
sp_id
,item_id_eq
,install_dt
,removal_dt
,eq_cnt
,commentsmed
,fa_id
,step_seq_nbr
,insert_dttm
,process_dttm
,error_msg
,staging_flg
)
VALUES
(
vwh_sp_id
,vwh_item_id
,vwh_read_dt_tm --Install Date
,NULL -- Remove Date
,v_eq_cnt -- set to be 1 (one)
,'NEW ITEM FFDS INSTALLED' -- fixed comment
,vwh_fa_id
,vwh_step_seq_nbr
,SYSDATE
,NULL
,p_space
,v_staging_flag --N = NEW
);

EXCEPTION
WHEN OTHERS
THEN
p_stuff_record := 'INSERTING INTO ps_cu_sp_eq_stg FOR NEW ITEMS'
|| vwh_fo_id || ' '
|| vwh_step_seq_nbr ||' '
|| vwh_item_id || ' '
|| vwh_prem_id ||' '
|| vwh_fa_id || ' '
|| vwh_sp_id;
ROLLBACK;

END;

 

[LKBrwnDBA]

1) Are you sure this is the statement that produces the error?

2) Is this the COMPLETE procedure?



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

As usual, I agree with LKBrwnDBA...this code cannot be all there is. Either this is a subordinate routine to something bigger that is occurring or, there are triggers that exist on "sysadm.ps_cu_sp_eq_stg" that are doing "more than meets the eye."

Let us know your findings on these suggestions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[speial]

Hello,

This is not the entire procedure. There are no triggers on the table. my p_stuff_record shows the resolved variables just before the Oracle Error Message is posted. Hence why I am puzzled as well. I am inserting dbms_out statements to see if there are any cursors being execuited at the same time of 'falling through'. Other Ideas ???

Thanks for your information.

 

[LKBrwnDBA]

Because of the error " ORA-01422: 'exact fetch returns more than requested number of rows' " I would look into either a SELECT..INTO statement or an UPDATE ... SET COL1=(SELECT...) statement.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb