ORA-01466: unable to read data – table definition has changed

I re-edited this post and it is unresolved yet. I thought it was related to system time, but apparently not 😮


SQL> create table t(x number);

Table created.

SQL> set transaction read only ;

Transaction set.

SQL> select * from t;
select * from t
*
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

If I wait one minute after my create table statement, it works


SQL> drop table t;

Table dropped.

SQL> create table t(x number);

Table created.

SQL> host sleep 60

SQL> set transaction read only;

Transaction set.

SQL> select * from t;

no rows selected

😈

avoid unnecessary updates

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

v$session_longops in 10gR2

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

RAC exam

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 😎

read user-input in plsql

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

rename column_value


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

to divide or to multiply

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...

create your database with dbca

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 :mrgreen:

Column qualification best practice

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 😎

sql*plus pagesize explained

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)

sequence / thread / system change number

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

select from a comma-separated string

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.

dbms_xplan and v$sql_plan

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.

rename

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)

constraints for referential integrity

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.

insert/update/delete/merge LOG ERRORS

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

MERGE syntax

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

Export to Excel

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 😛

How to avoid ORA errors when dropping inexistant objects

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 :mrgreen:

clob hello world

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

csv part 3

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

csv with XML revisited…

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

Oracle Certification

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

csv format with select *

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;

v$sql and bind variable

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)