AUTO LOG WITH TRIGGER

create
TRIGGER TRIG_MYTBL_LOG AFTER
  UPDATE ON  MYTBL  FOR EACH ROW BEGIN
  INSERT
  INTO  MYTBL_LOG
    (
      ITEM_CODE,
      GTIN,
      REGID,
      REGIP,
      REGDATE
    )
    VALUES
    (
      :OLD.ITEM_CODE,
      :OLD.GTIN,
      :OLD.REGID,
      :OLD.REGIP,
      :OLD.REGDATE
    );
END;

Share Article : AUTO LOG WITH TRIGGER

AUTO LOG WITH TRIGGER

Killing an Oracle session to remove a LOCK

Get List of LOCKS

SELECT  l.inst_id,
SUBSTR(L.ORACLE_USERNAME,1,8) ORA_USER,
SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
DECODE(L.LOCKED_MODE,   0,'NONE',
1,'NULL',
2,'ROW SHARE',
3,'ROW EXCLUSIVE',
4,'SHARE',
5,'SHARE ROW EXCLUSIVE',
6,'EXCLUSIVE',
NULL) LOCK_MODE
FROM    sys.GV_$LOCKED_OBJECT L
, DBA_OBJECTS O
, sys.GV_$SESSION S
, sys.GV_$PROCESS P
WHERE     L.OBJECT_ID = O.OBJECT_ID
  and     l.inst_id = s.inst_id
  AND     L.SESSION_ID = S.SID
  and     s.inst_id = p.inst_id
  AND     S.PADDR = P.ADDR(+)
order by l.inst_id

Just a matter of Killing 
ALTER SYSTEM KILL SESSION 'sid#,serial#' IMMEDIATE;

Share Article : Killing an Oracle session to remove a LOCK

Killing an Oracle session to remove a LOCK

Getting missing dates in Oracle

WITH DATA AS (
select to_date(to_char(unit_date,'yyyy/mm/dd')) dat from hr_attendance where empid = 'EMP0001'
)
SELECT mi+LEVEL missing FROM(
SELECT MIN(dat) mi,MAX(dat)ma FROM DATA)
CONNECT BY LEVEL <= ma-mi
MINUS
SELECT dat FROM DATA;


  

Share Article : Getting missing dates in Oracle

Getting missing dates in Oracle

Oracle Jobs, user and privileges

How Jobs Execute

Jnnn processes execute jobs. To execute a job, the process creates a session to run the job. When a Jnnn process runs a job, the job is run in the same environment in which it was submitted and with the owner's default privileges. The owner must be explicitly granted the necessary object privileges for all objects referenced within the job definition.

When you force a job to run using the procedure DBMS_JOB.RUN, the job is run by your user process and with your default privileges only. Privileges granted to you through roles are unavailable. You must be explicitly granted the necessary object privileges for all objects referenced within the job definition.

Share Article : Oracle Jobs, user and privileges

Oracle Jobs, user and privileges

String Concat function-Oracle ( similar usage to SUM)

SELECT deptno, wm_concat(ename) AS employees

FROM   emp
GROUP BY deptno;

Result -----> 

    DEPTNO EMPLOYEES
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,FORD,ADAMS,SCOTT,JONES
        30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD

Base Data:

    DEPTNO ENAME
---------- ----------
        20 SMITH
        30 ALLEN
        30 WARD
        20 JONES
        30 MARTIN
        30 BLAKE
        10 CLARK
        20 SCOTT
        10 KING
        30 TURNER
        20 ADAMS
        30 JAMES
        20 FORD
        10 MILLER


For Oracle 9 --> as wm_concat() function is not available use following custom function. 
CREATE OR REPLACE FUNCTION get_it (invoice in VARCHAR2)
  RETURN VARCHAR2
IS
  l_text  VARCHAR2(32767) := NULL;
BEGIN
  FOR cur_rec IN (SELECT x AS itemName FROM invoice_breakdown where invoice_no = invoice /*query returning multiple rows*/) LOOP
    l_text := l_text || ',' || cur_rec.itemName;
  END LOOP;
  RETURN LTRIM(l_text, ',');
END;
/
SHOW ERRORS

Share Article : String Concat function-Oracle ( similar usage to SUM)

String Concat function-Oracle ( similar usage to SUM)

Auto Incrementing primary key with Oracle

SQL> CREATE TABLE test
(id NUMBER PRIMARY KEY,
name VARCHAR2(30));

Table created.

SQL> CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

Sequence created.

Now we can use that sequence in an BEFORE INSERT trigger on the table.

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;

Trigger created.

Share Article : Auto Incrementing primary key with Oracle

Auto Incrementing primary key with Oracle