PL/SQL for Sending Email with Mandrill API :: Oracle UTL_SMTP with Authentication

CREATE OR REPLACE PR OCEDURE p_send_mail ( p_sender_name IN VARCHAR2, p_sender IN VARCHAR2, p_recipient IN VARCHAR2, p_subject IN VARCHAR2, –p_cc IN VARCHAR2, p_message IN VARCHAR2) IS conn UTL_SMTP.CONNECTION; crlf VARCHAR2(2) := CHR(13) || CHR(10); — Mandrill Login Details uname VARCHAR2(200) := ‘mandrillusername@yourdomain.com’; EmailServer VARCHAR2(60) := ‘smtp.mandrillapp.com’; pwd VARCHAR2(200) := ‘mandrillappkey’; p_port VARCHAR2(10) := ’25’; — if you have issue use port 587 pq_message VARCHAR2(5000); — Do Not Edit Below This Line — This PL/SQL procedure is created and tested by Haneef Puttur , for Mandrill BEGIN conn := utl_smtp.open_connection(EmailServer, p_port); utl_smtp.ehlo(conn, EmailServer); utl_smtp.command(conn, ‘AUTH LOGIN’); utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode( utl_raw.cast_to_raw(uname)))); utl_smtp.command(conn, utl_raw.cast_to_varchar2(utl_encode.base64_encode( utl_raw.cast_to_raw(pwd)))); utl_smtp.mail(conn, p_sender); utl_smtp.rcpt(conn, p_recipient); pq_message:= ‘Date: ‘|| TO_CHAR(SYSDATE, ‘DD MON RR HH24:MI:SS’ )|| crlf || ‘From: “‘ || p_sender_name || ‘” ‘ || p_sender || crlf || ‘Subject: ‘ || p_subject || crlf || — ‘Cc: ‘ || p_cc || crlf || — uncomment this line for sending cc ‘Content-Type: text/html;’ ||crlf || — Comment this line if you want only plain text email ‘To: ‘|| p_recipient || crlf|| p_message || crlf || crlf; utl_smtp.data(conn, pq_message); utl_smtp.quit(conn); EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20001,SQLERRM); END ; To Test This Procedure: EXEC .p_send_mail(‘Haneef Puttur’,’yourname@youremail.com’,’toaddress@test.com’,’Message Subject’,’Haneef Puttur, This is test messgae.<br> I am […]

» Read more

Create New Schema in Oracle 11G express

In order to create a new schema in oracle 11G express, we need to create a user account and assign all the privileges. Step 1 : Open SQL command Line Step 2 : Run Connect Step 3 : Enter Username : SYSTEM Step 4 : Enter Password for The SYSTEM account , which is specified while installing oracle Step 5 : Create a new user called Haneef_Puttur with Password : test1000 create user haneef_puttur identified by test1000; Step 6 : Assign permission to the newly created user. grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, – CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, – CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, – CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE – to haneef_puttur; .

» Read more

Installing Oracle OCI (oci8) library to Lubuntu 10.04.4 LTS – Using OCI instantclient 12.1

First Check Version of your Ubuntu Run Command :  lsb_release -a Distributor ID: Ubuntu Description: Ubuntu 10.04.4 LTS Release: 10.04 Codename: lucid Pre Requisites: sudo apt-get install build-essential sudo apt-get install php5-dev php-pear libaio1 Instant Client installation requires some environment variables to be able to see the library folder. Edit /etc/environment and add at the end: # Oracle Instant Client LD_LIBRARY_PATH=”/usr/local/lib/instantclient_12_1″ TNS_ADMIN=”/usr/local/lib/instantclient_12_1″ ORACLE_BASE=”/usr/local/lib/instantclient_12_1″ ORACLE_HOME=$ORACLE_BASE Now Download Right Version of Oracle Install Client 32 Bit : Basic Version SDK Version 64 Bit Basic Version SDK Version Now transfer the downloaded file from local pc to ubuntu server using WinSCP cd /usr/local/lib sudo unzip <location-of-instant-client-basic> sudo unzip <location-of-instant-client-sdk> cd instantclient_12_1 sudo ln -s libclntsh.so.12.1 libclntsh.so The symbolic link is needed during the compilation step later. sudo pecl install oci8 You will be prompted for the location of the library: instantclient,/usr/local/lib/instantclient_12_1 PHP5 Instructions You should now create a new oci8.ini file which will be included when loading php.ini (Editing the php.ini is discouraged): nano /etc/php5/conf.d/oci8.ini this will create a blank file; add to this file: extension=oci8.so In Ubuntu 13.10, you should create a new oci8.ini in: nano /etc/php5/apache2/conf.d/oci8.ini this will create a blank file. Then add to this file: extension=oci8.so Restart Apache sudo […]

» Read more

Create First Visual Studio 2013 , MVC – Oracle Application with ZERO Coding

Please refer my tutorials for Creating first oracle database using Developer tools without writing any codes. Just Download Oracle Devloper Tools and start Creating Database in minutes Read Entire tutorial with step by step instructions including video. Create Edit/ Delete / Update / View Controllers from Oracle Database without any codes just using entity frame work, Read Entire Tutorial including step by step instruction and Video    .

» Read more

First MVC ODP.net project using entity framework – Part 2/2

Task : Connect to oracle DB using ODP.net and generate forms using Entity Frame Work without any coding. Pre Requisites : Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio  http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html Microsoft Visual Studio 2013 Oracle Database Instance Credentials. Entity Framework 6 Tools for Visual Studio 2012 & 2013 (http://www.microsoft.com/en-us/download/details.aspx?id=40762) Oracle Database with Required Tables Employee and Department ( Refer Part 1 of This tutorial) Complete Video Tutorial        Right Click Models –> Add –> Select ADO.net entity data model Enter name like “EmployeeModel” and press OK Click New Connection Please note if you cant find Oracle database ODP.net managed Driver , then please install ODP.net driver from following link before you proceed further. http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html   Now before you proceed further please build solution to make sure all is WELL upto this stage. If you can see above image means then your configuration for ODP.net is fine . Now stop the instance using Shift+F5 and go ahead with adding Controllers Now Add the two controllers (Employee and Departments) using Entity frame work     Follow the above same procedure and create another model for department.   Now open Layout.cshtml from the view/shared folder and add […]

» Read more

Create and Manage Oracle Database Using Oracle SQL Developer GUI

Pre Requisites: Oracle SQL Developer Oracle Database Instance Login Details Complete Video Tutorial Step 1: Install Oracle SQL Developer http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html Please note that in order to install Developer Tool you must have JDK 7 or above in your PC. If you do not have please download JDK from following link prior to downloading to SQL Developer tool. http://www.oracle.com/technetwork/java/javase/downloads/jdk7-downloads-1880260.html   Step 2: Please make sure you have following details of Oracle DB is handy with you. Hostname : Username : Password : Service Name : Port No : Step 3 : Extract the Downloaded SQL Developer tools to a folder and open the file SQL Developer       This will Open Developer Now Import Data Dictionary as shown Enter the Oracle DB Details Select Schema Dont select any DB Tables as we are going to create new Tables     Now click Insert Table. Drag workspace to new table , enter table Name it as Employee and Click OK Now Create Columns as Shown Similarly create second table : department   Now Create Foreign Key as shown to link Department Id from Employee table to Department Table Please note that Developer tool will not create the table automatically. We need […]

» Read more
1 2