Ufdjrw

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

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.