-- E.Gallas Jan 13, 2001
-- Test Oracle v817 for sqlldr bug seen previously
-- using temporary account/instance speakerdb@dev2
--------------------------------------------------
-- go to the ddl area
cd ~gallas/sb_data1/ora

-- enter sqlplus under table owner speakerdb on test instance dev2
-- run the ddl 
sqlplus speakerdb@dev2
  @cdsddl
  exit
grep error cdsddl.lst (got errors on grants - okay don't need roles for this test)

-- go to the data loading directory
cd ~gallas/sb_data1/ora/load

-- populate the instit and whod0 tables using sqlplus
sqlplus speakerdb@dev2
  set echo on;
  spool ins_insert.log;
  @ins_insert
  spool off;
  select institute from instits;
  
  spool who_insert.log;
  @who_insert
  spool off;
  select name from whod0s;

-- sqlldr the following ctl files (this should work correctly)
sqlldr speakerdb@dev2
control files: con_load, peo_load, con_load,con_load_0010, lin_load,lin_load_0010

-- look for errors, one peo not correct so his links got error too.
dir *.bad 
xemacs peo_load.ctl (correct Evgeny POPKOV)

-- modify ~gallas/sb_data1/cgi/spdb_index_def.py to use new instance
-- use web page: http://www-d0.fnal.gov/~d0conf/d0_private/index-gallas.html
-- to query the new database instance
-- The data looks correct.

-- delete all links, then sqlldr the known bad links file
sqlplus speakerdb@dev2
  delete from links;
  commit;
  exit;
sqlldr speakerdb@dev2
control file: lin_bad1

-- use web page: http://www-d0.fnal.gov/~d0conf/d0_private/index-gallas.html
-- to query the new database instance.
-- The data looks correct.
-- Summary:  sqlldr bug seems to be fixed in Oracle v817 :)
---- I have built the D0 speakerdb database on the test instance dev2
---- running the new version of Oracle v817.
---- I have loaded data using sqlldr files which previously resulted
---- in gross record corruption into tables where
---- constraints existed from more than one other table.
---- 
----    * sqlldr now enters only good records (previously entered them
----      multiple times when there were constraint violations in other
----      records).
----    * sqlldr now does not enter corrupt records
----    * sqlldr now stops loading after about 50 constraint violations are
----      found with appropriate errors in the .log file
----    * the .bad file looks right
---- 
---- Because of the severity of the corruption seen in previous versions,
---- I would still advise caution when using sqlldr,
---- but it seems to work correctly now from what I can see.

