Category Archives: Oracle

Oracle PL/SQL to check whether variable is text or number

Issue : sometime its very important to check a variable is number or text in order to avoid any exception. Assume your variable is xyz SELECT DECODE(TRIM(TRANSLATE(xyz,’0123456789′,’  ‘)), NULL, ‘number’,’characters’) FROM DUAL; SELECT DECODE(TRIM(TRANSLATE(’12s3456′,’0123456789′,’ ‘)), NULL, ‘number’,’characters’) FROM DUAL; Returns : characters SELECT DECODE(TRIM(TRANSLATE(‘1233456′,’0123456789′,’ ‘)), NULL, ‘number’,’characters’) FROM DUAL; Returns : number What is Translate Function: For… Read More »

Oracle pl/sql dynamic query with IN paramters

Issue: Sending dynamic paramter to oracle pl/sql. Example : you will pass a paramter of employee names to the procedure and you want to dynamically construct the SQL query. Parameter Passed : v_emp   Value Passed : v_emp = ‘haneef,ganesh,rajesh,minaal’   This query will not work:   SELECT emp_id, emp_name from, FROM EMP_TABLE WHERE emp_name… Read More »

Installing 64 Bit Oracle Client : Windows 10 – 64 Bit

Scenario : You want to install Oracle Client and ODBC driver in Windows 10 – 64 Bit Files Required : Oracle Instant Client Basic : http://download.oracle.com/otn/nt/instantclient/122010/instantclient-basic-windows.x64-12.2.0.1.0.zip Instant Client Package – ODBC : http://download.oracle.com/otn/nt/instantclient/122010/instantclient-odbc-windows.x64-12.2.0.1.0.zip Step 1 : Download Above 2 Packages. Step 2: Copy all the contents to a single folder and name the folder as “oracle”… Read More »

Oracle PL/SQL to Send ARABIC Emails with SMTP AUTHENTICATED Server UTL_SMTP

declare TO_EMAIL VARCHAR2(255) := ‘toemailaddress’; FROM_EMAIL VARCHAR2(255) := ‘fromemailaddress’; MAIL_SUBJECT VARCHAR2(255) := ‘Test Subject’; FROM_NAME VARCHAR2(255) := ‘FROM NAME’; EMAIL_MESSAGE VARCHAR2(255) := ‘سؤال اليوم : ما هو الحل ‘; smtp_conn UTL_SMTP.CONNECTION; smtp_username VARCHAR2 (200) := ‘username’; smtp_server VARCHAR2 (60) := ‘smtpserver address’; smtp_password VARCHAR2 (200) := ‘smtp password’; begin smtp_conn := UTL_SMTP.Open_Connection(smtp_server); UTL_SMTP.Helo(smtp_conn, smtp_server); UTL_SMTP.command… Read More »

Install Oracle DB Express and Connect to ASP.Net MVC C# Using Dapper and ODP.net

Tools Used : Oracle DB Express Toad for Oracle ( To explore database– You can also user Oracle Developer)  Visual Studio .net 2013 This tutorial covers following points. Install Oracle DB Express.  ==< Link http://www.oracle.com/technetwork/database/database-technologies/express-edition/downloads/index.html Create a user  — Refer my post => Create user and Schema Create a Schema — Refer my post => Create user… Read More »

How to Check / Kill Oracle Live Sessions

Step 1 : Run following script to display active sessions; SELECT O.OBJECT_NAME, S.SID, S.SERIAL#, P.SPID, S.PROGRAM, SQ.SQL_FULLTEXT, S.LOGON_TIME FROM V$LOCKED_OBJECT L, DBA_OBJECTS O, V$SESSION S, V$PROCESS P, V$SQL SQ WHERE L.OBJECT_ID = O.OBJECT_ID AND L.SESSION_ID = S.SID AND S.PADDR = P.ADDR AND S.SQL_ADDRESS = SQ.ADDRESS; To Kill Specific Session Run following command alter system kill… Read More »

AJAX Query to fill Select List MVC , C# asp.net

This is Proof of Concept for Querying data using AJAX and filling the drop down list from a oracle database using DB procedures. Model : public class listmodel     {         public string txtdesc{ get; set; }         public string txtval { get; set; } } Controller public JsonResult get_crn_term(string term)         {             var crnlist… Read More »

Installing Oracle ODBC Driver to Windows 2012 Server R2

Step1:   Download 32-bit Oracle Data Access Components (ODAC) with Oracle Developer Tools for Visual Studio http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html   After installing the above software create a envirnoment variable called TNS_ADMIN Variable Name :TNS_ADMIN Variable Value : C:\oracle\product\10.2.0\client_2\network\admin  (Make sure path is correct. If your machine is connected to Domain path may differ) Step3: Open C:\Windows\SysWOW64\odbcad32.exe USER DSN –>… Read More »

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) := ‘[email protected]’; EmailServer VARCHAR2(60) := ‘smtp.mandrillapp.com’; pwd VARCHAR2(200) := ‘mandrillappkey’; p_port VARCHAR2(10) := ’25’; –… Read More »