DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: ORA-01403 no data found error

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    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.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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.

  3. #3
    Join Date
    Mar 2009
    Posts
    2
    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;

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,555
    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
  •  


Click Here to Expand Forum to Full Width