This content has been marked as final. Show 22 replies
does your script use the APEX_MAIL-API or other means to send email?
If it uses APEX_MAIL you could monitor your mail queue to see what happens to the mails. To do that, login to the APEX internal administration (workspace internal) and go to Home>Manage Service>Mail Queue
It may be helpful to see the email-part of your script anyway to determine possible other reasons for the failure.
Do you have any log on your Exchange server where you could monitor if the database server you run APEX on actually reaches it? Perhaps you need some additional firewall rule to allow the host of the database server to connect to your new Exchange environment.
thanks for the reply
it does seem to use apex_mail
the leaver notification process code is pasted below
how do i get into the internal administration?
PROCEDURE send_leaver_notification IS
CURSOR leavers IS SELECT employee_id,
first_name || ' ' || last_name employee_name,
WHERE end_date <= SYSDATE
AND leaver_notification_sent IS NULL ;
FOR r IN leavers
l_body:='This email has been generated automatically to notify you that the named person below is leaving Attenda Limited.
This IS confidential information, DO NOT discuss OR divulge this information UNTIL formal notification has been given BY the Line Manager.
1) You must complete the tasks AS listed IN the attached spreadsheet
2) Print OFF the complete worksheet (specific TO your area)
3) SIGN it
4) RETURN TO HR
This IS a ISO27001 AUDIT requirement AND must be completed immediately you receive this notification
Employee Name: ' || r.employee_name || chr(10) ||
'Last Employment Date (date leaving the building): ' || r.end_date || CHR(10) ||
'Last Pay Date (end of notice period: ' || r.last_pay_date ||CHR(10) ||
'Leaving Status: ' || r.leaving_status || CHR(10) || CHR(10) ||
Attenda Human Resources';
WHERE TYPE='New Starter/Leaver Notification';
WHERE TYPE='Finance Notification';
l_to := l_to || ',' || temp;
OPEN l_attachment_cursor FOR
SELECT doc,r.employee_name || '.xls',mime_type FROM documents WHERE doc_type='Leaver Spread Sheet';
p_to => l_to,
p_subj => 'Leaver Notification ' || r.employee_name ,
p_attachments => l_attachment_cursor);
SET LEAVER_NOTIFICATION_SENT= 'Y',
WHERE EMPLOYEE_ID =r.employee_id;
you script uses APEX_MAIL, but the actual mail send process seems to be stored in the package Att_Hr_Pkg.
The internal administration can be reached by logging in as "ADMIN" (with APEX Admin password) to the workspace "INTERNAL" or by calling <your_base_url>/apex/apex_admin and logging in as ADMIN there.
In the internal administration, you also could check if the right SMTP server is configured for APEX. This is done in Home>Manage Service>Instance Settings in the Email section. If you haven't been there before, this might already be the solution for your problem.
hi thanks again for the reply
this system was set up when the work was outsiourced to india
we dont have an admin password to go into the apex_admin area
is there a way of setting one up do you know?
you can download the apex installation package that corresponds to the APEX-version you currently use, unpack it on any machine with a database client (e.g. SQL*Plus) on it and connect to the database instance of your APEX as sys and run apxchpwd.sql , for example in sqlplus like that:
You'll be prompted to enter your new APEX Admin password.
thanks again for the reply
is this the download you mean?
Exactly, if you're currently using APEX 3.1.x
hi mate thanks again
i cant see how to open it
im probably sounding stupid now, but how do i get on it once it is unpacked?
once you downloaded the file, it's a simple zip archive (e.g., http://download.oracle.com/otn/java/appexpress/apex_3.1.2.zip - Downloadlink appears after you accepted the OTN License Agreement). It contains a directory "apex" and inside that directory you'll find apxchpwd.sql
The script does not have any further dependencies on other scripts, so you could copy that file to any other workstation with access to a SQL client programm.
If you don't get it working, I could also post the content.
yep, im with you now
i have the content of the file
i have pasted it into the sql plus worksheet in the oracle clent and it kind of just runs all commands without waiting for me to enter the password
it just immediately returns all below
Enter a password for the ADMIN user 
...changing password for ADMIN
Enter value for passwd: Enter value for passwd:
PL/SQL procedure successfully completed.
It seems the input-directive is not supported by the sqlplus worksheet when executing the script as pasted version. Do you have access to the command line version of sqlplus, where you could run the script as described above? (connect as sys and execute the script with @apxchpwd.sql). Possibly, the @apxchpwd.sql also works in the worksheet. You could give the absolute path to the script, if it is not found by the worksheet, like that
it was looking so good mate
entered the new password and pressed enter and then it stuck on the line
...changing the password for admin user
by the way, once it works so i log into apex_admin as user ADMIN?
entered the new password and pressed enter and then it stuck on the lineThat's unusual. Perhaps you just press return/enter once more? If that doesn't help, you may have a lock situation caused by the previous session. Try to terminate it so your transaction may continue.
...changing the password for admin user
by the way, once it works so i log into apex_admin as user ADMIN?That's right.
nope cant get it to work
the only way to get it to stop processing is to press control and break and then it just completely stops
thanks for all your help anyway, i have learned a lot