You can "rollback" a table in a past time in Oracle.
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS
SQL> grant select on v_$database to hr;
Grant succeeded
SQL> con hr/hr;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as hr
SQL> create table flassh(x number);
Table created
SQL> alter table flassh enable row movement;
Table altered
SQL> insert into flassh values(1);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT db.CURRENT_SCN FROM v$database db;
CURRENT_SCN
-----------
7869034
SQL> insert into flassh values(1111);
1 row inserted
SQL> commit;
Commit complete
SQL> flashback table flassh to scn 7869034;
Done
SQL> SELECT * FROM flassh;
X
----------
1
SQL> insert into flassh values(1111);
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM flassh;
X
----------
1
1111
SQL> update flassh set x = 5 where x = 1;
1 row updated
SQL> commit;
Commit complete
SQL> SELECT * FROM flassh;
X
----------
5
1111
SQL> flashback table flassh to scn 7869034;
Done
SQL> SELECT * FROM flassh;
X
----------
1
SQL>
27 October 2006
22 October 2006
Bulk Processing Performance Analysis On Oracle
Analyzing performance statistics is very common procedure for oracle programmers. You can always do the same work with various ways. At this point you have to choose the most suitable one for your business. Especally this suitability stands on performance. Your boss wants to you for making prosceses faster ans faster. So, you have to analyze some performance statistics when doing some jobs.
If you are a good oracle programmer, you will always make your code "faster". The boss will pay more for this velocity. If you are always do your work "faster" you will never loss your job :)
Bulk processing comes in action when you are doing insert or "select" heavily. For instance if you are inserting 10000 row in pl/sql, there will be a context switching between two engines, PL/SQL and SQL engines. A context switch has a time payload. For this reason, to get more performant programs, you have to consider this.
For bulk processing you have to work with collections.
Below i demonstrated a simple example to show performance effects of bulk and nonbulk process. The demonstration was repeated with 50000, 100000 and 250000 rows.
The result is
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> create type number_list is table of number;
2 /
Type created
Executed in 0,251 seconds
SQL> create table numbers(i number );
Table created
Executed in 0,091 seconds
SQL> BEGIN
2 FOR i IN 1 .. 50000 LOOP
3 INSERT INTO numbers values(i);
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed
Executed in 4,026 seconds
SQL> commit;
Commit complete
Executed in 0,24 seconds
SQL> DECLARE
2 total NUMBER := 0;
3 BEGIN
4 FOR rec IN (SELECT i FROM numbers) LOOP
5 total := total + rec.i;
6 END LOOP;
7 dbms_output.put_line('Total is ' || total);
8 END;
9 /
Total is 1250025000
PL/SQL procedure successfully completed
Executed in 0,17 seconds
SQL> drop table numbers;
Table dropped
Executed in 0,04 seconds
SQL> create table numbers(i number);
Table created
Executed in 0,05 seconds
SQL> DECLARE
2 number_arr number_list;
3 BEGIN
4 number_arr := number_list();
5 number_arr.EXTEND(50000);
6 FOR i IN 1 .. 50000 LOOP
7 number_arr(i) := i;
8 END LOOP;
9 FORALL i IN number_arr.FIRST .. number_arr.LAST
10 INSERT INTO numbers VALUES (number_arr(i));
11
12 END;
13 /
PL/SQL procedure successfully completed
Executed in 0,321 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> DECLARE
2 total NUMBER := 0;
3 number_arr number_list;
4 BEGIN
5 number_arr := number_list();
6 SELECT i BULK COLLECT INTO number_arr FROM numbers;
7 FOR i IN number_arr.FIRST .. number_arr.LAST LOOP
8 total := total + number_arr(i);
9 END LOOP;
10 dbms_output.put_line('Total is ' || total);
11 END;
12 /
Total is 1250025000
PL/SQL procedure successfully completed
Executed in 0,11 seconds
If you are a good oracle programmer, you will always make your code "faster". The boss will pay more for this velocity. If you are always do your work "faster" you will never loss your job :)
Bulk processing comes in action when you are doing insert or "select" heavily. For instance if you are inserting 10000 row in pl/sql, there will be a context switching between two engines, PL/SQL and SQL engines. A context switch has a time payload. For this reason, to get more performant programs, you have to consider this.
For bulk processing you have to work with collections.
Below i demonstrated a simple example to show performance effects of bulk and nonbulk process. The demonstration was repeated with 50000, 100000 and 250000 rows.
The result is
| | Bulk Insert | Insert | % | Bulk Select | Select | % |
| 50000 | 0,321 | 4,026 | 1154,21 | 0,11 | 0,17 | 54,55 |
| 100000 | 0,821 | 7,521 | 816,08 | 0,191 | 0,27 | 41,36 |
| 250000 | 0,941 | 21,891 | 2226,35 | 0,431 | 0,651 | 51,04 |
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> create type number_list is table of number;
2 /
Type created
Executed in 0,251 seconds
SQL> create table numbers(i number );
Table created
Executed in 0,091 seconds
SQL> BEGIN
2 FOR i IN 1 .. 50000 LOOP
3 INSERT INTO numbers values(i);
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed
Executed in 4,026 seconds
SQL> commit;
Commit complete
Executed in 0,24 seconds
SQL> DECLARE
2 total NUMBER := 0;
3 BEGIN
4 FOR rec IN (SELECT i FROM numbers) LOOP
5 total := total + rec.i;
6 END LOOP;
7 dbms_output.put_line('Total is ' || total);
8 END;
9 /
Total is 1250025000
PL/SQL procedure successfully completed
Executed in 0,17 seconds
SQL> drop table numbers;
Table dropped
Executed in 0,04 seconds
SQL> create table numbers(i number);
Table created
Executed in 0,05 seconds
SQL> DECLARE
2 number_arr number_list;
3 BEGIN
4 number_arr := number_list();
5 number_arr.EXTEND(50000);
6 FOR i IN 1 .. 50000 LOOP
7 number_arr(i) := i;
8 END LOOP;
9 FORALL i IN number_arr.FIRST .. number_arr.LAST
10 INSERT INTO numbers VALUES (number_arr(i));
11
12 END;
13 /
PL/SQL procedure successfully completed
Executed in 0,321 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
SQL> DECLARE
2 total NUMBER := 0;
3 number_arr number_list;
4 BEGIN
5 number_arr := number_list();
6 SELECT i BULK COLLECT INTO number_arr FROM numbers;
7 FOR i IN number_arr.FIRST .. number_arr.LAST LOOP
8 total := total + number_arr(i);
9 END LOOP;
10 dbms_output.put_line('Total is ' || total);
11 END;
12 /
Total is 1250025000
PL/SQL procedure successfully completed
Executed in 0,11 seconds
20 October 2006
Paging Techniques In Oracle
Oracle, mysql gibi bir limit cümlesi sunmadığı için, bunun bazı yöntemler ile yapılması gerekir. En temelde paging işleminde tutarsızlık olacağı unutulmamalıdır. Sorguyu ilk çektiğiniz anda commit olmayan bir kayıt, ikinci sayfada commit edilirse, bu yeni kaydı göremeyebilirsiniz. Paging için birden fazla yöntem olabilir. Ben bunlardan rownum ve row_number ile ilgili olanlarını belirtmeye çalışacağım. Daha fazla sözü uzatmadan örneklere geçmek istiyorum:
İlk Yöntem row_number ile:
SQL> create table pagination_test( x varchar2(128) );
Table created
Executed in 0,09 seconds
SQL> BEGIN
2 FOR i IN 1 .. 1000000 LOOP
3 INSERT INTO pagination_test VALUES ( 'aassddff' || i);
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed
Executed in 64,693 seconds
SQL> SELECT inner_view.x
2 FROM (SELECT x, row_number() over( ORDER BY x ASC) row_num
3 FROM pagination_test) inner_view
4 WHERE inner_view.row_num BETWEEN 400500 AND 400510;
X
--------------------------------------------------------------------------------
aassddff460446
aassddff460447
aassddff460448
aassddff460449
aassddff46045
aassddff460450
aassddff460451
aassddff460452
aassddff460453
aassddff460454
aassddff460455
11 rows selected
Executed in 14,591 seconds
SQL>
İkinci Yöntem rownum ile:
Yukarıdaki sonuçlardan görüleceği üzere rownum ile içiçe 3 select ile yapılan sorgu daha hızlı çalışmıştır. Detaylar için her bir sorgunun execution plan ine bakılabilir
İlk Yöntem row_number ile:
SQL> create table pagination_test( x varchar2(128) );
Table created
Executed in 0,09 seconds
SQL> BEGIN
2 FOR i IN 1 .. 1000000 LOOP
3 INSERT INTO pagination_test VALUES ( 'aassddff' || i);
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed
Executed in 64,693 seconds
SQL> SELECT inner_view.x
2 FROM (SELECT x, row_number() over( ORDER BY x ASC) row_num
3 FROM pagination_test) inner_view
4 WHERE inner_view.row_num BETWEEN 400500 AND 400510;
X
--------------------------------------------------------------------------------
aassddff460446
aassddff460447
aassddff460448
aassddff460449
aassddff46045
aassddff460450
aassddff460451
aassddff460452
aassddff460453
aassddff460454
aassddff460455
11 rows selected
Executed in 14,591 seconds
SQL>
İkinci Yöntem rownum ile:
SQL> drop table pagination_test;
Table dropped
Executed in 1,422 seconds
SQL> create table pagination_test( x varchar2(128) );
Table created
Executed in 0,02 seconds
SQL> BEGIN
2 FOR i IN 1 .. 1000000 LOOP
3 INSERT INTO pagination_test VALUES ( 'aassddff' || i);
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed
Executed in 74,487 seconds
SQL> SELECT outer_view.x
2 FROM (SELECT inner_view.x, rownum inner_rownum
3 FROM (SELECT x FROM pagination_test ORDER BY x ASC) inner_view
4 WHERE rownum <= 400510) outer_view
5 WHERE outer_view.inner_rownum >= 400500;
X
--------------------------------------------------------------------------------
aassddff460446
aassddff460447
aassddff460448
aassddff460449
aassddff46045
aassddff460450
aassddff460451
aassddff460452
aassddff460453
aassddff460454
aassddff460455
11 rows selected
Executed in 7,631 seconds
SQL>
Table dropped
Executed in 1,422 seconds
SQL> create table pagination_test( x varchar2(128) );
Table created
Executed in 0,02 seconds
SQL> BEGIN
2 FOR i IN 1 .. 1000000 LOOP
3 INSERT INTO pagination_test VALUES ( 'aassddff' || i);
4 END LOOP;
5 COMMIT;
6 END;
7 /
PL/SQL procedure successfully completed
Executed in 74,487 seconds
SQL> SELECT outer_view.x
2 FROM (SELECT inner_view.x, rownum inner_rownum
3 FROM (SELECT x FROM pagination_test ORDER BY x ASC) inner_view
4 WHERE rownum <= 400510) outer_view
5 WHERE outer_view.inner_rownum >= 400500;
X
--------------------------------------------------------------------------------
aassddff460446
aassddff460447
aassddff460448
aassddff460449
aassddff46045
aassddff460450
aassddff460451
aassddff460452
aassddff460453
aassddff460454
aassddff460455
11 rows selected
Executed in 7,631 seconds
SQL>
Yukarıdaki sonuçlardan görüleceği üzere rownum ile içiçe 3 select ile yapılan sorgu daha hızlı çalışmıştır. Detaylar için her bir sorgunun execution plan ine bakılabilir
19 October 2006
LEFT OUTER JOIN and NULL Values
I have mentioned some join operations on my previous post. LEFT OUTER JOIN takes all records of "left" table; so some of records of "right" table can stay NULL. Suppose you need to make some calculations on that NULL fields. Equality operations can not work on NULL values. NULL values are special values. If you add a condition on that field like = or <= it will skip all NULL values which are taken by OUTER JOIN. For clarity follow the example:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table eq_h_test;
Table dropped
SQL> create table eq_h_test( i number, z number );
Table created
SQL> INSERT INTO eq_h_test VALUES(1, 100 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(1, 103 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(1, 109 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(2, 10 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(2, 1020 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(3, 1700 );
1 row inserted
SQL> drop table eq_test;
Table dropped
SQL> create table eq_test( i number, k varchar2(3) );
Table created
SQL> INSERT INTO eq_test VALUES( 1, 'aaa' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 2, 'fff' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 3, 'rrr' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 4, 'ttt' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 5, 'qqq' );
1 row inserted
SQL> commit;
Commit complete
Let's selecting and operating. Aggreagating functions return NULL values even if they have no record.(For more please read Aggregation Functions and NO_DATA_FOUND Exception In Oracle) I use the join field in where clause for equality. I can not get NULL values and can not get power of OUTER JOIN. So i use an extra clause that contains special equality for NULL values.
SQL> SELECT * FROM eq_test t LEFT OUTER JOIN eq_h_test h ON t.i = h.i;
I K I Z
---------- --- ---------- ----------
1 aaa 1 100
1 aaa 1 103
1 aaa 1 109
2 fff 2 10
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
8 rows selected
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i);
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE decode(h.z,
5 (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i),
6 1,
7 0) = 1;
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i)
5 OR h.z IS NULL;
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
SQL> ---
You must keep in mind NULL values are special. You must handle them. An alternative way, you can use DECODE for equality of NULLs. I wrote a simple example to show this. You can also get more information in my On NULL Values post.
SQL>
SQL> SELECT decode(NULL, NULL, 'null is null', 'null is not null') FROM dual;
DECODE(NULL,NULL,'NULLISNULL',
------------------------------
null is null
SQL> --
SQL> SELECT dummy FROM dual WHERE 1 = 1;
DUMMY
-----
X
SQL> SELECT dummy FROM dual WHERE NULL = NULL;
DUMMY
-----
SQL> SELECT dummy FROM dual WHERE NULL IS NULL;
DUMMY
-----
X
SQL>
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table eq_h_test;
Table dropped
SQL> create table eq_h_test( i number, z number );
Table created
SQL> INSERT INTO eq_h_test VALUES(1, 100 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(1, 103 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(1, 109 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(2, 10 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(2, 1020 );
1 row inserted
SQL> INSERT INTO eq_h_test VALUES(3, 1700 );
1 row inserted
SQL> drop table eq_test;
Table dropped
SQL> create table eq_test( i number, k varchar2(3) );
Table created
SQL> INSERT INTO eq_test VALUES( 1, 'aaa' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 2, 'fff' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 3, 'rrr' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 4, 'ttt' );
1 row inserted
SQL> INSERT INTO eq_test VALUES( 5, 'qqq' );
1 row inserted
SQL> commit;
Commit complete
Let's selecting and operating. Aggreagating functions return NULL values even if they have no record.(For more please read Aggregation Functions and NO_DATA_FOUND Exception In Oracle) I use the join field in where clause for equality. I can not get NULL values and can not get power of OUTER JOIN. So i use an extra clause that contains special equality for NULL values.
SQL> SELECT * FROM eq_test t LEFT OUTER JOIN eq_h_test h ON t.i = h.i;
I K I Z
---------- --- ---------- ----------
1 aaa 1 100
1 aaa 1 103
1 aaa 1 109
2 fff 2 10
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
8 rows selected
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i);
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE decode(h.z,
5 (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i),
6 1,
7 0) = 1;
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
SQL> SELECT *
2 FROM eq_test t
3 LEFT OUTER JOIN eq_h_test h ON t.i = h.i
4 WHERE h.z = (SELECT MAX(h2.z) FROM eq_h_test h2 WHERE h2.i = h.i)
5 OR h.z IS NULL;
I K I Z
---------- --- ---------- ----------
1 aaa 1 109
2 fff 2 1020
3 rrr 3 1700
5 qqq
4 ttt
SQL> ---
You must keep in mind NULL values are special. You must handle them. An alternative way, you can use DECODE for equality of NULLs. I wrote a simple example to show this. You can also get more information in my On NULL Values post.
SQL>
SQL> SELECT decode(NULL, NULL, 'null is null', 'null is not null') FROM dual;
DECODE(NULL,NULL,'NULLISNULL',
------------------------------
null is null
SQL> --
SQL> SELECT dummy FROM dual WHERE 1 = 1;
DUMMY
-----
X
SQL> SELECT dummy FROM dual WHERE NULL = NULL;
DUMMY
-----
SQL> SELECT dummy FROM dual WHERE NULL IS NULL;
DUMMY
-----
X
SQL>
How To Join Tables In Oracle With LEFT RIGHT OUTER INNER FULL Keywords
In relational database systems, information is stored in multiple tables. You joins multiple tables to get information. Standart SQL, has defined join to combine multiple set based on desired attributes.
I simply demonstrate a complete example to show how to joins take place in Oracle.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table student;
Table dropped
SQL> drop table project;
Table dropped
SQL> drop table course;
Table dropped
SQL> create table student(student_id number, taken_project_id number, given_course_id number, last_name varchar2(32) );
Table created
SQL> create table project(project_id number, project_description varchar2(32));
Table created
SQL> create table course(course_id number, course_description varchar2(32));
Table created
SQL> INSERT INTO course values(1, 'Database Management Sys');
1 row inserted
SQL> INSERT INTO course values(2, 'Logic');
1 row inserted
SQL> INSERT INTO course values(3, 'Circuit Theory');
1 row inserted
SQL> INSERT INTO project values(100, 'Reservation System');
1 row inserted
SQL> INSERT INTO project values(101, 'Online Booking');
1 row inserted
SQL> INSERT INTO project values(102, 'Face Recog. with brute force');
1 row inserted
SQL> INSERT INTO project values(103, 'On Fly Form Generator');
1 row inserted
SQL> INSERT INTO student values(60001, null, null, 'Alonso');
1 row inserted
SQL> INSERT INTO student values(60002, 102, 1, 'Richar');
1 row inserted
SQL> INSERT INTO student values(60001, 100, 2, 'Tekbir');
1 row inserted
SQL> INSERT INTO student values(60001, null, 1, 'Sergio');
1 row inserted
SQL> INSERT INTO student values(60001, 101, null, 'Alexov');
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM student;
STUDENT_ID TAKEN_PROJECT_ID GIVEN_COURSE_ID LAST_NAME
---------- ---------------- --------------- --------------------------------
60001 Alonso
60002 102 1 Richar
60001 100 2 Tekbir
60001 1 Sergio
60001 101 Alexov
SQL> SELECT * FROM project;
PROJECT_ID PROJECT_DESCRIPTION
---------- --------------------------------
100 Reservation System
101 Online Booking
102 Face Recog. with brute force
103 On Fly Form Generator
SQL> SELECT * FROM course;
COURSE_ID COURSE_DESCRIPTION
---------- --------------------------------
1 Database Management Sys
2 Logic
3 Circuit Theory
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id(+);
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st LEFT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> SELECT st.last_name, pr.project_description FROM student st RIGHT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> --SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id(+);--ERROR
SQL> SELECT st.last_name, pr.project_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
On Fly Form Generator
6 rows selected
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st, project pr, course co WHERE st.taken_project_id = pr.project_id AND st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id INNER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Tekbir Reservation System Logic
Richar Face Recog. with brute force Database Management Sys
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id FULL OUTER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Sergio Database Management Sys
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
On Fly Form Generator
Alonso
Alexov Online Booking
Circuit Theory
7 rows selected
SQL>
I simply demonstrate a complete example to show how to joins take place in Oracle.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table student;
Table dropped
SQL> drop table project;
Table dropped
SQL> drop table course;
Table dropped
SQL> create table student(student_id number, taken_project_id number, given_course_id number, last_name varchar2(32) );
Table created
SQL> create table project(project_id number, project_description varchar2(32));
Table created
SQL> create table course(course_id number, course_description varchar2(32));
Table created
SQL> INSERT INTO course values(1, 'Database Management Sys');
1 row inserted
SQL> INSERT INTO course values(2, 'Logic');
1 row inserted
SQL> INSERT INTO course values(3, 'Circuit Theory');
1 row inserted
SQL> INSERT INTO project values(100, 'Reservation System');
1 row inserted
SQL> INSERT INTO project values(101, 'Online Booking');
1 row inserted
SQL> INSERT INTO project values(102, 'Face Recog. with brute force');
1 row inserted
SQL> INSERT INTO project values(103, 'On Fly Form Generator');
1 row inserted
SQL> INSERT INTO student values(60001, null, null, 'Alonso');
1 row inserted
SQL> INSERT INTO student values(60002, 102, 1, 'Richar');
1 row inserted
SQL> INSERT INTO student values(60001, 100, 2, 'Tekbir');
1 row inserted
SQL> INSERT INTO student values(60001, null, 1, 'Sergio');
1 row inserted
SQL> INSERT INTO student values(60001, 101, null, 'Alexov');
1 row inserted
SQL> commit;
Commit complete
SQL> SELECT * FROM student;
STUDENT_ID TAKEN_PROJECT_ID GIVEN_COURSE_ID LAST_NAME
---------- ---------------- --------------- --------------------------------
60001 Alonso
60002 102 1 Richar
60001 100 2 Tekbir
60001 1 Sergio
60001 101 Alexov
SQL> SELECT * FROM project;
PROJECT_ID PROJECT_DESCRIPTION
---------- --------------------------------
100 Reservation System
101 Online Booking
102 Face Recog. with brute force
103 On Fly Form Generator
SQL> SELECT * FROM course;
COURSE_ID COURSE_DESCRIPTION
---------- --------------------------------
1 Database Management Sys
2 Logic
3 Circuit Theory
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id = pr.project_id(+);
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st LEFT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
SQL> SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> SELECT st.last_name, pr.project_description FROM student st RIGHT OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Richar Face Recog. with brute force
Tekbir Reservation System
Alexov Online Booking
On Fly Form Generator
SQL> --SELECT st.last_name, pr.project_description FROM student st, project pr WHERE st.taken_project_id(+) = pr.project_id(+);--ERROR
SQL> SELECT st.last_name, pr.project_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id;
LAST_NAME PROJECT_DESCRIPTION
-------------------------------- --------------------------------
Tekbir Reservation System
Alexov Online Booking
Richar Face Recog. with brute force
Sergio
Alonso
On Fly Form Generator
6 rows selected
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st, project pr, course co WHERE st.taken_project_id = pr.project_id AND st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st INNER JOIN project pr ON st.taken_project_id = pr.project_id INNER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Tekbir Reservation System Logic
Richar Face Recog. with brute force Database Management Sys
SQL> SELECT st.last_name, pr.project_description, co.course_description FROM student st FULL OUTER JOIN project pr ON st.taken_project_id = pr.project_id FULL OUTER JOIN course co ON st.given_course_id = co.course_id;
LAST_NAME PROJECT_DESCRIPTION COURSE_DESCRIPTION
-------------------------------- -------------------------------- --------------------------------
Sergio Database Management Sys
Richar Face Recog. with brute force Database Management Sys
Tekbir Reservation System Logic
On Fly Form Generator
Alonso
Alexov Online Booking
Circuit Theory
7 rows selected
SQL>
18 October 2006
Using Sequences For Data Integrity
You can use as some keys in your tables. Especially these keys are unique and identifies the row of a table. Sometimes you take care of these keys. Incrementing the key values 1 by 1 is a useful practise. But how the incrementation must be?
There are some ways. One is creating a trigger and taking max value + 1 of table for new key value. This is not a good solution. Because of invalidates your data integrity. For multi user environments same values can be inserted to table. If you create a unique constraint on key column you can somehow handle duplicate values. In performance situation, it is not preferable.
To demonstrate this, i opened two sessions. i wrote a before trigger and insert 10000 records to table. As you see it will create duplicates.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table t;
Table dropped
Executed in 0,121 seconds
SQL> create table t(x number, y number);
Table created
Executed in 0,02 seconds
SQL> INSERT INTO t(x, y) values(1,1);
1 row inserted
Executed in 0 seconds
SQL> CREATE OR REPLACE TRIGGER trg_t
2 BEFORE INSERT ON t
3 FOR EACH ROW
4 DECLARE
5 i t.x%TYPE;
6 BEGIN
7 SELECT MAX(x) INTO i FROM t;
8 :NEW.x := i + 1;
9 END trg_t;
10 /
Trigger created
Executed in 0,02 seconds
SQL> INSERT INTO t(y) values(1);
1 row inserted
Executed in 0,01 seconds
SQL> SELECT * FROM t;
X Y
---------- ----------
1 1
2 1
Executed in 0,02 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
Now i opened two sessions concurently and runs the same query both sides.
SQL>
SQL> BEGIN
2 FOR i IN 3 .. 10000 LOOP
3 INSERT INTO t( y) values( i);
4 COMMIT;
5 END LOOP;
6 END
7 ;
8 /
PL/SQL procedure successfully completed
Executed in 60,046 seconds
--This is other session
PL/SQL procedure successfully completed
Executed in 68,199 seconds
Now checking the duplicates:
SQL> SELECT count(count(*)) FROM t group by x having count(*) > 1;
COUNT(COUNT(*))
---------------
470
Executed in 0,15 seconds
As you see there are some duplicates. You can avoid by defining unique constraint. But again this will make performance worse.
On the other side you can create a sequence and every insert you can take nextval from sequence. It consists your data integrity gains performance.
SQL> drop table t;
Table dropped
Executed in 0,07 seconds
SQL> create table t(x number, y number);
Table created
Executed in 0,01 seconds
SQL> drop sequence seq_t;
Sequence dropped
Executed in 0,33 seconds
SQL> create sequence seq_t start with 1 increment by 1;
Sequence created
Executed in 0,01 seconds
Now i opened two sessions concurently and runs the same query both sides.
SQL>
SQL> BEGIN
2 FOR i IN 3 .. 10000 LOOP
3 INSERT INTO t(x, y) values(seq_t.nextval, i);
4 COMMIT;
5 END LOOP;
6 END;
7
8 /
PL/SQL procedure successfully completed
Executed in 4,627 seconds
PL/SQL procedure successfully completed
Executed in 5,077 seconds
Now checking the duplicates:
SQL> SELECT count(count(*)) FROM t group by x having count(*) > 1;
COUNT(COUNT(*))
---------------
0
Executed in 0,15 seconds
When using key values it is better to use sequence. It is atomic.
There are some ways. One is creating a trigger and taking max value + 1 of table for new key value. This is not a good solution. Because of invalidates your data integrity. For multi user environments same values can be inserted to table. If you create a unique constraint on key column you can somehow handle duplicate values. In performance situation, it is not preferable.
To demonstrate this, i opened two sessions. i wrote a before trigger and insert 10000 records to table. As you see it will create duplicates.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table t;
Table dropped
Executed in 0,121 seconds
SQL> create table t(x number, y number);
Table created
Executed in 0,02 seconds
SQL> INSERT INTO t(x, y) values(1,1);
1 row inserted
Executed in 0 seconds
SQL> CREATE OR REPLACE TRIGGER trg_t
2 BEFORE INSERT ON t
3 FOR EACH ROW
4 DECLARE
5 i t.x%TYPE;
6 BEGIN
7 SELECT MAX(x) INTO i FROM t;
8 :NEW.x := i + 1;
9 END trg_t;
10 /
Trigger created
Executed in 0,02 seconds
SQL> INSERT INTO t(y) values(1);
1 row inserted
Executed in 0,01 seconds
SQL> SELECT * FROM t;
X Y
---------- ----------
1 1
2 1
Executed in 0,02 seconds
SQL> commit;
Commit complete
Executed in 0 seconds
Now i opened two sessions concurently and runs the same query both sides.
SQL>
SQL> BEGIN
2 FOR i IN 3 .. 10000 LOOP
3 INSERT INTO t( y) values( i);
4 COMMIT;
5 END LOOP;
6 END
7 ;
8 /
PL/SQL procedure successfully completed
Executed in 60,046 seconds
--This is other session
PL/SQL procedure successfully completed
Executed in 68,199 seconds
Now checking the duplicates:
SQL> SELECT count(count(*)) FROM t group by x having count(*) > 1;
COUNT(COUNT(*))
---------------
470
Executed in 0,15 seconds
As you see there are some duplicates. You can avoid by defining unique constraint. But again this will make performance worse.
On the other side you can create a sequence and every insert you can take nextval from sequence. It consists your data integrity gains performance.
SQL> drop table t;
Table dropped
Executed in 0,07 seconds
SQL> create table t(x number, y number);
Table created
Executed in 0,01 seconds
SQL> drop sequence seq_t;
Sequence dropped
Executed in 0,33 seconds
SQL> create sequence seq_t start with 1 increment by 1;
Sequence created
Executed in 0,01 seconds
Now i opened two sessions concurently and runs the same query both sides.
SQL>
SQL> BEGIN
2 FOR i IN 3 .. 10000 LOOP
3 INSERT INTO t(x, y) values(seq_t.nextval, i);
4 COMMIT;
5 END LOOP;
6 END;
7
8 /
PL/SQL procedure successfully completed
Executed in 4,627 seconds
PL/SQL procedure successfully completed
Executed in 5,077 seconds
Now checking the duplicates:
SQL> SELECT count(count(*)) FROM t group by x having count(*) > 1;
COUNT(COUNT(*))
---------------
0
Executed in 0,15 seconds
When using key values it is better to use sequence. It is atomic.
16 October 2006
Passing Object Parameters To Functions Remotely In Oracle
Sometimes you need to invoke some functions in remote database via database link. Passing pirimitive types like number or varchar2 are not so dificult. But ow about passing user defined data types? When you encourage this, you may get consider some points.
Firstly, Oracle has some restrictions on object types when passing them remotely. You are not allowed to pass objects remote database's functions via db links. Although you create the same object both two databases, you can not pass parameter.
SQL>con db03/db03
SQL> DROP TYPE test_type;
Type dropped
SQL> CREATE OR REPLACE TYPE test_type IS OBJECT
2 (
3 i NUMBER,
4 z VARCHAR2(12)
5 )
6 ;
7 /
Type created
SQL> CREATE OR REPLACE PROCEDURE set_test_type( pit_test_type in test_type, res out number)
2 IS
3 BEGIN
4 res := pit_test_type.i;
5 END;
6 /
Procedure created
SQL>
SQL> con db01/db01
SQL> DROP TYPE test_type;
Type dropped
SQL> CREATE OR REPLACE TYPE test_type IS OBJECT
2 (
3 i NUMBER,
4 z VARCHAR2(12)
5 )
6 ;
7 /
Type created
SQL> drop public database link test_link;
Database link dropped
SQL> create public database link test_link
2 connect to sysadm identified by sysadm
3 using 'db03';
Database link created
SQL> DECLARE
2 i NUMBER;
3 t test_type := test_type(101, 'hello');
4
5 BEGIN
6 set_test_type@test_link(t, i);
7 DBMS_OUTPUT.PUT_LINE('i is ' || i);
8 END;
9 /
DECLARE
i NUMBER;
t test_type := test_type(101, 'hello');
BEGIN
set_test_type@test_link(t, i);
DBMS_OUTPUT.PUT_LINE('i is ' || i);
END;
ORA-06550: line 6, column 3:
PLS-00306: wrong number or types of arguments in call to 'SET_TEST_TYPE'
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
You can create your type only locally. It is also not a solution. Oracle does not know your objects:
SQL>
SQL>con db01/db01
SQL> CREATE OR REPLACE PACKAGE set_test_type_pkg IS
2 TYPE test_type IS RECORD(
3 i NUMBER,
4 z VARCHAR2(12));
5 PROCEDURE set_test_type(pit_test_type IN test_type, res OUT NUMBER);
6 END set_test_type_pkg;
7 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY set_test_type_pkg IS
2
3 PROCEDURE set_test_type(pit_test_type IN test_type, res OUT NUMBER) IS
4 BEGIN
5 res := pit_test_type.i;
6 END;
7 END set_test_type_pkg;
8 /
Package body created
SQL> show err;
No errors for PACKAGE BODY SYSADM.SET_TEST_TYPE_PKG
SQL>con db03/db03
SQL> DECLARE
2 i NUMBER;
3 TYPE test_type IS RECORD(
4 i NUMBER,
5 z VARCHAR2(12));
6 tt_test_type test_type;
7 BEGIN
8 tt_test_type.i := 101;
9 tt_test_type.z := 'hiii';
10 set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
11 DBMS_OUTPUT.PUT_LINE('i is ' || i);
12 END;
13 /
DECLARE
i NUMBER;
TYPE test_type IS RECORD(
i NUMBER,
z VARCHAR2(12));
tt_test_type test_type;
BEGIN
tt_test_type.i := 101;
tt_test_type.z := 'hiii';
set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
DBMS_OUTPUT.PUT_LINE('i is ' || i);
END;
ORA-06550: line 10, column 3:
PLS-00306: wrong number or types of arguments in call to 'SET_TEST_TYPE'
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored
SQL>
You can overcome this situation by referencing a local type both the databases. Important thing is, you have to reference the same type in one database.
SQL>con db01/db01
SQL> DECLARE
2 i NUMBER;
3
4 tt_test_type set_test_type_pkg.test_type@test_link;
5 BEGIN
6 tt_test_type.i := 101;
7 tt_test_type.z := 'hiii';
8 set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
9 DBMS_OUTPUT.PUT_LINE('i is ' || i);
10 END;
11 /
i is 101
PL/SQL procedure successfully completed
SQL>
Firstly, Oracle has some restrictions on object types when passing them remotely. You are not allowed to pass objects remote database's functions via db links. Although you create the same object both two databases, you can not pass parameter.
SQL>con db03/db03
SQL> DROP TYPE test_type;
Type dropped
SQL> CREATE OR REPLACE TYPE test_type IS OBJECT
2 (
3 i NUMBER,
4 z VARCHAR2(12)
5 )
6 ;
7 /
Type created
SQL> CREATE OR REPLACE PROCEDURE set_test_type( pit_test_type in test_type, res out number)
2 IS
3 BEGIN
4 res := pit_test_type.i;
5 END;
6 /
Procedure created
SQL>
SQL> con db01/db01
SQL> DROP TYPE test_type;
Type dropped
SQL> CREATE OR REPLACE TYPE test_type IS OBJECT
2 (
3 i NUMBER,
4 z VARCHAR2(12)
5 )
6 ;
7 /
Type created
SQL> drop public database link test_link;
Database link dropped
SQL> create public database link test_link
2 connect to sysadm identified by sysadm
3 using 'db03';
Database link created
SQL> DECLARE
2 i NUMBER;
3 t test_type := test_type(101, 'hello');
4
5 BEGIN
6 set_test_type@test_link(t, i);
7 DBMS_OUTPUT.PUT_LINE('i is ' || i);
8 END;
9 /
DECLARE
i NUMBER;
t test_type := test_type(101, 'hello');
BEGIN
set_test_type@test_link(t, i);
DBMS_OUTPUT.PUT_LINE('i is ' || i);
END;
ORA-06550: line 6, column 3:
PLS-00306: wrong number or types of arguments in call to 'SET_TEST_TYPE'
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
You can create your type only locally. It is also not a solution. Oracle does not know your objects:
SQL>
SQL>con db01/db01
SQL> CREATE OR REPLACE PACKAGE set_test_type_pkg IS
2 TYPE test_type IS RECORD(
3 i NUMBER,
4 z VARCHAR2(12));
5 PROCEDURE set_test_type(pit_test_type IN test_type, res OUT NUMBER);
6 END set_test_type_pkg;
7 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY set_test_type_pkg IS
2
3 PROCEDURE set_test_type(pit_test_type IN test_type, res OUT NUMBER) IS
4 BEGIN
5 res := pit_test_type.i;
6 END;
7 END set_test_type_pkg;
8 /
Package body created
SQL> show err;
No errors for PACKAGE BODY SYSADM.SET_TEST_TYPE_PKG
SQL>con db03/db03
SQL> DECLARE
2 i NUMBER;
3 TYPE test_type IS RECORD(
4 i NUMBER,
5 z VARCHAR2(12));
6 tt_test_type test_type;
7 BEGIN
8 tt_test_type.i := 101;
9 tt_test_type.z := 'hiii';
10 set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
11 DBMS_OUTPUT.PUT_LINE('i is ' || i);
12 END;
13 /
DECLARE
i NUMBER;
TYPE test_type IS RECORD(
i NUMBER,
z VARCHAR2(12));
tt_test_type test_type;
BEGIN
tt_test_type.i := 101;
tt_test_type.z := 'hiii';
set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
DBMS_OUTPUT.PUT_LINE('i is ' || i);
END;
ORA-06550: line 10, column 3:
PLS-00306: wrong number or types of arguments in call to 'SET_TEST_TYPE'
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored
SQL>
You can overcome this situation by referencing a local type both the databases. Important thing is, you have to reference the same type in one database.
SQL>con db01/db01
SQL> DECLARE
2 i NUMBER;
3
4 tt_test_type set_test_type_pkg.test_type@test_link;
5 BEGIN
6 tt_test_type.i := 101;
7 tt_test_type.z := 'hiii';
8 set_test_type_pkg.set_test_type@test_link(tt_test_type, i);
9 DBMS_OUTPUT.PUT_LINE('i is ' || i);
10 END;
11 /
i is 101
PL/SQL procedure successfully completed
SQL>
Naming and Coding Standards for Oracle
I have read a very useful article from William Robertson. You can read entire article from here...
11 October 2006
NVL == COALESCE == DECODE
Sometimes you can use many functions to achieve one goal. Today i saw a question on forums.oracle and i put it seperate here.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table t;
Table dropped
SQL> create table t( a varchar2(1), b varchar2(1),c varchar2(1));
Table created
SQL> INSERT INTO t VALUES ('a','b','c');
1 row inserted
SQL> INSERT INTO t VALUES (null,'d','e');
1 row inserted
SQL> INSERT INTO t VALUES (null, null, 'f');
1 row inserted
SQL> INSERT INTO t VALUES (null, 'g', null);
1 row inserted
SQL> INSERT INTO t VALUES ('h', null, 'i');
1 row inserted
SQL> SELECT * FROM t;
A B C
- - -
a b c
d e
f
g
h i
SQL> SELECT coalesce(a, b, c) FROM t;
COALESCE(A,B,C)
---------------
a
d
f
g
h
SQL> SELECT nvl(a, nvl(b, c)) FROM t;
NVL(A,NVL(B,C))
---------------
a
d
f
g
h
SQL> SELECT decode(a, NULL, decode(b, NULL, c, b), a) FROM t;
DECODE(A,NULL,DECODE(B,NULL,C,
------------------------------
a
d
f
g
h
SQL>
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> drop table t;
Table dropped
SQL> create table t( a varchar2(1), b varchar2(1),c varchar2(1));
Table created
SQL> INSERT INTO t VALUES ('a','b','c');
1 row inserted
SQL> INSERT INTO t VALUES (null,'d','e');
1 row inserted
SQL> INSERT INTO t VALUES (null, null, 'f');
1 row inserted
SQL> INSERT INTO t VALUES (null, 'g', null);
1 row inserted
SQL> INSERT INTO t VALUES ('h', null, 'i');
1 row inserted
SQL> SELECT * FROM t;
A B C
- - -
a b c
d e
f
g
h i
SQL> SELECT coalesce(a, b, c) FROM t;
COALESCE(A,B,C)
---------------
a
d
f
g
h
SQL> SELECT nvl(a, nvl(b, c)) FROM t;
NVL(A,NVL(B,C))
---------------
a
d
f
g
h
SQL> SELECT decode(a, NULL, decode(b, NULL, c, b), a) FROM t;
DECODE(A,NULL,DECODE(B,NULL,C,
------------------------------
a
d
f
g
h
SQL>
06 October 2006
Dependent Objects and Object Statuses In Oracle
A question was asked on package dependencies at oracleturk. I want to describe a bit more here:
Objects may have some dependencies on theirselves. When creating a package or a trigger you may need other objects. When compiling an object or executing an alter command, you make dependent objects invalid. On calling objects firstly Oracle check object status. If object s valid tehere is no problem. But if an object is invalid, Oracle tries to compile the dependent objects. If there is no problem, statement executed and object status will become valid.
Oracle finds the object dependents with some internal tables. You can find the dependencies via selecting user_dependencies view or executing dbms_utility.get_dependency procedure.
There is a demonstration below. p3 dependent of p2 and p2 dependent of p1.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> CREATE OR REPLACE PACKAGE p1 IS
2
3 FUNCTION get_date RETURN DATE;
4
5 END p1;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p1 IS
2
3 FUNCTION get_date RETURN DATE IS
4 BEGIN
5 RETURN SYSDATE;
6 END get_date;
7
8 END p1;
9 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P1
SQL> CREATE OR REPLACE PACKAGE p2 IS
2
3 FUNCTION get_increment_date RETURN date DETERMINISTIC;
4
5 END p2;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p2 IS
2
3 FUNCTION get_increment_date RETURN date DETERMINISTIC IS
4 BEGIN
5 RETURN p1.get_date + 1;
6 END get_increment_date;
7
8 END p2;
9 /
Package body created
SQL> show err;No errors for PACKAGE BODY HR.P2
SQL> CREATE OR REPLACE PACKAGE p3 IS
2
3 PROCEDURE write_date;
4
5
6 END p3;
7 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p3 IS
2
3 PROCEDURE write_date IS
4 BEGIN
5 dbms_output.put_line(p2.get_increment_date);
6 END write_date;
7
8 END p3;
9 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P3
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1 PACKAGE VALID
P1 PACKAGE BODY VALID
P2 PACKAGE VALID
P2 PACKAGE BODY VALID
P3 PACKAGE VALID
P3 PACKAGE BODY VALID
6 rows selected
sds SQL> alter package p1 compile;
Package altered
SQL> SELECT ud.name, ud.type, ud.referenced_name FROM user_dependencies ud WHERE ud.referenced_name = 'P1';
NAME TYPE REFERENCED_NAME
------------------------------ ----------------- ----------------------------------------------------------------
P1 PACKAGE BODY P1
P2 PACKAGE BODY P1
SQL> exec dbms_utility.get_dependency('PACKAGE', 'HR', 'P1');
-
DEPENDENCIES ON HR.P1
------------------------------------------------------------------
*PACKAGE HR.P1()
* PACKAGE BODY HR.P1()
* PACKAGE BODY HR.P2()
PL/SQL procedure successfully completed
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1 PACKAGE VALID
P1 PACKAGE BODY VALID
P2 PACKAGE VALID
P2 PACKAGE BODY INVALID
P3 PACKAGE VALID
P3 PACKAGE BODY VALID
6 rows selected
SQL> exec p3.write_date;
07/10/2006
PL/SQL procedure successfully completed
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1 PACKAGE VALID
P1 PACKAGE BODY VALID
P2 PACKAGE VALID
P2 PACKAGE BODY VALID
P3 PACKAGE VALID
P3 PACKAGE BODY VALID
6 rows selected
SQL>
Oracle stores session variables in memory. So, when package becomes invalid, firstly Oracle checks whether package has global variables. If yes, oracle unsets package state. To handle this situation you can reinitialize the package with dbms_session package or sets pragma SERIALLY_REUSABLE. Follow the examples:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> create table invalid(x number );
Table created
SQL> CREATE OR REPLACE PACKAGE p4 IS
2
3 FUNCTION get_count RETURN NUMBER;
4
5 END p4;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p4 IS
2 k NUMBER := 3;
3
4 FUNCTION get_count RETURN NUMBER IS
5 i NUMBER;
6 BEGIN
7 SELECT COUNT(*) INTO i FROM invalid;
8 k := k + 1;
9 RETURN i + k;
10 END get_count;
11
12 END p4;
13 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P4
SQL> exec dbms_output.put_line(p4.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(p4.get_count );
5
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
* PACKAGE BODY HR.P4()
PL/SQL procedure successfully completed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P4';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P4';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p4.get_count );
begin dbms_output.put_line( p4.get_count ); end;
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "HR.P4" has been invalidated
ORA-06508: PL/SQL: could not find program unit being called: "HR.P4"
ORA-06512: at line 1
SQL> set serveroutput on;
SQL> exec dbms_output.put_line( p4.get_count );
4
PL/SQL procedure successfully completed
SQL> CREATE OR REPLACE PACKAGE p5 IS
2
3 FUNCTION get_count RETURN NUMBER;
4
5 END p5;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p5 IS
2 FUNCTION get_count RETURN NUMBER IS
3 i NUMBER;
4 BEGIN
5 SELECT COUNT(*) INTO i FROM invalid;
6 RETURN i;
7 END get_count;
8
9 END p5;
10 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P5
SQL> exec dbms_output.put_line(p5.get_count);
0
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
* PACKAGE BODY HR.P5()
PL/SQL procedure successfully completed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P5';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P5';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p5.get_count );
0
PL/SQL procedure successfully completed
SQL> CREATE OR REPLACE PACKAGE p6 IS
2 PRAGMA SERIALLY_REUSABLE;
3
4 FUNCTION get_count RETURN NUMBER;
5
6 END p6;
7 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p6 IS
2 PRAGMA SERIALLY_REUSABLE;
3
4 k NUMBER := 3;
5
6 FUNCTION get_count RETURN NUMBER IS
7 i NUMBER;
8 BEGIN
9 SELECT COUNT(*) INTO i FROM invalid;
10 k := k + 1;
11 RETURN i + k;
12 END get_count;
13
14 END p6;
15 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P6
SQL> exec dbms_output.put_line(p6.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(p6.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
* PACKAGE BODY HR.P6()
PL/SQL procedure successfully completed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P6';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P6';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p6.get_count );
4
PL/SQL procedure successfully completed
SQL> CREATE OR REPLACE PACKAGE p7 IS
2
3 FUNCTION get_count RETURN NUMBER;
4
5 END p7;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p7 IS
2 k NUMBER := 3;
3
4 FUNCTION get_count RETURN NUMBER IS
5 i NUMBER;
6 BEGIN
7 SELECT COUNT(*) INTO i FROM invalid;
8 k := k + 1;
9 RETURN i + k;
10 END get_count;
11
12 END p7;
13 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P7
SQL> exec dbms_output.put_line(p7.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(p7.get_count );
5
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
* PACKAGE BODY HR.P7()
PL/SQL procedure successfully completed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P7';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P7';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_session.modify_package_state(dbms_session.reinitialize);
PL/SQL procedure successfully completed
SQL> set serveroutput on;
SQL> exec dbms_output.put_line( p7.get_count );
4
PL/SQL procedure successfully completed
SQL> --
SQL> drop table invalid;
Table dropped
SQL> drop package p7;
Package dropped
Objects may have some dependencies on theirselves. When creating a package or a trigger you may need other objects. When compiling an object or executing an alter command, you make dependent objects invalid. On calling objects firstly Oracle check object status. If object s valid tehere is no problem. But if an object is invalid, Oracle tries to compile the dependent objects. If there is no problem, statement executed and object status will become valid.
Oracle finds the object dependents with some internal tables. You can find the dependencies via selecting user_dependencies view or executing dbms_utility.get_dependency procedure.
There is a demonstration below. p3 dependent of p2 and p2 dependent of p1.
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> CREATE OR REPLACE PACKAGE p1 IS
2
3 FUNCTION get_date RETURN DATE;
4
5 END p1;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p1 IS
2
3 FUNCTION get_date RETURN DATE IS
4 BEGIN
5 RETURN SYSDATE;
6 END get_date;
7
8 END p1;
9 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P1
SQL> CREATE OR REPLACE PACKAGE p2 IS
2
3 FUNCTION get_increment_date RETURN date DETERMINISTIC;
4
5 END p2;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p2 IS
2
3 FUNCTION get_increment_date RETURN date DETERMINISTIC IS
4 BEGIN
5 RETURN p1.get_date + 1;
6 END get_increment_date;
7
8 END p2;
9 /
Package body created
SQL> show err;No errors for PACKAGE BODY HR.P2
SQL> CREATE OR REPLACE PACKAGE p3 IS
2
3 PROCEDURE write_date;
4
5
6 END p3;
7 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p3 IS
2
3 PROCEDURE write_date IS
4 BEGIN
5 dbms_output.put_line(p2.get_increment_date);
6 END write_date;
7
8 END p3;
9 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P3
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1 PACKAGE VALID
P1 PACKAGE BODY VALID
P2 PACKAGE VALID
P2 PACKAGE BODY VALID
P3 PACKAGE VALID
P3 PACKAGE BODY VALID
6 rows selected
sds SQL> alter package p1 compile;
Package altered
SQL> SELECT ud.name, ud.type, ud.referenced_name FROM user_dependencies ud WHERE ud.referenced_name = 'P1';
NAME TYPE REFERENCED_NAME
------------------------------ ----------------- ----------------------------------------------------------------
P1 PACKAGE BODY P1
P2 PACKAGE BODY P1
SQL> exec dbms_utility.get_dependency('PACKAGE', 'HR', 'P1');
-
DEPENDENCIES ON HR.P1
------------------------------------------------------------------
*PACKAGE HR.P1()
* PACKAGE BODY HR.P1()
* PACKAGE BODY HR.P2()
PL/SQL procedure successfully completed
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1 PACKAGE VALID
P1 PACKAGE BODY VALID
P2 PACKAGE VALID
P2 PACKAGE BODY INVALID
P3 PACKAGE VALID
P3 PACKAGE BODY VALID
6 rows selected
SQL> exec p3.write_date;
07/10/2006
PL/SQL procedure successfully completed
SQL> SELECT o.object_name, o.object_type, o.status FROM user_objects o WHERE o.object_name in ('P1','P2', 'P3');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------------------------------------------------------------------- ------------------- -------
P1 PACKAGE VALID
P1 PACKAGE BODY VALID
P2 PACKAGE VALID
P2 PACKAGE BODY VALID
P3 PACKAGE VALID
P3 PACKAGE BODY VALID
6 rows selected
SQL>
Oracle stores session variables in memory. So, when package becomes invalid, firstly Oracle checks whether package has global variables. If yes, oracle unsets package state. To handle this situation you can reinitialize the package with dbms_session package or sets pragma SERIALLY_REUSABLE. Follow the examples:
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr
SQL>
SQL> create table invalid(x number );
Table created
SQL> CREATE OR REPLACE PACKAGE p4 IS
2
3 FUNCTION get_count RETURN NUMBER;
4
5 END p4;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p4 IS
2 k NUMBER := 3;
3
4 FUNCTION get_count RETURN NUMBER IS
5 i NUMBER;
6 BEGIN
7 SELECT COUNT(*) INTO i FROM invalid;
8 k := k + 1;
9 RETURN i + k;
10 END get_count;
11
12 END p4;
13 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P4
SQL> exec dbms_output.put_line(p4.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(p4.get_count );
5
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
* PACKAGE BODY HR.P4()
PL/SQL procedure successfully completed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P4';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P4';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p4.get_count );
begin dbms_output.put_line( p4.get_count ); end;
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "HR.P4" has been invalidated
ORA-06508: PL/SQL: could not find program unit being called: "HR.P4"
ORA-06512: at line 1
SQL> set serveroutput on;
SQL> exec dbms_output.put_line( p4.get_count );
4
PL/SQL procedure successfully completed
SQL> CREATE OR REPLACE PACKAGE p5 IS
2
3 FUNCTION get_count RETURN NUMBER;
4
5 END p5;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p5 IS
2 FUNCTION get_count RETURN NUMBER IS
3 i NUMBER;
4 BEGIN
5 SELECT COUNT(*) INTO i FROM invalid;
6 RETURN i;
7 END get_count;
8
9 END p5;
10 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P5
SQL> exec dbms_output.put_line(p5.get_count);
0
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
* PACKAGE BODY HR.P5()
PL/SQL procedure successfully completed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P5';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P5';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p5.get_count );
0
PL/SQL procedure successfully completed
SQL> CREATE OR REPLACE PACKAGE p6 IS
2 PRAGMA SERIALLY_REUSABLE;
3
4 FUNCTION get_count RETURN NUMBER;
5
6 END p6;
7 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p6 IS
2 PRAGMA SERIALLY_REUSABLE;
3
4 k NUMBER := 3;
5
6 FUNCTION get_count RETURN NUMBER IS
7 i NUMBER;
8 BEGIN
9 SELECT COUNT(*) INTO i FROM invalid;
10 k := k + 1;
11 RETURN i + k;
12 END get_count;
13
14 END p6;
15 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P6
SQL> exec dbms_output.put_line(p6.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(p6.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
* PACKAGE BODY HR.P6()
PL/SQL procedure successfully completed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P6';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P6';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_output.put_line( p6.get_count );
4
PL/SQL procedure successfully completed
SQL> CREATE OR REPLACE PACKAGE p7 IS
2
3 FUNCTION get_count RETURN NUMBER;
4
5 END p7;
6 /
Package created
SQL> CREATE OR REPLACE PACKAGE BODY p7 IS
2 k NUMBER := 3;
3
4 FUNCTION get_count RETURN NUMBER IS
5 i NUMBER;
6 BEGIN
7 SELECT COUNT(*) INTO i FROM invalid;
8 k := k + 1;
9 RETURN i + k;
10 END get_count;
11
12 END p7;
13 /
Package body created
SQL> show err;
No errors for PACKAGE BODY HR.P7
SQL> exec dbms_output.put_line(p7.get_count);
4
PL/SQL procedure successfully completed
SQL> exec dbms_output.put_line(p7.get_count );
5
PL/SQL procedure successfully completed
SQL> exec dbms_utility.get_dependency( 'TABLE', 'HR', 'INVALID');
-
DEPENDENCIES ON HR.INVALID
------------------------------------------------------------------
*TABLE HR.INVALID()
* PACKAGE BODY HR.P7()
PL/SQL procedure successfully completed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P7';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY VALID
SQL> rename invalid to invalid2;
Table renamed
SQL> SELECT o.object_type, o.status FROM user_objects o WHERE o.object_name = 'P7';
OBJECT_TYPE STATUS
------------------- -------
PACKAGE VALID
PACKAGE BODY INVALID
SQL> rename invalid2 to invalid;
Table renamed
SQL> exec dbms_session.modify_package_state(dbms_session.reinitialize);
PL/SQL procedure successfully completed
SQL> set serveroutput on;
SQL> exec dbms_output.put_line( p7.get_count );
4
PL/SQL procedure successfully completed
SQL> --
SQL> drop table invalid;
Table dropped
SQL> drop package p7;
Package dropped
Subscribe to:
Posts (Atom)