DDL Triggers Oracle

DML triggers explained in another post DML Triggers

DDL Triggers are triggers which associated with DDL (Data Definition Language) such as Dropping a table, Altering a column  etc. DDL triggers execute every time a DDL statement is executed. Generally DBA’s create DDL triggers for auditing and enforcement purposes.

General Syntax

create or replace trigger
DDLTrigger_name
AFTER DDL/LOGON/LOGOFF ON DATABASE/SCHEMA
BEGIN

— code here —

END;
/

Example :-

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on DATABASE
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

Note :- ddl_table is a user defined table which is in DBA schema.

Within the database any DDL issued, details will be saved into ddl_table table.

similarly DDL trigger can be created within schema also.

1) Connect to system

2)

create or replace trigger
ddl_trigger1
after DDL on SCHEMA
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
sysdate || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

DDL Triggers for LOGON/LOGOFF

LOGON

create or replace trigger
ddl_trigger3
after LOGON on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

LOGOFF

create or replace trigger
ddl_trigger3
after LOGOFF on database
begin
insert into ddl_table values (
ora_dict_obj_name || ‘-‘||
ora_login_user || ‘-‘||
to_char(sysdate,’dd-mon-yyyy hh:mi am’) || ‘-‘||
ora_sysevent || ‘-‘||
ora_dict_obj_type || ‘-‘||
ora_dict_obj_owner || ‘-‘||
ora_dict_obj_name
);
end;
/

Advertisements

High Water Mark Oracle

In simple term High water mark is a logical mark in the memory area to separate blocks having data and no data. As you know that the blocks represent the most granular form of data in memory.

When any table creates the high water mark will be in the ‘starting’ position. The high water mark keep moving forward as data get saved into the database. After inserting some records High Water Mark will be moved to forward.

See below given logical and pictorial representation of high water mark when a table data insert/update/delete/truncate

High Water Mark Oracle
High Water Mark Oracle

By default High Water Mark starts at the first block for new tables. When table gets data via insert/update more blocks being used High Water Mark moves forward. When data get deleted, High Water Mark remain same even though blocks doesn’t have any data.

Delete has no impact on high water mark and Truncate reset the High Water Mark. This is the one difference between delete and truncate ( Read more difference between Delete and Truncate )

When full table scan takes place, Oracle scans all blocks below High Water Mark even though it has no data.

Change password Oracle

How to change password in Oracle

Data is the back bone of any Organization, so we must protect it from un-authorize access. First step is protect the Oracle Account is enforce strong password which people cannot guess or brute force. If you think that Oracle account password is weak, you can use any of the below method to change it

1. Using SQL Plus

Step 1. Connect to SQL Plus
Step 2. Issue below command
SQL> password
Changing password for MANOJ
Old password:
New password:
Retype new password:
Password changed
SQL>

Please make sure that password policies are met. In general password must contain a capital letter,one symbol and one numeric.

2. Using TOAD

a) Using GUI

Step1. Connect to TOAD
Step2. Open Session –> Change Password

change password TOAD
change password TOAD

Step3. Enter old and new password and click “Execute”

change password TOAD window
change password TOAD window

b) Using Command
Step1. Connect to TOAD
Step2. Open SQL Editor

 

 

 

 

 

Step3. type keyword “password” and run. It will popup a password change window

3. Using SQL Developer

Step1. Connect to SQL eveloper
Step2. Open SQL Editor
Step3. type keyword “password” and run. It will popup a password change window

change password SQL Developer
change password SQL Developer

4. Get help from DBA

DBAs also can change/reset password for you.

5 different ways to test Oracle Ref Cursor results

In the previous posts we have covered Ref Cursors and Ref Cursor examples.
Now we will examine how Ref Cursor examples are tested.

For all the examples I have used the package we have created in Ref Cursor Example post

create or replace package pkg_refcur 
is
TYPE ref_strong_emptyp IS REF CURSOR RETURN emp%ROWTYPE;

procedure p_get_employees(pi_deptno   in  integer,
po_results  out ref_strong_emptyp);

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ);
………………………………………….
………………………………………….
End pkg_refcur;
/

create or replace package body pkg_refcur
is
procedure p_get_employees(pi_deptno  in  integer,
po_results  out ref_strong_emptyp)
is
Begin
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_employees;

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ)
is
Begin
Open po_results for
select e.empno,e.empname,d.deptno,d.deptname from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_dept_emp;

………………………………………….
………………………………………….
End pkg_refcur;

1. Using sqlplus 

— Declare a variable for refcursor
SQL> variable v_refcur refcursor;

— Execute the package
SQL> exec pkg_refcur.p_get_employees(2,:v_refcur);

PL/SQL procedure successfully completed.

— Print the refcursor variable
SQL> print v_refcur;

EMPNO EMPNAME                  SALARY     DEPTNO    MANAGER
—————————————————————–
16 Henry                      8500          2         11
17 Robert                     9500          2         16
18 Paul                       7700          2         17

Please note that v_refcur is a bind variable here.

2. Using %rowtype 

This will be useful only when the ref cursor is based on a single table

SQL> set serveroutput on;
SQL> declare
rec_emp  emp%rowtype;
v_refcur sys_refcursor;
Begin
pkg_refcur.p_get_employees(2,v_refcur);
loop
fetch v_refcur into rec_emp;
exit when v_refcur%notfound;
dbms_output.put_line(‘Empno   –>’ || rec_emp.empno);
dbms_output.put_line(‘Empname –>’ || rec_emp.empname);
dbms_output.put_line(‘Salary   –>’ || rec_emp.salary);
dbms_output.put_line(‘deptno –>’ || rec_emp.DEPTNO);
end loop;
End;
/
Empno   –>16
Empname –>Henry
Salary   –>8500
deptno –>2
Empno   –>17
Empname –>Robert
Salary   –>9500
deptno –>2
Empno   –>18
Empname –>Paul
Salary   –>7700
deptno –>2

PL/SQL procedure successfully completed.

The above logic you can implement inside your calling program or procedure for debug purpose

3. Using PL/SQL table of type and record 

Procedure p_get_dept_emp cannot be tested with using (2). So declare a record and type on it.

SQL> Declare
2  Type rec_emp_dept is record
3  (
4  empno    emp.empno%type,
5  empname  emp.empname%type,
6  deptno   dept.deptno%type,
7  deptname dept.deptname%type
8  );
9  rec      rec_emp_dept;
10  v_refcur sys_refcursor;
11  Begin
12  pkg_refcur.p_get_dept_emp(2,v_refcur);
13   loop
14       fetch v_refcur into rec;
15       exit when v_refcur%notfound;
16       dbms_output.put_line(‘Empno    –>’ || rec.empno);
17       dbms_output.put_line(‘Empname  –>’ || rec.empname);
18       dbms_output.put_line(‘Deptno   –>’ || rec.deptno);
19       dbms_output.put_line(‘Deptname –>’ || rec.deptname);
20   end loop;
21  End;
22  /
Empno    –>16
Empname  –>Henry
Deptno   –>2
Deptname –>RESEARCH
Empno    –>17
Empname  –>Robert
Deptno   –>2
Deptname –>RESEARCH
Empno    –>18
Empname  –>Paul
Deptno   –>2
Deptname –>RESEARCH

PL/SQL procedure successfully completed.

The above logic you can implement inside your calling program or procedure for debug purpose.
This is not ideal for a Ref Cursor returning many columns.

4. Using a wrapper function to call 

This is a pretty much easy method. But may not work if you have a procedure having DML and ref cursor return

SQL> create or replace function f_refcur return sys_refcursor
2  is
3  v_refcur sys_refcursor;
4  Begin
5    pkg_refcur.p_get_dept_emp(2,v_refcur);
6    return v_refcur;
7  End;
8  /

Function created.

SQL> select f_refcur from dual;

F_REFCUR
——————–
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

EMPNO EMPNAME                  DEPTNO DEPTNAME
———- ——————– ———- ——————–
16 Henry                         2 RESEARCH
17 Robert                        2 RESEARCH
18 Paul                          2 RESEARCH

5. Using Developer tools like TOAD, Sql Developer etc

Normally professional developers use any of the development tools like TOAD or SQL Developer etc.

a) Using TOAD

Step 1

Connect to TOAD

Open Schema Browser

Click “Package” tab

Locate the package and choose “Execute Procedure” from right click menu

Test Ref Cursor TOAD Step 1
Test Ref Cursor TOAD Step 1

Step 2

Click right most icon of the opened window as shown below

Test ref cursor from TOAD step 2
Test ref cursor from TOAD step 2

Step 3

Choose the last radio button “Load into grid from memory (strong and weak)”

Test ref cursor from TOAD step 3
Test ref cursor from TOAD step 3

Click OK

Click OK

Now you can see the ref cursor results in another window as shown below

Test ref cursor from TOAD Step 4
Test ref cursor from TOAD Step 4

In similar manner you can test any ref cursor within package/procedure/function

b) SQL Developer

SQL Developer is a Oracle Corporation product.

Step 1

Connect to SQL Developer ( I Have used version 3.0.4)

Click “Package” node and locate the package

Test red cursor from SQL Developer Step 1
Test red cursor from SQL Developer Step 1

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Step 2

Click Run button or Ctrl-F10

Test red cursor from SQL Developer Step 2
Test red cursor from SQL Developer Step 2

Click OK

Step 3

Test red cursor from SQL Developer Step 3
Test red cursor from SQL Developer Step 3

 

 

 

 

 

 

 

 

 

 

(In the message window you have to choose “Output variables”)

Conclusion :- Most of the ref cursors can be tested with either of the method. Also you can use the base query directly in SQL window or SQL plus to test the ref cursors.

Ref Cursors Examples

REF CURSOR is a data type in the Oracle. To read more about REF CURSOR

I have created a package to explain few different ref cursor examples.
It covers most of the typical and simple examples.

create or replace package pkg_refcur 
is
— Declaring a Strong Ref Cursor Type
TYPE ref_strong_emptyp IS REF CURSOR RETURN emp%ROWTYPE;

— Declaring a Weak Ref Cursor Type
TYPE ref_weak_typ IS REF CURSOR;

–Package Signatures
procedure p_get_employees(pi_deptno   in  integer,
po_results  out ref_strong_emptyp);

procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ);

procedure p_get_deptemp(pi_deptno  in  integer,
po_results out sys_refcursor);

procedure p_getdeptemp(pi_deptno  in  integer,
pi_type    in  varchar2,
po_results out sys_refcursor);

procedure p_dynamic(pi_tablename  in   integer,
po_results    out  sys_refcursor);

End pkg_refcur;
/

create or replace package body pkg_refcur
is
— 1)
— Using Strong ref cursor to return employees in a department
procedure p_get_employees(pi_deptno  in  integer,
po_results  out ref_strong_emptyp)
is
Begin
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_employees;

— 2)
— Using weak ref cursor to return employees and department details
procedure p_get_dept_emp(pi_deptno  in  integer,
po_results out ref_weak_typ)
is
Begin
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_dept_emp;

— 3)
— Using system ref cursor to return employees and department details
procedure p_get_deptemp(pi_deptno  in  integer,
po_results out sys_refcursor)
is
Begin
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
Exception
when others then
raise;
End p_get_deptemp;

— 4)
— Associating multiple query
procedure p_getdeptemp(pi_deptno  in  integer,
pi_type    in  varchar2,
po_results out sys_refcursor)
is
Begin
if upper(pi_type) = ‘DEPT’ then
Open po_results for
select * from emp e
where e.deptno = pi_deptno;
elsif upper(pi_type) = ‘EMP’ then
Open po_results for
select * from dept e
where e.deptno = pi_deptno;
elsif upper(pi_type) = ‘BOTH’ then
Open po_results for
select * from emp e,dept d
where e.deptno = d.deptno
and e.deptno = pi_deptno;
end if;
Exception
when others then
raise;
End p_getdeptemp;

— 5)
— Associating multiple query with dynamic query
procedure p_dynamic(pi_tablename  in  integer,
po_results    out sys_refcursor)
is
Begin
if upper(pi_tablename) in (‘EMP’,’DEPT’) then — for security
Open po_results for
‘select * from ‘ || pi_tablename ;
end if;
Exception
when others then
raise;
End p_dynamic;

End pkg_refcur;
/

Conclusion :- Ref Cursors are very ideal for interchanging data between two different application involving Oracle as a database, like Java and Oracle, Dot net and oracle, Oracle forms and Oracle etc.

Ref Cursors Oracle

The REF CURSOR is a data type in the Oracle. REF CURSOR also referred as Cursor Variables.Cursor variables are like pointers to result sets. Cursor can be attached to only one query while REF CURSOR can be used to associate multiple queries at run time. 

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
refcur1  empcurtyp;
Begin
Open refcur1 for select * from emp;
Open refcur1 for select * from dept;
End;

REF CURSOR can be categorized into three

1. Strong Ref Cursor
Ref Cursors which has a return type is classified as Strong Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR RETURN emp%ROWTYPE;
…..
End;

Here empcurtyp is a Strong Ref Cursor

2. Weak Ref Cursor
Ref Cursors which has no return type is classified as Weak Ref Cursor.

Example :-

Declare
TYPE empcurtyp IS REF CURSOR;
…..
End;

Here empcurtyp is a Weak Ref Cursor

3. System Ref Cursor
This is a system defined Ref Cursor. This also considered weak. System Ref Cursor need not declare explicitly.

Declare
empcurtyp SYS_REFCURSOR;
…..
End;

Advantages 
1. Ref Cursor it self is a data type and easy to declare
2. More flexible because it is not tied to a specific query
3. Easily pass as arguments from subroutine to subroutine.
4. Very handy in transferring data between multi-language application (ex:- Java and Oracle, Dot.net and Oracle, Oracle Forms and Oracle). Since it is a pointer to the result set any client and server program can use the pointer to access the data.
5. Cursor variables are bind variables ( Read more about BIND VARIABLES )

Dis-advantages
1. Ref Cursors are not efficient as StatiC Cursor
2. Need additional code to print Ref Cursor values

In general Ref Cursors are only be used when static cursor cannot do the work. 

Bind variables Oracle

In general bind variables are Oracle variables which holds data and resides in separate memory area for performance benefits and faster access.

Example 1

SQL> variable i number;
SQL> exec :i := 1;

PL/SQL procedure successfully completed.

SQL> print :i

I
———-
1

Here :i act as a bind variable

Example 2

declare
v_dname varchar2(30);
begin
execute immediate ‘select deptname from dept d where d.deptno = :x’
into v_dname
using 1;
dbms_output.put_line(v_dname);
end;

Here 😡 is a bind variable. 😡 got assigned value 1 at run-time.

Example 3

All cursor variables are bind variables

declare
cursor c1(cv_dno integer)
is
select count(*) from emp e where e.deptno = cv_dno;
n_cnt integer;
begin
open c1(1);
fetch c1 into n_cnt;
close c1;
dbms_output.put_line(‘n_cnt–>’|| n_cnt);
end;

Here cv_dno is a bind variable.

Example 4

All PL/SQL variables are bind variables.

CREATE OR REPLACE PROCEDURE
P_INSERT_DEPT(p_deptno integer,p_deptname varchar2,p_location varchar2)
AS
BEGIN
insert into dept values(p_deptno,p_deptname,p_location);
commit;
END P_INSERT_DEPT;

Here p_deptno,p_deptname and p_location are bind variables. So it is a nice idea to wrap every thing using PL/SQL variables.

Misconception about Bind Variables

1. Any variable used with : (colon) is a bind variable – not true always

2. Bind variables only referenced from SQL Plus – not true

3. Static SQL in PL/SQL block must be re-written with EXECUTE IMMEDIATE to utilize bind variables ( same as example 2) – Not at all true

Conclusion :- It is true that bind variables improves the performance, doesn’t mean that you always need to use it. But keep it in mind that for large queries bind variables will save a lot of time.