How To Create A Table In Oracle

How To Create A Table In Oracle

October 15, 2019
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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.

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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

create directory import_files as '/tmp';

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

And this example is for Windows environment:

create directory import_files as 'C:\Temp';

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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;

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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.
dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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

dba oracle dba database management system oracle performance tuning oracle create table oracle create table in oracle the stock market plumbing oracle partition oracle create extrenal table in oracle create directory oracle select oracle ORA-06564: object does not exist ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file in not found personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*