CODEWITHSUNDEEP
oracle-databaseplsql
upog
upog

Reputation: 5531

Handling ORA-01403: no data found

I want to handle no data found. Whenever this exception is raised I want the program to continue, without stopping on error. Below is code snippet

BEGIN
  OPEN C_TABLE_PARTITON_LIST;
  LOOP
    FETCH C_TABLE_PARTITON_LIST INTO TABLE_PARTITION_LIST;
    EXIT WHEN C_TABLE_PARTITON_LIST%NOTFOUND;  
    SELECT COLUMN_NAME INTO PARTITION_COLUMN_NAME from ALL_PART_KEY_COLUMNS 
    sqlstring :='SELECT ( '|| PARTITION_COLUMN_NAME ||'from test';
    EXECUTE IMMEDIATE sqlstring INTO F_RESULT;  
    exception when no_data_found then
      dbms_output.put_line('no data found.');
      DBMS_OUTPUT.put_line( F_RESULT);

  END LOOP;
  CLOSE C_TABLE_PARTITON_LIST;
END;

When I add Exception, my code is breaking with below error

PLS-00103: Encountered the symbol "EXCEPTION" when expecting one of the following:
( begin case declare end exit for goto if loop mod null pragma raise return select update while with << continue close current delete fetch lock insert open rollback savepoint set sql execute commit forall merge pipe purge json_exists json_value json_query json_object json_array
ORA-06550: line 29, column 3:
PLS-00103: Encountered the symbol "CLOSE" when expecting one of the following:
end not pragma final instantiable order overriding static member constructor map

Upvotes: 1

Views: 3960

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

You have to enclose offending part of the script into its own BEGIN-EXCEPTION-END block, e.g.

BEGIN
  OPEN C_TABLE_PARTITON_LIST;
  LOOP
    FETCH C_TABLE_PARTITON_LIST INTO TABLE_PARTITION_LIST;
    EXIT WHEN C_TABLE_PARTITON_LIST%NOTFOUND;  

    begin     --> you need this ...

      SELECT COLUMN_NAME INTO PARTITION_COLUMN_NAME from ALL_PART_KEY_COLUMNS 
      sqlstring :='SELECT ( '|| PARTITION_COLUMN_NAME ||'from test';
      EXECUTE IMMEDIATE sqlstring INTO F_RESULT;  
    exception when no_data_found then
      dbms_output.put_line('no data found.');
      DBMS_OUTPUT.put_line( F_RESULT);

    end;      --> ... and this

  END LOOP;
  CLOSE C_TABLE_PARTITON_LIST;
END;

Note that I just showed the way to do that. Code you posted

  • is incomplete (misses the DECLARE section)
  • is invalid (SELECT statement lacks semi-colon, and probably a WHERE clause
  • SQLSTRING variable won't work; 'from test' should have a leading space, otherwise that statement will be invalid
  • I suggest you first DBMS_OUTPUT the SQLSTRING to make sure it is correct; then execute it.

Upvotes: 2

Related Questions