DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: ORA-01403: no data found, how do you solve this?

  1. #1
    Join Date
    Dec 2000
    Posts
    87
    Hi all,

    I'm trying do a 'INSERT' into a table and I received this message...

    ORA-01403: no data found

    What does it mean, and how do you solve it? Thanks.

  2. #2
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    I got this from Oracle Book.

    I don't know if this will help you.

    ORA-01403 no data found

    Cause: In a host language program, all records have been fetched. The return code from the fetch was +4, indicating that all records have been returned from the SQL query.

    Action: Terminate processing for the SELECT statement.


  3. #3
    Join Date
    Dec 2000
    Posts
    87
    Thanks. I also checked the documentation and yield exact the same content as what you got here, but it still doesn't solve my problem.

  4. #4
    Join Date
    Dec 2000
    Location
    Virginia, USA
    Posts
    455
    Can you post your insert statement with Table Defination?

  5. #5
    Join Date
    Dec 2000
    Posts
    87
    Actually, the problem laid on the trigger that associate with the table, the part starting with

    select nvl(keypart1_use,'nothing').....
    if .......
    If I comment out that section there is no problem, just that primary key platformid is not generated. So I'm not too sure if there is something wrong with it.


    I was trying to do insert as:

    INSERT INTO Platforms (Platform, DefAssignedToType, KeyPart1_Use, KeyPart2_Use, KeyPart3_Use, DistributedSystem, AllowNoCharge, SupportFac, VendorID)
    VALUES ('Test Platform', 'n/a','User ID','Password',null,0, 0,0,560);

    **************************

    The trigger codes are:
    create or replace trigger tri_platforms
    before insert on platforms
    for each row
    declare
    stoo_selcnt integer;
    stoo_error integer;
    stoo_rowcnt integer := 1;
    stoo_crowcnt integer := 0;
    stoo_fetchstatus integer := 0;
    stoo_errmsg varchar2(255);
    stoo_sqlstatus integer;
    num_of_rows number(10,0);
    noruntrg number(1);
    v_next_id integer;
    v_keypart1_use varchar(16);
    v_keypart1indic integer;
    v_keypart2_use varchar(16);
    v_keypart2indic integer;
    v_keypart3_use varchar(16);
    v_keypart3indic integer;

    begin
    num_of_rows := stoo_rowcnt ;

    if num_of_rows = 0 then
    return ;
    end if ;


    begin
    noruntrg := checkruntrigger ;
    if (noruntrg = 1) then
    begin
    dbms_output.put_line('trigger is bypassed') ;
    return ;
    end;
    end if ;
    end ;



    select count(*) into num_of_rows
    from mdsvendors v
    where :new.vendorid=v.vendorid;

    if num_of_rows=0 then
    begin
    RAISE_APPLICATION_ERROR(-20500,'Trigger forces a rollback');
    ROLLBACK ;
    raise_application_error(-20999, 3100 || '-' || 'Unable to insert.');
    return;
    end;
    end if;


    select next_id into v_next_id
    from next_id
    where table_name='Platforms';

    :new.platformid:=v_next_id+1;

    update next_id
    set next_id=next_id+1
    where table_name='Platforms';

    select nvl(keypart1_use,'nothing'),nvl(keypart2_use,'nothing'),nvl(keypart3_use,'nothing')
    into v_keypart1_use,v_keypart2_use, v_keypart3_use
    from platforms
    where platform=:new.platform;
    dbms_output.put_line('The values are'||v_keypart1_use||','||v_keypart2_use||','||v_keypart3_use);
    / if (v_keypart1_use = 'nothing') then
    v_keypart1indic := 0;
    else
    v_keypart1indic := 1;
    end if;

    if (v_keypart2_use = 'nothing') then
    v_keypart2indic := 0;
    else
    v_keypart2indic := 1;
    end if;

    if (v_keypart3_use = 'nothing') then
    v_keypart3indic := 0;
    else
    v_keypart3indic := 1;
    end if;

    insert into platformmapkeys( platformid, keypart1indic, keypart2indic, keypart3indic )
    select platformid, v_keypart1indic, v_keypart2indic, v_keypart3indic
    from platforms
    where platform=:new.platform;


    end tri_platforms;

  6. #6
    Join Date
    Dec 2000
    Posts
    87
    The error means:

    you have a trigger on the table and the trigger has a select ... into
    and the select into is finding no data.

    Just got it from someone.

  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Oh my...

    First off, the NO_DATA_FOUND error can only come from a SELECT...INTO that returns no data. Therefore, one of the SELECT...INTOs that you have is not returning any data, hence your error.

    Now, for the laundry list of issues with this trigger, in no particular order:

    - IF..END IF does not require a BEGIN..END within it

    - You twice use num_of_rows to check for the simple existence of rows. In the first usage, you set it equal to another variable, which I don't get, but the second usage is an issue. If you want to check for the simple existence of data, don't waste time *counting the entire table*. Add AND ROWNUM = 1 to the SELECT COUNT(*) to make it run much faster!

    - The rollback will not be performed
    ---RAISE_APPLICATION_ERROR(-20500,'Trigger forces a rollback');
    ---ROLLBACK ;
    ---raise_application_error(-20999, 3100 || '-' || 'Unable to insert.');
    This may be intentional.

    - This will *not* work in a multi-user environment:

    ---select next_id into v_next_id
    ---from next_id
    ---where table_name='Platforms';

    ---:new.platformid:=v_next_id+1;

    ---update next_id
    ---set next_id=next_id+1
    ---where table_name='Platforms';

    This truly needs to be implemented via a trigger. Trust me.

    - This is probably where your NO_DATA_FOUND error is coming from:

    ---select nvl(keypart1_use,'nothing'),nvl(keypart2_use,'nothing'),nvl(keypart3_use,'nothing')
    ---into v_keypart1_use,v_keypart2_use, v_keypart3_use
    ---from platforms
    ---where platform=:new.platform;

    The NVL call will not do anything when the statement returns no data. Do this instead:

    ---BEGIN
    ------select keypart1_use, keypart2_use, keypart3_use
    ------into v_keypart1_use,v_keypart2_use, v_keypart3_use
    ------from platforms
    ------where platform=:new.platform;
    ---EXCEPTION
    ------WHEN NO_DATA_FOUND THEN
    ---------keypart1_use := 'nothing';
    ---------keypart2_use := 'nothing';
    ---------keypart3_use := 'nothing';
    ------WHEN OTHERS THEN
    ---------RAISE;
    ---END;

    - Why are you going to the platforms table twice?

    ---select nvl(keypart1_use,'nothing'),nvl(keypart2_use,'nothing'),nvl(keypart3_use,'nothing')
    ---into v_keypart1_use,v_keypart2_use, v_keypart3_use
    ---from platforms
    ---where platform=:new.platform;

    ---select platformid, v_keypart1indic, v_keypart2indic, v_keypart3indic
    ---from platforms
    ---where platform=:new.platform;

    Get everything you need in one statement.

    - Another point for the 2 SELECT..INTO statements you have. It looks like you are inserting into the same table you are selecting from. This means that you can have more than one row with the same platform value, no? If that is the case, then your SELECT..INTOs will blow up on these as well. SELECT..INTO *requires* 1 and only 1 row be returned. Any other number of rows will generate an error. You need to re-think what you are doing here.

    ----------------------------------------------------

    You must remember that you are executing *all* this code for every single record you ever insert into this table. Triggers are wonderful things when used responsibly. You need to seriously consider everything you do inside a trigger as it can have a significant impact on the performance of your database.

    Finally, I'm honestly not trying to bust on you or anything, but this is a lot of issues for a single trigger. I hope my critiques help more than they hurt.

    - Chris


  8. #8
    Join Date
    Dec 2000
    Posts
    87
    Chris,
    Thanks, your critiques is very helpful.
    A lot of codes are generated by migration workbench during conversion, I haven't got time to clean it up. Right now, I'm only trying to make it work.
    I will definitely spend sometime to read your comment again.

    Also would you please help me looking at this trigger, I changed to:

    declare
    stoo_selcnt integer;
    stoo_error integer;
    stoo_rowcnt integer := 1;
    stoo_crowcnt integer := 0;
    stoo_fetchstatus integer := 0;
    stoo_errmsg varchar2(255);
    stoo_sqlstatus integer;
    num_of_rows number(10,0);
    noruntrg number(1);
    v_next_id integer;
    v_keypart1_use varchar(16);
    v_keypart1indic integer;
    v_keypart2_use varchar(16);
    v_keypart2indic integer;
    v_keypart3_use varchar(16);
    v_keypart3indic integer;

    begin
    num_of_rows := stoo_rowcnt ;

    if num_of_rows = 0 then
    return ;
    end if ;


    begin
    noruntrg := checkruntrigger ;
    if (noruntrg = 1) then
    begin
    dbms_output.put_line('trigger is bypassed') ;
    return ;
    end;
    end if ;
    end ;



    select count(*) into num_of_rows
    from mdsvendors v
    where :new.vendorid=v.vendorid;

    if num_of_rows=0 then
    begin
    RAISE_APPLICATION_ERROR(-20500,'Trigger forces a rollback');
    ROLLBACK ;
    raise_application_error(-20999, 3100 || '-' || 'Unable to insert.');
    return;
    end;
    end if;


    select next_id into v_next_id
    from next_id
    where table_name='Platforms';

    :new.platformid:=v_next_id+1;

    update next_id
    set next_id=next_id+1
    where table_name='Platforms';

    if (nvl(:new.keypart1_use,'nothing') = 'nothing') then
    v_keypart1indic := 0;
    else
    v_keypart1indic := 1;
    end if;

    if (nvl(:new.keypart2_use,'nothing') = 'nothing') then
    v_keypart2indic := 0;
    else
    v_keypart2indic := 1;
    end if;

    if (nvl(:new.keypart3_use,'nothing') = 'nothing') then
    v_keypart3indic := 0;
    else
    v_keypart3indic := 1;
    end if;

    insert into platformmapkeys( platformid, keypart1indic, keypart2indic, keypart3indic )
    select platformid, v_keypart1indic, v_keypart2indic, v_keypart3indic
    from platforms
    where platform=:new.platform;


    end tri_platforms;


    It works when I try to insert into table Platforms, the Platformid is inserted through this trigger. However, the last "insert into platformmapkeys" was not successful, ie. no record is inserted nor any error message.

    I suspect that whether the table 'Platforms' table is mutating since a record is just inserted and at the same time it is performing insert into 'platformmapkeys' using 'platformid'? Please advise.

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Well, the first thing to realize is that the insert in question is not based on the *current row you are inserting*. It is assuming that *another* row with the same platform column value already exists in the platforms table. If this is the first record being inserted into the platform table with, say, a value of 5 in the platform column, then that last insert will *not* insert anything.

    Did that make sense?

    - Chris

  10. #10
    Join Date
    Dec 2000
    Posts
    87
    Now it make sense.
    But what should I do if I'd like to insert the newly created
    'platformid' into 'platformmapkeys' table?

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