Pages
▼
Thursday, April 19, 2018
Wednesday, April 18, 2018
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
Wednesday, February 28, 2018
Tuesday, February 20, 2018
Monday, February 19, 2018
Sunday, February 18, 2018
Saturday, February 17, 2018
Friday, February 16, 2018
Tuesday, February 13, 2018
Monday, February 12, 2018
Saturday, February 10, 2018
Friday, February 9, 2018
Thursday, February 8, 2018
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 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;
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 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 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 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