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

Thread: ORA-01403: no data found

  1. #1
    Join Date
    Jul 2001
    Posts
    108

    Unhappy ORA-01403: no data found

    Hello,

    When I tried to execute the following PL/SQL code, I am facing the ORA-01403: no data found error:

    ------------------------------------------------------------
    DECLARE
    Tname varchar2(30) := 'CUSTOM';
    Crows Integer(10);
    Long varchar2(106);
    BEGIN

    Execute Immediate 'select data_type from DBA_TAB_COLUMNS where data_type=''LONG RAW'''||' and table_name='''||Tname||'''' into Long;

    If Long = 'LONG RAW' Then
    dbms_output.put_line( Tname );
    End If;

    END;
    /

    ------------------------------------------------------------
    Error Message:

    ERROR at line 1:
    ORA-01403: no data found
    ------------------------------------------------------------

    The error is occuring when the Execute Immediate statement returns an empty value. The happens if the table do not contain LONG RAW data_type.

    I do not know how to handle this?

    ThanK You,
    Seenu
    Last edited by Nikee; 12-18-2002 at 11:39 PM.

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    You may want to check your query by using SQL*PLus to confirm there really is no data found (0 rows returned). Why? Because of your use of quotation marks in forming a string value.

    Also, your query can be coded without the dynamic SQL. If you already know the table name, why bother with the dynamic SQL?

    If it is the case where your query is structured properly AND there is no data to be returned, then you can wrap an IF-THEN-ELSE around your execute immediate. You can code it this way as well:

    DECLARE
    v_cnt NUMBER;
    BEGIN
    select count (*) into v_cnt
    from dba_tab_columns
    where data_type = 'LONG RAW'
    and table_name = 'CUSTOM';

    IF v_cnt > 0 THEN
    dbms_output.put_line('There is long raw data in table CUSTOM');
    ELSE
    dbms_output.put_line('There is no long raw data in table CUSTOM');
    END IF;
    END;

    You can also use exception handling, cursors, no data found, just to name a few other ways to do this.

  3. #3
    Join Date
    Nov 2000
    Location
    Israel
    Posts
    268
    Hi,
    You can use EXCEPTIONS:

    DECLARE
    Tname varchar2(30) := 'CUSTOM';
    Crows Integer(10);
    Long varchar2(106);
    BEGIN

    Execute Immediate 'select data_type from DBA_TAB_COLUMNS where data_type=''LONG RAW'''||' and table_name='''||Tname||'''' into Long;

    If Long = 'LONG RAW' Then
    dbms_output.put_line( Tname );
    End If;

    EXCEPTIONS
    WHEN NO_DATA_FOUND
    DBMS_OUTPUT.PUT_LINE('No Data Found');


    END;
    /

    Cheers.
    It is better to ask and appear ignorant, than to remain silent and remain ignorant.

    Oracle OCP DBA 9i,
    C++, Java developer

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    hm what´s the point of that exception if you see the same message as there werent any exceptions defined

  5. #5
    Join Date
    Jul 2001
    Posts
    108
    Hi Stecal,

    Thanks for the solution.

    Regards,
    Seenu
    -Nikee

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