ORA-01403 no data found error

[sobha]

hi all,

i am writing a trigger for update.while i am executing the query i am getting no data found error.could anyone help me how to solve this query.

thanks in advance,
sobha.

[PAVB]

Does your code includes a "select into" statement?

Most probably you are getting ORA-01403 because of "select into" statement is returning nothing. Either fix your query to ensure a single value is returned or fix your code not to execute "select into" when you know no result is going to be returned.

[sobha]

hi PAVB,
i have used Select into.my query is below.what should i use instead of into not get get the no data found error.

create or replace trigger exp_lc_document_txn_status_trg
after update on exp_lc_document_trg
for each row
declare
-- local variables here
workflowID varchar2(16);

begin

select workflow.r_object_id into workflowID

from dm_workflow_s workflow,
dmi_package_s packs,
dmi_package_r packr


where
workflow.r_object_id=packs.r_workflow_id
and packs.r_object_id=packr.r_object_id
and packr.r_component_id=ld.r_object_id
and workflowID <> ' ';



if (:new.TRANSACTION_STATUS <> ld.TRANSACTION_STATUS) THEN

INSERT INTO T_REP_TXN_STATUS
VALUES
(
workflowID,
ld.transaction_status,
sysdate,
sysdate
) ;
end if;


commit;
end exp_lc_document_txn_status_trg;

[PAVB]

The issue is restricted to query below; I don't know your business specs and I know nothing about the data in those tables so I cannot even tell if the query is correct or not so, I'm assuming the query is correct.

Problem is query is returning no rows, that's why you are getting ORA-01403.

select  workflow.r_object_id 
into    workflowID
from    dm_workflow_s workflow,
        dmi_package_s packs,
        dmi_package_r packr
where   workflow.r_object_id=packs.r_workflow_id
    and packs.r_object_id=packr.r_object_id
    and packr.r_component_id=ld.r_object_id
    and workflowID <> ' ';

I would suggest to test the query independently and be sure it always returns one row, no more, no less.