How To Create A Table In Oracle

This online tutorial is based on examples to make it easier to follow. Statement create table in Oracle has many different table type options for example you can create a temporary table where all inserted lines will be kept during the session is alive. After closing your oracle database connection the Oracle global temporary table will be cleaned up and once you logged to the database again with it creates a new session the Oracle global temporary table will be empty again. This Oracle temporary table can be created as keeping data in after doing commit (ON COMMIT PRESERVE ROWS) or deleting them once commit is done. Please note you need to declare in the create table statement an option to keep the data even with commit, otherwise by default it wouldn’t keep them. The statement for an Oracle create temporary table is:

CREATE GLOBAL TEMPORARY TABLE sales_temp
  ( id         NUMBER(17)  NOT NULL,
    sale_date  DATE        NOT NULL,
    sale_month VARCHAR2(6) NOT NULL,
    product_id NUMBER(17)  NOT NULL 
  ) ON COMMIT PRESERVE ROWS;

The next global temporary table will not keep the data with commit. As you see the last keyword has changed to “ON COMMIT DELETE ROWS” and the table statement is following:

CREATE GLOBAL TEMPORARY TABLE sales_temp
 ( id NUMBER(17) NOT NULL,
   sale_date DATE NOT NULL,
   sale_month VARCHAR2(6) NOT NULL,
   product_id NUMBER(17) NOT NULL 
  ) ON COMMIT DELETE ROWS;

The third example is about of how to create a general table in Oracle. The table name is “SALES” and it contains daily basis sales information. We are storing the data depending on the sale dates (sale_date) and every line has it’s unique ID number and every line refers to PRODUCTS table via column product_id. The sale_month column contains a text as date “YYYYMM“. When you do compare the Oracle global temporary table statement with the following one the both syntax have common parts, but they are two different table type.

CREATE TABLE sales
 ( id         NUMBER(17)  NOT NULL,
   sale_date  DATE        NOT NULL,
   sale_month VARCHAR2(6) NOT NULL,
   product_id NUMBER(17)  NOT NULL );

Oracle DBA suggest to use an Oracle partition for big tables. This way your data will be better organised and it is more easily removed or re-indexed. For the Oracle partitions we suggest to use local indexes instead of a global index. Oracle local index is created automatically per partition. To add new partitions or to remove the existing ones you would not need to rebuild the local indexes.

The third type of tables are the Oracle external tables. The Oracle external table is for to read data from an external source like from csv files. The Oracle external table is using ORACLE_LOADER access driver to load data from the csv file. This way created tables can be using for importing data from external sources and it is one of the fastest way to do it. The Oracle external tables are widely used for importing in Oracle warehouse systems and the statement for an external table is:

CREATE TABLE sales_extern (
    id         NUMBER(17),
    sale_date  DATE,
    sale_month VARCHAR2(2000),
    product_id NUMBER(17) 
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY import_files
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  BADFILE 'import_sales.bad'
  DISCARDFILE 'import_sales.dis'
  LOGFILE 'import_sales.log'
  SKIP 1
  FIELDS TERMINATED BY ","  OPTIONALLY ENCLOSED BY '"'
  (
    id         INTEGER EXTERNAL(17),
    sale_date  CHAR(10) date_format DATE mask "dd/mm/yyyy",
    sale_month CHAR(2000),
    product_id INTEGER EXTERNAL(17) 
  )
 )
 LOCATION ('import_sales.csv')
)
REJECT LIMIT UNLIMITED;

You can create an external table in oracle with the same structure as the other tables above. We would like to point out some important parameters like the import_sales.csv that should be located in Oracle directory named import_files. The directory in Oracle should have read-write permission to your Oracle user since the log and bad ( so called “error” ) files will be written into it. The Oracle external table will skip the first line (SKIP 1) as usually the files have the column names written as the 1st line and the value is separated with coma (,) and text can be in quotes (“).

Important to know:

While you tried to create the external table and it returned error ORA-06564: object IMPORT_FILES does not exist then that means you didn’t create Oracle directory IMPORT_FILES and you would need to do it first before creating the table.

To create a directory in Oracle for Unix or Linux environment use the following command:

create directory import_files as '/tmp';

And this example is for Windows environment:

create directory import_files as 'C:\Temp';

Now the sales_extern table has been created and you may try to see what is inside it. This Oracle external table is like any normal database table so to see the data we are using the Oracle Select statement as on the following example:

SELECT * FROM sales_extern;

When you did run the SQL query and Oracle returned the error above then the following error is raised because you don’t have the “import_sales.csv” file in the directory that you have just created.

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file import_sales.csv in IMPORT_FILES not found

To fix the error just create empty file “import_sales.csv” into your directory “/tmp” or “C:\Temp“. When you will try again the Select statement again the output should be empty.

To see some data just insert the following lines into your empty file and run the query again.

ID,Sale Date,Sale Month,Product_id
1,01/02/2012,”February”,14
2,03/03/2012,”March”,14
3,20/08/2013,”August”,13

As you see from the output all lines except the 1st line appeared in the query. We did declare in the external table source that it should always skip the first line “SKIP 1“.

See Also:
Oracle Home