Importing an Excel into an Oracle database

There are several ways to do this, but I will only show you how to do this using SQL Developer. Why? Because I just came across this functionality the other day.
However there are some things you have to pay attention to, you might get frustrated with it otherwise.

Oracle PL/SQL Programming Preview

This post will be mainly in Dutch. It's about a Preview we are doing in our office in Nieuwegein, The Netherlands.
The sessions will probably be in Dutch, but can be changed on the fly to English if desired.
If you are not able to make it to our office, you still have a chance to see Patrick do his presentations in Atlanta, Georgia during the Oracle PL/SQL Programming Conference, November 10 and 11.
You can register by following this link.

Our Event in Nieuwegein will be on October 20 (Thanks Erik for pointing out the date was missing)

Here is the agenda for this evening:

De Oracle PL/SQL Programming conference vind dit jaar in Atlanta, Georgia plaats. Patrick Barel is daarbij om een drietal presentaties te geven. Voor degene die niet aanwezig zullen zijn in Atlanta vind deze OPP-Preview plaats.
Tijdens deze KC zal Patrick twee van deze presentaties geven.
Na het diner zal Alex een re-run geven van zijn ODTUG presentatie: "SQL Holmes".

Het programma voor deze avond:
16:30
"Pipelined table functions" - Patrick Barel
Pipelined table functions offer an ideal convergence of the elegance and simplicity of PL/SQL with the performance of SQL. Complex data transformations are effortless to develop and support with PL/SQL, yet to achieve high-performance data processing, we often resort to set-based SQL solutions. Pipelined functions bridge the gap between the two methods effortlessly, but they also have some unique performance features of their own, making them a superb performance optimization tool.

18:00
diner: Chinees

Na diner (rond 19:00):
"SQL Holmes: The Case of the Missing Performance" - Alex Nuijten
During this presentation, a case study is unfolded to reveal the true cause of a slow performing query. Did the database just "have a bad day"? Was the evil DBA to blame? The PL/SQL developer who didn't get enough coffee? Or was it the application sending the "wrong" query in the first place?
In this classic "whodunnit" you will take a tour past the crime scene. Investigate the query, use the tools of the trade and collect all the relevant information. Follow the trail to uncover the truth and nothing but the truth...

20:00
Ter afsluiting:
"Optimizing SQL with Collections" - Patrick Barel
Collections (array-like structures in PL/SQL) are used in two of the most important performance features of PL/SQL: FORALL and BULK COLLECT. This session demonstrates the power of these features and offers in-depth guidance on how to apply them.

Just the plain DDL, please.. DBMS_METADATA

The other day I needed to get some DDL statements from the datadictionary. In the old days you could write your own queries to extract all this. Nowadays you can use the built in package DBMS_METADATA to get the DDL for you.
Let's take a look at how you can use this. The DDL that I want to extract is the infamous EMP table, normally in the SCOTT schema.

SQL> create table emp
  2  as
  3  select *
  4    from scott.emp
  5  /

Table created.

To make it a little more interesting, we also add the DEPT table and place some constraints on them.

SQL> create table dept
  2  as
  3  select *
  4    from scott.dept
  5  /

Table created.

SQL> 
SQL> alter table dept
  2  add constraint dept_pk primary key (deptno)
  3  /

Table altered.

SQL> 
SQL> alter table emp
  2  add constraint emp_dept_fk foreign key (deptno) references dept (deptno)
  3  /

Table altered.

If you use DBMS_METADATA just like that, you might get more than you asked for.

SQL> set long 5000
SQL> select dbms_metadata.get_ddl('TABLE', 'EMP')
  2    from dual
  3  /

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "ALEX"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "EMP_DEPT_FK" FOREIGN KEY ("DEPTNO")
          REFERENCES "ALEX"."DEPT" ("DEPTNO") ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

As you can in the code block above, you get the complete DDL including the storage clauses and the constraints. If this is not what you want, and I didn't want all this, than you need to modify some transformation parameters.

SQL> 
SQL> begin
  2     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'STORAGE',false);
  3     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'TABLESPACE',false);
  4     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES', false);
  5     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'REF_CONSTRAINTS', false);
  6     dbms_metadata.set_transform_param (dbms_metadata.session_transform,'CONSTRAINTS', false);
  7  end;
  8  /

PL/SQL procedure successfully completed.

Each of these transformation parameters take out bit by bit parts of the generated DDL. After running the above anonymous block we will get the following result.

SQL> select dbms_metadata.get_ddl ('TABLE', 'EMP')
  2    from dual
  3  /

DBMS_METADATA.GET_DDL('TABLE','EMP')
--------------------------------------------------------------------------------

  CREATE TABLE "ALEX"."EMP"
   (    "EMPNO" NUMBER(4,0),
        "ENAME" VARCHAR2(10),
        "JOB" VARCHAR2(9),
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0)
   )

That's more like it, just what I was after. The plain DDL for the EMP table.
One of the nice things is that you don't need to modify all the transformation parameters again to go back to the default. They made it really easy to return to the default settings:

SQL> begin
  2     dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'DEFAULT');
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> 

Documentation on DBMS_METADATA

UPDATE July 10, 2013 (especially for Erhan Sarigul): Remove the Schema from the DDL-script

create or replace function remap_schema return clob is
   -- Define local variables.
   h   number; --handle returned by OPEN
   th  number; -- handle returned by ADD_TRANSFORM
   doc clob;
begin
   -- Specify the object type.
   h := dbms_metadata.open('TABLE');
   -- Use filters to specify the particular object desired.
   dbms_metadata.set_filter(h
                           ,'SCHEMA'
                           ,'ALEX');
   dbms_metadata.set_filter(h
                           ,'NAME'
                           ,'EMP');
   -- Request that the schema name be modified.
   th := dbms_metadata.add_transform(h
                                    ,'MODIFY');
   dbms_metadata.set_remap_param(th
                                ,'REMAP_SCHEMA'
                                ,'ALEX'
                                ,null);
   -- Request that the metadata be transformed into creation DDL.
   th := dbms_metadata.add_transform(h
                                    ,'DDL');
   -- Specify that segment attributes are not to be returned.
   dbms_metadata.set_transform_param(th
                                    ,'SEGMENT_ATTRIBUTES'
                                    ,false);
   -- Fetch the object.
   doc := dbms_metadata.fetch_clob(h);
   -- Release resources.
   dbms_metadata.close(h);
   return doc;
end remap_schema;
/

select remap_schema -- dbms_metadata.get_ddl ('TABLE','EMP')
  from dual
/

Using the DBMS_METADATA API

OPP 5: The Big Fat Book on PL/SQL

Yesterday I got the fifth edition of "Oracle PL/SQL Programming" by Steven Feuerstein. Another 1200+ pages on PL/SQL. This edition has been updated to cover versions through Oracle 11gR2.
It is loaded with the latest on Oracle PL/SQL Programming, hence the title.
When I started programming in PL/SQL I had a great mentor. When I told him I had a hard time writing PL/SQL, he advised me to get Feuerstein's book (the second edition was just out).
Immediately I loved it, couldn't put it down. I dragged this book everywhere. Reading it in the traffic jam on my way to work, reading it before I went to sleep. By the time I finished that book, people at work started asking me questions about PL/SQL. Next to my copy of the Second Edition are also the Third and the Fourth edition. And now, I need to clear some space on the shelve, the fifth edition will take it's place soon. After I'm finished reading it.
I'm sure this book is a must have for anyone who uses PL/SQL on a daily basis.

I did mention in an earlier blogpost that I was reviewing some chapters for an upcoming book, well it was this book. And needless to say I'm very proud that I was mentioned in the preface. But I have to say I just submitted the first errata. Not a biggie, but still... My lastname in the book was spelled Nuitjen, while it should be Nuijten. Oh well...

Update 22-10-2009:
My friend and colleague Patrick Barel pointed out that Google knows who I am ;)