Oracle External Table
External tables are created using the sql create table.organization table statement.
To load the bulk data using sql loader
Step 1:To create a file in os level
oracle@db ~]$ mkdir d1
oracle@db ~]$ cd d1
oracle@db d1]$ vi f1
oracle@db ~]$ export ORACLE_SID=<databasename>
oracle@db ~]$ sqlplus / as sysdba
First we connect the ‘SYS’ user then we can create a new directory
Syntax:
Sql> conn sqlplus / as sysdba
You must have any directory system privilege to create directories.
Syntax:
Sql> grant create any directory to username;
Example:
Sql> grant create any directory to sai;
Syntax:
Sql> create directory <directory_name> as <path_name>;
Example:
Sql> create directory d1 as ‘/home/oracle/db/’;
Syntax:
Sql> grant read,write on directory directoryname to username;
Example:
Sql> grant read,write on directory d1 to nijam;
Create table:
Organization external table:using type oracle loader
Default directory:
Access parameters( :
Records delimited by newline
Fields terminated by
Location :specifies the location of the external date,the location is specified as a list of directory objects and filenames.if the directory objects is not specified,then the default directory objects is used as the file location.
Example program:
Create table filename(study) (id number(5),name varchar2(15))
Organization external (type oracle_loader
Default directory directoryname
Access parameters(
Records delimited by newline
Fields terminated by ‘ , ‘)
Location (‘filename’));
SQL > select * from tablename;
Comments
Post a Comment