Category Archives: Blogroll

How to compare schema

If you have receive ddl statements from your developer and you want to check if it matches the current state of the development database, because the developer have done a lot of change in a quick and undocumented manner, what are your options?

I found this handy feature in Toad :
1) I create my objects on a separate database with the ddl I received from development
2) I compare the schema they use with the schema I created in Toad
–> Database –> Compare –> Schema
I select the options I want:
–> functions, indexes, packages, procedures, triggers, tables, view
I select the Reference and Comparison connections/schemas. Then I click compare
3) I receive the result
(only) 29 differences
4) the real bonus, I receive a script to update the live data according to the script I received. Undocumented change should never happen, so I do some communication with the developers


drop index foo;
drop table bar;
alter table gaz drop column bop;
alter table gaz modify (quux null);

this is not going to be blind-executable, some change are simply impossible to implement, but for my little test, I was happy to discover that function

I have been using ERwin for this purpose before, but the version I have (4.1) is very buggy and does not support a lot of syntaxes (ex: deferred constraints, create view v as select cast(1 as number(1)) x from dual, etc…). Also ERwin can compare only with the current model, so no direct comparison between 2 database schema.

how to spell 999999999 ?


begin
  dbms_output.put_line(
    to_char(
      timestamp '9999-12-31 23:59:59.999999999',
      'FF9SP'));
end;
/ 
NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE 
THOUSAND NINE HUNDRED NINETY-NINE

Unfortunately, I could not get this in sql/10.2.0.2


select 
  to_char(
    timestamp '9999-12-31 23:59:59.999999999',
    'FF9SP') X
from
  dual;
ORA-01877: string is too long for internal buffer

Well, since the string is too long, let’s try with a LONGER string :twisted:


select 
  regexp_substr(
    to_char(
      timestamp '9999-12-31 23:59:59.999999999',
'FF9SP/FMDAY MONTH DDTHSP YYYYSP A.D. HH24SP MISP SSSP')
    ,'[^/]+')X
from
  dual;

X                                                                                          
---------------------------------------------------------
----------------------------------
NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE 
THOUSAND NINE HUNDRED NINETY-NINE

SQL Expert?

I have attended the sql certified expert beta exam this morning. There were a lot of errors in it, I added in the comment that they have to groundly review their regular expressions questions. There were a lot of rubbish question, but hardly any challenge, it is more like detecting the incorrect syntax. So I am deceived. I have to wait 3 months for the result, but I expect no more than 90% 8-)

They even have an exhibit with a table containing many columns with the same name (!)

Well, I hope they will improve with the comments I made when the production release will come out.

The time to answer the question is sufficient. I had only 139 questions in 3 hours, and I needed only 2 hours actually with plenty of time to review.

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


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

:twisted:

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 8-)

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

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 8-)

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)

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

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