Update showing no data found error

Question

The following update is not updating my table. It is stating the following error message when submitting:

ORA-01403: no data found

I have made sure all the fields are input with some data, but it still states the following error of there being no data. any ideas or help?

DECLARE

  l_upload_size INTEGER;
  l_upload_blob BLOB;
  l_image_id    NUMBER;
  l_image       ORDSYS.ORDImage;
  l_name        VARCHAR2(100);
  l_address        VARCHAR2(100);  
  l_postcode       VARCHAR2(100);
  l_description   VARCHAR2(100);

BEGIN

  --
  -- Get the BLOB of the new image from the APEX_APPLICATION_TEMP_FILES (synonym for WWV_FLOW_TEMP_FILES)
  -- APEX 5.0 change from APEX_APPLICATION_FILES which has been deprecated
  -- APEX_APPLICATION_TEMP_FILES has fewer columns and is missing doc_size
  --

  SELECT
    blob_content
  INTO
    l_upload_blob
  FROM
    apex_application_temp_files
  WHERE
    name = :P3_filename;
  --
  -- Insert a new row into the table, initialising the image and
  -- returning the newly allocated image_id for later use
  --
UPDATE bars
SET
      image_id = :P3_IMAGE_ID,
      filename = :P3_FILENAME,
      image = ORDSYS.ORDImage(),
      name = :P3_NAME,
      address = :P3_ADDRESS,
      postcode = :P3_POSTCODE,
      description = :P3_DESCRIPTION

  WHERE
    image_id = l_image_id;

  -- find the size of BLOB (get doc_size)
  l_upload_size := dbms_lob.getlength(l_upload_blob);
  -- copy the blob into the ORDImage BLOB container
  DBMS_LOB.COPY( l_image.SOURCE.localData, l_upload_blob, l_upload_size );

  -- set the image properties
  l_image.setProperties(); 
  create_blob_thumbnail(l_image_id);



END;

You have not set `l_image_id`, so it is not surprising that the `WHERE` clause fails to find any matches. — Gordon Linoff, Jan 02 '16 at 13:23
The update won't get that error though, it will just update zero rows without complaining. The ORA-01403 is coming from the earlier select, or possibly a function you call. The full exception stack trace will make that clearer. So is `p3_filename` set, and does it exatl match a single row in the table (including case, and possibly trailing whitespace)? — Alex Poole, Jan 02 '16 at 13:56

score 3 · Accepted Answer · answered Jan 02 '16 at 14:57

Like Alex mentioned, the ORA-01403 is not thrown by the UPDATE, but by the SELECT. Make sure that P3_filename has a value and there is matching data in the table.

Simple to prove this, just enclose the SELECT in an exception block like this:

....
BEGIN

  SELECT
    blob_content
  INTO
    l_upload_blob
  FROM
    apex_application_temp_files
  WHERE
    name = :P3_filename;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
     <<Add your troubleshooting code here, like display the variable>>
END
.....

score 1 · Answer 2 · edited May 23 '17 at 12:25

1

Maybe you use the SQL%ROWCOUNT to check the number of rows affected. Here is a good example for it.

edited May 23 '17 at 12:25

Community

1
1

answered Jan 02 '16 at 16:30

Rohan

1,960
3
22
30

Update showing no data found error

2 Answers2