Sending an Email from an Oracle Apex Application

jagdev's blog on database technology

Done it ..

1. Here is the customer feedback form – customer enters feedback text and presses submit feedback button

feedbackform

2. In the Action When Button Clicked section, set Action value equal to Defined by Dynamic Action

3. Create a Dynamic Action. In the When section set the following values:

Event: Click

Selection Type: Button

Button: SUBMIT (Submit Feedback)

4. Add a TRUE Action

5. Set Action=Execute PL/SQL code

6. Under Settings, add the following PL/SQL code (Note p-body has been assigned the feedback bind variable :P102_FEEDBACK so that whatever text is entered by the customer will be sent in the email

BEGIN
apex_mail.send(  p_to       => ‘receiver.co.uk’,   — change to your email address
p_from     => ‘sender.co.uk’, — change to a real senders email address
p_body     => :P102_FEEDBACK,         p_subj     => ‘My Plain text message’);
END;

7. Run the page. Add some feedback and press the submit feedback button.

8. Voila! Within…

View original post 22 more words

Advertisements

Send query result from Apex 5.0 application as HTML Email

Sreejith 's Blog

Recently I had a requirement to send a HTML based email from my apex application. The HTML content was actually the result of an sql query I run on my application database.

I used UTL_SMTP with database stored procedure to send mails in a plain text format already in my application.New requirement to send HTML based emails from UTL_SMTP looked bit complex and more coding effort. This can be easily achievable using APEX_MAIL.

To use APEX_MAIL package we have to configure Apex environment and database

http://docs.oracle.com/cd/E59726_01/doc.50/e39151/adm_wrkspc002.htm#AEADM29176

1. Enabling Network Services

If you are running Oracle Application Express with Oracle Database 11g or later, you must enable outbound mail. Starting with Oracle Database 11g Release 1 (11.1), the ability to interact with network services is disabled by default.

By default, the ability to interact with network services is disabled in Oracle Database 11g or later. Therefore, if you are running Oracle…

View original post 192 more words

ORA-29548 release of java system classes in the database (12.2.0.2.160419.1.6 ) does not match that of oracle executable (12.1.0.2.170418 1.6)

This error is mostly due to a mismatch in the Java system classes. It happened to us when we cloned a 12c Database which was on a lower home version to a 12c home on a higher version. It requires data patch to be run and rollback the old patch and apply the new JVM patch.

 

If it is a RAC database, put it in single instance mode by disabling cluster_database parameter and mount the database in upgrade exclusive mode.

 

SQL> show parameter cluster

 

NAME     TYPE VALUE
———————————— ———– ——————————
cluster_database     boolean TRUE
cluster_database_instances     integer 2
cluster_interconnects     string
SQL>

SQL>

SQL> alter system set cluster_database=false scope=spfile sid=’*’;

 

 

System altered.

 

SQL>

SQL>

SQL> shutdown immediate;

 

Database closed.

 

Database dismounted.

 

ORACLE instance shut down.

 

SQL>

SQL>

SQL> startup upgrade exclusive;

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

ORACLE instance started.

 

Total System Global Area 3.4360E+10 bytes
Fixed Size    5304248 bytes
Variable Size 1.8119E+10 bytes
Database Buffers 1.6173E+10 bytes
Redo Buffers   61804544 bytes

 

Database mounted.

 

Database opened.
SQL>

SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
nessy1::12c–> cd /u03/app/oracle/product/12.1.0.2/dbhome_1/OPatch

nessy1::12c–> ./datapatch -verbose

SQL Patching tool version 12.1.0.2.0 Production on Sat Aug 12 14:47:54 2017Copyright (c) 2012, 2017, Oracle.  All rights reserved.
Log file for this invocation: /u03/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_125122_2017_08_12_14_47_54/sqlpatch_invocation.log
Connecting to database…OKBootstrapping registry and package to current versions…doneDetermining current state…done
Current state of SQL patches:Patch 21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY):  Installed in the SQL registry onlyPatch 22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016)):  Installed in the SQL registry onlyPatch 25437695 (Database PSU 12.1.0.2.170418, Oracle JavaVM Component (APR2017)):  Installed in the binary registry onlyBundle series DBBP:  ID 170418 in the binary registry and ID 160419 in the SQL registry
Adding patches to installation queue and performing prereq checks…Installation queue:  The following patches will be rolled back:    22674709 (Database PSU 12.1.0.2.160419, Oracle JavaVM Component (Apr2016))    21171382 (AUTO DOP COMPUTES A HIGH DOP UNNECESSARILY)  The following patches will be applied:    25437695 (Database PSU 12.1.0.2.170418, Oracle JavaVM Component (APR2017))    25397136 (DATABASE BUNDLE PATCH 12.1.0.2.170418)
Installing patches…Patch installation complete.  Total patches installed: 4
Validating logfiles…Patch 22674709 rollback: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/22674709/20057886/22674709_rollback_NESSY_2017Aug12_14_48_20.log (no errors)Patch 21171382 rollback: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/21171382/19741104/21171382_rollback_NESSY_2017Aug12_14_49_09.log (no errors)Patch 25437695 apply: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/25437695/21057393/25437695_apply_NESSY_2017Aug12_14_49_17.log (no errors)Patch 25397136 apply: SUCCESS  logfile: /u03/app/oracle/cfgtoollogs/sqlpatch/25397136/21145057/25397136_apply_NESSY_2017Aug12_14_49_17.log (no errors)SQL Patching tool complete on Sat Aug 12 14:49:54 2017nessy1::12c–>nessy1::12c–>nessy1::12c–>sqld

SQL*Plus: Release 12.1.0.2.0 Production on Sat Aug 12 14:50:05 2017

 

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

 

 

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

 

SQL>

SQL>

SQL> shutdown immediate;

 

Database closed.
Database dismounted.

 

ORACLE instance shut down.

 

SQL>

SQL>

SQL> startup;

 

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

 

ORACLE instance started.

 

Total System Global Area 3.4360E+10 bytes
Fixed Size    5304248 bytes
Variable Size 1.8119E+10 bytes
Database Buffers 1.6173E+10 bytes
Redo Buffers   61804544 bytes

 

Database mounted.

 

Database opened.
SQL>

SQL>

SQL> set lines 9990
SQL>

SQL>

 

SQLselect * from dba_registry;

 

 

COMP_ID       COMP_NAME       VERSION      STATUS  MODIFIED NAMESPACE       CONTROL SCHEMA PROCEDURE       STARTUP PARENT_ID       OTHER_SCHEMAS
—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– —————————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
APEX       Oracle Application Express       4.2.5.00.08      VALID  02-JUL-2016 16:08:09 SERVER       SYS APEX_040200 VALIDATE_APEX       FLOWS_FILES
OLS       Oracle Label Security       12.1.0.2.0      VALID  02-JUL-2016 16:08:08 SERVER       SYS LBACSYS VALIDATE_OLS
DV       Oracle Database Vault       12.1.0.2.0      VALID  02-JUL-2016 16:08:09 SERVER       SYS DVSYS VALIDATE_DV       DVF
ORDIM       Oracle Multimedia       12.1.0.2.0      VALID  02-JUL-2016 16:08:05 SERVER       SYS ORDSYS VALIDATE_ORDIM       MDSYS,ORDDATA,ORDPLUGINS,SI_INFORMTN_SCHEMA
CONTEXT       Oracle Text       12.1.0.2.0      VALID  02-JUL-2016 16:08:05 SERVER       SYS CTXSYS VALIDATE_CONTEXT
OWM       Oracle Workspace Manager       12.1.0.2.0      VALID  02-JUL-2016 16:08:01 SERVER       SYS WMSYS VALIDATE_OWM
SDO       Spatial       12.1.0.2.0      VALID  02-JUL-2016 16:08:07 SERVER       SYS MDSYS VALIDATE_SDO       MDDATA,MDSYS,SPATIAL_CSW_ADMIN_USR,SPATIAL_WFS_ADMIN_USR
XDB       Oracle XML Database       12.1.0.2.0      VALID  02-JUL-2016 16:07:57 SERVER       SYS XDB DBMS_REGXDB.VALIDATEXDB       ANONYMOUS,XS$NULL
CATALOG       Oracle Database Catalog Views       12.1.0.2.0      VALID  02-JUL-2016 16:07:55 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATALOG
CATPROC       Oracle Database Packages and Types       12.1.0.2.0      VALID  02-JUL-2016 16:07:55 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATPROC       APPQOSSYS,DBSNMP,DIP,GSMADMIN_INTERNAL,ORACLE_OCM,OUTLN,SYSTEM
JAVAVM       JServer JAVA Virtual Machine       12.1.0.2.0      VALID  12-AUG-2017 14:49:17 SERVER       SYS SYS INITJVMAUX.VALIDATE_JAVAVM

 

COMP_ID       COMP_NAME       VERSION      STATUS  MODIFIED NAMESPACE       CONTROL SCHEMA PROCEDURE       STARTUP PARENT_ID       OTHER_SCHEMAS
—————————— ————————————————————————————————————————————————————————————————————————————————————— —————————— ———– —————————– —————————— ——————————————————————————————————————————– ——————————————————————————————————————————– ————————————————————- ——– —————————— —————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————-
XML       Oracle XDK       12.1.0.2.0      VALID  02-JUL-2016 16:08:04 SERVER       SYS SYS XMLVALIDATE
CATJAVA       Oracle Database Java Packages       12.1.0.2.0      VALID  02-JUL-2016 16:08:04 SERVER       SYS SYS DBMS_REGISTRY_SYS.VALIDATE_CATJAVA
APS       OLAP Analytic Workspace       12.1.0.2.0      VALID  02-JUL-2016 16:08:06 SERVER       SYS SYS APS_VALIDATE
XOQ       Oracle OLAP API       12.1.0.2.0      VALID  02-JUL-2016 16:08:07 SERVER       SYS SYS XOQ_VALIDATE
RAC       Oracle Real Application Clusters       12.1.0.2.0      VALID  02-JUL-2016 16:08:09 SERVER       SYS SYS DBMS_CLUSTDB.VALIDATE

 

16 rows selected.

 

SQL>

SQL>

SQL>
SQL>

SQL>

SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options

ORA-39071: Value for EXCLUDE is badly formed.

Oracle Database 101

Quick post more for myself as I seem to continually forget how to construct a proper EXCLUDE on the command line (no parameter file) on Linux with DataPump so here it is .

"exclude=SCHEMA:"IN('SCHEMA1','SCHEMA2')""

And yes the quotes before exclude and at the very end are required. This of course isn’t the only way but hopefully one I can remember.

View original post

Script to SET NEWNAME for DATAFILES in RMAN for Cloning

— If doing on Source Server —

::12c–>sqlplus “/as sysdba”

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select ‘set newname for datafile ‘ || file_id || ‘ to ”/u01/oradata/’ ||
substr(file_name,instr(file_name,’/’,-1)+1) || ”’;’
from dba_data_files ;
spool off
exit;

— If doing on Target Server —

[oracle@testorcl ~]$ sqlplus “/as sysdba”

set head off pages 0 feed off echo off verify off
set lines 200
spool rename_datafiles.lst
select ‘set newname for datafile ‘ || file# || ‘ to ”/u01/oradata/’ ||
substr(name,instr(name,’/’,-1)+1) || ”’;’
from v$datafile ;
spool off
exit;

Rman backup from production and restore in test machine

ORACLE RAC DBA

We want to make a test database from production database. And our production database is on ASM and our test machine was linux file sysetm.
see the steps how we restored it.

1) Start the test database in nomount mode

sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 – Production on Tue Oct 2 08:30:29 2012

Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1610612736 bytes
Fixed Size 2096632 bytes
Variable Size 385876488 bytes
Database Buffers 1207959552 bytes
Redo Buffers 14680064 bytes
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

2) Restore controlfile from backup

[oracle@test rman]$ rmant target / Recovery Manager: Release 10.2.0.5.0 - Production on Tue Oct 2 08:31:07 2012 Copyright (c) 1982, 2007, Oracle. All rights reserved. connected to target database: prod (not…

View original post 1,039 more words

Shell Script to Ping Hostnames and Send SMS if Host is not Available

We use the below procedure to ping hosts and based on availability send SMS to our team. The SMS is send by calling a web service from the BASH Shell script using curl 

The hosts are defined in a file called hosts.txt

— Shell Script —

#!/bin/bash
current_time=$(date “+%Y%m%d%H%M%S%3N”)
#### Above Date Format is in MilliSeconds Accuracy ###
cd /home/online
rm -rf /home/online/SMSHOST.TXT
date
#### Below Code Will Loop the Hosts.TXT file for the Ping Probes ####
cat /home/online/hosts.txt | while read output
do
ping -w 30 -c 1 $output > /dev/null
if [ $? -ne 0 ]
then
SMSTEXT=’The+Host+’$output’+is+not+reachable.+Please+Contact+the+Database+Team.’
echo “Host $output is Down”
curl “http://192.168.10.100:8080/SMSConnect/SendServlet?application=http_gw2&password=http_gw2&content=$SMSTEXT&destination=9998887190&source=97249&mask=COMPANY”
else
echo “Host $output is Up”
fi
done

 

— hosts.txt file containing list of hostnames —

proddb01

proddb02

proddb03

drdb01

drdb02

drdb03