-- setup
create table SQLLDR_TBL
(
    ID   NUMBER,
    NAME VARCHAR2(20),
    CON  VARCHAR2(20),
    DT   DATE
);
-- testcase 1 truncate
\! @abs_bindir@/gsql -t -r -p @portstring@ -d regression -c "load data infile '@abs_srcdir@/data/sqlldr_test.csv' truncate into table sqlldr_tbl fields terminated by ',' TRAILING NULLCOLS;" > @abs_srcdir@/data/sqlldr_copy.sql
\! cat @abs_srcdir@/data/sqlldr_copy.sql | @abs_bindir@/gsql -t -r -p @portstring@ -d regression
TRUNCATE TABLE
select * from SQLLDR_TBL order by 1,2,3,4;
 id  | name | con |            dt            
-----+------+-----+--------------------------
   1 | a    | X   | 
   2 | b    | Y   | Tue Jul 08 00:00:00 2008
   3 | c    | Z   | Wed Jul 08 00:00:00 2009
  40 | d    | U   | 
 567 | a    | V   | Sun Jul 08 00:00:00 2007
(5 rows)

-- testcase 2 append
\! @abs_bindir@/gsql -t -r -p @portstring@ -d regression -c "load data infile '@abs_srcdir@/data/sqlldr_ver.txt' append into table sqlldr_tbl fields terminated by '|' TRAILING NULLCOLS;" > @abs_srcdir@/data/sqlldr_copy.sql
\! cat @abs_srcdir@/data/sqlldr_copy.sql | @abs_bindir@/gsql -t -r -p @portstring@ -d regression
select * from SQLLDR_TBL order by 1,2,3,4;
 id  | name | con |            dt            
-----+------+-----+--------------------------
   1 | a    | X   | 
   1 | a    | X   | 
   2 | b    | Y   | Tue Jul 08 00:00:00 2008
   2 | b    | Y   | Tue Jul 08 00:00:00 2008
   3 | c    | Z   | Wed Jul 08 00:00:00 2009
   3 | c    | Z   | Wed Jul 08 00:00:00 2009
  40 | d    | U   | 
  40 | d    | U   | 
 567 | a    | V   | Sun Jul 08 00:00:00 2007
 567 | a    | V   | Sun Jul 08 00:00:00 2007
(10 rows)

-- testcase 3 replace
\! @abs_bindir@/gsql -t -r -p @portstring@ -d regression -c "load data infile '@abs_srcdir@/data/sqlldr_tab.txt' replace into table sqlldr_tbl TRAILING NULLCOLS;" > @abs_srcdir@/data/sqlldr_copy.sql
\! cat @abs_srcdir@/data/sqlldr_copy.sql | @abs_bindir@/gsql -t -r -p @portstring@ -d regression
TRUNCATE TABLE
select * from SQLLDR_TBL order by 1,2,3,4;
 id  | name | con |            dt            
-----+------+-----+--------------------------
   1 | a    | X   | 
   2 | b    | Y   | Tue Jul 08 00:00:00 2008
   3 | c    | Z   | Wed Jul 08 00:00:00 2009
  40 | d    | U   | 
 567 | a    | V   | Sun Jul 08 00:00:00 2007
(5 rows)

-- testcase 4 when
\! @abs_bindir@/gsql -t -r -p @portstring@ -d regression -c "load data infile '@abs_srcdir@/data/sqlldr_tab.txt' replace into table sqlldr_tbl WHEN (1-2) = '40' TRAILING NULLCOLS;" > @abs_srcdir@/data/sqlldr_copy.sql
\! cat @abs_srcdir@/data/sqlldr_copy.sql | @abs_bindir@/gsql -t -r -p @portstring@ -d regression
TRUNCATE TABLE
select * from SQLLDR_TBL order by 1,2,3,4;
 id | name | con | dt 
----+------+-----+----
 40 | d    | U   | 
(1 row)

\! @abs_bindir@/gsql -t -r -p @portstring@ -d regression -c "load data infile '@abs_srcdir@/data/sqlldr_tab.txt' replace into table sqlldr_tbl WHEN (7-9) = '200' TRAILING NULLCOLS;" > @abs_srcdir@/data/sqlldr_copy.sql
\! cat @abs_srcdir@/data/sqlldr_copy.sql | @abs_bindir@/gsql -t -r -p @portstring@ -d regression
TRUNCATE TABLE
select * from SQLLDR_TBL order by 1,2,3,4;
 id | name | con |            dt            
----+------+-----+--------------------------
  2 | b    | Y   | Tue Jul 08 00:00:00 2008
  3 | c    | Z   | Wed Jul 08 00:00:00 2009
(2 rows)

\! @abs_bindir@/gsql -t -r -p @portstring@ -d regression -c "load data infile '@abs_srcdir@/data/sqlldr_ver.txt' replace into table sqlldr_tbl fields terminated by '|' WHEN (2-2) = '|' TRAILING NULLCOLS;" > @abs_srcdir@/data/sqlldr_copy.sql
\! cat @abs_srcdir@/data/sqlldr_copy.sql | @abs_bindir@/gsql -t -r -p @portstring@ -d regression
TRUNCATE TABLE
select * from SQLLDR_TBL order by 1,2,3,4;
 id | name | con |            dt            
----+------+-----+--------------------------
  1 | a    | X   | 
  2 | b    | Y   | Tue Jul 08 00:00:00 2008
  3 | c    | Z   | Wed Jul 08 00:00:00 2009
(3 rows)

\! @abs_bindir@/gsql -t -r -p @portstring@ -d regression -c "load data infile '@abs_srcdir@/data/sqlldr_ver.txt' replace into table sqlldr_tbl fields terminated by '|' WHEN (2-4) = '|b|' TRAILING NULLCOLS;" > @abs_srcdir@/data/sqlldr_copy.sql
\! cat @abs_srcdir@/data/sqlldr_copy.sql | @abs_bindir@/gsql -t -r -p @portstring@ -d regression
TRUNCATE TABLE
select * from SQLLDR_TBL order by 1,2,3,4;
 id | name | con |            dt            
----+------+-----+--------------------------
  2 | b    | Y   | Tue Jul 08 00:00:00 2008
(1 row)

-- teardown
drop table SQLLDR_TBL;