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;