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

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)