Skip to content
≡

Administrator

Developer

Tools

External Tools

3. FORM Files#

About FORM Files#

A FORM file is similar to a Create Table statement in that contains information for iLoader about the attributes of a table with which data are to be exchanged. iLoader can be used to automatically create FORM files. They can also be directly edited to use the following additional options

  • SEQUENCE sequence_name column_name [NEXTVAL | CURRVAL ]

  • DATEFORM date_format

  • DOWNLOAD CONDITION "WHERE condition"

  • DATA_NLS_USE=US7ASCII

  • NCHAR_UTF16=YES

Writing SEQUENCE Statements#

A FORM file can be modified to implement a SEQUENCE.

To modify the FORM file, enter the word SEQUENCE followed by the sequence name, the name of the column(s) to be used, and pseudocolumns such as NEXTVAL or CURRVAL, etc in order at the very beginning of the form file.

SEQUENCE  sequence_name  column_name  [ NEXTVAL | CURRVAL ]

The default value for pseudocolumns is NEXTVAL. The maximum number of columns that can be used is 8.

The default format of the FORM file is as follows, and if the table has a column for which the data type is date, the date format will be the default.

table sample
{
A numeric (10);
B numeric (10, 4);
C char (10);
D varchar (10);
E date;
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS

Example#

iSQL> CREATE TABLE seqTable(
  num INTEGER,
  name VARCHAR(30));
Create success.
iSQL> INSERT INTO seqTable VALUES(1, 'JANE');
1 row inserted.
iSQL> INSERT INTO seqTable VALUES(2, 'SARA');
1 row inserted.
iSQL> CREATE SEQUENCE seq1
    START WITH 30 INCREMENT BY 2
    MINVALUE 0 NOMAXVALUE;
Create success.

iLoader> formout -T seqTable -f seqTable.fmt
$ vi seqTable.fmt
SEQUENCE seq1 NUM       <- Revised part
table seqTable
{
"NUM" integer;
"NAME" varchar (30);
}
DATA_NLS_USE=US7ASCII

iSQL> INSERT INTO seqTable(name) VALUES('JOHN');
1 row inserted.
iSQL> INSERT INTO seqTable(name) VALUES('JOHNSON');
1 row inserted.
iSQL> SELECT * FROM seqTable;
SEQTABLE.NUM SEQTABLE.NAME                   
-----------------------------------------------
1           JANE                            
2           SARA                            
            JOHN                            
            JOHNSON                         
4 rows selected.

After using the FORM file to back up seqTable table to a file, when the file is examined it looks like:,

iLoader> out -f seqTable.fmt -d seqTable.dat
DATA_NLS_USE: US7ASCII

Total 4 record download(SEQTABLE)

DOWNLOAD : 53.4390 msec

$ cat seqTable.dat
1,"JANE"
2,"SARA"
,"JOHN"
,"JOHNSON"

After uploading the backup file to the current database, the following statement is used: 

iLoader> in -f seqTable.fmt -d seqTable.dat -mode replace
DATA_NLS_USE: US7ASCII

UPLOAD: 16467

Load Count  : 4

If the records in the seqTable table are queried, the results will be as follows:

iSQL> SELECT * FROM seqTable;
SEQTABLE.NUM SEQTABLE.NAME                   
-----------------------------------------------
30          JANE                            
32          SARA                            
34          JOHN                            
36          JOHNSON                         
4 rows selected.

Modifying Date Format Strings in a FORM File#

The format for date data type columns can be set as follows (case-insensitive), and download or upload actions can be conducted on the basis of the format.

The date format should be specified at the bottom of the FORM file when modifying it. The default is DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS.

dateFORM YYYY-MM-DD HH:MI:SS:SSSSSS
dateFORM YYYY/MM/DD HH:MI:SS:SSSSSS
dateFORM YY/MM/DD HH:MI:SS:SSSSSS
dateFORM YY-MM-DD HH:MI:SS:SSSSSS
dateFORM YYYYMMDDHHMISSSSSSSS
dateFORM YYMMDDHHMISSSSSSSS
dateFORM YY
dateFORM YYYY
dateFORM YYMMDD
dateFORM YYYYMMDD
dateFORM HHMISSSSSSS
dateFORM HH:MI:SS:SSSSS
dateFORM HH:MI:SS

Single quotation marks ( ' and ' ) can be used to define a new date format that differs from the date formats given above.

For example, a date format that uses a space as the delimiter between the year, month and day elements can be defined thus: "DATEFORM YY MM DD" or" DATEFORM YYYY MM DD".

iSQL> CREATE TABLE t1(
  i1 INTEGER,
  i2 DATE,
  i3 INTEGER);
Create success.
iSQL> INSERT INTO t1 VALUES(1, to_date('2002-01-23 10:11:12:222222', 'YYYY-MM-DD HH:MI:SS:SSSSSS'), 1);
1 row inserted.
iSQL> INSERT INTO t1 VALUES(2, to_date('2002-01-23 13:14:15:333333', 'YYYY-MM-DD HH:MI:SS:SSSSSS'), 2);
1 row inserted.
iSQL> INSERT INTO t1 VALUES(3, to_date('2002-01-23 16:17:18:444444',  'YYYY-MM-DD HH:MI:SS:SSSSSS'), 3);
1 row inserted.
iSQL> SELECT TO_CHAR(i2, 'YYYY-MM-DD HH:MI:SS:SSSSSS') TESTDATE FROM t1;

TESTDATE                                            
-----------------------------------------------
2002-01-23 10:11:12:222222                          
2002-01-23 13:14:15:333333                          
2002-01-23 16:17:18:444444                          
3 rows selected.

The created FORM file is modified as follows:

iLoader> formout -T t1 -f t1.fmt

$ vi t1.fmt 
table t1
{
I1 integer;
I2 date;
I3 integer;
}
DATEFORM YYYY-MM-DD HH:MI:SS:SSSSSS   <- Revised part DATA_NLS_USE=US7ASCII

After the FORM file is used to back up the seqTable table to a file, examining the file reveals the following,

iLoader> out -f t1.fmt -d t1.dat
DATE FORMAT : YYYY-MM-DD HH:MI:SS:SSSSSS
DATA_NLS_USE: US7ASCII

Total 3 record download(T1)

DOWNLOAD : 982.0000 usec

$ cat t1.dat
1,"2002-01-23 10:11:12:222222",1
2,"2002-01-23 13:14:15:333333",2
3,"2002-01-23 16:17:18:444444",3

To upload the backup file to the database, the following command is used.

iLoader> in -f t1.fmt -d t1.dat -mode replace
DATE FORMAT : YYYY-MM-DD HH:MI:SS:SSSSSS
DATA_NLS_USE: US7ASCII

UPLOAD: 4026

Load Count  : 3

Selecting the records in table t1 using the TO_CHAR function results in the following:

iSQL> SELECT TO_CHAR(i2, 'YYYY-MM-DD HH:MI:SS:SSSSSS') TESTDATE FROM t1;
TESTDATE                                            
-----------------------------------------------
2002-01-23 10:11:12:222222                          
2002-01-23 13:14:15:333333                          
2002-01-23 16:17:18:444444                          
3 rows selected.

However, when uploading data, it is important to ensure that the previously downloaded data have the same date format specified in the FORM file.

DATE FORMAT Environment Variable#

In the FORM file structure, an environment variable that plays a role analogous to that of DATEFORM can be specified as follows. For example, when using the Bourne, Korn, or Bash Shells:

$ export ILO_DATEFORM='YYYY-MM-DD'

How to specify the DATE FORMAT for Respective Columns#

When dealing with DATE data type columns that have different formats, if the FORM file is modified by entering DATEFORM and a desired date_format in double quotes (" ") after the DATE type declaration (not case sensitive), data can be uploaded or downloaded in this format.

iSQL> CREATE TABLE t1 (i1 INTEGER, i2 DATE, i3 DATE, i4 DATE);
Create success.
iSQL> INSERT INTO t1 VALUES (1,
TO_DATE('2004/11/24 09:55:30 181133','YYYY/MM/DD HH:MI:SS SSSSSS'),
TO_DATE('2004/11/25 09:55:30 181133','YYYY/MM/DD HH:MI:SS SSSSSS'),
TO_DATE('2004/11/26 09:55:30 181133','YYYY/MM/DD HH:MI:SS SSSSSS'));
1 row inserted.
iSQL> SELECT * FROM t1;
T1.I1               T1.I2
T1.I3       T1.I4                
-----------------------------------------------
1               2004/11/24 09:55:30  2004/11/25 09:55:30     2004/11/26 09:55:30  
1 row selected.

$ iloader formout -s 127.0.0.1 -u sys -p manager -T t1 -f t1.fmt;

$ vi t1.fmt 
table t1
{
"I1" integer;
"I2" date DATEFORM "YYYY-MM-DD"; <- Revised part
"I3" date;
"I4" date DATEFORM "YYYY/MM/DD HH:MI:SS SSSSSS"; <- Revised part
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE=US7ASCII

$ iloader out -s 127.0.0.1 -u sys -p manager -f t1.fmt -d t1.dat;
DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

Total 1 record download
DOWNLOAD: 25893

$ cat t1.dat
1,"2004-11-24","2004/11/25 09:55:30","2004/11/26 09:55:30 181133"

$ iloader in -s 127.0.0.1 -u sys -p manager -f t1.fmt -d t1.dat;
DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

UPLOAD: 3556

Load Count  : 1

iSQL> SELECT * FROM t1;
T1.I1       T1.I2                T1.I3                T1.I4                
-----------------------------------------------
1           2004/11/24 09:55:30  2004/11/25 09:55:30  2004/11/26 09:55:30  
1           2004/11/24 00:00:00  2004/11/25 09:55:30  2004/11/26 09:55:30  
2 rows selected.
iSQL> SELECT TO_CHAR(I2,'YYYY/MM/DD HH:MI:SS SSSSSS') FROM t1;
TO_CHAR(I2,'YYYY/MM/DD HH:MI:SS SSSSSS')            
-----------------------------------------------
2004/11/24 09:55:30 181133                          
2004/11/24 00:00:00 000000                          
2 rows selected.
iSQL> SELECT TO_CHAR(I3,'YYYY/MM/DD HH:MI:SS SSSSSS') FROM t1;
TO_CHAR(I3,'YYYY/MM/DD HH:MI:SS SSSSSS')            
-----------------------------------------------
2004/11/25 09:55:30 181133                          
2004/11/25 09:55:30 000000                          
2 rows selected.
iSQL> SELECT TO_CHAR(I4,'YYYY/MM/DD HH:MI:SS SSSSSS') FROM t1;
TO_CHAR(I4,'YYYY/MM/DD HH:MI:SS SSSSSS')            
-----------------------------------------------
2004/11/26 09:55:30 181133                          
2004/11/26 09:55:30 181133                          
2 rows selected.

* Note : The date format specifications take precedence in the following order (from highest precedence to lowest):

  • the specification following the DATE column in the FORM file
  • the ILO_DATEFORM environment variable
  • 'DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS' in the FORM file

Executing Functions#

Data being uploaded can be placed in a particular column using a function. However, this is not possible for the data types DATE, TIMESTAMP or GEOMETRY. When modifying a FORM file, the function name should be specified in double quotes (" ") after the other options. Furthermore, a question mark (?) should be used to indicate the location at which the column is to be bound.

$ vi t2.fmt
table t2
{
"I1" integer "trim(?)";   < - Revised part
"I2" varchar(10) "trim(?)";   < - Revised part
"I3" varchar(10) "concat(trim(?),'value')";   < - Revised part
}

Writing Condition Clauses#

FORM files can be modified so that condition clauses are used when downloading data from tables. Hints can also be added to realize improved performance.

Enter the words "DOWNLOAD CONDITION" (without quotes) at the very end of a FORM file, followed by the condition clause within double quotes (" "). At the time of execution, the executed query can be checked to verify that hints and condition clauses specified by the user are working properly using the "-displayquery" option.

Syntax#

DOWNLOAD CONDITION  "Where_conditions_clause" [HINT "hint_string"]

Where_conditions_clause : For specifying conditions. Has the same form as the WHERE clause of a SELECT statement.

hint_string : For specifying a hint in order to increase the performance of a SELECT statement. Has the same form as a HINT used with a SELECT statement.

Example#

iSQL> CREATE TABLE table1(
   t1 INTEGER,
   t2 INTEGER,
   t3 DATE);
Create success.
iSQL> INSERT INTO table1 VALUES(1, 1, '01-May-2002');
1 row inserted.
iSQL> INSERT INTO table1 VALUES(2, 2, '02-Jun-2002');
1 row inserted.
iSQL> INSERT INTO table1 VALUES(3, 3, '03-Apr-2002');
1 row inserted.
iSQL> INSERT INTO table1 VALUES(4, 4, '04-Sep-2002');
1 row inserted.
iSQL> INSERT INTO table1 VALUES(5, 5, '05-Oct-2002');
1 row inserted.
iSQL> SELECT * FROM table1;
TABLE1.T1   TABLE1.T2   TABLE1.T3            
-----------------------------------------------
1           1           2002/05/01 00:00:00  
2           2           2002/06/02 00:00:00  
3           3           2002/04/03 00:00:00  
4           4           2002/09/04 00:00:00  
5           5           2002/10/05 00:00:00  
5 rows selected.

The FORM file created with this command:

iLoader> formout -T table1 -f table1.fmt

is modified as follows:

$ vi table1.fmt 
table table1
{
"T1" integer;
"T2" integer;
"T3" date;
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE=US7ASCII
DOWNLOAD CONDITION "where t1 > 2"    <- Revised part

After using the FORM file to back up table1 to a file, when the file is examined, the results of application of the condition clause can be seen.

iLoader> out -f table1.fmt -d table1.dat
DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

Total 3 record download(TABLE1)

DOWNLOAD : 1.4150 msec 

$ cat table1.dat
3,3,"2002/04/03 00:00:00"
4,4,"2002/09/04 00:00:00"
5,5,"2002/10/05 00:00:00"

Handling TIMESTAMP Columns#

After downloading a data file from a current Altibase database using iLoader, a value pertaining to a TIMESTAMP column in a data file can be uploaded by using the following options to modify the FORM file.

  • ADD DEFAULT

  • ADD NULL

  • ADD YYYYMMDD[HHMISS]

  • SKIP DEFAULT

  • SKIP NULL

  • SKIP YYYYMMDD[HHMISS]

Below is an example of the use of the above option to download the data in a database table to a file and upload the data to the table again.

  • A FORM file is created based on the table to be downloaded from the current Altibase database.
  • The created FORM file is used to back up the table data, downloaded from the current database, to a file.
  • The FORM file to be uploaded is modified according to the following guidelines as the circumstances require.

If the data file has no value in a TIMESTAMP column#

  • If there are no TIMESTAMP data in the data file, but the current time is to be entered in the TIMESTAMP column: enter "ADD DEFAULT" (without quotation marks) at the end of the line describing the TIMESTAMP column in the FORM file to be uploaded (or delete the line describing the TIMESTAMP column from the FORM file).
  • If there are no TIMESTAMP data in the data file, and a null value is to be entered in the TIMESTAMP column: enter "ADD NULL" at the end of the line describing the TIMESTAMP column in the FORM file to be used for uploading data.

  • If there are no TIMESTAMP data in the data file, but a specified value is to be entered in the TIMESTAMP column: enter "ADD YYYYMMDD[HHMISS]" at the end of the line describing the TIMESTAMP column in the FORM file to be used for uploading data.

    YYYYMMDD[HHMISS]
ADD 20040623
ADD 20040623102315

If a value corresponding to a TIMESTAMP column is in the data file#

  • To discard the value in the data file and enter the current time instead: enter "SKIP DEFAULT" at the end of the TIMESTAMP column of the FORM file to be uploaded.
  • To enter NULL instead of the value in the data file: enter "SKIP NULL" at the end of the TIMESTAMP column of the FORM file to be uploaded.
  • To enter a specified value instead of the value in the data file: enter "ADD YYYYMMDD [HHMISS]" at the end of the TIMESTAMP column of the FORM file to be uploaded.

Example#

Use the edited FORM file to upload data to the current database.

ADD DEFAULT#
iSQL> CREATE TABLE test(i1 INTEGER);
Create success.
iSQL> CREATE TABLE test2 (i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> INSERT INTO test VALUES(1);
1 row inserted.
iSQL> INSERT INTO test VALUES(2);
1 row inserted.
iSQL> INSERT INTO test VALUES(3);
1 row inserted.

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST -f test.fmt;

$ iloader out -s 127.0.0.1 -u sys -p manager  -f test.fmt -d test.dat;

DATA_NLS_USE: US7ASCII

Total 3 record download(TEST)

DOWNLOAD : 728.0000 usec

$ cat test.dat
1
2
3

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST2 -f t2_add_default.fmt;

$ vi t2_add_default.fmt
table TEST2
{
"I1" integer;
"I2" timestamp ADD DEFAULT; <= Revised part
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE=US7ASCII

$ iloader in -s 127.0.0.1 -u sys -p manager  -f t2_add_default.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

UPLOAD : 8.8130 msec

Load Count  : 3(TEST2)

iSQL> SELECT * FROM test2;
I1          I2
---------------------------------
1           4F179985000B5290
2           4F179985000B52E1
3           4F179985000B5310
3 rows selected.
ADD NULL#
iSQL> CREATE TABLE test (i1 INTEGER);
Create success.
iSQL> CREATE TABLE test2 (i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> INSERT INTO test VALUES(1);
1 row inserted.
iSQL> INSERT INTO test VALUES(2);
1 row inserted.
iSQL> INSERT INTO test VALUES(3);
1 row inserted.

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST -f test.fmt;

$ iloader out -s 127.0.0.1 -u sys -p manager  -f test.fmt -d test.dat;

DATA_NLS_USE: US7ASCII

Total 3 record download(TEST)

DOWNLOAD : 728.0000 usec

$ cat test.dat
1
2
3

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST2 -f t2_add_null.fmt;

$ vi t2_add_null.fmt

table TEST2
{
"I1" integer;
"I2" timestamp ADD NULL;    <= Revised part
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE=US7ASCII

$ iloader in -s 127.0.0.1 -u sys -p manager  -f t2_add_null.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

UPLOAD : 3.8490 msec

Load Count  : 3(TEST2)

iSQL> SELECT * FROM test2;
I1          I2
---------------------------------
1
2
3
3 rows selected.
ADD YYYYMMDD[HHMISS]#
iSQL> CREATE TABLE test(i1 INTEGER);
Create success.
iSQL> CREATE TABLE test2(i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> INSERT INTO test VALUES(1);
1 row inserted.
iSQL> INSERT INTO test VALUES(2);
1 row inserted.
iSQL> INSERT INTO test VALUES(3);
1 row inserted.

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST -f test.fmt;

$ iloader out -s 127.0.0.1 -u sys -p manager  -f test.fmt -d test.dat;

DATA_NLS_USE: US7ASCII

Total 3 record download(TEST)

DOWNLOAD : 728.0000 usec

$ cat test.dat
1
2
3

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST2 -f t2_add_val.fmt;

$ vi t2_add_val.fmt

table TEST2
{
I1 integer;
I2 timestamp ADD 20040623;  <= Revised part
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE=US7ASCII


$ iloader in -s 127.0.0.1 -u sys -p manager  -f t2_add_val.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

UPLOAD : 12.4080 msec

Load Count  : 3(TEST2)

iSQL> SELECT * FROM test2;
I1          I2
---------------------------------
1           40D8497000000000
2           40D8497000000000
3           40D8497000000000
3 rows selected.
SKIP DEFAULT#
iSQL> CREATE TABLE test(i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> CREATE TABLE test2(i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> INSERT INTO test VALUES(1, BYTE'40D930BF000A7566');
1 row inserted.
iSQL> INSERT INTO test VALUES(2, BYTE'40D930BF000A7566');
1 row inserted.
iSQL> INSERT INTO test VALUES(3, BYTE'40D930BF000A7566');
1 row inserted.

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST -f test.fmt;

$ iloader out -s 127.0.0.1 -u sys -p manager  -f test.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

Total 3 record download(TEST)

DOWNLOAD : 941.0000 usec

$ cat test.dat
1,40D930BF000A7566
2,40D930BF000A7566
3,40D930BF000A7566

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST2 -f t2_skip_default.fmt;

$ vi t2_skip_default.fmt
table TEST2
{
"I1" integer;
"I2" timestamp SKIP DEFAULT;    <= Revised part
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE=US7ASCII

$ iloader in -s 127.0.0.1 -u sys -p manager  -f t2_skip_default.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

UPLOAD : 5.0200 msec

Load Count  : 3(TEST2)

iSQL> SELECT * FROM test2;
I1          I2
---------------------------------
1           4F179CE100035500
2           4F179CE10003554D
3           4F179CE100035577
3 rows selected.
SKIP NULL#
iSQL> CREATE TABLE test(i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> CREATE TABLE test2 (i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> INSERT INTO test VALUES(1, BYTE'40D930BF000A7566');
1 row inserted.
iSQL> INSERT INTO test VALUES(2, BYTE'40D930BF000A7566');
1 row inserted.
iSQL> INSERT INTO test VALUES(3, BYTE'40D930BF000A7566');
1 row inserted.

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST -f test.fmt;

$ iloader out -s 127.0.0.1 -u sys -p manager  -f test.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

Total 3 record download(TEST)

DOWNLOAD : 769.0000 usec

$ cat test.dat
1,40D930BF000A7566
2,40D930BF000A7566
3,40D930BF000A7566

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST2 -f t2_skip_null.fmt;

$ vi t2_skip_null.fmt

table TEST2
{
"I1" integer;
"I2" timestamp SKIP NULL;       <= Revised part
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE=US7ASCII

$ iloader in -s 127.0.0.1 -u sys -p manager  -f t2_skip_null.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

UPLOAD : 3.7900 msec

Load Count  : 3(TEST2)

iSQL> SELECT * FROM test2;
I1          I2
---------------------------------
1
2
3
3 rows selected.
SKIP YYYYMMDD[HHMISS]#
iSQL> CREATE TABLE test(i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> CREATE TABLE test2(i1 INTEGER,
        i2 TIMESTAMP);
Create success.
iSQL> INSERT INTO test VALUES(1, BYTE'40D930BF000A7566');
1 row inserted.
iSQL> INSERT INTO test VALUES(2, BYTE'40D930BF000A7566');
1 row inserted.
iSQL> INSERT INTO test VALUES(3, BYTE'40D930BF000A7566');
1 row inserted.

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST -f test.fmt;

$ iloader out -s 127.0.0.1 -u sys -p manager  -f test.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

Total 3 record download(TEST)

DOWNLOAD : 726.0000 usec

$ cat test.dat
1,40D930BF000A7566
2,40D930BF000A7566
3,40D930BF000A7566

$ iloader formout -s 127.0.0.1 -u sys -p manager -T TEST2 -f t2_skip_val.fmt;

$ vi t2_skip_val.fmt

table TEST2
{
"I1" integer;
"I2" timestamp SKIP 20040623;   <= Revised part
}
DATEFORM YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE=US7ASCII

$ iloader in -s 127.0.0.1 -u sys -p manager  -f t2_skip_val.fmt -d test.dat;

DATE FORMAT : YYYY/MM/DD HH:MI:SS
DATA_NLS_USE: US7ASCII

UPLOAD : 15.3680 msec

Load Count  : 3(TEST2)

iSQL> SELECT * FROM test2;
I1          I2
---------------------------------
1           40D8497000000000
2           40D8497000000000
3           40D8497000000000
3 rows selected.