PL/SQL

jamesccnacmp
jamesccnacmp used Ask the Experts™
on
ORA-01403: no data found
 Error  
OK
 
 

-- Procedure will determine e-mail address of user who selects this page
-- and use it as the return address for mail
 
DECLARE    
 
  CURSOR cur_Members IS
    SELECT First_Name,
           Email_Address AS Email_Address 
      FROM MEMBERS
      WHERE Members_Pk IN 
        (SELECT c001 FROM APEX_collections WHERE collection_name = 'MULTISELECTCOL'); 
l_id number;
l_sender varchar2(100); 
l_receiver varchar2(50);
l_subject varchar2(50);
l_body varchar2(2000);
F104_USER_ID NUMBER := 1;
 
BEGIN
-- Setup Sender Name, Message Title & Message Body to send
-- Will not change while we loop through list of users
  
  SELECT Email_Address INTO l_sender
     FROM MEMBERS
  WHERE Members_Pk = F104_USER_ID; 
 
  l_subject := :P945_EMAIL_SUBJECT;
  l_body := :P945_EMAIL_TEXT;
 
-- Now we get member First Name & E-mail Address
  :P945_USERIDS := :P945_EMAIL_ATTACHMENT;
 
  FOR r_Member in cur_Members LOOP
 
      l_id := APEX_MAIL.SEND(
        p_to        => r_Member.Email_Address,
        p_from      => l_Sender,
        p_subj      => l_subject,
        p_body      => l_body,
        p_replyto => l_Sender);
 
   IF :P945_EMAIL_ATTACHMENT IS NOT NULL THEN
 
    FOR cur_Attach IN (SELECT filename,
                      blob_content, 
                      mime_type
                 FROM apex_application_files
                WHERE name = :P945_EMAIL_ATTACHMENT ) LOOP
        --
        apex_mail.add_attachment( p_mail_id => l_id,
                                  p_attachment => cur_Attach.blob_content,
                                  p_filename =>   cur_Attach.filename,
                                  p_mime_type =>  cur_Attach.mime_type);
    END LOOP;
    COMMIT;
   
   END IF;
  
   APEX_MAIL.PUSH_QUEUE;
 
  END LOOP;
END;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
mrjoltcola
Top Expert 2009

Commented:
One of your queries is returning no rows. Add some debugging lines to print out at each step so you know where it is happening or check

If it is acceptable not to have data, then you can handle the error with a exception handler for the whole procedure.



BEGIN
 ...

EXCEPTION
  WHEN NO_DATA_FOUND THEN
      ...
  WHEN TOO_MANY_ROWS THEN
      ...
  WHEN OTHERS THEN ROLLBACK;  -- blanket handler of all errors
END;
Ivo Stoykov

Commented:
... or your where clause cuts off all records...
Check tables separately. is collection_name upper case? Does Members_Pk = F104_USER_ID exist?
HTH
Ivo Stoykov
Commented:
-- Procedure will determine e-mail address of user who selects this page
-- and use it as the return address for mail
 
DECLARE    
 
  CURSOR cur_Members IS
    SELECT First_Name,
           Email_Address AS Email_Address
      FROM MEMBERS
      WHERE Members_Pk IN
        (SELECT c001 FROM APEX_collections WHERE collection_name = 'MULTISELECTCOL');
l_id number;
l_sender varchar2(100);
l_receiver varchar2(50);
l_subject varchar2(50);
l_body varchar2(2000);
F104_USER_ID NUMBER := 1;
 
BEGIN
-- Setup Sender Name, Message Title & Message Body to send
-- Will not change while we loop through list of users
  BEGIN
  SELECT Email_Address INTO l_sender
     FROM MEMBERS
  WHERE Members_Pk = F104_USER_ID;
  EXCEPTION WHEN NO_DATA_FOUND THEN
    l_sender := ' ';
 END;      
 
  l_subject := :P945_EMAIL_SUBJECT;
  l_body := :P945_EMAIL_TEXT;
 
-- Now we get member First Name & E-mail Address
  :P945_USERIDS := :P945_EMAIL_ATTACHMENT;
 
  FOR r_Member in cur_Members LOOP
 
      l_id := APEX_MAIL.SEND(
        p_to        => r_Member.Email_Address,
        p_from      => l_Sender,
        p_subj      => l_subject,
        p_body      => l_body,
        p_replyto => l_Sender);
 
   IF :P945_EMAIL_ATTACHMENT IS NOT NULL THEN
 
    FOR cur_Attach IN (SELECT filename,
                      blob_content,
                      mime_type
                 FROM apex_application_files
                WHERE name = :P945_EMAIL_ATTACHMENT ) LOOP
        --
        apex_mail.add_attachment( p_mail_id => l_id,
                                  p_attachment => cur_Attach.blob_content,
                                  p_filename =>   cur_Attach.filename,
                                  p_mime_type =>  cur_Attach.mime_type);
    END LOOP;
    COMMIT;
   
   END IF;
 
   APEX_MAIL.PUSH_QUEUE;
 
  END LOOP;
END;


TRY THIS..
jamesccnacmp

Author

Commented:
thank you it work

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial