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.
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');
const app = express();
app.use(cors({ origin: "*" }));
app.use(bodyParser.urlencoded({ extended: false }))
app.use(bodyParser.json())
const transporter = nodemailer.createTransport({
host: 'smtp.gmail.com',
port: 587,
auth: {
user: process.env.EMAIL,
pass: process.env.PASS,
},
});
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,
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')
}
});
});
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
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 := 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
);
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;
l_response := apex_web_service.make_rest_request(p_url => v_base_api_url || v_endpoint
, p_http_method => 'POST', p_body => l_request);
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
);
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(),
reportsGroupControls = toolbarData.toolbarFind( "actions1" ).controls;
reportsGroupControls.push({
type: "BUTTON",
action: "download_ig",
icon:"fa fa-download",
iconOnly: true
});
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 ) {
}
} );
}
} );
};
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 ) {
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.)
