Monday, March 26, 2018

How to Change weblogic Server Password


changing weblogic passwords
----------------------
take a back of below all mentioned directory inside files
C:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\servers\AdminServer\data
C:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\config\oracledi\nodemanager
C:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\servers\AdminServer\security
C:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\security

node manager password
->C:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\config\oracledi\nodemanager
nm_password.properties in this file
hashed={Algorithm\=SHA-256}kjx+pETIjTE/hao3pSBONtYL6ChYf8YJDq0k7MdSfRE\=
remove above line and add below two password and username
password=Admin123
username=weblogic

weblogic main admin server:
----------------------
C:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\servers\AdminServer\security
boot.properties   change the password
password=Admin123
username=weblogic

Changing password in Weblogic Console
----------------------------------
Go to: Security Realm -> myrealm > Users and Groups >weblogic -> Passwords
change the password

Changing the Nodemanager password in the Weblogic Console:
----------------------------------------------
Go to Domain -> General -> Security -> Advanced
change the password.

Or Changing the password from Command line using below command
------------------------------------------------------

java weblogic.security.utils.AdminAccount  weblogic welcome123 .

after this new security file will be created in
C:\Oracle\Middleware\Oracle_Home\user_projects\domains\base_domain\security

DefaultAuthenticatorInit.ldift

Wednesday, March 21, 2018

SQL Performance Tuning 2 and tips

Oracle SQL Performance Tuning 1

PL/SQL Tutorial : PLSQL Interview Questions and answers

SQL Tutorial 6 : SQL ORDER BY Clause

OBIEE 12c : OBIEE 12c Installations Part 1 on Windows 10

OBIEE 12c : OBIEE 12c Installations part 2 on Windows 10

SQL Tutorial 5 : SQL Operators Precedence

SQL 12c Tutorial 20 : SQL 12c Creating Multiple Indexes on column

SQL 12c Tutorial 19 : SQL IDENTITY Column for generating Sequence Valu...

SQL 12c Tutorial 18 : SQL Invisible Column options in SQL 12c New Feat...

SQL 12c Tutorial 17 : SQL Inline View Sub-Queries examples

SQL 12c Tutorial 16 : SQL Correlated Sub Queries

SQL 12c Tutorial : SQL Sub Queries IN , ANY, ALL

SQL 12c Tutorial 14 : SQL Set Operators UNION, UNION ALL, INTERSECT an...

SQL Tutorial 13 : SQL General Functions NVL , NVL2, NULLIF and COALESCE

SQL Tutorial 11 : SQL Numeric Functions ROUND TRUNC MOD

SQL Tutorial 10 : SQL Single Row Character Functions

SQL Tutorial 7 : SQL 12c ROW Limiting Clause

SQL Tutorial 8 : SQL Substitution variables

SQL Tutorial 9 : SQL DEFINE , UNDEFINE & SET VERIFY Commands

SQL 12c Tutorial 21 : SQL 12c Features APPROX_COUNT_DISTINCT

SQL Tutorial 12 : SQL Date Manipulation functions

Monday, February 19, 2018

PL/SQL Tutorial: What is PL SQL Nested Table Collections

PL/SQL Tutorial : Weak REF CURSOR

What is FOR UPDATE and WHERE CURRENT OF Clauses in PLSQL

PL/SQL Tutorial: BULK COLLECT LIMIT Clause

PL/SQL Tutorial : FORALL with SAVE EXCEPTION

PL/SQL Tutorial: Differences Between PLS_INTEGER and BINARY_INTEGER

PL/SQL Tutorial : Types of Dynamic Cursors

PL/SQL Tutorial: What is Forward Declaration in PL SQL

What is a Data Lake? Big Data Data Lake

List Of Available Data Lake Tools

PL/SQL Tutorial : REF CURSOR with USING Clause

PLSQL Tutorial: Difference between PL SQL Collections Varray nested tabl...

PL/SQL Tutorial : PLSQL Collections and Types

PL/SQL Tutorial: Strong Ref Cursor in Dynamic Cursors

PL/SQL Tutorial : SYS REFCURSOR Dynamic Cursor Variable

PL/SQL Tutorial : EXECUTE IMMEDIATE with USING Clause

PL/SQL Tutorial : EXECUTE IMMEDIATE with INTO Clause

PL/SQL Tutorial : RETURNING INTO Clause examples

PL/SQL Tutorial: EXECUTE IMMEDIATE Native Dynamic SQL

PL/SQL Tutorial : Using RETURNING INTO Clause in FORALL

Monday, February 12, 2018

PLSQL Interview Questions

Slowly Changing Dimensions and Types in Data Warehousing

exchange Partition , Move partition in oracle

Types Facts in Data Warehousing

What is Non Additive Fact?

What is Semi Additive Fact in Data Warehousing

What is Galaxy Schema in Dimensional Modeling

What is Star Schema in Dimensional Modeling

What Is Confirmed Dimension In Dimensional Modeling

Types Of Dimensions in Dimensional Modeling

What is Junk Dimension in Dimensional Modeling

What is Role Playing Dimension in Dimensional Modeling

What is Static Dimension in Dimensional Modeling

What Is Degenerate Dimension in Dimensional Modeling

What is Late Arriving Dimension or Inferred Dimension

What is Fact less Fact table in Dimensional Modeling

Types Of Fact Tables in Data Warehousing

Saturday, February 10, 2018

Types Of Fact Tables in Data Warehousing

What is Fact less Fact table in Dimensional Modeling

What is Late Arriving Dimension or Inferred Dimension

What is Late Arriving Dimension or Inferred Dimension

What Is Degenerate Dimension in Dimensional Modeling

What is Role Playing Dimension in Dimensional Modeling

What is Junk Dimension in Dimensional Modeling

Types Of Dimensions in Dimensional Modeling

What is Star Schema in Dimensional Modeling

What is Galaxy Schema in Dimensional Modeling

Thursday, February 8, 2018

Oracle Data integrator Interview Questions & Answers

How To Delete Duplicate Records in SQL Oracle







 select rowid,empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

Using self-join with ROWID
=========================

delete FROM emp a
WHERE rowid NOT IN
  (SELECT max(rowid) FROM emp b WHERE a.empno = b.empno );
 
(OR)

select rowid,empno,ename FROM emp a
WHERE rowid NOT IN
  (SELECT min(rowid) FROM emp b WHERE a.empno = b.empno );
 
 rollback;
 
Using rowid with GROUP BY Clause
================================

delete FROM emp
WHERE rowid not  IN   (SELECT min(rowid) FROM emp GROUP BY empno);

(OR)

select * FROM emp
WHERE rowid NOT IN   (SELECT MAX(rowid) FROM emp GROUP BY empno);

rollback;


Using rowid with  row_number()
===============================
select * FROM emp
WHERE rowid IN
  (SELECT delrowid  FROM
    (SELECT rowid delrowid,row_number() over(partition BY empno order by empno) rownm
    FROM emp    )
  WHERE rownm > 1  );



Using rowid with dense_rank()
=============================
delete  FROM emp
WHERE rowid IN  (SELECT delrowid
  FROM
    (SELECT rowid delrowid, dense_rank() over(partition BY empno order by rowid) rownm
    FROM emp    )
  WHERE rownm > 1  );

rollback;

Using Group By
===============

select * FROM emp
WHERE (empno,ename,sal) IN
  (SELECT empno,ename,sal FROM emp GROUP BY empno,ename,sal having count(empno)>1 );
 
rollback;
 
 

sub partitioning in oracle or composite partitioning in Oracle RANGE-LI...










******************************************************************************/
drop table EMP_COMP_RH;
 CREATE TABLE EMP_COMP_RH
 (
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )
 PARTITION BY RANGE (HIREDATE)
 SUBPARTITION BY HASH (EMPNO) SUBPARTITIONS 4
(Partition P1 values less than (TO_DATE ('01-JAN-1980','dd-MON-yyyy')),
Partition P2 values less than (TO_DATE ('01-JAN-1985','dd-MON-yyyy')),
Partition P3 values less than (TO_DATE ('01-JAN-1990','dd-MON-yyyy')),
Partition P4 values less than (TO_DATE ('01-JAN-2000','dd-MON-yyyy'))
)ENABLE ROW MOVEMENT;;

INSERT INTO EMP_COMP_RH select * from EMP;
commit;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_COMP_RH');
END;
 /


 select * from EMP_COMP_RH;


drop table EMP_COMP_RL;
 CREATE TABLE EMP_COMP_RL
 (
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )
 PARTITION BY RANGE (HIREDATE)
 SUBPARTITION BY LIST(DEPTNO)
(Partition P1 values less than (TO_DATE ('01-JAN-1980','dd-MON-yyyy'))
      (
       SUBPARTITION SP1 VALUES ('10'),
       SUBPARTITION SP2 VALUES ('20'),
       SUBPARTITION SP3 VALUES ('30'),
       SUBPARTITION SP4 VALUES ('40')
     ) ,
Partition P2 values less than (TO_DATE ('01-JAN-1985','dd-MON-yyyy'))
(
       SUBPARTITION SP11 VALUES ('10'),
       SUBPARTITION SP21 VALUES ('20'),
       SUBPARTITION SP31 VALUES ('30'),
       SUBPARTITION SP41 VALUES ('40')
     ),
Partition P3 values less than (TO_DATE ('01-JAN-1990','dd-MON-yyyy'))
(
       SUBPARTITION SP12 VALUES ('10'),
       SUBPARTITION SP22 VALUES ('20'),
       SUBPARTITION SP32 VALUES ('30'),
       SUBPARTITION SP42 VALUES ('40')
     ),
Partition P4 values less than (TO_DATE ('01-JAN-2000','dd-MON-yyyy'))
(
       SUBPARTITION SP13 VALUES ('10'),
       SUBPARTITION SP23 VALUES ('20'),
       SUBPARTITION SP33 VALUES ('30'),
       SUBPARTITION SP43 VALUES ('40')
     )
)ENABLE ROW MOVEMENT;

INSERT INTO EMP_COMP_RL select * from EMP;
commit;

select * from EMP_COMP_RL;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_COMP_RL');
END;
 /

What is a Hash Partition in Oracle








/* =========================================================================*/
DROP TABLE EMP_HASH;

CREATE TABLE EMP_HASH
  (
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )
  PARTITION BY HASH ( EMPNO )
  PARTITIONS 4
  ENABLE ROW MOVEMENT;
 
insert into EMP_HASH select * from EMP;
commit;


BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_HASH');
END;
 /


select * from EMP_HASH where empno=7369;

What is a LIST Partition in Oracle








/* ==========================================================================*/

DROP TABLE EMP_LIST;

CREATE TABLE EMP_LIST
  (
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )
   PARTITION BY LIST (DEPTNO)
      (
       PARTITION P1 VALUES ('10'),
       PARTITION P2 VALUES ('20'),
       PARTITION P3 VALUES ('30'),
       PARTITION P4 VALUES ('40')
     ) ENABLE ROW MOVEMENT;
  
INSERT INTO EMP_LIST SELECT * FROM EMP;  
COMMIT;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_LIST');
END;
 /
select * from EMP_LIST where deptno=20;

select * from emp where deptno=10;

What is a Partition in Oracle and Type of Partitions , Range Partition







 /*****************************************************************************
ORACLE PARTITIONS AND TYPES.

1) Range Partitioning
2) LIST Partitioning
3) HASH Partitioning
4) Composite RANGE-HASH Partitioning
5) Composite RANGE-LIST Partitioning
******************************************************************************/


drop table emp_part;

CREATE TABLE emp_part
(
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )PARTITION BY RANGE (sal)
           INTERVAL  (1000)
          (
     PARTITION p1 VALUES LESS THAN (1000)   ,
     PARTITION p2 VALUES LESS THAN (2000)  ,
     PARTITION p3 VALUES LESS THAN (3000)  ,
     PARTITION p4 VALUES LESS THAN (4000)
     )ENABLE ROW MOVEMENT ;
   
 select * from emp_part PARTITION(P2); --where sal>4000;
 ;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_PART');
 END;
 /
 insert into emp_part select * from emp;
 commit;

  select * from emp_part PARTITION(p3);



drop table emp_RANGE_DT;
 CREATE TABLE emp_RANGE_DT
 (
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )
 PARTITION BY RANGE (HIREDATE)
(Partition P1 values less than (TO_DATE ('01-JAN-1980','dd-MON-yyyy')),
Partition P2 values less than (TO_DATE ('01-JAN-1985','dd-MON-yyyy')),
Partition P3 values less than (TO_DATE ('01-JAN-1990','dd-MON-yyyy')),
Partition P4 values less than (TO_DATE ('01-JAN-2000','dd-MON-yyyy'))
) ENABLE ROW MOVEMENT;;

 insert into emp_RANGE_DT select * from emp;
 commit;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_RANGE_DT');
END;
 /
 

/* ==========================================================================*/

DROP TABLE EMP_LIST;

CREATE TABLE EMP_LIST
  (
    EMPNO    NUMBER(4,0) ,
    ENAME    VARCHAR2(10 ),
    JOB      VARCHAR2(9 ),
    MGR      NUMBER(4,0),
    HIREDATE DATE,
    SAL      NUMBER(7,2),
    COMM     NUMBER(7,2),
    DEPTNO   NUMBER(2,0)
  )
   PARTITION BY LIST (DEPTNO)
      (
       PARTITION P1 VALUES ('10'),
       PARTITION P2 VALUES ('20'),
       PARTITION P3 VALUES ('30'),
       PARTITION P4 VALUES ('40')
     ) ENABLE ROW MOVEMENT;
 
INSERT INTO EMP_LIST SELECT * FROM EMP; 
COMMIT;

BEGIN
 DBMS_STATS.gather_table_stats('SDEV', 'EMP_LIST');
END;
 /
select * from EMP_LIST where deptno=20;

select * from emp where deptno=10;

What is PRAGMA AUTONOMOUS TRANSACTION in PLSQL?

What is Cardinality and High Cardinality and Low Cardinality in Oracle

Difference Between Translate & Replace in SQL Oracle

Difference Between Case & Decode in SQL Oracle

Difference Between NVL and NVL2 functions in sql

Difference between substring and instring

Difference Between IN and EXISTS Operators in Oracle

Difference Between UNION and UNION ALL in oracle

What is NULLIF Function in Oracle

What is LEAD Function in Oracle

What is LAG Function in Oracle

What is Global temporary Tables in Oracle







/*****************************************************************************
 DECLARE GLOBAL TEMPORARY TABLE table-Name
    { column-definition [ , column-definition ] * }
[ ON COMMIT {DELETE | PRESERVE} ROWS ] 
NOT LOGGED [ON ROLLBACK DELETE ROWS]

 *****************************************************************************/
  CREATE GLOBAL TEMPORARY TABLE students 
   ( student_id numeric(10) NOT NULL, 
     student_name varchar2(50) NOT NULL, 
     student_address varchar2(50) 
    ); 
   
drop table GLOBAL_TEMP_TABLE ;   
 CREATE GLOBAL TEMPORARY TABLE GLOBAL_TEMP_TABLE
 ( id NUMBER,
 description VARCHAR2(20)
 ) ON COMMIT   DELETE ROWS;

insert into GLOBAL_TEMP_TABLE values(1,'this is first row');

select * from GLOBAL_TEMP_TABLE;

commit;
drop  table GLOBAL_TEMP PURGE;

CREATE GLOBAL TEMPORARY TABLE GLOBAL_TEMP
(
  id           NUMBER,
  description  VARCHAR2(50)
)ON COMMIT PRESERVE ROWS;

commit;
insert into global_temp values(2,'this is ON COMMIT PRESERVE ROWS');

select * from global_temp;

What is SYS_CONTEXT in Oracle with Example

Introduction To PLSQL Triggers and Types of Triggers









/*****************************************************************************
         Oracle PLSQL TRIGGERS..
TYPES OF TRIGGERS
-----------------
1) DML Triggers
2) DDL Triggers
3) SYSTEM Triggers (STARTUP, SHUTDOWN) and user Evens (LOGON & LOGOFF)
4) INSTEAD OF Trigger

TYPES OF DML Triggers:
----------------------
ROW LEVEL & STATEMENT  LEVEL TRIGGERS
=====================================
Firing Point : BEFORE
=========================
  1)  BEFORE INSERT TRIGGER
  2)  BEFORE UPDATE TRIGGER
  3)  BEFORE DELETE TRIGGER

Firing Point : AFTER
=====================
  1)  AFTER INSERT TRIGGER
  2)  AFTER UPDATE TRIGGER
  3)  AFTER DELETE TRIGGER
 

OLD & NEW Qualifiers.
-----------------------
INSERT :  NEW
UPDATE :  OLD & NEW
DELETE :  OLD

Conditional Predicates
--------------------------
INSERTING
UPDATING
DELETING
*******************************************************************************/
CREATE [OR REPLACE] TRIGGER trigger_name       
    BEFORE | AFTER
    [INSERT, UPDATE, DELETE [COLUMN NAME..]
    ON table_name

    Referencing [ OLD AS OLD | NEW AS NEW ]
    FOR EACH ROW | FOR EACH STATEMENT [ WHEN Condition ]

DECLARE
    [declaration_section
        variable declarations;
        constant declarations;
    ]

BEGIN
    [executable_section
        PL/SQL execute/subprogram body
    ]

EXCEPTION
    [exception_section
        PL/SQL Exception block
    ]

END;
********************************************************************************
/


CREATE or REPLACE TRIGGER EMP_TRIGGER
    BEFORE
    INSERT ON emp
    FOR EACH ROW
BEGIN
    :new.ename := upper(:new.ename);
END;



CREATE or REPLACE TRIGGER EMP_DELETE_TRIGGER
    AFTER
    DELETE ON emp
    FOR EACH ROW
BEGIN
    IF :old.empno = 7782 THEN
  null;
        raise_application_error(-20015, 'You cant delete this row');
    END IF;
END;


delete from emp where empno=7782;

select * from mytable;
select * from emp;


CREATE or REPLACE TRIGGER EMP_DELETE_TRG_1
    BEFORE
    DELETE ON emp
    FOR EACH ROW
BEGIN
   
        insert into mytable values(:old.empno,sysdate);
   
END;

What is Conditional Predicates and Qualifiers in TRIGGERS PLSQL








 CREATE OR REPLACE TRIGGER EMP_TRIGGER_PREDICATES
      BEFORE INSERT OR DELETE OR UPDATE ON emp
      FOR EACH ROW
    DECLARE
      v_ChangeType CHAR(1);
    BEGIN
      /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
      IF INSERTING THEN
        v_ChangeType := 'I';
     ELSIF UPDATING THEN
       v_ChangeType := 'U';
     ELSE
       v_ChangeType := 'D';
     END IF;

     DBMS_OUTPUT.put_line(v_ChangeType ||'- '|| USER ||'-' ||SYSDATE);
   END EMP_TRIGGER_PREDICATES;
   /
   

PLSQL DML Triggers and Types of DML Triggers





/*****************************************************************************
         Oracle PLSQL TRIGGERS..
TYPES OF TRIGGERS
-----------------
1) DML Triggers
2) DDL Triggers
3) SYSTEM Triggers (STARTUP, SHUTDOWN) and user Evens (LOGON & LOGOFF)
4) INSTEAD OF Trigger

TYPES OF DML Triggers:
----------------------
ROW LEVEL & STATEMENT  LEVEL TRIGGERS
=====================================
Firing Point : BEFORE
=========================
  1)  BEFORE INSERT TRIGGER
  2)  BEFORE UPDATE TRIGGER
  3)  BEFORE DELETE TRIGGER

Firing Point : AFTER
=====================
  1)  AFTER INSERT TRIGGER
  2)  AFTER UPDATE TRIGGER
  3)  AFTER DELETE TRIGGER
 

OLD & NEW Qualifiers.
-----------------------
INSERT :  NEW
UPDATE :  OLD & NEW
DELETE :  OLD

Conditional Predicates
--------------------------
INSERTING
UPDATING
DELETING
*******************************************************************************/
CREATE [OR REPLACE] TRIGGER trigger_name       
    BEFORE | AFTER
    [INSERT, UPDATE, DELETE [COLUMN NAME..]
    ON table_name

    Referencing [ OLD AS OLD | NEW AS NEW ]
    FOR EACH ROW | FOR EACH STATEMENT [ WHEN Condition ]

DECLARE
    [declaration_section
        variable declarations;
        constant declarations;
    ]

BEGIN
    [executable_section
        PL/SQL execute/subprogram body
    ]

EXCEPTION
    [exception_section
        PL/SQL Exception block
    ]

END;
********************************************************************************
/


CREATE or REPLACE TRIGGER EMP_TRIGGER
    BEFORE
    INSERT ON emp
    FOR EACH ROW
BEGIN
    :new.ename := upper(:new.ename);
END;



CREATE or REPLACE TRIGGER EMP_DELETE_TRIGGER
    AFTER
    DELETE ON emp
    FOR EACH ROW
BEGIN
    IF :old.empno = 7782 THEN
  null;
        raise_application_error(-20015, 'You cant delete this row');
    END IF;
END;

What is Vsize Function in Oracle

What is Instead of Trigger in PLSQL








/*****************************************************************************
INSTEAD OF TRIGGER -- To Update Complex Views...
******************************************************************************/
drop table employee;
CREATE TABLE employee
(
    employee_id number(10) primary key,
    employee_name varchar2(20),
    location varchar2(20),
  department_id number references departments(department_id)
);

DROP TABLE departments;
CREATE TABLE departments
(
    department_id number(10) primary key,
    department_name varchar2(30),
    creation_date date default sysdate
);


CREATE OR REPLACE VIEW EMPLOYEE_DEPART_V AS
   SELECT dept.department_id, dept.department_name,dept.creation_Date,
          emp.employee_id,
          emp.employee_name, emp.location
   FROM DEPARTMENTS dept, EMPLOYEE emp
   WHERE dept.department_id = emp.department_id;


insert into EMPLOYEE_DEPART_V values(1,'IT',SYSDATE,1,'RAVEENDRA','BANGALORE');
insert into EMPLOYEE_DEPART_V values(2,'SALES',SYSDATE,2,'RESHWANTH','HYDERABAD');
insert into EMPLOYEE_DEPART_V values(2,'IT',SYSDATE,2,'RAVEENDRA','BANGALORE');

commit;

delete from departments;

select * from EMPLOYEE_DEPART_V;
select * from employee;
select * from departments;
DROP TRIGGER EMP_DEPT_Instead_OF_TRG;

CREATE OR REPLACE TRIGGER EMP_DEPT_Instead_OF_TRG
   INSTEAD OF INSERT ON EMPLOYEE_DEPART_V
   DECLARE
     duplicate_info EXCEPTION;
     PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
   BEGIN

INSERT INTO departments (department_id, department_name, creation_date)
   VALUES (
     :new.department_id,
     :new.department_name,
     :new.creation_date);
    
   INSERT INTO employee
       (employee_id,employee_name,location,DEPARTMENT_ID)
     VALUES (:new.employee_id, :new.employee_name, :new.location,:new.DEPARTMENT_ID);

   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate employee or department id');
END EMP_DEPT_Instead_OF_TRG;

PLSQL DDL Triggers with examples








/*****************************************************************************
 DDL Triggers Examples..

CREATE OR REPLACE TRIGGER DDLTrigger_name
AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN
-----
END;
******************************************************************************/
CREATE TABLE DDL_TABLE_LOG
  (
    ora_dict_obj_name  VARCHAR2(100),
    ora_login_user     VARCHAR2(100),
    creation_date      DATE,
    ora_sysevent       VARCHAR2(100),
    ora_dict_obj_type  VARCHAR2(100),
    ora_dict_obj_owner VARCHAR2(100)
  );
/

CREATE OR REPLACE TRIGGER MY_DDL_TRIGGER AFTER DDL ON DATABASE
  BEGIN
    INSERT
    INTO DDL_TABLE_LOG VALUES
      (
        ora_dict_obj_name ,
        ora_login_user ,
        sysdate ,
        ora_sysevent ,
        ora_dict_obj_type ,
        ora_dict_obj_owner
      );
  END;
  /
 
 
select * from DDL_TABLE_LOG;

truncate table DDL_TABLE_LOG;

LOGON & LOGOFF Triggers in PLSQL







/*****************************************************************************
 USER EVENT LOGON & LOGOFF Triggers Examples..

CREATE OR REPLACE TRIGGER DDLTrigger_name
AFTER LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN
-----
END;
******************************************************************************/
DROP TABLE USER_EVENT_LOG;
CREATE TABLE USER_EVENT_LOG
  (
    ora_login_user     VARCHAR2(100),
    creation_date      DATE,
    ora_sysevent       VARCHAR2(100)
  );
/


CREATE OR REPLACE TRIGGER MY_DDL_TRIGGER_LOGON AFTER LOGON   ON DATABASE
  BEGIN
    INSERT
    INTO USER_EVENT_LOG VALUES
      (
        ora_login_user ,
        sysdate ,
        ora_sysevent 
      );
  END;
  /
 
 
CREATE OR REPLACE TRIGGER MY_DDL_TRIGGER_LOGOFF BEFORE LOGOFF   ON DATABASE
  BEGIN
    INSERT
    INTO USER_EVENT_LOG VALUES
      (
        ora_login_user ,
        sysdate ,
        ora_sysevent  
      );
  END;
  /
  

What is PRAGMA AUTONOMOUS TRANSACTION in PLSQL?







/*****************************************************************************
PRAGMA AUTONOMOUS_TRANSACTION
*****************************************************************************/
 The Child Code runs independently of its parent
 the Child code can commit or rollback and parent resumes
 the parent code can continue without affecting child work

 drop table my_test;
 CREATE TABLE my_test (
  id           NUMBER       NOT NULL,
  description  VARCHAR2(50) NOT NULL
);

INSERT INTO my_test (id, description) VALUES (1, 'Description for 1');
INSERT INTO my_test (id, description) VALUES (2, 'Description for 2');

DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  FOR i IN 3 .. 10 LOOP
    INSERT INTO my_test (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  commit;
END;
/


select * from my_test;

rollback;

delete from my_test;




CREATE or REPLACE TRIGGER EMP_TRIGGER
    BEFORE
    INSERT ON emp
    FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    :new.ename := upper(:new.ename);
  commit;
END;

/

insert into emp   select * from scott.emp;

commit;

What is Mutating Table Error in Triggers











/*****************************************************************************
WHAT IS MUTATING TABLE ERROR???????????????????????????????????????????
*****************************************************************************/
CREATE TABLE mutate_tables1 ( num    NUMBER );

CREATE TABLE mutate_tables2 ( id NUMBER );

INSERT INTO mutate_tables2 VALUES (NULL);
COMMIT;

CREATE OR REPLACE TRIGGER mutate_Trigger1
AFTER INSERT ON mutate_tables2
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  UPDATE mutate_tables1
     SET num = ( SELECT MAX(id) FROM mutate_tables2 );
   COMMIT; 
END;
/

INSERT INTO mutate_tables2 VALUES (1);

commit;

select * from mutate_tables2;
select * from mutate_tables1;

BULK COLLECT and BULK BIND in PLSQL





/*****************************************************************************
BULK COLLECT & FORALL (BULK BINDING)......
******************************************************************************/
SET SERVEROUTPUT ON;
create table bulk_table(no number);
create table bulk_bind(no number);
----------------------------

BEGIN
  FOR i IN 1..1000000
  LOOP
    INSERT INTO bulk_table VALUES  (i);
  END LOOP;
  COMMIT;
END;
/

select count(*) from bulk_table;

------------------------

DECLARE
TYPE rt IS  TABLE OF bulk_table%rowtype;
  vt rt;
BEGIN
  SELECT * BULK COLLECT INTO vt FROM bulk_table;
  FORALL i IN 1..vt.count
  INSERT INTO bulk_bind VALUES vt(i);
  COMMIT;
END;
/

----------------------------------
select count(*) from bulk_bind;





Sunday, January 14, 2018

Oracle PLSQL Tutorials : PLSQL Explicit Cursors with Examples

Oracle PLSQL Tutorials For Beginners 5 : PLSQL Cursors , Implicit Cursor...







/*****************************************************************************
             PLSQL  Cursors & Types.
             ------------------------
              1) Implicit Cursors
              2) Explicit Cursors
             
              Cursor Atributes.
              --------------------
              1) %FOUND
              2) %NOTFOUND
              3) %ISOPEN
              4) %ROWCOUNT
             
              Explicit Cursors Process:
              -----------------
              1) DECLARE - Cursor
              2) OPEN  - Cursor
              3) FETCH - Cursor
              4) CLOSE - Cursor
              Declaring the cursor for initializing the memory
              Opening the cursor for allocating the memory
              Fetching the cursor for retrieving the data
              Closing the cursor to release the allocated memory

******************************************************************************/
-- Implecit Cursor Attributes....

select * from emp;

-- SQL%ROWCOUNT

DECLARE
  l_vc_name emp.ename%type;
  l_vc_sal emp.sal%type;
BEGIN
  SELECT ENAME, SAL
  INTO l_vc_name, l_vc_SAL
  FROM EMP
  WHERE empno=7566;
  dbms_output.put_line('Number of rows processed: '||sql%rowcount);
END;
/

---  SQL%FOUND, SQL%NOTFOUND & SQL%ROWCOUNT

DECLARE 
   v_total_Updated_rows number(2);
BEGIN
   UPDATE emp
   SET sal = sal + 500;    
   --- Checking Implicit Cursor  Data %NOTFOUND Attribute..
   IF sql%notfound THEN
      dbms_output.put_line('None of the Employee Salaries Updated');
   -- Checking Implicit Cursor Data using %FOUND Attribute..
   ELSIF sql%found THEN
   -- Getting Row Count using %ROWCOUNT Attribute..
      v_total_Updated_rows := sql%rowcount;
      dbms_output.put_line( v_total_Updated_rows || ' Total No.Of Employee Salaries Updated ');
   END IF; 
   commit;
END;
/  

/*****************************************************************************
                 Explicit Cursor Example....
*****************************************************************************/
CREATE OR REPLACE PROCEDURE Get_emp_names_Proc (Dept_num IN NUMBER) IS
   Emp_name       VARCHAR2(10);
   V_Total_Employees NUMBER(10);
   CURSOR         c1 (Depno NUMBER) IS
                     SELECT Ename FROM Emp
                        WHERE deptno = Depno;
BEGIN
   OPEN c1(Dept_num);
   LOOP
      FETCH c1 INTO Emp_name;
      EXIT WHEN C1%NOTFOUND;     
      DBMS_OUTPUT.PUT_LINE(Emp_name);     
   END LOOP;
   V_Total_employees:=c1%rowcount;
   DBMS_OUTPUT.PUT_LINE('Total Employees for Dept 10 :: '||V_Total_employees);
   CLOSE c1;
END;
/

select * from emp;


exec Get_emp_names_Proc(10);



Oracle PLSQL Tutorial For Beginners 4 : PLSQL Procedures %Type, %ROWTYP...

Oracle PLSQL Tutorials for Beginners 3 : Oracle PLSQL Procedure with ex...











/******************************************************************************
                  SAMPLE PLSQL PROCEDURE
                  =>   IN Parameter -- Passed By Reference
                  =>   OUT Parameter  -- Passed By Value
                  =>   IN OUT Parameter   -- Passed By Value
                  =>   Position Based Parameters Notation.
                  =>   Named Based Parameters  Notation. (=>) Arrow Symbol
                  =>   Mixed Parameters Notaion.
                  =>   NOCOPY Clause for changing Pass by value to reference.
******************************************************************************/
CREATE OR REPLACE  PROCEDURE First_PROC
AS
BEGIN
   dbms_output.put_line(' Welcome To First PLSQL Procedure...');
END;
/

exec first_proc;

/*****************************************************************************
                           IN & OUT Parameter
*****************************************************************************/

DECLARE
   V_Num1 number;
   V_Num2 number;
   V_Num3 number;
PROCEDURE findMax(A IN number, B IN number, C OUT number) IS
BEGIN
   IF A > B THEN
      C:= A;
   ELSE
      C:= B;
   END IF;
END;  
BEGIN
   V_Num1:= 12;
   V_Num2:= 55;
   --- Position based Notation
   findMax(V_num1, V_Num2, V_NUM3);
   dbms_output.put_line(' Position Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
   --- Named Based Notation using Arrow =>
   findMax(c=>V_NUM3,b=>V_Num2,a=>V_num1 );
   dbms_output.put_line(' Named Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
   -- Mixed Notation
   findMax( V_num1, c=>V_NUM3, b=>V_Num2);
   dbms_output.put_line(' MIXED Notation ::: Maximum Value of (12, 55) Is => ' || V_NUM3);
END;
/


/******************************************************************************
                        IN OUT Parameter Example
******************************************************************************/

DECLARE
  -- Declaring A Variable for Taking output from Procedure..
   a number;
  -- Declaring Procedure in Declaration Block for IN OUT Parameter....
 
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
/** Inside Procedure Doing Calculation for Square Root... */
  x := x * x;
END; 
-- Ending Procedure....
BEGIN
   --Declaring A Value for In Put Parameter for Above Proc..
   a:= 5;
  /**  Calling Procedure  **/
   dbms_output.put_line('Calling PLSQL Procedure here....With IN OUT Parameter');
   dbms_output.put_line('...................');
   squareNum(a);
   /* Printing OUT Parameter Value using DBMS_OUTPUT Package */
   dbms_output.put_line(' Square of (5): ' || a);
   dbms_output.put_line('...................');

END;
/

/******************************************************************************
                      Creating Procedure in DB
                      => TYPE --- Record Type
                      => ROWTYPE  -- Collection Type
******************************************************************************/
varchar2(30)

CREATE OR REPLACE PROCEDURE Get_emp_rec_proc (Emp_number  IN  Emp.Empno%TYPE,
                       Emp_ret     OUT Emp%ROWTYPE) IS
BEGIN
   SELECT Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno
      INTO Emp_ret
      FROM emp
      WHERE Empno = Emp_number;
END;
/
/******************************************************************************
                      Executing Procedure with Parameters....                    
******************************************************************************/

DECLARE
   Emp_row      Emp%ROWTYPE;     -- declare a record matching a
                                     -- row in the Emp table
BEGIN
   Get_emp_rec_proc(7499, Emp_row);   -- call for Emp_tab# 7499
   DBMS_OUTPUT.PUT(Emp_row.Ename || ' '                || Emp_row.Empno);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Job || ' ' || Emp_row.Mgr);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Hiredate   || ' ' || Emp_row.Sal);
   DBMS_OUTPUT.PUT(' '           || Emp_row.Comm || ' '|| Emp_row.Deptno);
   DBMS_OUTPUT.NEW_LINE;
END;
/

/******************************************************************************
                      OUT &  IN OUT Parameter WITH NOCOPY Example
******************************************************************************/


create or replace procedure No_Copy_Proc
               (PI_Name     in             varchar2,
                PO_Date     out    nocopy  date,
                PIO_Status in out nocopy  varchar2) is
begin
   PO_Date := sysdate;
   PIO_Status := PI_name || ' ::: Sample IN OUT Parameter NOCOPY Testing';
end;
/

DECLARE
  V_DATE DATE;
  V_Status Varchar2(200);
BEGIN
No_Copy_Proc('Raveendra Reddy',V_DATE,V_Status);
dbms_output.put_line(V_DATE);
dbms_output.put_line(V_Status);

END;

Oracle PLSQL Tutorials: PLSQL Functions with OUT Parameter & DML Operati...









/******************************************************************************
                  PLSQL FUNCTION -  SUBPROGRAM....
*******************************************************************************/
-- PLSQL FUNCTION SYNTAX

CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
   < function_body >
END [function_name];

/******************************************************************************
                  Sample Example For PLSQL Function...
*******************************************************************************/

CREATE OR REPLACE FUNCTION Get_Max_Sal(FI_Deptno IN NUMBER)
   RETURN NUMBER
   IS
   max_sal NUMBER(11,2);
   BEGIN
  
      SELECT max(sal)
      INTO max_sal
      FROM EMP
      WHERE deptno = FI_Deptno;
     
      RETURN(max_sal);
     
    END Get_Max_Sal;
/

select * from emp where deptno=20;

select Get_Max_Sal(20) from dual;

select * from emp where deptno=10;


/*****************************************************************************
                PLSQL Function without Parameter Example....
*****************************************************************************/

CREATE OR REPLACE FUNCTION Total_employees
RETURN number IS

V_Total number(3) := 0;
BEGIN

SELECT count(*) into V_Total
FROM emp;

RETURN V_Total;
END;
/

select Total_employees from dual;

/*****************************************************************************
                 Function with OUT Parameter Example..
*****************************************************************************/

CREATE OR REPLACE FUNCTION get_outparam (A IN NUMBER, B IN NUMBER ,C OUT NUMBER)
RETURN NUMBER IS
BEGIN
  C := A+B;
  RETURN A-B;
END get_outparam;
/

select get_outparam(12,15,A) from dual;

SET serveroutput ON;


DECLARE
  A NUMBER:=25;
  B NUMBER:=15;
  C NUMBER;
  D NUMBER;
BEGIN
  D:=get_outparam(A,B,C);
  DBMS_OUTPUT.put_line('OUT PARAMETER VALUE ::: '||C);
    DBMS_OUTPUT.put_line(' FUNCTION RETURN VALUE ::: '||D);

END;
/

/*****************************************************************************
                 Recursive Function Example..
*****************************************************************************/

DECLARE
   num number;
   factorial number; 
  
FUNCTION fact(x number)
RETURN number 
IS
   f number;
BEGIN
   IF x=0 THEN
      f := 1;
   ELSE
      f := x * fact(x-1);
   END IF;
RETURN f;
END; 

BEGIN
   num:= 6;
   factorial := fact(num);
   dbms_output.put_line(' Factorial '|| num || ' is ' || factorial);
END;

/

CREATE OR REPLACE FUNCTION EMP_Update(depnt IN NUMBER)
RETURN VARCHAR2 IS
v_max_sal_before number := 0;
v_max_sal_after number := 0;
BEGIN
SELECT max(sal) into v_max_sal_before FROM emp
where deptno =depnt;

update emp
set sal = sal+500
where deptno =depnt;
commit;

SELECT max(sal) into v_max_sal_after FROM emp
where deptno =depnt;

RETURN 'Before Updating :::'||v_max_sal_before||' ::After Update :::'||v_max_sal_after;
END;
/

select * from emp where deptno=10;

select EMP_UPDATE(10) from dual;

DECLARE
V_MAX_SAL VARCHAR2(100);
BEGIN
 V_MAX_SAL:= EMP_UPDATE(10);

dbms_output.put_line(V_MAX_SAL);
END;
/

select EMP_Update(10) from dual;


Oracle PLSQL Tutorials For Beginners 2, PLSQL Variables & SubTypes Tu...













/*****************************************************************************
   PLSQL DATA TYPES
   => Char
   => Varchar2
   => Date
   => NUMBER ( Integer )
   => Boolean True or Flase
*****************************************************************************/

DECLARE
V_Char char(100):='This is Char';
V_Varchar varchar2(100):='This is Varchar Data Type';
V_Number  Number(10):=55555;
V_Date    DATE:=SYSDATE;
V_Boolean BOOLEAN:=true;
BEGIN
dbms_output.put_line('Welcome Char Variable : ::: '||V_Char );
dbms_output.put_line('Welcome Varchar Variable : ::: '||V_Varchar );
dbms_output.put_line('Welcome Number Variable : ::: '||V_Number );
dbms_output.put_line('Welcome Date Variable : ::: '||V_DATE );
IF V_Boolean  THEN
dbms_output.put_line( 'This is Boolean Variable :::  TRUE');
else
dbms_output.put_line( 'This is Boolean Variable ::: FALSE');
END IF;
END;
/




/*****************************************************************************
   PLSQL SUB TYPES
*****************************************************************************/

DECLARE
   SUBTYPE name IS char(20);
   SUBTYPE address IS varchar2(100);
   SUBTYPE phone IS NUMBER(10);
   V_MyName name;
   V_My_Address address;
   V_My_Phone phone;
BEGIN
   V_MyName := 'Test Char... ';
   V_My_Address := 'Welcome to the PLSQL SubTypes..';
   V_My_Phone :=999999999;
   dbms_output.put_line('Sub Type Char : ::: ' || V_MyName );
   dbms_output.put_line('Sub Type Varchar2 : ::: '|| V_My_Address);
   dbms_output.put_line('Sub Type Varchar2 : ::: '|| V_My_Phone);
END;

Oracle PLSQL Tutorials for Beginners Introduction To PLSQL













DECLARE
 /*   this is multi line <declarations section> */
 -- this is a single line comment
BEGIN
 /*  <executable command(s)> */
EXCEPTION
 /*  <exception handling> */

END;
/

BEGIN
NULL;
END;
/

SET SERVEROUTPUT OFF;
SET SERVEROUTPUT ON;


BEGIN
dbms_output.put_line('This Is First PLSQL Program');
END;
/


DECLARE
   V_MyVar  varchar2(100):= 'Hello, World! This is First PLSQL Program';
   V_Number number:=55;
BEGIN
   dbms_output.put_line(V_Myvar);
   dbms_output.put_line(V_Number);
END;
/


/******************************************************************************
                          PL/SQL Program Objects -  Execution Units
******************************************************************************/

A PL/SQL unit is any one of the following Objects.......

   => PL/SQL Default block
   => Procedure - Object
   => Function - Object
   => Package - Object
   => Package body - Object
   => Triggers - Object
   => Type - Object
   => Type body -Object

Saturday, January 6, 2018

Oracle Data integrator Interview Questions & Answers

Linux Tutorials Basic Commands for beginners LS, PASSWD, USERADD

Linux Basic Commands for beginners 2 DU, DF & FREE Disk utilization comm...

Linux Beginners Commands - Compression and Uncompression using gzip, gu...

Linux Beginners Tutorials 4 - Sort,Diff , CP, RM , MKDIR & RMDIR Commands

Linux Beginners Tutorials - PS and KILL commands with examples

Linux Beginners Tutorials 6 How to Configure Crontab in Linux

Linux Basic Commands for beginners 2 DU, DF & FREE Disk utilization comm...