Laurent Schneider

Oracle Certification Program: Exam Details

I immediately registered for this exam and will do it next week, I just cannot wait for this one…

I do update t set x=:a;

If I do it twice, I am doing a lot of unnecessary updates. This is true in an update, and also in the update clause of a MERGE.

I need to take care of null, I can update null with something, or something with null, but update null with null is also unnecessary.


SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.34
SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.32
SQL> update t set x=:a
2 where x!=:a
3 or (x is null and :a is not null)
4 or (x is not null and :a is null);

0 rows updated.

Elapsed: 00:00:00.04


I have read an excellent overview of DBMS_APPLICATION_INFO in Andy Campbell blog on If only…

I have written a procedure P which is executing 5 statements. I want to add the name of the procedure P as a target. I do not want to hardcode the name however… So I can use $$PLSQL_UNIT in 10gR2 (documented in Using Predefined Inquiry Directives With Conditional Compilation)

Ok, here I go

create or replace procedure p is
rindex binary_integer;
slno binary_integer;
sofar number;
totalwork number;
target number;
begin
totalwork := 5;
sofar := 0;
select object_id
into target
from user_objects
where object_name = $$PLSQL_UNIT;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
-- do something
sofar:=sofar+1;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(
rindex,slno,'Executing...',target,null,
sofar,totalwork,null,'Statement');
end;
/

Now I execute it

SQL> exec p

PL/SQL procedure successfully completed.


and monitor it

SQL> select sid,opname,target,sofar,totalwork,units
2 from v$session_longops;
SID OPNAME TARGET SOFAR TOTALWORK UNITS
---- ------------ -------- ----- --------- ---------
538 Executing... SCOTT.P 5 5 Statement


I have attend the RAC beta exam this afternoon. 181 questions in 3.5 hours, it is a lot of questions! I prepared by reading the 2-day dba RAC document. There are also some questions about Maximum Availability Architecture (MAA) with physical and logical standby. Also some question which are Linux speci

I have been short on time, the question were very long to read, and 181 questions in 210 minutes, I did not have the time to review all.

Now I must wait about 10 weeks to get the result, but I am quite optimistic 😎

watch the launch event live
http://www.oracle.com/pls/ebn/live_viewer.main?p_shows_id=5717957

How can I read user input in plsql?

kind of

begin
write('Enter a value for x : ');
read(x);
write('you enterred '||x);
end;
/


the short answer is : you cannot do that.

Ok, let’s try to do it in Linux !


$ cat interactiveplsql.sql
set feedb off

create or replace directory tmp as '/tmp';

declare
inFile utl_file.file_type;
outFile utl_file.file_type;
x varchar2(40);
begin
inFile := utl_file.fopen('TMP','in','R');
outFile := utl_file.fopen('TMP','out','W');
utl_file.put_line(outFile,'Enter a value for x : ');
utl_file.fflush(outFile);
utl_file.get_line(inFile,x);
utl_file.put_line(outFile,'you enterred '||x);
utl_file.fclose(inFile);
utl_file.fclose(outFile);
end;
/

quit

$ mknod /tmp/out p; mknod /tmp/in p
$ (cat /tmp/out &);(sqlplus -s scott/tiger @interactiveplsql &
);cat>/tmp/in
Enter a value for x :
ABC123
you enterred ABC123



SQL> create or replace type t is
2 table of varchar2(12);
3 /
Type created.

SQL> create or replace function f return t is
2 begin return t('foo'); end;
3 /
Function created.

SQL> select * from table(f);

COLUMN_VALUE
------------
foo

What is this column_value field? It is a pseudo-column. But you may want to have an user-defined column name.


SQL> create or replace type o is
2 object(BAR varchar2(12));
3 /
Type created.

SQL> create or replace type t is
2 table of o;
3 /
Type created.

SQL> create or replace function f return t is
2 begin return t(o('foo')); end;
3 /
Function created.
SQL> select * from table(f);

BAR
------------
foo


Lutz announced his 11g courses in Switzerland in this postOracle University Switzerland announces brand new Oracle 11g courses

warning, this test is cpu intensive, do not try on your productive server

One user on the developpez.net French forums asked today about rewritting a division in a multiplication for tuning. Like select avg(sal)/2 from emp; in select avg(sal)*.5 from emp;.

Well, I had to test this ! I execute 41055 divisions in a plsql loop. To avoid incrementation, I divide by 1.014… and multiply by 0.986… in a way that the result keep the same all over the loop.

SQL>

SQL> var z number

SQL> var y number

SQL> exec :z := power(2,102)*2e-31;

SQL> exec :y := 1e125;

SQL> set timi on

SQL> exec while (:y>1e-125) loop :y:=:y/:z; end loop

Elapsed: 00:00:00.42

SQL> set timi off

SQL> print y

                               Y

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

9.9879215917842541374103299E-126
SQL> exec :z := power(2,-104)*2e31;

SQL> exec :y := 1e125;

SQL> set timi on

SQL> exec while (:y<1e-125) loop :y:=:y*:z; end loop
Elapsed: 00:00:00.28
SQL> set timi off

SQL> print y
                               Y

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

9.9879215917842541374103299E-126

It has been difficult to find an example with clear difference and not too weird :twisted:
The operation divide is slower than multiply, probably to catch divide by zero errors...
		

With dbca you can fasten the procedure of creating databases in your company. You can also run this in silent mode and create exactly the database you want, with your redo/undo size, with your parameters settings. You create the template once, and use it many times. Fast and easy 😀

I have one database which I created with SQL*PLUS called LSC01.

1) Create template lsc-template
dbca
–> Manage Templates
–> Create a database template
–> from an existing database (structure as well as data)
–> LSC01
–> lsc-template
–> convert the file locations to use OFA structure
This takes some place on disk and will speed up database creation. Technically speaking, it is doing a compressed backup with RMAN that will be restore, and restore is way faster than create database

2) Create database LSC99 in silent mode or progressOnly mode.
dbca -silent -createDatabase -templateName lsc-template.dbc -gdbName LSC99.lcsys.ch

It took me only two minutes to create my database on my notebook !

Try it ! Of course I expect comments on this post

Lazyness at the development can have dramatic costs in production and maintenance. I want to summarize why and where you should always qualify your columns.

Why? when you select or modify data from a table, you must qualify the columns you are using so if the order of the column change, or if one column is added, renamed or removed, so long this column is not related to your query your code should keep working. A typical example is when the dba add a LAST_MODIFICATION_DATE and a trigger to automatically fill that column for auditing purpose. It should never make your application fail.

0) sample table

create table t1(x number, y number);
create table t2(a number, b number);
create table t3(x number, z number);

1) insert into table
bad:insert into t1 values(1,2);
good:insert into t1(x,y) values(1,2);

bad:insert into t1
select * from t2;
good:insert into t1(x,y)
select t2.a,t2.b from t2;

bad:insert all
when a>0 then into t1
select * from t2;
good:insert all
when a>0 then into t1(x,y)
select t2.a,t2.b from t2;

2) merge
bad:merge into t1
using t2 on (x=a)
when matched then
update set y=b
where (b>y)
delete
where (b>y)
when not matched then
insert values(a,b)
where (b>0);
good:merge into t1
using t2 on (t1.x=t2.a)
when matched then
update set y=t2.b
where (t2.b>t1.y)
delete
where (t2.b>t1.y)
when not matched then
insert (x,y) values(t2.a,t2.b)
where (t2.b>0);

Not prefixing the column here will bug as soon as a column a or b is added to table t1

2) joins

just never use natural join in production

bad:select *
from t1 natural join t3;
good:select x, t1.y, t3.z
from t1 join t3 using (x);

bad:select *
from t1,t2
where x=a;
good:select t1.x,t1.y,t2.a,t2.b
from t1,t2
where t1.x=t2.a;

It is not about good looking code, it is about data quality and stability 😎

I just read on the General Notes For Oracle Database – Enterprise Edition in Metalink that 10.1.0.5 is the terminal patchset for 10gR1

SQL*Plus is a not only the command-line interface to the database server, it is also a featured reporting tool with paging capabilities. The pagesize is the number of rows of one page. The default is 14 and the maximum is 50000. One of the common property of the page is the headers when selecting from a table.


SQL> sho pages
pagesize 14
SQL> select empno,ename from emp;

EMPNO ENAME
---------- ----------
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS

EMPNO ENAME
---------- ----------
7900 JAMES
7902 FORD
7934 MILLER

14 rows selected.


This is rather an annoying effect of the default setting than a feature and there is no set pagesize unlimited. The only way to have the header only once, is to set the pagesize to the maximum or use this trick : set pages 0 emb on newp none. Unfortunately, the later does not work for HTML reporting.

One of the less known and advanced sql*plus capabilities is the title of the page.

Today on the developpez.net forums I had a question about generating a describe for each table in user_tables.

The short answer is select * from user_tab_columns

In my output, I want to have each table on a separate page, with the table_name in the title, and the column names, not null options and datatypes in the page.

First I want to have a page per table, this I can define with break

bre on table_name ski page


I want to have the table name in the title of the page, but not as a column. I add some blank lines in the top title and in the bottom title.


col table_name new_v table_name nopri
tti le table_name s 2
bti s 1


Finally I set the pagesize to something bigger than the maximum number of columns of tables plus 6 for header and title

set pages 0
col pages new_v pages nopri
select max(count(*))+6 pages
from user_tab_columns
where table_name in (‘EMP’,’DEPT’,’T’)
group by table_name;
set pages &pages

Now the select

select
table_name,
column_name “Name”,
decode(nullable,’N’,’NOT NULL’) “Null?”,
DATA_TYPE||
case when DATA_TYPE in (‘NUMBER’,’FLOAT’)
and (data_precision is not null
or data_scale is not null) then
‘(‘||nvl(DATA_precision,38)||
case when data_scale!=0 then
‘,’||DATA_SCALE
end
||’)’
when data_type like ‘%CHAR%’ then
‘(‘||DATA_LENGTH||’)’ end
“Type”
from user_tab_columns
where table_name in (‘EMP’,’DEPT’,’T’);

DEPT

Name                           Null?    Type
------------------------------ -------- --------------------
DEPTNO                         NOT NULL NUMBER(2)
DNAME                                   VARCHAR2(14)
LOC                                     VARCHAR2(13)






EMP

Name                           Null?    Type
------------------------------ -------- --------------------
EMPNO                          NOT NULL NUMBER(4)
ENAME                                   VARCHAR2(10)
JOB                                     VARCHAR2(9)
MGR                                     NUMBER(4)
HIREDATE                                DATE
SAL                                     NUMBER(7,2)
COMM                                    NUMBER(7,2)
DEPTNO                                  NUMBER(2)

For a HTML report, set mark html on

set mark html on
/


DEPT

Name	Null?	Type
DEPTNO	NOT NULL	NUMBER(2)
DNAME		VARCHAR2(14)
LOC		VARCHAR2(13)

 
 

EMP

Name	Null?	Type
EMPNO	NOT NULL	NUMBER(4)
ENAME		VARCHAR2(10)
JOB		VARCHAR2(9)
MGR		NUMBER(4)
HIREDATE		DATE
SAL		NUMBER(7,2)
COMM		NUMBER(7,2)
DEPTNO		NUMBER(2)

I have seen a confusion between sequence and system change number quite often.

The sequence is a fairly little number which is the number of log switch since database creation (it can be resetted by open resetlogs).

The thread is only relevant in RAC. In single-instance database, it is always 1.

You can find the current sequence number with the following query

select sequence#,thread#
from v$log
where status='CURRENT';


The system change number (SCN) is a much higher number, which is continously increasing, even when you do nothing. The dbms_flashback package has a function to return the current system change number :


select dbms_flashback.get_system_change_number
from dual;


In 10g, there is a standard function to get the current system change number

select timestamp_to_scn(current_timestamp) from dual;


Before a major application/database upgrade, it is good practice to make a backup and write down the SCN for an easier recovery procedure.

read my comment about non-accuracy of timestamp_to_scn

This is one question I solved today with XML on the developpez.net/forums

I have a table T

Un,Trois,Cinq,Six

Un,Deux,Quatre

Trois

Sept,Huit

Un,Six

I want to select un,deux,trois,quatre,cinq,six,sept,huit.

I could well have written a plsql function like

create or replace type c as object (value varchar2(40));
/
create or replace type t_c as table of c;
/
sho err
create or replace function f(
list varchar2,
sep varchar2 default ',')
return t_c pipelined is
i number:=1;
begin
loop
if (instr(list,sep,1,i)>0)
then
if (i=1) then
pipe row(c(substr(list,
1, instr(list,sep)-1)));
else
pipe row(c(substr(list,
instr(list,sep,1,i-1)+1,
instr(list,sep,1,i)-
instr(list,sep,1,i-1)-1)));
end if;
else
if (i=1) then
pipe row(c(list));
else
pipe row(c(substr(list,
instr(list,sep,1,i-1)+1)));
end if;
return;
end if;
i:=i+1;
end loop;
end;
/
select distinct value
from t,
table(f(c))
;


or anything using recursion or whatever.

I just tried with XML

SQL> select distinct extractvalue(column_value,’/x’)
2 from t,
3 table(xmlsequence(extract(xmltype(
4 ‘<list><x>’||replace(
5 c,’,’,'</x><x>’)||
6 ‘</x></list>’),
7 ‘/list/x’)));

EXTRACTVALUE(COLUMN_VALUE,’/X’)
——————————-
Trois
Un
Quatre
Cinq
Six
Deux
Sept
Huit

8 rows selected.

do not miss yas comment !

tested in 10.2


create or replace procedure
explain_plan(hash_value number)
is begin
insert into plan_table
select
null,
(select nvl(max(plan_id),0)+1 from plan_table),
timestamp,
remarks,
operation,
options,
object_node,
object_owner,
object_name,
object_alias,
null,
object_type,
optimizer,
search_columns,
id,
parent_id,
depth,
position,
cost,
cardinality,
bytes,
other_tag,
partition_start,
partition_stop,
partition_id,
other,
other_xml,
distribution,
cpu_cost,
io_cost,
temp_space,
access_predicates,
filter_predicates,
projection,
time,
qblock_name
from v$sql_plan
where hash_value=explain_plan.hash_value;
for f in (
select PLAN_TABLE_OUTPUT from table(dbms_xplan.DISPLAY))
loop
dbms_output.put_line(f.PLAN_TABLE_OUTPUT);
end loop;
end;
/

SQL> set lin 200 trims on pages 0 serverout on
SQL> select ename from emp where empno=7788;
ENAME
----------
SCOTT

SQL> select hash_value from v$sql where sql_text like
2 'select ename from emp where empno=7788%';
HASH_VALUE
----------
1019401098

SQL> exec explain_plan(1019401098)
Plan hash value: 4066871323
------------------------------------------------------
| Id | Operation | Name | Rows |
------------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 |
|* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 |
------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("EMPNO"=7788)

PL/SQL procedure successfully completed.


Views:

SQL> create view v1 as select * from dual;

View created.

SQL> rename v1 to v2;

Table renamed.

Tables:

SQL> create table t1 as select * from dual;

Table created.

SQL> rename t1 to t2;

Table renamed.

Sequences:

SQL> create sequence s1;

Sequence created.

SQL> rename s1 to s2;

Table renamed.

Synonyms:

SQL> create synonym syn1 for dual;

Synonym created.

SQL> rename syn1 to syn2;

Table renamed.


Constraints :

SQL> create table t(x number not null);

Table created.

SQL> select constraint_name
2 from user_constraints
3 where table_name='T';

CONSTRAINT_NAME
------------------------------
SYS_C0018686

SQL> alter table t rename constraint SYS_C0018682 to X_NOT_NULL;

Table altered.


Indexes :

SQL> create index i1 on t(x);

Index created.

SQL> alter index i1 rename to i2;

Index altered.


Columns :

SQL> alter table t rename column x to y;

Table altered.


Partitions :

SQL> create table t (x number, y number)
2 partition by range(x)
3 subpartition by hash(y) (
4 partition p1 values less than(1)
5 (subpartition sp1)
6 ) ;

Table created.

SQL> alter table t rename partition p1 to p2;

Table altered.


Subpartitions:

SQL> alter table t rename subpartition sp1 to sp2;

Table altered.


Triggers:

SQL> create trigger tr1 after update on t
2 begin null; end;
3 /

Trigger created.

SQL>
SQL> alter trigger tr1 rename to tr2;

Trigger altered.


For the dba, you can also rename datafile, logfile and even tablespaces (10g)

On the developpez.net forums I answered a question about referential integrity. How can you delete/update a parent row when the child exist ?


SQL> create table continent(
2 name varchar2(10),
3 constraint continent_pk primary key(name));

Table created.

SQL> create table country(
2 name varchar2(10),
3 continent varchar2(10),
4 constraint country_pk
5 primary key(name),
6 constraint country_continent_fk
7 foreign key(continent) references continent);

Table created.

SQL> insert into continent values('Africa');

1 row created.

SQL> insert into country values('Benin', 'Africa');

1 row created.

CONTINENT

NAME
Africa

COUNTRY

NAME	CONTINENT
Benin	Africa

So in this default constellation, I cannot delete a parent row where the child exist.


SQL> delete continent where name='Africa';
delete continent where name='Africa'
*
ERROR at line 1:
ORA-02292: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK)
violated - child record found


I could specify CASCADE to delete the child rows automatically

SQL> alter table country
2 drop constraint country_continent_fk;

Table altered.

SQL> alter table country
2 add constraint country_continent_fk
3 foreign key(continent)
4 references continent
5 on delete cascade;

Table altered.

SQL> delete continent where name='Africa';

1 row deleted.


CONTINENT

NAME

COUNTRY

NAME	CONTINENT

but this is dangerous. When I read 1 row deleted., I am not informed that I have deleted rows in the child table.

I could rather set the column to null


SQL> rollback;

Rollback complete.

SQL> alter table country
2 drop constraint country_continent_fk;

Table altered.

SQL> alter table country
2 add constraint country_continent_fk
3 foreign key(continent)
4 references continent
5 on delete set null;

Table altered.

SQL> delete continent where name='Africa';

1 row deleted.


CONTINENT

NAME

COUNTRY

NAME	CONTINENT
Benin

Ok, but what If I need to rename Africa to EMEA ? I can do this neither on the child nor on the parent


SQL> rollback;

Rollback complete.

SQL>
SQL> update continent
2 set name='EMEA'
3 where name='Africa';
update continent
*
ERROR at line 1:
ORA-02292: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK)
violated - child record found

SQL> update country
2 set continent ='EMEA'
3 where continent ='Africa';
update country
*
ERROR at line 1:
ORA-02291: integrity constraint (AUDBA.COUNTRY_CONTINENT_FK)
violated - parent key not found


So I could defer the constraint validation to the commit

SQL> alter table country
2 drop constraint country_continent_fk;

Table altered.

SQL> alter table country
2 add constraint country_continent_fk
3 foreign key(continent)
4 references continent
5 deferrable initially deferred;

Table altered.

SQL> update continent
2 set name='EMEA'
3 where name='Africa';

1 row updated.

SQL> update country
2 set continent='EMEA'
3 where continent ='Africa';

1 row updated.

SQL> commit;

Commit complete.


CONTINENT

NAME
EMEA

COUNTRY

NAME	CONTINENT
Benin	EMEA

You can use triggers too, but constraints are more efficient than triggers.

the following works only on 10gR2

Table PRODUCTCATEGORY:

CATEGORY (PK)
Fruit
Fish

Table TMP_PRODUCT:

PRODUCT	CATEGORY
Banana	Fruit
Chicken	Meat

Table PRODUCT:

PRODUCT (PK)	CATEGORY (FK)

Now I want to load product


SQL> insert into product select * from tmp_product;
insert into product select * from tmp_product
*
ERROR at line 1:
ORA-02291: integrity constraint (AUDBA.SYS_C0016368)
violated - parent key not found


Well, there is a missing category. I will fix it later. I want to load the valid data now.


SQL> exec dbms_errlog.create_error_log('PRODUCT')

PL/SQL procedure successfully completed.

SQL> insert into product select * from tmp_product
2 log errors reject limit unlimited;

1 row created.


Table PRODUCT:

PRODUCT (PK)	CATEGORY (FK)
Banana	Fruit

Now I have successfully loaded my valid data. I need to fix the problem. Let’s identify the offending row :


SQL> select ORA_ERR_OPTYP$,ORA_ERR_MESG$,CATEGORY
2 from err$_product;

ORA_ERR_OPTYP$ ORA_ERR_MESG$ CATEGORY
-------------- -------------------------------- --------
I ORA-02291: integrity constraint Meat
(AUDBA.SYS_C0016368) violated -
parent key not found

1 row selected.


Let’s create the missing category manually


SQL> insert into productcategory(category) values ('Meat');

1 row created.

SQL> insert into product
2 select * from tmp_product
3 where category='Meat';

1 row created.


Table PRODUCTCATEGORY:

CATEGORY (PK)
Fruit
Fish
Meat

Table PRODUCT:

PRODUCT (PK)	CATEGORY (FK)
Banana	Fruit
Chicken	Meat

With merge you can insert, update or both

I want to import changes from one table in another table.

Table T1:

ID	NAME
1	Apple
2	Orange

Table T2:

ID	NAME
1	Pineapple
3	Lemon

To insert the missing row from T2 into T1, I could need the following subquery with INSERT.
insert into t1
select * from t2
where not exists (
select * from t1
where t1.id=t2.id);
The same code would be done in Merge with
merge into t1 using t2
on (t1.id=t2.id)
when not matched then
insert values(t2.id,t2.name);

And what about updating the T1 table with the T2 values
update t1
set (name)=(select name from t2 where id=t1.id)
where id in (select id from t2);
with merge :
merge into t1 using t2 on (t1.id=t2.id)
when matched then
update set name=t2.name;

Quite nice, is not it! I find this syntax very intuitive.

Apart from syntax looking, Merge can also combine insert and update in once, which make it more stable (only one statement) and more performant (table t2 is read only once).
merge into t1 using t2 on (t1.id=t2.id)
when not matched then
insert values(t2.id,t2.name)
when matched then
update set name=t2.name;

ID	NAME
1	Pineapple
2	Orange
3	Lemon

One more neat solution from Michaels about exporting the data to Excel in this otn post


set feed off markup html on spool on
alter session set nls_date_format='YYYY-MM-DD';
spool emp.xls
select * from emp;
spool off
set markup html off spool off


And it perfectly opens in Excel. No hassle with separator, no time lost in defining the column length, no bizarre xml format. Plain html, that is cool!

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980-12-17	800		20
7499	ALLEN	SALESMAN	7698	1981-02-20	1600	300	30
7521	WARD	SALESMAN	7698	1981-02-22	1250	500	30
7566	JONES	MANAGER	7839	1981-04-02	2975		20
7654	MARTIN	SALESMAN	7698	1981-09-28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981-05-01	2850		30
7782	CLARK	MANAGER	7839	1981-06-09	2450		10
7788	SCOTT	ANALYST	7566	1987-04-19	3000		20
7839	KING	PRESIDENT		1981-11-17	5000		10
7844	TURNER	SALESMAN	7698	1981-09-08	1500	0	30
7876	ADAMS	CLERK	7788	1987-05-23	1100		20
7900	JAMES	CLERK	7698	1981-12-03	950		30
7902	FORD	ANALYST	7566	1981-12-03	3000		20
7934	MILLER	CLERK	7782	1982-01-23	1300		10

Simply great 😛

There were a similar question in the otn forums today. Ok, when I have to run a script in production, the operators complain about errors like ORA-00942 table or view does not exist. Of course I can provide some documentation to explain what can be ignored, but then they then tend to ignore all ORA errors.

A script to create table t will drop table t if existant. There is no CREATE OR REPLACE TABLE command. So I will simply check the dictionary and drop only if existant.

exec for f in (select 1 from user_tables where
table_name='T') loop execute immediate
'drop table t cascade constraints'; end loop
create table t(x number);


For context, directory, function, indextype (9iR1), java, library, operator, outline, package, procedure, synonym (9iR2), trigger, type, view, it is possible to use the create or replace syntax.

create or replace public synonym x for y;

For indexes (in case they are not dropped with the table)

exec for f in (select 1 from user_indexes where
index_name='I') loop execute immediate
'drop index i'; end loop
create index i on t(x);


For sequences

exec for f in (select 1 from user_sequences where
sequence_name='S') loop execute immediate
'drop sequence s'; end loop
create sequence s;


Let’s try

SQL> exec for f in (select 1 from user_tables where tabl

PL/SQL procedure successfully completed.

SQL> create table t(x number);

Table created.

SQL> exec for f in (select 1 from user_indexes where ind

PL/SQL procedure successfully completed.

SQL> create index i on t(x);

Index created.

SQL> exec for f in (select 1 from user_sequences where s

PL/SQL procedure successfully completed.

SQL> create sequence s;

Sequence created.

End of complaints from production people

write to a clob

SQL> var x clob
SQL> begin
2 dbms_lob.createtemporary(:x,true);
3 dbms_lob.writeappend(:x,12,'Hello World!');
4 end;
5 /

PL/SQL procedure successfully completed.

SQL> print x

X
--------------------------------------------------
Hello World!


read from a clob

SQL> var c varchar2(10)
SQL> var n number
SQL> exec :n := 5 /* read 5 characters, if possible */

PL/SQL procedure successfully completed.

SQL> begin
2 dbms_lob.open(:x,dbms_lob.lob_readonly);
3 dbms_lob.read(:x,:n,7 /*position*/,:c);
4 dbms_lob.close(:x);
5 end;
6 /

PL/SQL procedure successfully completed.

SQL> print c

C
--------------------------------
World


dbms_lob.createtemporary(:x,true); caches the lob. If the lob is large (a large object is often large), it may use a lot of memory, use cache=false for slower performance but more economic memory consumption

In part 1, I tried with pure SQL+XML, but empty columns were missing. In Part 2, I had a mix between PL/SQL and XML functions. Now I would like to publish the solution Vadim Tropashko posted in the OTN Forums. It is a NO XML pipelined function. I did a bit formatting in it. And I pipe clobs.

Update: I added the column header


create or replace type strings as table of clob;
/

create or replace function CSV(txt varchar2)
return strings pipelined is
tmp clob;
curid NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER;
namevar VARCHAR2(4000);
begin
curid := dbms_sql.open_cursor;
dbms_sql.parse(curid, txt, dbms_sql.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
FOR i IN 1 .. colcnt LOOP
DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
END LOOP;
if DBMS_SQL.execute(curid) = 0 THEN
dbms_lob.createtemporary(tmp, true);
FOR i IN 1 .. colcnt LOOP
if (i>1) then
dbms_lob.writeappend(tmp, 1, ';');
end if;
dbms_lob.writeappend(tmp,
length(desctab(i).col_name),
desctab(i).col_name);
END LOOP;
pipe row(tmp);
dbms_lob.freetemporary(tmp);
WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
dbms_lob.createtemporary(tmp, true);
FOR i IN 1 .. colcnt LOOP
if (i>1) then
dbms_lob.writeappend(tmp, 1, ';');
end if;
DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
if (namevar is not null)
then
dbms_lob.writeappend(tmp, length(namevar),
namevar);
end if;
END LOOP;
pipe row(tmp);
dbms_lob.freetemporary(tmp);
END LOOP;
END IF;
DBMS_SQL.CLOSE_CURSOR(curid);
end;
/

select * from table(csv('select * from emp'));

EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO
7369;SMITH;CLERK;7902;17-DEC-80;800;;20
7499;ALLEN;SALESMAN;7698;20-FEB-81;1600;300;30
7521;WARD;SALESMAN;7698;22-FEB-81;1250;500;30
7566;JONES;MANAGER;7839;02-APR-81;2975;;20
7654;MARTIN;SALESMAN;7698;28-SEP-81;1250;1400;30
7698;BLAKE;MANAGER;7839;01-MAY-81;2850;;30
7782;CLARK;MANAGER;7839;09-JUN-81;2450;;10
7788;SCOTT;ANALYST;7566;19-APR-87;3000;;20
7839;KING;PRESIDENT;;17-NOV-81;5000;;10
7844;TURNER;SALESMAN;7698;08-SEP-81;1500;0;30
7876;ADAMS;CLERK;7788;23-MAY-87;1100;;20
7900;JAMES;CLERK;7698;03-DEC-81;950;;30
7902;FORD;ANALYST;7566;03-DEC-81;3000;;20
7934;MILLER;CLERK;7782;23-JAN-82;1300;;10


If you want to be the very first Application Server OCP you can register today on 2test.com for the 1Z1-312 beta exam. The first possible date seems to be May 29th, 2007

Special thanks to Tom for pointing and Michaels for fixing the missing manager of King in my previous post : csv format with select

Ok, here is a my PL/SQL table function.

create or replace type tv as table of
varchar2(4000);
/

create or replace function CSV(sqlQuery varchar2)
return tv pipelined is
ctx dbms_xmlgen.ctxhandle;
begin
ctx:=dbms_xmlgen.newcontext(sqlquery);
dbms_xmlgen.setnullhandling(ctx,dbms_xmlgen.empty_tag);
for f in (select
cast(
xmltransform(column_value,
XMLTYPE(‘<xsl:stylesheet version=”1.0″ xmlns:xsl’||
‘=”http://www.w3.org/1999/XSL/Transform”>’||
‘<xsl:output method=”text”/><xsl:variable name’||
‘=”new_line” select=””&#xA;”” /><xsl:template’||
‘ match=”ROWSET”><xsl:apply-templates select=”‘||
‘ROW”/></xsl:template><xsl:template match=”ROW”‘||
‘><xsl:for-each select=”*”><xsl:value-of select’||
‘=”.”/><xsl:if test=”position() != last()”><xsl’||
‘:value-of select=””,””/></xsl:if></xsl:for-‘||
‘each><xsl:value-of select=”$new_line” /></xsl:’||
‘template></xsl:stylesheet>’
)) as varchar2(4000))
c from table(xmlsequence(dbms_xmlgen.getxmltype(ctx)
.extract(‘ROWSET/ROW’))))
loop
pipe row(f.c);
end loop;
end;
/

select * from table(csv(‘select * from emp’));
COLUMN_VALUE
————————————————
7369,SMITH,CLERK,7902,17-DEC-80,800,,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
7900,JAMES,CLERK,7698,03-DEC-81,950,,30
7902,FORD,ANALYST,7566,03-DEC-81,3000,,20
7934,MILLER,CLERK,7782,23-JAN-82,1300,,10

I have been waiting for three years for the OCM 10g upgrade certification. Still waiting…
OCM DBA 10g Upgrade

Gavin just posted about the Oracle Expert Program

The beta phase has started for 1Z1-046 Managing Oracle on Linux for DBAs and 1Z1-048 Administering Real Application Clusters. Those exams will lead to Linux and RAC Certified Expert certifications.

Beta exam 1Z1-312 Oracle Application Server 10g: Administration II has not started yet. This exam will lead to Oracle Application Server OCP certification.

I have never taken a beta exam. They are quite long (3.5 hours), and you have to wait a few months to get your score. Since the OCM DBA 10g has not been released, I may well go for the RAC Expert exam if I have the time in June.

References :
Oracle Certification Program Beta Exams
Expert Program Page

One more trick with xml.

I want to get a semi-column separated format without having to specify the columns

alter session set nls_date_format=’YYYY-MM-DD’;

Session altered.

select regexp_replace(column_value,’ *<[^>]*>[^>]*>’,’;’)
from table(xmlsequence(cursor(select * from emp)));

;7369;SMITH;CLERK;7902;1980-12-17;800;20;
;7499;ALLEN;SALESMAN;7698;1981-02-20;1600;300;30;
;7521;WARD;SALESMAN;7698;1981-02-22;1250;500;30;
;7566;JONES;MANAGER;7839;1981-04-02;2975;20;
;7654;MARTIN;SALESMAN;7698;1981-09-28;1250;1400;30;
;7698;BLAKE;MANAGER;7839;1981-05-01;2850;30;
;7782;CLARK;MANAGER;7839;1981-06-09;2450;10;
;7788;SCOTT;ANALYST;7566;1987-04-19;3000;20;
;7839;KING;PRESIDENT;1981-11-17;5000;10;
;7844;TURNER;SALESMAN;7698;1981-09-08;1500;0;30;
;7876;ADAMS;CLERK;7788;1987-05-23;1100;20;
;7900;JAMES;CLERK;7698;1981-12-03;950;30;
;7902;FORD;ANALYST;7566;1981-12-03;3000;20;
;7934;MILLER;CLERK;7782;1982-01-23;1300;10;

When you see something like

select * from t where x = :1

you may wonder what is :1

Ok, here is a quick join I tested in 10gR2

SQL> var y varchar2(255)
SQL> exec :y:='SCOTT'

PL/SQL procedure successfully completed.

SQL> select job from emp where ename=:y;
JOB
---------
ANALYST

SQL> select sql_text,name,value_string,datatype_string
  2  from v$sql_bind_capture join v$sql using (hash_value)
  3  where sql_text like
  4    'select job from emp where ename=:y%';
SQL_TEXT                              NAME VALUE DATATYPE_STRING
------------------------------------- ---- ----- ---------------
select job from emp where ename=:y    :Y   SCOTT VARCHAR2(2000)

I am data-modeling those days.

Each table has it own description table (yes, it is Java). To keep it simple, FRUIT[id,description] and MEAT[id,description] (35 of them right now). There must be one table which contains all descriptions in it GLOBAL[type,id,description]. I wish to preserve the data quality. Both the little (FRUIT,MEAT) and the big (GLOBAL) must be selectable and editable. Someone, the little ones are views of the big one, or the big one is a view of the little ones.

Plan 1: there is one big table and many small views

create table global(
type varchar2(15),
id number,
description varchar2(10),
primary key(type,id));
create or replace view fruit as
select id,description
from global
where type='fruit';
create or replace view meat as
select id,description
from global
where type='meat';


plan 2: one view which union all all little tables

create table fruit(
id number,
description varchar2(10),
primary key(id));
create table meat(
id number,
description varchar2(10),
primary key(id));
create or replace view global as
select 'fruit' type,id,description
from fruit
union all
select 'meat',id,description
from meat;


Is this fine? As long as I only select rows, both are possible. However, a select * from fruit will take much longer if fruit is a view and the rows are retrieved from a huge table, probably per Index scan.

In the solution 2, I keep small lookup tables, and it should be faster to retrieve data from those. However, the UNION ALL view is not updatable.

In solution 1, the small views seem updatable, but it will not work.

Let’s see

SQL> create table global(
2 type varchar2(15),
3 id number,
4 description varchar2(10),
5 primary key(type,id));

Table created.

SQL> create or replace view fruit as
2 select id,
3 description
4 from global
5 where type='fruit';

View created.

SQL> insert into fruit values(1,'Mango');
insert into fruit values(1,'Mango')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("AUDBA"."GLOBAL"."TYPE")

I cannot tell the view to default the type to fruit when inserting in fruit

In plan 2, the view is not updatable at all

SQL> create table fruit(
2 id number,
3 description varchar2(10),
4 primary key(id));

Table created.

SQL> create table meat(
2 id number,
3 description varchar2(10),
4 primary key(id));

Table created.

SQL> create or replace view global as
2 select 'fruit' type,id,description
3 from fruit
4 union all
5 select 'meat',id,description
6 from meat;

View created.

SQL> insert into global values(
2 'meat',1,'beef');
insert into global values(
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


So the solution is called INSTEAD OF trigger. An instead of trigger is a very special trigger specific to views. It will make all views updatable, how magic!

As a DBA, I am not very confident with triggers, because the developer is doing the consistency check, no more the Oracle Engine. It will therefore be much less performant and it may contain errors. Here I write a trigger for each case. They certainly have bugs. I am myself not convinced by using triggers at all. Those kind of DML home-made rewrites are just too dangerous, but what else can I do…

OK, plan 1: one instead of trigger for every single view

create or replace trigger fruit_insteadof
instead of insert or update or delete on fruit
begin
if inserting then
insert into global
values('fruit',:new.id,:new.description);
elsif deleting then
delete from global
where type='fruit'
and id=:old.id
and description=:old.description;
elsif updating then
update global
set type='fruit',
id=:new.id,
description=:new.description
where type='fruit'
and id=:old.id
and description=:old.description;
end if;
end;
/


Plan 2: One instead of trigger for the global view
create or replace trigger global_insteadof
instead of insert or update or delete on global
begin
if inserting then
if (:new.type='fruit') then
insert into fruit values(
:new.id,:new.description);
elsif (:new.type='meat') then
insert into meat values(
:new.id,:new.description);
else
raise_application_error(-20001,
'Trigger cannot insert into '||:new.type);
end if;
elsif deleting then
if (:old.type='fruit') then
delete fruit where
id=:old.id
and description=:old.description;
elsif (:old.type='meat') then
delete meat where
id=:old.id
and description=:old.description;
else
raise_application_error(-20001,
'Trigger cannot delete from '
|| :old.type);
end if;
elsif updating then
if (:new.type!=:old.type) then
delete from global
where type=:old.type
and id=:old.id
and description=:old.description;
insert into global values(
:new.type,:new.id,:new.description);
elsif (:new.type='fruit') then
update fruit set
id=:new.id,
description=:new.description
where id=:old.id
and description=:old.description;
elsif (:new.type='meat') then
update meat set
id=:new.id,
description=:new.description
where id=:old.id
and description=:old.description;
else
raise_application_error(-20001,
'Trigger cannot update '||:old.type);
end if;
end if;
end;


I am not at all convinced about the data consistency in such an approach.

Well, I will try to push a solution with no GLOBAL table. We probably do not need it.

How do i store the counts of all tables …

My answer to the question above using dbms_xmlgen

SQL> select
  2    table_name,
  3    to_number(
  4      extractvalue(
  5        xmltype(
  6 dbms_xmlgen.getxml('select count(*) c from '||table_name))
  7        ,'/ROWSET/ROW/C')) count
  8  from user_tables;

TABLE_NAME                      COUNT
------------------------------ ------
DEPT                                4
EMP                                14
BONUS                               0
SALGRADE                            5