Create a job to send out emails from APEX

 You can automate stock price update emails in Oracle APEX using Oracle Scheduler and APEX_MAIL. Here's how:

---

1. Create a Table to Store User Emails

If you don't already have a table for user emails, create one:

CREATE TABLE STOCK_SUBSCRIBERS (
    user_id   NUMBER PRIMARY KEY,
    email     VARCHAR2(255) UNIQUE NOT NULL
);

Populate this table with the 200 users who should receive emails.

---

2. Create a PL/SQL Procedure to Send Emails

CREATE OR REPLACE PROCEDURE SEND_STOCK_EMAILS AS
    v_subject    VARCHAR2(100);
    v_message    CLOB;
    v_email      VARCHAR2(255);
    CURSOR c_users IS SELECT email FROM STOCK_SUBSCRIBERS;
BEGIN
    -- Set email subject
    v_subject := 'Stock Price Update';

    -- Construct email body
    v_message := '<h2>Latest Stock Prices</h2><table border="1"><tr><th>Stock Code</th><th>Price</th></tr>';

    FOR rec IN (SELECT stock_code, current_price FROM STOCK_PRICES) LOOP
        v_message := v_message || '<tr><td>' || rec.stock_code || '</td><td>' || rec.current_price || '</td></tr>';
    END LOOP;

    v_message := v_message || '</table>';

    -- Send emails to all users
    FOR user_rec IN c_users LOOP
        v_email := user_rec.email;
        APEX_MAIL.SEND(
            p_to        => v_email,
            p_from      => 'noreply@yourdomain.com',
            p_subj      => v_subject,
            p_body      => v_message,
            p_body_html => v_message
        );
    END LOOP;

    -- Commit email queue
    APEX_MAIL.PUSH_QUEUE;
END SEND_STOCK_EMAILS;

• This procedure fetches stock prices from STOCK_PRICES and sends an HTML-formatted email.
• APEX_MAIL.PUSH_QUEUE ensures emails are processed.

---

3. Schedule the Job to Run at 9:30 AM & 3:30 PM on Weekdays

Use Oracle DBMS Scheduler to trigger the email procedure.

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'STOCK_EMAIL_JOB',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN SEND_STOCK_EMAILS; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,15; BYMINUTE=30',
        enabled         => TRUE
    );
END;

• Runs only on weekdays (BYDAY=MON,TUE,WED,THU,FRI).
• Executes at 9:30 AM and 3:30 PM.

---

4. Verify APEX Email Configuration

Ensure that APEX_INSTANCE_ADMIN has email configured:

BEGIN
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'your.smtp.server');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'your_email_user');
    APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'your_email_password');
END;

---

5. Monitor Scheduled Job

Check if the job runs correctly:

SELECT job_name, state, last_start_date, next_run_date 
FROM USER_SCHEDULER_JOBS 
WHERE job_name = 'STOCK_EMAIL_JOB';

Handling ORDS error for database outages

 When handling database outages in an Oracle REST Data Services (ORDS) API, it's essential to implement graceful failure handling to ensure the API remains responsive and provides meaningful error messages. Here’s how you can manage a database outage effectively:

---

1. Implement Exception Handling in PL/SQL

If your ORDS API is backed by a PL/SQL procedure, modify it to catch database errors and return a proper HTTP response.

Example PL/SQL Exception Handling

CREATE OR REPLACE PROCEDURE my_api_procedure (
    p_input   IN  VARCHAR2,
    p_output  OUT VARCHAR2,
    p_status  OUT NUMBER
)
AS
BEGIN
    -- Your database logic here
    SELECT some_value INTO p_output FROM my_table WHERE some_column = p_input;

    -- Success status
    p_status := 200;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_output := 'Data not found';
        p_status := 404;
    WHEN OTHERS THEN
        p_output := 'Database error occurred';
        p_status := 503; -- Service Unavailable
END my_api_procedure;

• Use 503 Service Unavailable for database connection issues.
• Use 404 Not Found for missing data.

---

2. Configure ORDS Error Handlers

ORDS allows custom error handling using error-handlers in ords-config.xml.

Example: ORDS Custom Error Handler

Modify the ORDS configuration to return JSON errors instead of exposing internal database errors.

1. Locate defaults.xml (inside ORDS config directory).
2. Add a custom error mapping:

   <entry key="error-mapping">
       <error>
           <status-code>503</status-code>
           <message>Database temporarily unavailable</message>
       </error>
   </entry>
   

3. Restart ORDS:

   java -jar ords.war standalone

---

3. Use Connection Pooling & Timeout Settings

If the database is slow or unreachable, configure ORDS connection pooling settings to avoid API timeouts.

Modify ords-config.xml:

<entry key="jdbc.InitialLimit">5</entry>
<entry key="jdbc.MinLimit">5</entry>
<entry key="jdbc.MaxLimit">50</entry>
<entry key="jdbc.InactivityTimeout">30</entry> <!-- Closes idle connections -->
<entry key="jdbc.AbandonedConnectionTimeout">60</entry> <!-- Reclaims lost connections -->

---

4. Implement a Health Check Endpoint

Create a simple ORDS endpoint to check if the database is up.

Example: Health Check SQL Query

SELECT 'OK' AS status FROM dual;

• If the database is down, the API should return a 503 Service Unavailable.

---

5. Enable ORDS Standalone Mode with Connection Fallback

If your ORDS API is running in standalone mode, configure a fallback response.

Modify standalone.properties:

standalone.failure.message = "Database is currently unavailable. Please try again later."
standalone.failure.status = 503

---

6. Monitor ORDS Logs & Set Alerts

• Enable ORDS Logging: Logs are available in ords.log
• Use Oracle APEX or Prometheus for monitoring.
• Set alerts in Oracle Cloud or a monitoring tool like Nagios.

Get GEOLOCATION based on the IP address of the user based on NEUSTAR data feed in PL/SQL

 CREATE OR REPLACE PROCEDURE GET_GEOLOCATION(

    p_ip_address   IN  VARCHAR2,

    p_city         OUT VARCHAR2,

    p_state        OUT VARCHAR2,

    p_country      OUT VARCHAR2,

    p_latitude     OUT NUMBER,

    p_longitude    OUT NUMBER,

    p_status_code  OUT NUMBER

) AS

    v_decimal_ip   NUMBER;

BEGIN

    -- Validate and convert IPv4 to decimal

    IF REGEXP_LIKE(p_ip_address, '^((25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[0-1]?[0-9][0-9]?)$') THEN

        v_decimal_ip := (TO_NUMBER(REGEXP_SUBSTR(p_ip_address, '^[^.]+')) * POWER(256, 3)) +

                        (TO_NUMBER(REGEXP_SUBSTR(p_ip_address, '[^.]+', 1, 2)) * POWER(256, 2)) +

                        (TO_NUMBER(REGEXP_SUBSTR(p_ip_address, '[^.]+', 1, 3)) * POWER(256, 1)) +

                        (TO_NUMBER(REGEXP_SUBSTR(p_ip_address, '[^.]+', 1, 4)));

    

    -- Validate and convert IPv6 to decimal

    ELSIF REGEXP_LIKE(p_ip_address, '^([0-9a-fA-F]{1,4}:){7}[0-9a-fA-F]{1,4}$') THEN

        -- IPv6 is usually stored as binary or separate parts, but this assumes a precomputed decimal representation

        v_decimal_ip := TO_NUMBER(UPPER(TRANSLATE(p_ip_address, ':', '')), 'XXXXXXXXXXXXXXXX');

    ELSE

        p_status_code := 400; -- Invalid IP address

        RETURN;

    END IF;

    -- Lookup IP in GEOLOCATION table

    BEGIN

        SELECT city, state, country, latitude, longitude

        INTO p_city, p_state, p_country, p_latitude, p_longitude

        FROM GEOLOCATION

        WHERE v_decimal_ip BETWEEN FROM_IP_ADDR AND TO_IP_ADDR;

        

        p_status_code := 200; -- Success

    EXCEPTION

        WHEN NO_DATA_FOUND THEN

            p_status_code := 404; -- IP address not found

    END;

END GET_GEOLOCATION;

Delays in Up suburban trains everyday in Central Railway Mumbai

No doubt, suburban trains are lifeline of Mumbai thus it is important they run all the time 7 days of the week and 365 days a year.

Thanks to dedicated team of Railway officials who work round the clock and on weekends to ensure the lifeline is running. 

But there are certain operational aspects which inhibit this inspite of best efforts, let me explain.

Trains in down direction reach their destination late due to various reasons and thus start their return journey later than the scheduled time. Suburban trains running long distances like Kasara, Karjat or Asangaon are often delayed in both directions. 

When these trains reach Kalyan or Thane in UP direction, trains which are running on-schedule or scheduled later get precedence and come on main up line. This leads to suburban trains running late, do not get clear block and have to run with Yellow or double Yellow aspect till Dadar/CSMT. The motorman of on-time train make best efforts to not reach earlier (3 mins) than scheduled time at the next stop thus the trains trailing them cannot catch-up with time inspite of rake capacity. Medha/Siemens rake are much more powerful than earlier rakes.

What is important noting is, someone who has started his journey at 60-90 mins earlier will only get delayed further without any scope to catch-up, as compared to anyone who is travelling from say Dombivli or Thane.

In order to bridge the delay, a operational suggestion 

1. All suburban trains running in UP direction run with 0:00 timing, Indicator only displays stops
2. Passenger's in between keen to know what is train destination or the interim stops so as to make the decision to board or skip, the upcoming train
3. Motorman make best use of clear signal and reaches Dadar/CSMT in best possible time no worry of early departure regulation
4. All late trains running catch-up with lost time and passenger reach the destination maybe just few mins late than original delay.

Similarly, the new timetable needs to consider revising time between stations as the running speed has improved drastically over the years with superior quality/powerful rakes.

Central Railway: Testing commuters patience !!

Since last one year, repairs have been carried out to Parsik Tunnel almost on regular basis. All this has imposed speed restrictions on the rail traffic on both up  down directions. Adding upto 10 mins of precious travel time of commuters. All this is happening with out any formal plan or end date in sight.

The speed restrictions often leads to pile-up of trains (both local and mail/exp) trains upto Diva station on the fast Up track while the Slow Up track is virtually free. For the organisation which boasts of having latest Train Management System (TMS) cannot sense this pile up and plan some remedial action immediately. Similar situation on Western Railway gets handled in a different manner, trains services are re-routed on other tracks thus limiting impact to just 2-3 consecutive locals.

Central Railway has majority of commuters from far off suburbs like Badlapur, Titwala, Asangaon. The commuter traffic has gone up exponentially in these new suburbs as you can see the trains crowded at starting and ending points. People travelling to these suburbs have a travel time of minimum 45+ mins, all the delays tests their patience on daily basis. We understand that services cannot be added due to capacity issues but even ensuring trains running ontime can be of great help/relief to commuters.

Another flow of commuters is from Kalyan and beyond to Navi Mumbai, they change to Trans Harbour link at Thane. This adds to commuters getting into fast locals to get down at Thane thus depriving genuine commuters who have to travel to Dadar/CST or Western suburbs. There have been numerous request made by passenger groups to start Kalyan to Vashi service via Parsik tunnel. This one service can make difference to 40% commuters commutting from Kalyan to CST. Earlier it was AC-DC conversion issue but now with everything AC, the service can be started. There are numerous slots in Thane Vashi schedule which can be taken up by these services, only leveraging available capacity.

Due to agitation of commuters in Diva new platform was built tracks diverted with objective to give halts to fast trains. CR fails to understand that the traffic between Thane & Dombivli is very dense, thus in the morning even if the train stops, there would be space only for one passenger to get it and others clinging on footboard. Waste of time. From what has been published in dailies, fast trains from Kasara and Karjat have been given halts one of the most sarcastic decisions !!

 Another Bottleneck is Kalyan Junction. Trains have to wait for upto 10 mins to get berthed on a platform, this is applicable to all trains passing thru this station. Why is it so difficult to have simple SOP to berth the train as it reaches home signal rather than thinking for 10 mins adding to travel time and commuter woes.

Looks like CR leadership team sitting out of Head Office is conveniently ignorant of the facts and proudly publishing on-time statistics of over 90%, ground reality is grossly different.

True test of Commuter patience !!

WebCenter Out-Of-Office notifications keep going out

Issue: When a task is approved by a user it is routed to next person in the process flow. If the person to whom the task is assigned has setup OOO notification, the first user will receive a notification (if such a alert has been enabled with adequate information about the task) with OOF Notification:

Cause: All OOO notifications are logged in SOAINFRA.WFUserVacation table. 

Check for the user record with start and end date, check if the dates are correct. This behaviour is observed when the start date and/or end date are NULL

Fix: look-up for second user (for whom the OOO is setup), update the start/end date whichever is blank, the other user should not receive any OOO notifications hereafter.

Regency Estate, Dombivili Bus Timings

Bus services of Regency Estate Co-operative Housing, Dombivli

The Society operates bus services for its residents & their guests between complex to Dombivli Station East.

Complex to Dombivli Station East leaving at 

Weekdays : Monday to Saturday (excluding public holidays) 

Morning hours: 5:30, 6:00, 6:30, 7:00, 7:20, 7:40, 8:00, 8:20, 8:40, 9:00, 9:20, 9:40.

Afternoon hours: 13:00, 13:30, 14:00, 14:30, 15:00, 15:30, 16:00, 16:30

Evening hours: 21:00, 21:20, 21:40, 22:00, 22:30, 22:45, 23:00

Sunday

Morning hours: 7:30, 8:00, 8:30, 9:00, 9:30

Afternoon hours: 13:00, 14:00, 15:00, 16:00

Evening hours: 21:00, 21:30

• Regency Estate Bus Stop (starting point)
• Venkatesh Petrol Pump
• Vico Naka/Hotel Suyog
• Pendharkar College (Opp GNP Arcadia)
• Gharda Circle
• Shelar Naka
• Tilak Putla/Statue
• Tai Pingle Chowk (Sarvesh Mangal Karyalay)
• Phadke Road corner
• MSEB office, Phadke Road (last stop)

Dombivli Station to Complex leaving at

Weekdays : Monday to Saturday (excluding public holidays) 

Morning hours: 5:45, 6:15, 6:45, 7:15, 7:35, 7:55, 8:15, 8:35, 8:55, 9:15, 9:35, 9:55

Afternoon hours : 13:15, 13:45, 14:15, 14:30, 15:15, 15:45, 16:15, 16:45

Evening hours :21:20, 21:40, 22:00, 22:15, 22:45, 23:00, 23:15

Sunday

Morning hours: 7:45, 8:15, 8:45, 9:15, 9:45

Afternoon hours: 13:15, 14:15, 15:15, 16:15

Evening hours: 21:15, 22:00

• PP Chambers (outside Maharaja Collections/Raymond shop) (starting point)
• Tai Pingle Chowk (opp Hotel Sunny)
• Tilak Statue (opp State Bank Of India )
• Shelar Naka
• Gharda Circle
• Pendharkar College
• Vico Naka
• Venkatesh Petrol Pump
• Regency Estate Bus Stop (last stop)

Services from 10:00 to 12:30 and 17:00 to 21:00 follow a bit different route due to traffic restrictions.

Complex to Phade Road leaving at 

Weekdays : Monday to Saturday (excluding public holidays)

Morning hours: 10:00, 10:20, 10:40, 11:00, 11:30, 12:00, 12:30.

Evening hours:  17:00, 17:30, 17:45, 18:00, 18:15, 18:30, 18:45, 19:00, 19:15, 19:30, 19:45, 20:00, 20:15, 20:30, 20:45

Sunday

Morning hours:10:00,10:30, 11:00, 11:15, 12:00

Evening hours:17:00, 18:00, 18:30, 19:00, 19:30, 20:00, 20:30

• Regency Estate Bus Stop (starting point)
• Venkatesh Petrol Pump
• Vico Naka/Hotel Suyog
• Pendharkar College (Opp GNP Arcadia)
• Gharda Circle
• Shelar Naka
• Tilak Putla/Statue
• Tai Pingle Chowk (Sarvesh Mangal Karyalay)
• Phadke Road corner (last stop)

Phadke Road to Complex leaving at 

Weekdays : Monday to Saturday (excluding public holidays)

Morning hours: 10:15, 10:40, 11:00, 11:15, 11:45, 12:15, 12:45.

Evening hours:  17:15, 17:45, 18:00, 18:15, 18:30, 18:45, 19:00, 19:15, 19:30, 19:45, 20:00, 20:15, 20:30, 20:45

Sunday

Morning hours: 10:15, 10:40, 11:00, 11:15, 11:45, 12:15, 12:45.

Evening hours:  17:15, 18:15, 18:45, 19:15, 19:45, 20:15, 20:45

• Phade Road (outside Gala chemost) (starting point)
• Manjunath Vidyalaya (opp Harish Snack bar, VP Road)
• Shelar Naka
• Gharda Circle
• Pendharkar College
• Vico Naka
• Venkatesh Petrol Pump
• Regency Estate Bus Stop (last stop)