Category Archives: Oracle

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 »

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 »

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 »

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 »