Skip to Main Content

General Cloud Infrastructure

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

I can't connect any smtp server ORA-29278: 421 service not available error

orkun_tunc_bilgicSep 6 2023 — edited Apr 1 2024

Hello,

I'm trying to send mails by using apex_mail with smtp.gmail.com. But i got 421 service not available error after validating configurations with this =>

BEGIN
APEX_INSTANCE_ADMIN.VALIDATE_EMAIL_CONFIG;
END;

Then i decided to try utl_smtp package to see why this error occurs. Then i basically write this code =>

DECLARE
   l_mailhost    VARCHAR2 (255) := 'smtp.gmail.com';
   l_port        NUMBER := 587;
   l_sender      VARCHAR2 (255) := 'my_sender_mail';
   l_recipient   VARCHAR2 (255) := 'mail_to';
   l_username    VARCHAR2 (255) := 'my_user_name';
   l_password    VARCHAR2 (255) := 'my_app_password_from_google';
   l_conn        UTL_SMTP.CONNECTION;
BEGIN
    dbms_output.put_line(0);
   l_conn := utl_smtp.open_connection
               ( host                          => l_mailhost, 
                 port                          => l_port
               );
   dbms_output.put_line(1);
   UTL_SMTP.EHLO (l_conn, l_mailhost);
   UTL_SMTP.STARTTLS (l_conn);
   UTL_SMTP.EHLO (l_conn, l_mailhost);
   UTL_SMTP.AUTH (l_conn, 'LOGIN', l_username, l_password);
   UTL_SMTP.MAIL (l_conn, l_sender);
   UTL_SMTP.RCPT (l_conn, l_recipient);
   
   UTL_SMTP.DATA (
      l_conn,
      'From: ' || l_sender || UTL_TCP.CRLF ||
      'To: ' || l_recipient || UTL_TCP.CRLF ||
      'Subject: Test email' || UTL_TCP.CRLF ||
      'MIME-Version: 1.0' || UTL_TCP.CRLF ||
      'Content-Type: text/html; charset=UTF-8' || UTL_TCP.CRLF ||
      UTL_TCP.CRLF ||
      '<html><body><h1>Hello World</h1><p>This is a test email with HTML</p></body></html>'
   );
   dbms_output.put_line(2);
   UTL_SMTP.CLOSE_DATA (l_conn);
   dbms_output.put_line(3);
   UTL_SMTP.QUIT (l_conn);
END;

/

But i realize that it never connect to smtp server.

Then i tried to connect it =>

DECLARE
  c utl_smtp.connection;
BEGIN
  c := UTL_SMTP.OPEN_CONNECTION(
       host => 'smtp.gmail.com',
       port => 587);
    UTL_SMTP.CLOSE_CONNECTION(c);
END;

But i get =>

Error report -
ORA-29278: SMTP temporary error: 421 Service not available
ORA-06512: position "SYS.UTL_SMTP",  line 57
ORA-06512: position "SYS.UTL_SMTP",  line 178
ORA-06512: position "SYS.UTL_SMTP",  line 239
ORA-06512: position  line 4
29278. 00000 -  "SMTP transient error: %s"
*Cause:    A SMTP transient error occurred.
*Action:   Correct the error and retry the SMTP operation.

I'm using oracle cloud always free. I can send mails by using Email Delivery service. Also i have a node.js API to send mails and i used nodemailer npm package to do that and it is successfully sending mails with the exactly same credentials.

I've got acl errors before but after adding some acl to my apex_230100 and admin user, that error no more exist.

Here is my acl config =>

BEGIN
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host =>'*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve','smtp'),
        principal_name => 'ADMIN',
        principal_type => xs_acl.ptype_db)
    );
    DBMS_NETWORK_ACL_ADMIN.APPEND_HOST_ACE(
        host =>'*',
        ace => xs$ace_type(privilege_list => xs$name_list('connect', 'resolve','smtp'),
        principal_name => 'APEX_230100',
        principal_type => xs_acl.ptype_db)
    );
END;

Can somebody help me about this ? Is there something wrong with my configurations ? I cant find any related solution about this. Most of them saying that it is about firewall or acl. But i dont have any specific firewall configuration and i think my acl configs are done .

UPDATE

Hello, i came here late enough. But i think this implementation i mentioned below can be useful many others. Because this is not exactly about just doing a workaround , this is about building couple of things to use them in workspace wide for many applications maybe. Here is the implementation i've done and if you have questions about it just ask ^_^

SERVER SIDE:

1 - Writing Nodejs API

I used nodemailer here , you can use whatever you like as npm package or even you can directly build your smtp server.

#!/usr/bin/env node 
require("dotenv").config({path: '/usr/local/bin/.env'}); 
const PORT = 3004; 

const express = require("express"); 
const cors = require("cors"); 
const nodemailer = require("nodemailer"); 
const multiparty = require("multiparty"); 
var bodyParser = require('body-parser'); 

// instantiate an express app 
const app = express(); 
// cors 
app.use(cors({ origin: "*" })); 
// parse application/x-www-form-urlencoded 
app.use(bodyParser.urlencoded({ extended: false })) 

// parse application/json 
app.use(bodyParser.json()) 

const transporter = nodemailer.createTransport({ 
host: 'smtp.gmail.com', 
port: 587, 
auth: { 
user: process.env.EMAIL, 
pass: process.env.PASS, 
}, 
}); 

// verify connection configuration 
transporter.verify(function (error, success) { 
if (error) { 
console.log(error); 
} else { 
console.log("Server is ready to take our messages"); 
} 
}); 

app.post("/sendMail", (req, res) => { 

var ip = req.headers['x-forwarded-for'] || req.socket.remoteAddress 
var body = req.body; 
var attachments = []; 
if( body.attachments ){ 
attachments = JSON.parse(body.attachments).attachments; 
} 
var cc = body.cc; 
var bcc = body.bcc; 
const mail = { 
sender: `${body.name} <${process.env.EMAIL}>`, 
to: body.email, // receiver email, 
cc:cc, 
bcc:bcc, 
subject: body.subject, 
html: body.html, 
attachments: attachments 
}; 
transporter.sendMail(mail, (err, data) => { 
if (err) { 
console.log(err); 
res.status(500).send('NOK') 
} else { 
res.status(200).send('OK') 
} 
}); 
}); 

/*************************************************/ 
// Express server listening... 
app.listen(PORT, () => { 
console.log(`Listening on port ${PORT}...`); 
}); 

2 - Adjusting the NGINX config

#we defined a limit which is 1 per minute. You can change it whatever you like but check supported time units. 
limit_req_zone $binary_remote_addr zone=limitreqsbyaddr:21m rate=1r/m; 

#if you dont define this underscores in headers, nginx will not recognize your headers like x_api_key we used below -> 
underscores_in_headers on; 

map $http_x_api_key $valid_api_key_sendmail { 
default 0; 
"YOUR API KEY (you can create a random combination with plsql or something else)" 1; 
} 

server { 
server_name yourdomain.com www.yourdomain.com; 
root /usr/share/nginx/html; 
index index.html; 
try_files $uri /index.html; 

listen [::]:443 ssl ipv6only=on; # managed by Certbot 
listen 443 ssl; # managed by Certbot 
ssl_certificate /etc/letsencrypt/live/yourdomain.com/fullchain.pem; # managed by Certbot 
ssl_certificate_key /etc/letsencrypt/live/yourdomain.com/privkey.pem; # managed by Certbot 
include /etc/letsencrypt/options-ssl-nginx.conf; # managed by Certbot 
ssl_dhparam /etc/letsencrypt/ssl-dhparams.pem; # managed by Certbot 

# This locaiton /ords/ and /i/ is for using oracle cloud with your own domain. 
location /ords/ { 
proxy_pass https://youroracleclouddomain.oraclecloudapps.com/ords/; 
proxy_set_header Origin "" ; 
proxy_set_header X-Forwarded-Host $host; 
proxy_set_header X-Real-IP $remote_addr; 
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; 
proxy_set_header X-Forwarded-Proto $scheme; 
proxy_connect_timeout 600; 
proxy_send_timeout 600; 
proxy_read_timeout 600; 
send_timeout 600; 
} 

location /i/ { 
proxy_pass https://youroracleclouddomain.oraclecloudapps.com/i/; 
proxy_set_header X-Forwarded-Host $host; 
proxy_set_header X-Real-IP $remote_addr; 
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; 
} 
location /api/ { 
proxy_set_header X-Forwarded-Host $host; 
proxy_set_header X-Real-IP $remote_addr; 
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; 
#here is our api to redirect all parameters to our nodejs for sending mails. 
location /api/sendMail{ 
# Set a variable to 1 if the request is from any subdomain of rapidapi.com, otherwise set it to 0 
limit_req zone=limitreqsbyaddr; 
set $hasaccess 0; 
if ($valid_api_key_sendmail != 0){ 
set $hasaccess 1 ; 
} 
if ($hasaccess = 0) { 
return 403; 
} 
proxy_pass http://localhost:3004/sendMail; 
} 
} 

} 
server { 
if ($host = www.yourdomain.com) { 
return 301 https://$host$request_uri; 
} # managed by Certbot 


if ($host = yourdomain.com) { 
return 301 https://$host$request_uri; 
} # managed by Certbot 


listen 80; 
listen [::]:80; 
server_name yourdomain.com www.yourdomain.com; 
return 404; # managed by Certbot 
}

3 - Adjusting Linux Services Automatically(Extra)

If you use linux, you can adjust a service in your server. But instead of doing it manual, you can make it dynamic. For example, i'm using webhooks on github and whenever something pushed, i run a shell script with nodejs on my server to pull it and running this shell script below →

#!/bin/bash 

# Set username and personal access token for authentication 
git config --global credential.username $GIT_USERNAME 
git config --global credential.helper '!f() { echo "username=$GIT_USERNAME"; echo "password=$GIT_ACCESS_TOKEN"; }; f' 

# Navigate to the root directory of the repository 
cd /usr/local/bin/YOUR_API 

# Pull the latest changes from the repository 
sudo git stash 
sudo git pull origin dev 

# Change to the YOUR_API directory 
cd /usr/local/bin/YOUR_API 

# Loop through each subdirectory in YOUR_API and run npm install, chmod on server.js, and create/update service file 
for dir in */; do 
# echo "Installing dependencies for $dir" 
(cd "$dir" && npm install) 
chmod +x "$dir/server.js" 

# Create/update the service file 
service_file="/etc/systemd/system/${dir%/}.service" 
cat > "$service_file" <<EOF 
[Unit] 
Description=Run my node js app in background 

[Service] 

# The path to node app file that will be executed in background when system startup. 
ExecStart=/usr/local/bin/YOUR_API/${dir}server.js 
Restart=always 
User=root 

# For Debian/Ubuntu Linux OS uses 'nogroup', for RHEL/Fedora Linux OS uses 'nobody' 
Group=root 
Environment=PATH=/usr/bin:/usr/local/bin 
Environment=NODE_ENV=DEV 
WorkingDirectory=/usr/local/bin/YOUR_API/${dir} 

[Install] 
WantedBy=multi-user.target 
EOF 
systemctl daemon-reload 
systemctl enable "${dir%/}" 
systemctl restart "${dir%/}" 
done

Our server side implementation done , and the best thing about it is you can use this in everywhere that allows you to send a request to api.

DB SIDE:

1- Procedure

Here is the procedure to send mails, i'm using html templates in my database.

As you can see i used api_key & parameters table to get sensitive informations. Also adjusted the admin privilege to be sure no one can see them.

----------------------------------------------------------------------------
   
   Procedure p_sendmail (
       p_name    Varchar2,
       p_email   Varchar2,
       p_subject Varchar2,
       p_html    Clob,
       p_attachments Clob Default Null,
       p_cc Varchar2 Default Null,
       p_bcc Varchar2 Default Null,
       p_user_id Number Default Null,
       p_credit_checkyn Varchar2 Default 'Y'
   ) As
   
   /* I defined a static sender address which is my business gmail address. You may dynamically define it with a parameter. */
       l_request  Clob;
       l_response Clob;
       v_api_key Varchar2(100 Char);
       v_base_api_url Varchar2(100 Char);
       v_endpoint Varchar2(20 Char);
       v_credit_check Boolean := True;
       v_credit_need Number;
   Begin
       Select value
           Into v_base_api_url
         From parameters
       Where name = 'BASE_API_URL';
       Select value
           Into v_endpoint
         From parameters
       Where name = 'ENDPOINT_SENDMAIL';
       Select api_key
          Into v_api_key
         From api_key
        Where endpoint = 'ENDPOINT_SENDMAIL';
       
       /* l_request should be a json, so we build a json here, you can use apex_json or json_object both is working but json_object way faster than apex_json fyi. */ 
       /*APEX_JSON.initialize_clob_output;
       
       APEX_JSON.OPEN_OBJECT;
           APEX_JSON.WRITE('name', p_name);
           APEX_JSON.WRITE('email', p_email);
           APEX_JSON.WRITE('subject', p_subject);
           APEX_JSON.WRITE('html', p_html);
           APEX_JSON.WRITE('cc', p_cc);
           APEX_JSON.WRITE('bcc', p_bcc);
           APEX_JSON.WRITE('attachments',p_attachments);
       APEX_JSON.CLOSE_OBJECT;
       l_request := APEX_JSON.get_clob_output;
       APEX_JSON.free_output;*/
        
       l_request := JSON_OBJECT (
           'name'        VALUE p_name,
           'email'       VALUE p_email,
           'subject'     VALUE p_subject,
           'html'        VALUE p_html,
           'cc'          VALUE p_cc,
           'bcc'         VALUE p_bcc,
           'attachments' VALUE p_attachments
       );
       
        
   -- Set the request headers
       apex_web_service.g_request_headers(1).name  := 'Content-Type';
       apex_web_service.g_request_headers(1).value := 'application/json';
       apex_web_service.g_request_headers(2).name  := 'x-api-key';
       apex_web_service.g_request_headers(2).value  := v_api_key;

   -- Make the request
       l_response := apex_web_service.make_rest_request(p_url => v_base_api_url || v_endpoint
       , p_http_method => 'POST', p_body => l_request);
      /* you can change this procedure to a function to return response too. */
   Exception
       When Others Then
           pkg_error.p_logerror(p_module => 'pkg_util.p_sendMail', 
                        p_errorcode => sqlcode, 
                        p_errormessage =>  ' Error; ' || sqlerrm ||' BackTrace; ' || dbms_utility.format_error_backtrace,
                        p_createuser=> nvl( sys_context('APEX$SESSION', 'APP_USER'),user));
               
   End p_sendmail;
   
   ----------------------------------------------------------------------------

Procedures For page 900 (i named it pkg_reports)→

create or replace Package Body           pkg_reports As

    Function f_get_grid_data (
        p_format       Varchar2,
        p_static_id    Varchar2,
        p_app_id       Number,
        p_page_id      Number,
        p_component_id Number,
        p_file_name    Varchar2 Default 'file',
        p_page_size   Varchar2 Default Null,
        p_page_orientation Varchar2 Default Null
    ) Return apex_data_export.t_export As
        l_export       apex_data_export.t_export;
        l_region_id    number;
    BEGIN
        l_region_id := f_get_region_id (
            p_static_id  => p_static_id,
            p_app_id     => p_app_id,
            p_page_id    => p_page_id
        );
        l_export := apex_region.export_data (
             p_format       => 
                Case 
                    When p_format = 'PDF' Then
                        apex_data_export.c_format_pdf
                    When p_format = 'CSV' Then
                        apex_data_export.c_format_csv
                    When p_format = 'XLS' Then
                        apex_data_export.c_format_xlsx
                    When p_format = 'HTML' Then
                        apex_data_export.c_format_html
                End,
             p_page_size    => p_page_size,
             p_orientation  => p_page_orientation,
             p_page_id      => p_page_id,
             p_region_id    => l_region_id,
             p_component_id => p_component_id,
             p_file_name    => nvl(p_file_name,'file') );

        Return l_export;
    End f_get_grid_data;
    
    ----------------------------------------------------------------------------

    Procedure p_send_mail_grid_data (
        p_format       Varchar2,
        p_static_id    Varchar2,
        p_app_id       Number,
        p_page_id      Number,
        p_component_id Number,
        p_to           Varchar2,
        p_cc           Varchar2,
        p_bcc          Varchar2,
        p_subject       Varchar2,
        p_message       Varchar2,
        p_file_name    Varchar2 Default 'file',
        p_page_size   Varchar2 Default Null,
        p_page_orientation Varchar2 Default Null,
        p_user_id Number
    ) As
        l_export apex_data_export.t_export;
        v_attachment Clob;
        v_extention Varchar2(10 Char);
    Begin
        l_export := f_get_grid_data (
                p_format       => p_format,
                p_static_id    => p_static_id,
                p_app_id       => p_app_id,
                p_page_id      => p_page_id,
                p_component_id => p_component_id,
                p_file_name    => nvl(p_file_name,'file'),
                p_page_size    => p_page_size,
                p_page_orientation => p_page_orientation
            );
        --l_export.content_blob , l_export.file_name,l_export.mime_type
        Case 
            When p_format = 'PDF' Then
                v_extention:= '.pdf';
            When p_format = 'CSV' Then
                v_extention:= '.csv';
            When p_format = 'XLS' Then
                v_extention:= '.xlsx';
            When p_format = 'HTML' Then
                v_extention:= '.html';
        End Case;

        /* you can use json_object here
        APEX_JSON.initialize_clob_output;
        
        APEX_JSON.OPEN_OBJECT;
            APEX_JSON.OPEN_ARRAY('attachments');
                APEX_JSON.OPEN_OBJECT;
                    APEX_JSON.WRITE('filename', l_export.file_name||v_extention);
                    APEX_JSON.WRITE('content', pkg_util.encode_base64(l_export.content_blob));
                    APEX_JSON.WRITE('encoding', 'base64');
                APEX_JSON.CLOSE_OBJECT;
            APEX_JSON.CLOSE_ARRAY;
        APEX_JSON.CLOSE_OBJECT;
        
        v_attachment := APEX_JSON.get_clob_output;
        APEX_JSON.free_output;
        
        pkg_util.p_sendmail (
            p_name    => v('APP_USER'),
            p_email   => p_to,
            p_subject => p_subject,
            p_html    => p_message,
            p_attachments => v_attachment,
            p_cc  => p_cc,
            p_bcc => p_bcc,
            p_user_id => p_user_id
        );
        
    End p_send_mail_grid_data;
        
    ----------------------------------------------------------------------------
        
    Function f_get_region_id (
        p_static_id         Varchar2,
        p_app_id            Number,
        p_page_id           Number
    ) Return Number As
        v_region_id Number;
    Begin
       Select region_id
         Into v_region_id
         From apex_application_page_regions
        Where application_id = p_app_id
          And page_id = p_page_id
          And static_id = p_static_id;
        Return v_region_id;
    End f_get_region_id;
    
    ----------------------------------------------------------------------------
    
End pkg_reports;

APEX SIDE

1 - General Javascript code & Application Process to adjust IG & IR

In javascript code, you will see an Application Process that exist as an ajax callback to get proper link to open modal pages. Here is the code for it →

Declare
    v_link Varchar2(4000 char);
Begin
    v_link := apex_page.get_url(
                  p_application => :APP_ID, 
                  p_page => apex_application.g_x01, 
                  p_items  => apex_application.g_x02,
                  p_values => apex_application.g_x03 ,
                  p_clear_cache => apex_application.g_x01);
                  
    apex_json.open_object();
        apex_json.write('link',v_link);
    apex_json.close_object();
End;

You can create a static workspace file. Here is the code →

function opt( options,static_id,region_type,available_formats, subscribeyn ) {
    var page_id = $v("pFlowStepId");

    var $ = apex.jQuery,
        toolbarData = $.apex.interactiveGrid.copyDefaultToolbar(),               // Make a copy of the default toolbar
        reportsGroupControls = toolbarData.toolbarFind( "actions1" ).controls;    // Locate the reports group

    // Add new buttons after the default report controls. Note the toolbar is action driven, so
    // we just need to define the correct action name with the button.
    reportsGroupControls.push({
        type: "BUTTON",
        action: "download_ig",
        icon:"fa fa-download",
        iconOnly: true
    });

    // Assign new toolbar data back to toolbarData configuration property
    options.toolbarData = toolbarData;
    options.initActions = function( actions ) {
        actions.add( {
            name: "download_ig",
            label: "Download",
            action: function( event, focusElement ) {
                $('[data-action="download_ig"]').attr("disabled",true)
                apex.server.process( "AP_GET_URL", {
                    x01: 900,
                    x02:'P900_STATIC_ID,P900_REGION_TYPE,P900_PAGE_ID,P900_AVAILABLE_FORMATS,P900_SUBSCRIBE_YN',
                    x03: static_id+','+region_type+','+page_id+','+available_formats+','+subscribeyn
                }, {
                    success: function( data )  {
                        apex.navigation.redirect ( data.link );
                        $('[data-action="download_ig"]').attr("disabled",false)
                    },
                    error: function( jqXHR, textStatus, errorThrown ) {
                        // handle error
                    }
                } );
            }
        } );
    };
    // Return the options
    return options;
}

As you can see i reserved page 900 to be used.

So how we use this option in our ig's →

function( options ) {

    /* Here is the parameter explanations.

     option, regionstaticid, type of region must be IG for this use case, allowed file types, subscription allowed Yes or No 

     */
    return opt(options,options.regionStaticId,'IG','PDF:XLS:CSV:HTML','Y')
}

How we use in IR's

The button is looks like this for ig after doing options implementation →

You don't need to create new button for IG's but you need to create one for IR's since they don't have javascript initialization attribute. I show both above.

Finally Page 900 →

I think you can directly install this by changing "p_default_workspace_id" ,"p_default_application_id", “p_default_owner”

f101_page_900.sql

One more extra but please read description →

I'm using a subscription system just same as Oracle's default. To do that i created a table and store user's preferences in it. So when you install the page probably you'll need to delete the form region if you don't want to use it. But i'll put my table here so you can use/edit it if you like →

  CREATE TABLE "WKSP_SALE"."SUBSCRIBED_REPORTS" 
   (	"ID" NUMBER DEFAULT ON NULL wksp_sale.subscribed_reports_id.nextval NOT NULL ENABLE, 
    "CREATE_USER" VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP", 
    "UPDATE_USER" VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP", 
    "CREATE_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE DEFAULT ON NULL current_timestamp NOT NULL ENABLE, 
    "UPDATE_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
    "CREATE_TIMEZONE" VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP" DEFAULT ON NULL sessiontimezone NOT NULL ENABLE, 
    "UPDATE_TIMEZONE" VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP", 
    "PROVISIONER_ID" NUMBER, 
    "USERS_ID" NUMBER, 
    "APPLICATION_ID" NUMBER, 
    "PAGE_ID" NUMBER, 
    "FILE_FORMAT" VARCHAR2(10 CHAR) COLLATE "USING_NLS_COMP", 
    "FILE_NAME" VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP", 
    "COMPONENT_ID" VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP", 
    "STATIC_ID" VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP", 
    "REGION_TYPE" VARCHAR2(10 CHAR) COLLATE "USING_NLS_COMP", 
    "START_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
    "END_DATE" TIMESTAMP (6) WITH LOCAL TIME ZONE, 
    "PAGE_ORIENTATION" VARCHAR2(50 CHAR) COLLATE "USING_NLS_COMP", 
    "PAGE_SIZE" VARCHAR2(50 CHAR) COLLATE "USING_NLS_COMP", 
    "MAIL_TO" VARCHAR2(4000 CHAR) COLLATE "USING_NLS_COMP", 
    "MAIL_CC" VARCHAR2(4000 CHAR) COLLATE "USING_NLS_COMP", 
    "MAIL_BCC" VARCHAR2(4000 CHAR) COLLATE "USING_NLS_COMP", 
    "SUBJECT" VARCHAR2(4000 CHAR) COLLATE "USING_NLS_COMP", 
    "MESSAGE" VARCHAR2(4000 CHAR) COLLATE "USING_NLS_COMP", 
    "FREQUENCY_NAME" VARCHAR2(100 CHAR) COLLATE "USING_NLS_COMP", 
    "FREQUENCY_NUMBER" NUMBER, 
     CONSTRAINT "SUBSCRIBED_REPORTS_FILE_FORMAT_NN" CHECK (file_format is not null) ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_FILE_NAME_NN" CHECK (file_name is not null) ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_COMPONENT_ID_NN" CHECK (component_id is not null) ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_STATIC_ID_NN" CHECK (static_id is not null) ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_PAGE_ID_NN" CHECK (page_id is not null) ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_APPLICATION_ID_NN" CHECK (application_id is not null) ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_REGION_TYPE_NN" CHECK (region_Type is not null) ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_PK" PRIMARY KEY ("ID")
  USING INDEX PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA"  ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_FK1" FOREIGN KEY ("PROVISIONER_ID")
      REFERENCES "WKSP_SALE"."PROVISIONER" ("ID") ON DELETE CASCADE ENABLE, 
     CONSTRAINT "SUBSCRIBED_REPORTS_FK2" FOREIGN KEY ("USERS_ID")
      REFERENCES "WKSP_SALE"."USERS" ("ID") ON DELETE CASCADE ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 10 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" ;

  CREATE INDEX "WKSP_SALE"."SUBSCRIBED_REPORTS_USERS_ID" ON "WKSP_SALE"."SUBSCRIBED_REPORTS" ("USERS_ID") 
  PCTFREE 10 INITRANS 20 MAXTRANS 255 COMPUTE STATISTICS 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DATA" ;

  CREATE OR REPLACE EDITIONABLE TRIGGER "WKSP_SALE"."SUBSCRIBED_REPORTS_BIU" Before
    Insert or Update On wksp_sale.subscribed_reports
    For Each Row
Begin
    If inserting Then
        :new.create_user := nvl(sys_context('APEX$SESSION', 'APP_USER'), user);
    End If;

    If updating Then
        :new.update_date     := current_timestamp;
        :new.update_user     := nvl(sys_context('APEX$SESSION', 'APP_USER'), user);
        :new.update_timezone := sessiontimezone;
    End If;

End;
/
ALTER TRIGGER "WKSP_SALE"."SUBSCRIBED_REPORTS_BIU" ENABLE;

Result:

Now i can send any html with apex, plsql easily with just one request.

I can add that download button for any interactive grids by using javascript initialization with just one line without doing extra implementation for each grid.

Also now i'm able to get any interactive grid or report data in any format by using a plsql procedure. (You may see it in page 900 i share above.)

Comments

InoL Apr 29 2024

How doc gen treats nulls values?

You have to be more specific. Are your referring to the pre-built Document Generator function?

If so:

When my query return null in some columns

What is the JSON that is generated?

You should always include the element in the JSON, with an empty value. Don't leave it out.

Francois Robert-Oracle Jan 8 2025 — edited on Jan 8 2025

Since September 2024, when a tag has no corresponding value in the JSON data or the value is null, the tag is replaced by an empty string.

See the September Release Notes

1 - 2

Post Details

Added on Sep 6 2023
10 comments
1,234 views