-
ORA-01403 no data found error
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.
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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;
-
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.
Code:
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|