SignalR & Oracle DB Change Notification [Part 2 of 2]

What is Oracle change Notification ? The Oracle Data Provider for .NET provides a notification framework that supports Database Change Notification in the database server. Database Change Notification enables applications to receive notifications from a registered callback routine when there is a change in a query result set, schema objects, or the state of the database that could affect the results of a cached client result set. Using this feature in ODP.NET, an application can maintain the validity of the client-side cache (for example, the ADO.NET DataSet) easily. The client does not need to maintain a connection to the database. It will receive notifications even when no longer connected to the database. This feature is intended for queries whose results change infrequently over time and for those who cannot maintain a large number of active database connections. Features you get after you complete this tutorial # Send real time message to any user for change in DB # Alert about the operation like update / insert / delete # Alert about any changes based on the SELECT statement defined. # Functionality can be extended to any ticket selling systems, room booking systems where you wish to block the booking based […]

» Read more

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 example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on. TRANSLATE(‘1tech23’, ‘123’, ‘456’) Result: ‘4tech56’ TRANSLATE(‘222tech’, ‘2ec’, ‘3it’) Result: ‘333tith’

» 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 IN v_emp;   Working Query  SELECT emp_id, emp_name from, FROM EMP_TABLE WHERE emp_name IN (SELECT EXTRACTVALUE (xt.COLUMN_VALUE, ‘e’) FROM TABLE ( XMLSEQUENCE ( EXTRACT ( xmltype ( ‘<coll><e>’ || REPLACE (v_emp, ‘,’, ‘</e><e>’) || ‘</e></coll>’), ‘/coll/*’))) xt) ORDER BY emp_id ASC;        

» 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” Step 3: Now copy the folder to c:\ So now the content of c drive should like below Step 4: Now create a folder called tns inside c:\oracle => this folder will be used to save tnsnames.ora file Step 5 : Add TNS_ADMIN variable in Environment Variables TNS_ADMIN  => c:\oracle\tns\ Step 6: Add c:\oracle to the path variable Step 7 : Right click the file c:\oracle\ Step 8 : Now create a sample tns file and save in c:\oracle\tns\ PROD= (DESCRIPTION= (ADDRESS= (PROTOCOL=TCP) (HOST=xxx.oracle.haneefputtur.com (PORT=1521) ) (CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=xxx.haneefputtur.com ) ) …. Now your oracle client and odbc driver is ready to use. Step 9 : Create ODBC DSN as follows   Note For 32 Bit Clients Link  for 32 bit Instant Client : http://download.oracle.com/otn/nt/instantclient/122010/instantclient-basic-nt-12.2.0.1.0.zip Link for 32 Bit ODBC Driver : http://download.oracle.com/otn/nt/instantclient/122010/instantclient-odbc-nt-12.2.0.1.0.zip  

» 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 (smtp_conn, ‘AUTH LOGIN’); UTL_SMTP.command (smtp_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (smtp_username)))); UTL_SMTP.command (smtp_conn, UTL_RAW.cast_to_varchar2 (UTL_ENCODE.base64_encode (UTL_RAW.cast_to_raw (smtp_password)))); UTL_SMTP.Mail(smtp_conn, FROM_EMAIL); UTL_SMTP.Rcpt(smtp_conn, TO_EMAIL); UTL_SMTP.OPEN_DATA(smtp_conn); UTL_SMTP.WRITE_DATA(smtp_conn, ‘Subject: =?UTF-8?Q?’ || UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(EMAIL_SUBJECT))) ||’?=’ || UTL_TCP.CRLF); UTL_SMTP.WRITE_DATA(smtp_conn, ‘MIME-version: 1.0’ || UTL_TCP.CRLF); UTL_SMTP.WRITE_DATA(smtp_conn, ‘Content-Type: text/html;charset=utf-8’ || UTL_TCP.CRLF); UTL_SMTP.WRITE_DATA(smtp_conn, ‘Content-Transfer-Encoding: quoted-printable ‘|| UTL_TCP.CRLF); UTL_SMTP.WRITE_DATA(smtp_conn, ‘Date: ‘ || TO_CHAR(SYSDATE, ‘dd Mon yy hh24:mi:ss’ ) ||’ -0800 (GMT)’ || UTL_TCP.CRLF); UTL_SMTP.WRITE_DATA(smtp_conn, ‘From: “‘|| FROM_NAME|| ‘”<‘|| FROM_EMAIL|| ‘>’ || UTL_TCP.CRLF); UTL_SMTP.WRITE_DATA(smtp_conn, ‘To: ‘ || TO_EMAIL || UTL_TCP.CRLF); UTL_SMTP.WRITE_DATA(smtp_conn, UTL_TCP.CRLF); UTL_SMTP.WRITE_RAW_DATA(smtp_conn, UTL_ENCODE.QUOTED_PRINTABLE_ENCODE(UTL_RAW.CAST_TO_RAW(EMAIL_MESSAGE))); UTL_SMTP.WRITE_DATA(smtp_conn, UTL_TCP.CRLF); UTL_SMTP.CLOSE_DATA(smtp_conn); UTL_SMTP.QUIT(smtp_conn); END;

» 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 and Schema Create a Table ALTER TABLE HPUTTUR.EMPLOYEE DROP PRIMARY KEY CASCADE; DROP TABLE HPUTTUR.EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE HPUTTUR.EMPLOYEE ( EMPLOYEE_ID NUMBER NOT NULL, EMPLOYEE_NAME VARCHAR2(200 CHAR) NOT NULL, EMPLOYEE_MOBILE VARCHAR2(50 BYTE) NOT NULL, EMPLOYEE_DEPARTMENT VARCHAR2(50 BYTE) NOT NULL ) TABLESPACE SYSTEM RESULT_CACHE (MODE DEFAULT) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; CREATE UNIQUE INDEX HPUTTUR.EMPLOYEE_PK ON HPUTTUR.EMPLOYEE (EMPLOYEE_ID) LOGGING TABLESPACE SYSTEM NOPARALLEL;  CREATE OR REPLACE TRIGGER HPUTTUR.EMPLOYEE_TRG BEFORE INSERT ON HPUTTUR.EMPLOYEE REFERENCING NEW AS New OLD AS Old FOR EACH ROW BEGIN — For Toad: Highlight column EMPLOYEE_ID :new.EMPLOYEE_ID := EMPLOYEE_SEQ.NEXTVAL; END EMPLOYEE_TRG; / ALTER TABLE HPUTTUR.EMPLOYEE ADD ( CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMPLOYEE_ID) USING INDEX HPUTTUR.EMPLOYEE_PK ENABLE VALIDATE); Insert into HPUTTUR.EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_MOBILE, EMPLOYEE_DEPARTMENT) Values (1, ‘Haneef Puttur’, ‘354354354345’, ‘Computer’); Insert into HPUTTUR.EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_MOBILE, EMPLOYEE_DEPARTMENT) Values (2, ‘Minnu’, ‘y676756565’, ‘Computer’); Insert into HPUTTUR.EMPLOYEE (EMPLOYEE_ID, EMPLOYEE_NAME, EMPLOYEE_MOBILE, EMPLOYEE_DEPARTMENT) Values (3, ‘Minaal Haneef’, […]

» 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 session ‘SID,SERIAL#’;  

» Read more

How to Clean up names and split names in Excel as First , Middle and Last Name

Scenario : You have a excel file with one column which is full name. It needs to clean up by trimming extra spaces, convert into sentense case and finally split into three names . Example : Orginal Name : MAHAMMAD HanEEF BALLeri PUttur    = > Firstname : Mahammad  Middlename : Haneef Balleri  Lastname : Puttur Steps Put the Row Names in Column A Column B , put a formula to trim and also to convert to Sentence Case  B2 =PROPER(TRIM(A2)) column C , put a formula to extract the first name from B2 .  C2=LEFT(B2,SEARCH(” “,B2,1)) column D , Put a formula to extract the Middle Name from B2 .   D2 = MID(B2,((SEARCH(” “,B2,1))),(LEN(B2)-((SEARCH(” “,B2,1)))-(LEN(B2)-FIND(“|”,SUBSTITUTE(B2,” “,”|”, LEN(B2)-LEN(SUBSTITUTE(B2,” “,””))))))) column E, Put a formula to extract the Last Name from B2,  E2 = RIGHT(B2,LEN(B2)-FIND(“|”,SUBSTITUTE(B2,” “,”|”, LEN(B2)-LEN(SUBSTITUTE(B2,” “,””))))) Download the sample excel file here :sample_haneef Splitting Full Name into First , Middle and Last name in Oracle Pl/SQL If you wish to achieve the same in oracle use the following trick>   –Firstname select substr (‘Mohammed Hannef Marakkar Puttur’,1,instr(‘Mohammed Hannef Marakkar Puttur’,’ ‘)) from dual; — Last Name select substr (‘Mohammed Hannef Marakkar Puttur’,instr(‘Mohammed Hannef Marakkar Puttur’,’ ‘,-1)) from dual; –Middle Name select […]

» 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 = functions.getCRN(term) as List<listmodel>;             return Json(crnlist, JsonRequestBehavior.AllowGet);         } Function to Call the Oracle procedure which has the data:   public static List<listmodel> getCRN(string term_code)         {             var p = new OracleDynamicParameters();             p.Add(“p_term”, term_code);             p.Add(“p_crn”, dbType: OracleDbType.RefCursor, direction: ParameterDirection.Output);             var crnlist = new List<listmodel>();             using (SqlMapper.GridReader gr = DB.GetMultiGrid(“procedure.p_get_term_crn”, p))             {                 crnlist = gr.Read<listmodel>().ToList<listmodel>();             }             return crnlist;         } AJAX Script in View   < script > function get_crn_data(data) {         $.ajax({             type: “POST”,             url: “get_crn_term”,             dataType: “json”,             data: “{ term: ‘” + data.value+ “‘}”,             contentType: “application/json; charset=utf-8”,             success: function (data) {                 $(“.crn”).empty();                 var optionhtml1 = ‘<option value=”‘ +                  0 + ‘”>’ + “–Select State–” + ‘</option>’;                 $(“.crn”).append(optionhtml1);                 $.each(data, function (i) {                     var optionhtml = ‘<option value=”‘ +                 data[i].txtval+ ‘”>’ +data[i].txtdesc+ ‘</option>’;                     $(“.crn”).append(optionhtml);                 }); […]

» 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 –> Add –> Oracle in Ora Clientxxx If you cannot see the TNS service name in drop down means you have not done previous step. You can also check the proper settings by pressing Test Connection and providing the Credentials.    .

» Read more
1 2