Coskan’s Approach to Oracle

August 13, 2009

DBMS_XMLGEN and bug

Filed under: Basics, Bugs, PL/SQL — coskan @ 4:35 pm

Last 2 weeks I had to deal with a post-upgrade problem (9.2.0.5 to 10.2.0.4) on a system which is not well tested before upgrade.  The issue was error stack below

ORA-06502: PL/SQL: numeric or value error:invalid LOB locator specified: ORA-22275

or for some records

ORA-29283: invalid file operation

ORA-06512: at “SYS.UTL_FILE”, line 488

Developer was insisting that this is a character set issue because of the first line of the error stack and I was saying it is impossible because both servers were UTF8 and only difference is NLS_NCHAR_CHARACTERSET which is never used anyway.  Because developer already gave the last decision, it was my turn to prove that this wasnt a characterset issue.

It took 2 weeks to understand the real problem (My development skills are not as good as my DBA skills and I did not get any proper help from development that’s why it took longer than it needs:) ) .  Basically application gets data from database converts it to XML (by DBMS_XMLGen) and writes it to a file (by DBMS_LOB).   We tried every possible solution we found from google and metaling for the error stacks and finally we understand that XML generation was the problem.  DBMS_XMLGEN.GETXML was not generating anything for some records but it was generating in 9i.  To be honest The error stack (was using  DBMS_UTILITY.FORMAT_ERROR_STACK and  DBMS_UTILITY.format_error_backtrace)   was not very clear  to me thats why I spent too much time with other things.
According to the bug numbers below DBMS_XMLGEN.GETXML is not working same in 10R2  as it is working on 9i. The way it handles nulls and special characters is buggy. Workaround it using DBMS_XMLQUERY instead.

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR
Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2
Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

Bug No. 8246403 NEED HINTS TO DEBUG “ORA-31011: XML PARSING FAILED” ERROR

Bug No. 8476233  DBMS_XMLGEN.GETXML RETURNS NULL IN 10.2.X, WORKED IN 9.2

Bug No. 6445329  GETXML() RETURNS NULL FROM QUERY AGAINST VIEW USING MAX() ON COLUMN WITH NULLS

This is the demo to show the different behaviour (taken from metalink ).

in 10G


SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
----------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

in 9i

SQL> create table t as select cast(null as varchar2(12))  as x from dual;

Table created.

SQL> select count(distinct x) x from t;

         X
----------
         0

SQL> select dbms_xmlgen.getxml('select count(distinct x) x from t') x from dual;
ERROR:
ORA-06502: PL/SQL: numeric or value error
ORA-24347: Warning of a NULL column in an aggregate function

no rows selected

SQL> select dbms_xmlquery.getxml('select count(distinct x) x from t') x from dual;

X
--------------------------------------------------------------------------------------
-------------------------------------------------
<?xml version = '1.0'?>
<ROWSET>
   <ROW num="1">
      <X>0</X>
   </ROW>
</ROWSET>

Lessons Learned.

1-Do not trust “we tested its ok”  and Try to understand the application and try to ask developers what packages they are using so you can search for possible bugs before you upgrade the system.

2-After 1 day of struggling, Search metalink first  for a possible bug.  Stop messing around google

June 30, 2009

How to explain plan on views when you don’t have access on base tables

Filed under: PL/SQL, Tips — coskan @ 11:28 am

One of my friends asked, how can developers see the explain plan on views without having access to the base tables.

After a small search in Oracle Forums I found the solution of John Spencer.

Basically he creates a procedure to run explain plan and gives the execute access to that procedure.

This is the procedure

CREATE OR REPLACE PROCEDURE explain_it (p_plan_id IN VARCHAR2,
p_sql IN VARCHAR2,
p_plan OUT my_types.expl_cur) AS
l_sqlstr VARCHAR2(32767);
BEGIN
l_sqlstr := 'SELECT LPAD('' '',2*(LEVEL-1))||OPERATION||'' ''||OPTIONS||'' ''||OBJECT_NAME||'' ''||';
l_sqlstr := l_sqlstr||'DECODE(ID,0,''COST = ''||POSITION) "QUERY PLAN" FROM plan_table ';
l_sqlstr := l_sqlstr||'START WITH ID = 0 AND STATEMENT_ID = :b1 ';
l_sqlstr := l_sqlstr||'CONNECT BY PRIOR ID = PARENT_ID AND STATEMENT_ID = :b2';

EXECUTE IMMEDIATE 'EXPLAIN PLAN SET statement_id = '''||p_plan_id||''' FOR '||p_sql;

OPEN p_plan FOR l_sqlstr USING p_plan_id, p_plan_id;
END;
/

or to use with dbms_xplan.display


CREATE OR REPLACE PROCEDURE explain_it (
p_sql IN VARCHAR2,
p_plan OUT sys_refcursor) AS
l_sqlstr VARCHAR2(32767);
BEGIN
l_sqlstr := 'select * from table (dbms_xplan.display)';
EXECUTE IMMEDIATE 'EXPLAIN PLAN FOR '||p_sql;
OPEN p_plan FOR l_sqlstr ;
END;
/

and this is how it works

SQL> create user read_only identified by read_only
  2  ;

User created.

SQL> grant create session to read_only;

Grant succeeded.

SQL> create table exp_test as select * from user_objects where rownum<1000;

Table created.

SQL> create view v_exp_test as select * from exp_test;

View created.

SQL> grant select on v_exp_test to read_only;

Grant succeeded.

SQL> grant execute on explain_it to read_only;

Grant succeeded.

----connect with read_only

SQL> connect read_only/read_only
Connected.

SQL> select count(*) from hr.v_exp_test;

  COUNT(*)
----------
       156

SQL> explain plan for select count(*) from hr.v_exp_test;
explain plan for select count(*) from hr.v_exp_test
                                         *
ERROR at line 1:
ORA-01039: insufficient privileges on underlying objects of the view

---TRY WITH NEW PROCEDURE

SQL> var my_cur refcursor;
SQL> EXEC hr.explain_it('SELECT * FROM hr.v_exp_test',:my_cur);

PL/SQL procedure successfully completed.

SQL> print my_cur;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
-------------------------------------------------
Plan hash value: 3488715207

------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |   156 | 29640 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| EXP_TEST |   156 | 29640 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

12 rows selected.

March 18, 2009

What I learned during Oracle SQL Expert Exam Study Part-2

Filed under: Basics, Certification, PL/SQL — coskan @ 9:31 pm

I wrote Part-1 of this series for covering what I learned from SQL Fundamentals I while studying for SQL Expert exam. Part-2 is for covering what I learned from SQL Fundamentals II.

Here are the things I should have learned before; (There are questions on 6 and 9 and answers will be prized with guinness if you can catch me (try my pony tail when you see me at a conference))

1- session_privs / session_roles views; never need, so never heard, these views can be very useful for normal database users to see what privileges they have what roles are enabled for them.

SQL> connect hr/hr;
Connected.
SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
...
...
...

14 rows selected.

-----ON SYS SESSION GRANT
-----SELECT ANY TABLE TO HR

SQL> grant select any table to hr;

Grant succeeded.

---ON HR SESSION
---SELECT ANY TABLE is shown 

SQL> select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
SELECT ANY TABLE
....
....

15 rows selected.

—SESSION ROLES

SQL> select * from session_roles;

ROLE
------------------------------
RESOURCE

---ON SYS SESSION GIVE DBA ROLE to HR

SQL> grant dba to hr;

Grant succeeded.
---on HR Session set Role DBA

SQL> set role dba;

Role set.

SQL> select * from session_roles;

ROLE
------------------------------
DBA
SELECT_CATALOG_ROLE
HS_ADMIN_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
...
...

18 rows selected.

2- PASSWORD command of SQLPLUS I was always using alter user command to change my password or somebody else’s password but this command is available and working as it works on Unix. If you want to change your password when you are a normal user it asks old password but if you are DBA and want to change some other users password, it lets you to set the password.


SQL> connect hr/hr
Connected.
---for SCOTT by HR
SQL> password scott
Changing password for scott
New password:
Retype new password:
ERROR:
ORA-01031: insufficient privileges

Password unchanged
SQL>---FOR HR itself
SQL> password
Changing password for HR
Old password:
New password:
Retype new password:
Password changed

—with sys user

SQL> password SCOTT
Changing password for SCOTT
New password:
Retype new password:
Password changed

3- References Object Privilege; Document says it lets the given user to create references on the table or view.

SQL> create user coskan identified by coskan ;

User created.

SQL> grant resource,create session to coskan;

Grant succeeded.

SQL> grant create table to coskan;

Grant succeeded.

SQL> connect coskan/coskan;
Connected.

SQL> create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id));
create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id))
                                                                *
ERROR at line 1:
ORA-00942: table or view does not exist

----GRANT REFERENCES priv
----on HR.EMPLOYEES by HR user

SQL> grant references on employees to coskan;

Grant succeeded.

---on COSKAN user
---it lets to user the table for ref.
SQL> create table t1 (id number CONSTRAINT t1_empid_fk REFERENCES hr.employees(employee_id));

Table created.

---PRIV is just for reference
---it is not for selecting
SQL> select count(*) from hr.employees;
select count(*) from hr.employees
                        *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

—on HR session, you cant revoke privilege without cascade option

SQL> revoke references on employees from coskan;
revoke references on employees from coskan
*
ERROR at line 1:
ORA-01981: CASCADE CONSTRAINTS must be specified to perform this revoke

SQL> revoke references on employees from coskan cascade constraints;

Revoke succeeded.

----on COSKAN session constraint is droppped

SQL> @ddl t1

PL/SQL procedure successfully completed.

DBMS_METADATA.GET_DDL(OBJECT_TYPE,OBJECT_NAME,OWNER)
-------------------------------------------------------------------------
-------------------------------------------------------------------------

  CREATE TABLE "COSKAN"."T1"
   (    "ID" NUMBER
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;

4- system and object privileges cannot be given in one sql statement.

SQL> grant create table,select on hr.employees to coskan;
grant create table,select on hr.employees to coskan
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege

SQL> grant create table to coskan;

Grant succeeded.

SQL> grant select on hr.employees to coskan;

Grant succeeded.

5-on delete set null Another shame for me. I think this option is better than on delete cascade for constraints

SQL> create table emp_2 (id number,
  2  emp_name varchar2(32),
  3  emp_id number
  4  CONSTRAINT t1_empid_fk
  5  REFERENCES hr.employees(employee_id)
  6  on delete set null);

Table created.

SQL> insert into emp_2 select employee_id,last_name
  2  ,employee_id from hr.employees where rownum<10;

9 rows created.

SQL> commit;

Commit complete.

SQL> select * from emp_2;

        ID EMP_NAME                             EMP_ID
---------- -------------------------------- ----------
       198 OConnell                                198
       199 Grant                                   199
       200 Whalen                                  200
       201 Hartstein                               201
       202 Fay                                     202
       203 Mavris                                  203
       204 Baer                                    204
       205 Higgins                                 205
       206 Gietz                                   206

9 rows selected.

SQL> delete from hr.employees where employee_id=198;

1 row deleted.

SQL> select * from emp_2;

        ID EMP_NAME                             EMP_ID
---------- -------------------------------- ----------
       198 OConnell
       199 Grant                                   199
       200 Whalen                                  200
       201 Hartstein                               201
       202 Fay                                     202
       203 Mavris                                  203
       204 Baer                                    204
       205 Higgins                                 205
       206 Gietz                                   206

9 rows selected.

SQL>

6- Inserting Using a Subquery as a Target This is the first time I came across an insert like this. When you do an insert using a subquery I think it is better to use it with check otherwise I cant think of a situation to use it

Here is the working example. When I use WITH CHECK I got the expected error



SQL> INSERT INTO
  2  (SELECT department_id, department_name, d.location_id
  3  FROM departments d,locations l
  4  WHERE d.location_id=l.location_id and
  5  d.location_id < 2000)
  6  VALUES (9999, 'Entertainment', 2500);

1 row created.

SQL> rollback;

Rollback complete.

SQL> INSERT INTO
  2  (SELECT department_id, department_name, d.location_id
  3  FROM departments d,locations l
  4  WHERE d.location_id=l.location_id and
  5  d.location_id < 2000 WITH CHECK OPTION)
  6  VALUES (9999, 'Entertainment', 2500);
FROM departments d,locations l
     *
ERROR at line 3:
ORA-01402: view WITH CHECK OPTION where-clause violation



This is is the failed one, when I use WITH CHECK OPTION, I got ORA-01733: instead of ORA-01402: and I really dont know, why it fails. I asked it on Oracle forums too and if you need points at the forum ,you can answer it at this link


SQL> INSERT INTO
  2  (SELECT
  3  location_id,
  4  city,
  5  l.country_id
  6  FROM countries c, locations l,regions r
  7  where l.country_id = c.country_id
  8  and r.region_id=c.region_id
  9  and  r.region_name = 'Asia'  )
 10  VALUES (5500, 'Wansdworth Common', 'UK');

1 row created.

SQL> rollback;

Rollback complete.

SQL> INSERT INTO
  2  (SELECT
  3  location_id,
  4  city,
  5  l.country_id
  6  FROM countries c, locations l,regions r
  7  where l.country_id = c.country_id
  8  and r.region_id=c.region_id
  9  and  r.region_name = 'Asia' WITH CHECK OPTION )
 10  VALUES (5500, 'Wansdworth Common', 'UK');
INSERT INTO
*
ERROR at line 1:
ORA-01733: virtual column not allowed here


7- Multiple inserts I heard about multiple inserts probably when I got DBA Workshop-1 , 4 years ago, but I never needed them after that date. Here is how you can use them

—unconditional INSERT ALL. As you might guess there is no condition and all inserts will run

create table sal_history as
select employee_id,hire_date,salary
from employees
where 1=2;

SQL> create table MGR_HISTORY as
  2  select EMPLOYEE_ID,MANAGER_ID,SALARY
  3  from employees
  4  where 1=2;

Table created.

SQL> create table SAL_HISTORY as
  2  select EMPLOYEE_ID,HIRE_DATE,SALARY
  3  from employees
  4  where 1=2;

Table created.

SQL>
SQL> INSERT ALL
  2  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  3  INTO mgr_history VALUES(EMPID,MGR,SAL)
  4  SELECT employee_id EMPID, hire_date HIREDATE,
  5  salary SAL, manager_id MGR
  6  FROM employees
  7  WHERE department_id > 20;

206 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
       103

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
       103

—conditional INSERT ALL. Only insert statements those fit to the condition will be inserted


SQL> truncate table sal_history;

Table truncated.

SQL> truncate table mgr_history;

Table truncated.

SQL>
SQL> select count(*) from employees
  2  where department_id<20;

  COUNT(*)
----------
         1

SQL>
SQL> select count(*) from employees
  2  where manager_id is null;

  COUNT(*)
----------
         2

SQL> INSERT ALL
  2  WHEN DEPARTMENT_ID <20 THEN
  3  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  4  WHEN MGR is null THEN
  5  INTO mgr_history VALUES(EMPID,MGR,SAL)
  6  SELECT employee_id EMPID, hire_date HIREDATE,
  7  salary SAL, manager_id MGR,DEPARTMENT_ID
  8  FROM employees;

3 rows created.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
         1

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
         2

—conditional INSERT FIRST Only first insert whose condition is TRUE will run if it is not true the second and continue.


SQL> truncate table sal_history;

Table truncated.

SQL> truncate table mgr_history;

Table truncated.

SQL> truncate table emp_history;

Table truncated.

SQL>
SQL> select count(*) from employees
  2  where manager_id is null;

  COUNT(*)
----------
         2

SQL>
SQL> select count(*) from employees
  2  where department_id is null;

  COUNT(*)
----------
         1

SQL>
SQL>
SQL> select count(*) from employees
  2  where department_id is null and manager_id is null;

  COUNT(*)
----------
         1

SQL> select count(*) from (
  2  SELECT employee_id EMPID, hire_date HIREDATE,
  3  salary SAL, manager_id MGR,DEPARTMENT_ID
  4  FROM employees) t;

  COUNT(*)
----------
       106

SQL>
SQL>
SQL> ---When it finds first row with manager_id is null
SQL> ---it will skip mgr_history insert and will do
SQL> ---only sal_history insert and
SQL> ---there is no other department_id is null
SQL> ---so second insert will never run
SQL> ---at the end of this transaction
SQL> ---sal_history 2 row
SQL> ---mgr_history 0 row
SQL> ---emp_history 104 row
SQL>
SQL> INSERT FIRST
  2  WHEN MGR is null THEN
  3  INTO sal_history VALUES(EMPID,HIREDATE,SAL)
  4  WHEN DEPARTMENT_ID is null THEN
  5  INTO mgr_history VALUES(EMPID,MGR,SAL)
  6  ELSE INTO emp_history VALUES(EMPID,SAL,MGR,DEPARTMENT_ID)
  7  SELECT employee_id EMPID, hire_date HIREDATE,
  8  salary SAL, manager_id MGR,DEPARTMENT_ID
  9  FROM employees;

106 rows created.

SQL> select count(*) from sal_history;

  COUNT(*)
----------
         2

SQL> select count(*) from mgr_history;

  COUNT(*)
----------
         0

SQL> select count(*) from emp_history;

  COUNT(*)
----------
       104

—pivoting INSERT- The one I really like. It does pivoting :)

suppose that you have a table like below


SQL> create table HALF_YEAR_BUDGET
  2  (DEPARTMENT varchar2(2),
  3  YEAR VARCHAR2(4),
  4  JAN number,
  5  FEB number,
  6  MAR number,
  7  APR number,
  8  MAY number,
  9  JUN number);

Table created.

SQL> insert into HALF_YEAR_BUDGET
  2  values ('IT','2009',5000,6000,2000,4000,7000,9000);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from HALF_YEAR_BUDGET;

DE YEAR        JAN        FEB        MAR        APR        MAY        JUN
-- ---- ---------- ---------- ---------- ---------- ---------- ----------
IT 2009       5000       6000       2000       4000       7000       9000

and you want to store it in 3 column 6 row table

all you need to do is pivoting the insert

usage is same with INSERT ALL

SQL> create table DEP_BUDGET
  2  (DEPARTMENT varchar2(2),
  3  YEAR VARCHAR2(4),
  4  BUDGET number);

Table created.

SQL> INSERT ALL
  2     INTO DEP_BUDGET VALUES (department,year,jan)
  3     INTO DEP_BUDGET VALUES (department,year,feb)
  4     INTO DEP_BUDGET VALUES (department,year,mar)
  5     INTO DEP_BUDGET VALUES (department,year,apr)
  6     INTO DEP_BUDGET VALUES (department,year,may)
  7     INTO DEP_BUDGET VALUES (department,year,jun)
  8     SELECT department, year, jan,feb,mar,apr,
  9     may,jun
 10     FROM HALF_YEAR_BUDGET;

6 rows created.

SQL> select * from DEP_BUDGET;

DE YEAR     BUDGET
-- ---- ----------
IT 2009       5000
IT 2009       6000
IT 2009       2000
IT 2009       4000
IT 2009       7000
IT 2009       9000

6 rows selected.

8- Explicit Defaults This is nice way to set a column to a default value. Just use the word DEFAULT

SQL> create table t1 (id number,last_name varchar2(32) default 'coskan');

Table created.

SQL> insert into t1 values (1,'oracle');

1 row created.

SQL> insert into t1 values (2,'larry');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID LAST_NAME
---------- --------------------------------
         1 oracle
         2 larry

SQL> update t1 set last_name=DEFAULT where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from t1;

        ID LAST_NAME
---------- --------------------------------
         1 coskan
         2 larry

9- Subquery in order by You need to show all the employees ordered by their department names.

FIRST WAY I thought was joining the tables


SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id
  4  order by d.department_name;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        206 Gietz
        205 Higgins
        200 Whalen
        202 Fay
	..
	..
	..

105 rows selected

SQL> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------
Plan hash value: 3011238288

---------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |   105 |  3255 |     7  (29)| 00:00:01 |
|   1 |  SORT ORDER BY                |             |   105 |  3255 |     7  (29)| 00:00:01 |
|   2 |   MERGE JOIN                  |             |   105 |  3255 |     6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |    27 |   432 |     2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | DEPT_ID_PK  |    27 |       |     1   (0)| 00:00:01 |
|*  5 |    SORT JOIN                  |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
       filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")

19 rows selected.

SECOND WAY I LEARNED- using corrolated subquery in order by

SQL> select employee_id,last_name from hr.employees e
  2  order by
  3  (select department_name
  4  from hr.departments d
  5  where e.department_id=d.department_id);

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        205 Higgins
        206 Gietz
        200 Whalen
	..
	..
	..
106 Rows selected

SQL> @x

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------
Plan hash value: 1231663137

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   3 |  SORT ORDER BY              |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   4 |   TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"=:B1)

If you are carefull enough you would have seen that plans are different and rownumbers are different ?

What is the difference between 2?

Second one doesn’t care if there isnt any matching row on employees table. So it is something like outer join
So if you want the same result with the first method you need outer join

SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id(+)
  4  order by d.department_name;

EMPLOYEE_ID LAST_NAME
----------- -------------------------
        206 Gietz
        205 Higgins
        200 Whalen
        202 Fay
	...
	...
106 rows selected

SQL> @x

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Plan hash value: 3271769410

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  3317 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |   107 |  3317 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |             |   107 |  3317 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

As you see they have different plan. Cost of corrolated one is better then the outer join version
but consistent gets are higher for corrolated one. 

<strong>I think this needs more  investigation if I find something I will write as a new blog post</strong>  (to be honest I just realised. One more benefit of blogging; paying more attention to what you learn/write)
1
SQL> set autotrace traceonly;
SQL> select employee_id,last_name from hr.employees e
  2  order by
  3  (select department_name
  4  from hr.departments d
  5  where e.department_id=d.department_id);

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1231663137

-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   3 |  SORT ORDER BY              |             |   107 |  1605 |     4  (25)| 00:00:01 |
|   4 |   TABLE ACCESS FULL         | EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("D"."DEPARTMENT_ID"=:B1)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         23  consistent gets
          0  physical reads
          0  redo size
       2200  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        106  rows processed

SQL> select employee_id,last_name
  2  from hr.employees e,hr.departments d
  3  where e.department_id=d.department_id(+)
  4  order by d.department_name;

106 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3271769410

-----------------------------------------------------------------------------------
| Id  | Operation           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |             |   107 |  3317 |     8  (25)| 00:00:01 |
|   1 |  SORT ORDER BY      |             |   107 |  3317 |     8  (25)| 00:00:01 |
|*  2 |   HASH JOIN OUTER   |             |   107 |  3317 |     7  (15)| 00:00:01 |
|   3 |    TABLE ACCESS FULL| EMPLOYEES   |   107 |  1605 |     3   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| DEPARTMENTS |    27 |   432 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       2200  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
        106  rows processed

SQL>

This is the end of this series as I promised.Please check the SQL Reference for remaining interesting things I bet you will find one no matter how much you think you know.

March 5, 2009

What I learned during Oracle SQL Expert Exam Study Part-1

Filed under: Basics, Certification, PL/SQL, Tips — coskan @ 9:36 am

Yesterday, I passed the Oracle SQL Expert Exam. After 4 full years of working with Oracle, it took 2.5 weeks to study for this exam. I have studied from the SQL Fundementals 1-2 course material from Oracle University for focusing the exam topics and I used Oracle official documentation to support what I have learned from the course material. During my studies, I can say I learned a lot of small things and sometimes I felt shamed about not knowing the things that I should have learned as a junior DBA. At the end of the day, I can easily say that I can not be a SQL expert before writing that much SQL code as a database developer nevertheless, I am glad that, I spent time on learning new things by the help of certification. After this brief history, lets talk about something technical. (Warning!!! Post will be long and if you are already good with sql, just review the headers to se if there is something new for you. )

1- q Operator for using character literals with qoutation marks I never heard about it before. Document says its good for readibility but I am not sure :) Usage is simple if you want to say “coskan’s” all you need to do is put it in the braclets q'[]’ or q'{}’ or q'()’ or q'<>’

SQL> select 'coskan''s approach to oracle' from dual;

'COSKAN''SAPPROACHTOORACLE'
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q[''s approach to oracle'] from dual;
ERROR:
ORA-01756: quoted string not properly terminated

SQL> select 'coskan'||q'['s approach to oracle]' from dual;

'COSKAN'||Q'['SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'('s approach to oracle)' from dual;

'COSKAN'||Q'('SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'{'s approach to oracle}' from dual;

'COSKAN'||Q'{'SAPPROACHTOOR
---------------------------
coskan's approach to oracle

SQL> select 'coskan'||q'<'s approach to oracle>' from dual;

'COSKAN'||Q'<'SAPPROACHTOOR
---------------------------
coskan's approach to oracle

2- null with logical operators . ( I felt really shamed that I didnt know (null&false=false) and (null or true=true) ) table is like below null and true = null null and false = false null OR true = true null OR false = null

SQL> select * from hr.departments where (1=1 and department_id not in (select null from dual)) and rownum<2;

no rows selected

SQL> select * from hr.departments where (1=2 and department_id not in (select null from dual)) and rownum<2;

no rows selected

SQL> select * from hr.departments where (1=1 or department_id not in (select null from dual)) and rownum<2;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700

SQL> select * from hr.departments where (1=2 or department_id not in (select null from dual)) and rownum<2;

no rows selected

3- Escape clause to avoid wildcard characters.

To use wildcard characters as normal character you can use Escape clause with Like

SQL> select first_name from hr.employees
     2  where first_name like '%E_%';

FIRST_NAME
--------------------
Ellen
Elizabeth
E_leni

SQL> select first_name from hr.employees 
     2 where first_name like '%E\_%' escape '\';

FIRST_NAME
--------------------
E_leni

SQL> select first_name from hr.employees 
    2   where first_name like '%E?_%' escape '?';

FIRST_NAME
--------------------
E_leni

4 Operator Precedence
You need to know these precedence to make sure your query is doing the right thing. (simple things can cause big problems)
1 Arithmetic operators
2 Concatenation operator
3 Comparison conditions
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Not equal to
7 NOT logical condition
8 AND logical condition
9 OR logical condition

5-Order by Nulls FIRST/LAST Clause If you want to put nulls in the beginning or end of you resultset all you need to do is add

order by ….. nulls first
or
order by ….. nulls last

 
SQL> select * from hr.departments 
   2 order by manager_id nulls last;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           90 Executive                             100        1700
           60 IT                                    103        1400
          100 Finance                               108        1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700

5 rows selected.

SQL> select * from hr.departments 
   2 order by manager_id nulls first;

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
          240 Government Sales                                 1700
          230 IT Helpdesk                                      1700
           90 Executive                             100        1700
           60 IT                                    103        1400
          100 Finance                               108        1700

5 rows selected.

6- How to see seconds past midnight (dont know how I missed this when I prepare take control of time post)

SQL> select to_char(sysdate,'sssss') seconds_pass_midnight
  2  from dual;

SECON
-----
61546

7- nullif function
Document says NULLIF compares expr1 and expr2. If they are equal, then the function returns null. If they are not equal, then the function returns expr1.

Say you want to put null for the lenght_diff for employees whose first_name lenght is equal to last_name lenght (dont ask why :) )

SQL> select  first_name,last_name
  2  ,nullif (length(first_name),length(last_name)) lengt_diff
  3  from employees;

FIRST_NAME LAST_NAME  LENGT_DIFF
---------- ---------- ----------
MARKO      JANKO
JOE        JANKO               3

8- Coalesce Function
Document says; COALESCE returns the first non-null expr in the expression list. You must specify at least two expressions. If all occurrences of expr evaluate to null, then the function returns null.

Lets see how ;


SQL> SELECT last_name, employee_id,manager_id,department_id,
  2  COALESCE(to_char(department_id),TO_CHAR(manager_id), 'No manager no department who are you ???')
  3  FROM hr.employees
  4  ;

LAST_NAME                 EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID COALESCE(TO_CHAR(DEPARTMENT_ID),TO_CHAR(
------------------------- ----------- ---------- ------------- ----------------------------------------
OConnell                          198        124            50 50
Grant                             199        124            50 50
Whalen                            200        101            10 10
Hartstein                         201        100            20 20
Fay                               202        201           160 160
Mavris                            203        101            40 40
Baer                              204        101            70 70
Higgins                           205        101           110 110
Gietz                             206        205           110 110
King                              100                       90 90	******************
Kochhar                           101        100            90 90
....................................................................................
....................................................................................
....................................................................................
....................................................................................
Feeney                            197                          No manager no department who are you ??? ****************

107 rows selected.

9- Need of group by clause for nested group by functions.

---single aggregate function

SQL> select avg(salary) from employees;

AVG(SALARY)
-----------
 6461.68224


---nested aggregate functions
SQL> select max(avg(salary)) from employees;
select max(avg(salary)) from employees
           *
ERROR at line 1:
ORA-00978: nested group function without GROUP BY

SQL> select max(avg(salary)) from employees
  2  group by department_id;

MAX(AVG(SALARY))
----------------
      19333.3333

10-Natural Join
Despite coming from a SQL Server DBA background with computer engineering diploma I never heard about natural joins or I heard at university but never cared about it ….

Document says:
A natural join is based on all columns in the two tables that have the same name. It selects rows from the two tables that have equal values in the relevant columns. When specifying columns that are involved in the natural join, do not qualify the column name with a table name or table alias.

SQL> select employee_id,department_name from employees natural join departments;

32 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2052257371

----------------------------------------------------------------------------------
| Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |             |    11 |   330 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |             |    11 |   330 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPARTMENTS |    11 |   209 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMPLOYEES   |   108 |  1188 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID"
              AND "EMPLOYEES"."MANAGER_ID"="DEPARTMENTS"."MANAGER_ID")
   2 - filter("DEPARTMENTS"."MANAGER_ID" IS NOT NULL)


11-When joining with JOIN…USING syntax table aliases are not allowed for the joining column

SQL> ---WRONG
SQL> select e.first_name,e.department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where d.department_id=10 and rownum<2;
where d.department_id=10 and rownum<2
      *
ERROR at line 4:
ORA-25154: column part of USING clause cannot have qualifier


SQL> ---WRONG after removing from where
SQL> select e.first_name,e.department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where department_id=10 and rownum<2;
select e.first_name,e.department_id
                    *
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier


SQL> --RIGHT
SQL> select e.first_name,department_id
  2  from employees e join departments d
  3  using (department_id)
  4  where department_id=10 and rownum<2;

FIRST_NAME           DEPARTMENT_ID
-------------------- -------------
Jennifer                        10 

12-ANY/ALL operator for subqueries
My face is going red for not using these two before. I think I never need them :)

Document says same thin for both of them;
Compares a value to each value in a list or returned by a query. Must be preceded by =, !=, >, <, =. Can be followed by any expression or subquery that returns one or more values.

The only difference is ANY Evaluates to FALSE if the query returns no rows. ALL Evaluates to TRUE if the query returns no rows.

I won’t go deep into but if you not already know them its good practice to play with them

SQL> ---ANY
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  salary < ANY
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'ST_MAN')
  7  AND job_id <> 'ST_MAN' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        132 Olson                     ST_CLERK         2100

SQL>
SQL> ----USING ANY WITH NOT CHECK THE SYNTAX
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  NOT salary < ANY
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'IT_PROG')
  7  AND job_id <> 'IT_PROG' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        158 McEwen                    SA_REP           9000

SQL> ---ALL
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  salary < ALL
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'ST_MAN')
  7  AND job_id <> 'ST_MAN' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        105 Austin                    IT_PROG          4800

SQL>
SQL> ----USING ALL WITH NOT CHECK THE SYNTAX
SQL> SELECT employee_id, last_name, job_id, salary
  2  FROM employees
  3  WHERE  NOT salary < ALL
  4  (SELECT salary
  5  FROM employees
  6  WHERE job_id = 'IT_PROG')
  7  AND job_id <> 'IT_PROG' and rownum<2;

EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        100 King                      AD_PRES         24000


13- Using NOT IN with subqueries with NULL values
I knew this but its nice to remind for the ones who doesnt know already. If you use NOT IN for a subquery with possible NULL clause it wont bring resultset because comparing null as you know equals to null. To workaround you can use NVL function.

Look what happens because of 2 NULL values


SQL> select count(*) from departments
  2  where department_id
  3  not in (select department_id
  4  from employees);

  COUNT(*)
----------
         0

SQL> select count(*) from employees
  2  where department_id is null;

  COUNT(*)
----------
         2
SQL>--change nulls with 999999

SQL> select count(*) from departments
  2  where department_id
  3  not in (select nvl(department_id,99999)
  4  from employees);

  COUNT(*)
----------
        16



14- Creating views with check option

To be honest I was totally unfamiliar to “with check option”. Document says; Specify WITH CHECK OPTION to indicate that Oracle Database prohibits any changes to the table or view that would produce rows that are not included in the subquery. When used in the subquery of a DML statement, you can specify this clause in a subquery in the FROM clause but not in subquery in the WHERE clause.


SQL> CREATE OR REPLACE VIEW empv
  2  AS SELECT *
  3  FROM employees
  4  WHERE department_id = 10
  5  WITH CHECK OPTION CONSTRAINT empv10_ck ;

View created.

SQL> update empv set department_id=15 where Last_name='Whalen';
update empv set department_id=15 where Last_name='Whalen'
       *
ERROR at line 1:
ORA-01402: view WITH CHECK OPTION where-clause violation

15- Sequences restart from MINVALUE(thanks Radino for the correction) with CYCLE option regardless from the START WITH value on the definition
I never used a sequence with cyle option before but I was thinking that it will re-use the already used ones when you recycle the values but it won’t. If you set MINVALUE it will use MINVALUE, not the START WITH value and if you dont set MINVALUE it will use 1 which is default MINVALUE like below.


SQL> CREATE SEQUENCE cos_seq
  2  INCREMENT BY 10
  3  START WITH 10
  4  MAXVALUE 30
  5  NOCACHE
  6  CYCLE;

Sequence created.

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        10

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        20

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        30

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
         1

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        11

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        21

SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
         1

16- NEXTVAL must be used before the initial usage of CURRVAL in a session.

Never tried, never got the error so never heard.


SQL> ---Brand New session
SQL> select cos_seq.currval from dual;
select cos_seq.currval from dual
       *
ERROR at line 1:
ORA-08002: sequence COS_SEQ.CURRVAL is not yet defined in this session


SQL> select cos_seq.nextval from dual;

   NEXTVAL
----------
        11
SQL> select cos_seq.currval from dual;

   CURRVAL
----------
        11

This is the end of PART-1. I hope I can complete the PART-2 of this series.

Warning: This post is not about what is being asked in Exam. It is about what I learned during my studies. If you want the questions just study and take the exam. Try to think what would you expect from a SQL Expert and study the things you think you would expect.

Update : Dont forget to check the links in Comment of Tonguc for better understanding of SQL Fundementals.

March 9, 2007

converting scn to a timestamp

Filed under: How To, PL/SQL — coskan @ 2:58 pm

I saw the nice function below while i was reading about locking and latches section of Mr Thomas Kytes Book (Expert Oracle Database Architecture 9i and 10g Programming Techniques and Solutions) and i said to myself i have to share it for the ones like me :)

If you know the SCN (system change number) you can get its timestamp value (within about +/–3 seconds) by the function scn_to_timestamp. After looking to the manual for more info i saw two other nice functions about scn. They are all under DBMS_FLASHBACK package and not available for the releases prior to 10g. I found these functions useful for dataguard issues recovery issues and flashback issues.

GET_SYSTEM_CHANGE_NUMBER: for getting the current system change number of the database.

SQL> select DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER from dual;

GET_SYSTEM_CHANGE_NUMBER
————————
884871

SCN_TO_TIMESTAMP: for converting given scn to timestamp value ;

SQL> select scn_to_timestamp(884871) as timestamp from dual;

TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000

TIMESTAMP_TO_SCN : For getting SCN equivalent of the given timestamp value. You must do to_timestamp convertion for the character value.

SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;

SCN
———-
845396

References Used:

Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solution Mr Thomas Kytes Book Pg 244

Oracle® Database PL/SQL Packages and Types Reference
10g Release 2 (10.2)

The Silver is the New Black Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 203 other followers