4. Create an External table

 

Sqlldr and external tables

First, prepare a directory for the user.

SQL> create directory extdir as '/opt/ora10g/oradata/sqlldr ';
SQL> grant read, write on directory extdir to aegis;

Sqlldr can generate external table scripts, which are not executable, but can be used as a reference for creating external table statements.

[Oracle @ Aegis sqlldr] $ sqlldr Aegis control = ext_ctl.ctl external_table = generate_only
Password:

An External table script is generated. Note that:

1. ext_ctl.ctl is a correct control file imported by sqlldr.
2. Although only generate a reference, the imported tables and data in ext_ctl.ctl must actually exist in the database.
3. After this statement is executed, except for an ext_ctl.log, almost nothing will happen.

In short, if this command drops the last sentence external_talbe = generate_only, it must be correctly imported into the database!

 

Then Cat the ext_ctl.log and you will be able to see a script. So happy that you don't have to write so many words...
So, just find a control file and type external_table = generate_only!

 

Create Table "sys_sqlldr_x_ext_gundam" -- table name
(
"Name" varchar2 (20 ),
"Pilot" varchar2 (20)
)
Organization external -- specify that an external table is generated now.
(
Type oracle_loader -- Data Loading Method. Another type is oracle_datapump.
Default directory extdir -- External table directory. The one you just created
Access parameters -- start to set parameters. This is a script, so all parameters have
(
Records delimited by newline characterset zhs16gbk -- end of the record. This is the default record and can be omitted.
Badfile 'extdir': 'ext _ CTL. bad' -- badfile path, same as sqlldr, can be omitted
Logfile 'ext _ CTL. log_xt '-- Logfile path, which is the same as sqlldr. If the two logs are omitted, a file with a strange name is automatically generated...
Readsize 1048576 -- size of the read log buffer. The default value is 1 MB, which can be omitted.
Skip 6 -- number of records skipped, same as sqlldr, can be omitted
Fields terminated by "," ldrtrim -- same as sqlldr Control File
Reject rows with all null fields -- if a column has a null value, it is not loaded.
(
"Name" char (255)
Terminated ",",
"Pilot" char (255)
Terminated ","
)
)
Location -- data (Control) file location. In actual use, you can also use a. dat data file.
(-- Because sqlldr is used to automatically generate scripts, you must use a correct control file.
'Ext _ CTL. CTL'
)
) Reject limit unlimited -- an acceptable error. The default value is 0.

In addition, it should be noted that if you actually use a control file as the data of an External table and do not write reject limit Unlimited
Then it is obvious that an error will occur (the pile of load data or something in the control file ..)
However, even if the table is created incorrectly, it will still be displayed after execution.
Table create
Then, if you select *, an error will be reported.
ORA-29913: Error in executing odciexttablefetch callout
ORA-30653: reject limit reached
ORA-06512: At "SYS. oracle_loader", line 52
Ah, it's so cute... a picture of Oracle's weak Lie report suddenly emerges in the head.
Okay, Oracle, you are selling cute again... in fact, the prompt is also very clear about reject limit reached

In this example, another reminder is that even if it tells you that it is successful, you should take a look at it and not be turned over by Oracle.

 

Additionally, you can create DMP files for external tables.

Create Table Name

Organization external

(

Type oracle_datapump

Default dirctory Object Name

Location ('dmp file name. dmp ')

)

As select * from Table

The DMP file of Table A is generated.

The statements used to generate a table through DMP are similar to those described above.

Is to write the full column name in create.

In addition

Select dbms_metadata.get_ddl ('table', 'table name') from dual; you can view the table creation statement.

Note that

1. You must have the select_catalog_role permission.

2. 'table' and 'table name' must be capitalized. Otherwise, an error will be reported.