Helpful sql during the ITIS process.

1. List of ITIS Names that are longer than 60 characters.

spool C:\Storet\STUU203\logs\rep_itis_names60.log
select itis_name from itis.itistaxa
where length(itis_name) >60;
spool off

2. Initial update of STORET.TSRITISC. Lee reviewed the list of
single-word comments from 03/10/2005 ITIS download and decided to
exclude the following list.  Run these update statements to TSRITISC
table after it is created in the process.

update storet.tsritisc
set action='APPLY';

update storet.tsritisc
set action='DROP'
where comment_name in ('?','Typifies','Suborder', 'Superfamily',
'Group', 'Section', 
     'Subgenus','Subgunus','Argentina', 'Brazil','Nearctic',
'Neotropical', 'Oriental');

commit;


3. Maximum counts in each of the ranks.

select lpad(max(substr(taxon_sort_code,1,1)),3) kng,
       lpad(max(substr(taxon_sort_code,2,1)),3) sbk,
       lpad(max(substr(taxon_sort_code,3,2)),3) phy,
       lpad(max(substr(taxon_sort_code,5,1)),3) sbp,
       lpad(max(substr(taxon_sort_code,6,1)),3) spc,
       lpad(max(substr(taxon_sort_code,7,2)),3) cls,
       lpad(max(substr(taxon_sort_code,9,1)),3) sbc,
       lpad(max(substr(taxon_sort_code,10,1)),3) ifc,
       lpad(max(substr(taxon_sort_code,11,2)),3) spo,
       lpad(max(substr(taxon_sort_code,13,2)),3) ord,
       lpad(max(substr(taxon_sort_code,15,2)),3) sbo,
       lpad(max(substr(taxon_sort_code,17,1)),3) ifo,
       lpad(max(substr(taxon_sort_code,18,2)),3) spf,
       lpad(max(substr(taxon_sort_code,20,3)),3) fam,
       lpad(max(substr(taxon_sort_code,23,2)),3) sbf,
       lpad(max(substr(taxon_sort_code,25,2)),3) tri,
       lpad(max(substr(taxon_sort_code,27,1)),3) sbt,
       lpad(max(substr(taxon_sort_code,28,3)),3) gen,
       lpad(max(substr(taxon_sort_code,31,2)),3) sbg,
       lpad(max(substr(taxon_sort_code,33,3)),3) spe,
       lpad(max(substr(taxon_sort_code,36,2)),3) ssp,
       lpad(max(substr(taxon_sort_code,38,2)),3) var,
       lpad(max(substr(taxon_sort_code,40,2)),3) form
from itis.itistaxa
spool C:\Storet\STUU203\logs\rep_itis_max_rank_counts.log
/
spool off

KNG SBK PHY SBP SPC CLS SBC IFC SPO ORD SBO IFO SPF FAM SBF TRI SBT GEN
SBG SPE SSP VAR FOR
--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
--- --- --- --- ---
  6   2  34   4   2  11   7   3  12  24  19   8  24 124  26  23   3 489
28 965  31  40  02

The above result shows that Species with 965, InfraOrder with 8 and
SubClass with 7 are closer to the allowable limits.









exp8i system/storet@gold file=gold.dmp log=exp_gold.log buffer=409600 owner=storet,storet1 compress=y
cd dbfix020
cd dbfix020
cd install
SQLPLUS SYSTEM/sto2et@storet @..\install\trc_tbls.sql
imp system/sto2et@storet file=gold.dmp log=imp_gold.log buffer=409600 fromuser=storet touser=storet ignore=y tables=TSMERMSG,TSMESTRY,TSMFHU,TSMGEOPA,TSMGEOUN,TSMLTHUN,TSMMAD,TSMNAICS,TSMNAL,TSMPRMVL,TSMPROLE,TSMPSR,TSMRRR,TSMSIC,TSMVSTC,TSRANLEQ,TSRBIOPT,TSRCALT,TSRCHALS,TSRCHAR,TSRCHTYP,TSRCPV,TSRCTCA,TSRFLDGR,TSRLBRMK,TSRMATRX,TSRUOM
imp system/sto2et@storet file=gold.dmp log=imp1gold.log buffer=409600 fromuser=storet1 touser=storet1 ignore=y tables=TSMEISN,TSMGNTXT,TSMORGAN,TSMPROGM,TSRANLPR,TSRCITN,TSRLSPP

SQLPLUS SYSTEM/sto2et@storet
truncate table STORET.TSMGEOPA ;
EXIT
imp system/sto2et@storet file=gold.dmp log=imp_TSMGEOPA.log buffer=409600 fromuser=storet touser=storet ignore=y tables=TSMGEOPA

sqlplus system/sto2et@storet
drop table STORET.APP_HELP cascade constraint ;
drop table STORET.FORMS_HELP cascade constraint ;
drop table STORET.HELP_KEYWORDS cascade constraint ;
drop table STORET.HELP_RELATED cascade constraint ;
drop table STORET.TSRPROJSQ cascade constraint ;
drop table STORET.TSRRLIST cascade constraint ;
drop table STORET.TSRRPARM cascade constraint ;
exit

sqlplus system/sto2et@storet
DELETE FROM STORET1.TSMEISN WHERE TSMEISN_ORG_ID<>'ZZZZZZZZ';
DELETE FROM STORET1.TSMGNTXT WHERE TSMGNTXT_ORG_ID<>'ZZZZZZZZ';
DELETE FROM STORET1.TSMORGAN WHERE ORG_ID<>'ZZZZZZZZ';
DELETE FROM STORET1.TSMPROGM WHERE TSMPROGM_ORG_ID<>'ZZZZZZZZ';
DELETE FROM STORET1.TSRANLPR WHERE TSRANLPR_ORG_ID<>'ZZZZZZZZ';
ALTER TRIGGER STORET1.TSRCITN_BEF_DEL    DISABLE;
DELETE FROM STORET1.TSRCITN WHERE TSRCITN_ORG_ID<>'ZZZZZZZZ';
ALTER TRIGGER STORET1.TSRCITN_BEF_DEL    ENABLE;
DELETE FROM STORET1.TSRLSPP WHERE TSRLSPP_ORG_ID<>'ZZZZZZZZ';
commit;
exit

sqlplus system/sto2et@storet
SET TRANSACTION USE ROLLBACK SEGMENT rbsbig;
-- Tables Schema Owner: STORET1
drop table STORET1.TSMADDR cascade constraints;
drop table STORET1.TSMALP cascade constraints;
drop table STORET1.TSMAMDA cascade constraints;
drop table STORET1.TSMCHKPT cascade constraints;
drop table STORET1.TSMCPORG cascade constraints;
drop table STORET1.TSMCSNG cascade constraints;
drop table STORET1.TSMEADDR cascade constraints;
drop table STORET1.TSMERS cascade constraints;
drop table STORET1.TSMESTLC cascade constraints;
drop table STORET1.TSMFCLTY cascade constraints;
drop table STORET1.TSMFILL cascade constraints;
drop table STORET1.TSMFSA cascade constraints;
drop table STORET1.TSMGLL cascade constraints;
drop table STORET1.TSMHOLE cascade constraints;
drop table STORET1.TSMIGUA cascade constraints;
drop table STORET1.TSMINTVL cascade constraints;
drop table STORET1.TSMLOG cascade constraints;
drop table STORET1.TSMOCNLC cascade constraints;
drop table STORET1.TSMOPNG cascade constraints;
drop table STORET1.TSMPCOA cascade constraints;
drop table STORET1.TSMPERSN cascade constraints;
drop table STORET1.TSMPIPE cascade constraints;
drop table STORET1.TSMPMGRD cascade constraints;
drop table STORET1.TSMPMTCT cascade constraints;
drop table STORET1.TSMPPA cascade constraints;
drop table STORET1.TSMPPRA cascade constraints;
drop table STORET1.TSMPRA cascade constraints;
drop table STORET1.TSMPROJ cascade constraints;
drop table STORET1.TSMPRPRA cascade constraints;
drop table STORET1.TSMPSA cascade constraints;
drop table STORET1.TSMPSRA cascade constraints;
drop table STORET1.TSMPUMP cascade constraints;
drop table STORET1.TSMRFLBL cascade constraints;
drop table STORET1.TSMSCTS cascade constraints;
drop table STORET1.TSMSOI cascade constraints;
drop table STORET1.TSMSPRNG cascade constraints;
drop table STORET1.TSMSTATN cascade constraints;
drop table STORET1.TSMTBINF cascade constraints;
drop table STORET1.TSMUOA cascade constraints;
drop table STORET1.TSMUSER cascade constraints;
drop table STORET1.TSMVSC cascade constraints;
drop table STORET1.TSMWELL cascade constraints;
drop table STORET1.TSMWLE cascade constraints;
--

drop table STORET1.TSRAAL cascade constraints;
drop table STORET1.TSRADL cascade constraints;
drop table STORET1.TSRBRG cascade constraints;
drop table STORET1.TSRBRGI cascade constraints;
drop table STORET1.TSRCCGA cascade constraints;
drop table STORET1.TSRCHDEF cascade constraints;
drop table STORET1.TSRCHGRP cascade constraints;
drop table STORET1.TSRCLDES cascade constraints;
drop table STORET1.TSRCPA cascade constraints;
drop table STORET1.TSRDQL cascade constraints;
drop table STORET1.TSREOD cascade constraints;
drop table STORET1.TSRFAFSA cascade constraints;
drop table STORET1.TSRFAPEA cascade constraints;
drop table STORET1.TSRFAPRA cascade constraints;
drop table STORET1.TSRFAQSA cascade constraints;
drop table STORET1.TSRFDACT cascade constraints;
drop table STORET1.TSRFDSET cascade constraints;
drop table STORET1.TSRFLDPR cascade constraints;
drop table STORET1.TSRFQS cascade constraints;
drop table STORET1.TSRFQSDP cascade constraints;
drop table STORET1.TSRFQSPA cascade constraints;
drop table STORET1.TSRGRCFG cascade constraints;
drop table STORET1.TSRHCSC cascade constraints;
drop table STORET1.TSRLAB cascade constraints;
drop table STORET1.TSROAPA cascade constraints;
drop table STORET1.TSROAPCA cascade constraints;
drop table STORET1.TSROLSPA cascade constraints;
drop table STORET1.TSROPPRD cascade constraints;
drop table STORET1.TSRPOPA cascade constraints;
drop table STORET1.TSRRCI cascade constraints;
drop table STORET1.TSRRLRA cascade constraints;
drop table STORET1.TSRRQCAF cascade constraints;
drop table STORET1.TSRRSULT cascade constraints;
drop table STORET1.TSRSDP cascade constraints;
drop table STORET1.TSRSMPLE cascade constraints;
drop table STORET1.TSRSTVST cascade constraints;
drop table STORET1.TSRTNOD cascade constraints;
drop table STORET1.TSRTOD cascade constraints;
drop table STORET1.TSRTPA cascade constraints;
drop table STORET1.TSRTRIP cascade constraints;
drop table STORET1.TSRTSA cascade constraints;
-- NEW STORET v2.0 Tables
drop table STORET1.TSMBLOB cascade constraints;
drop table STORET1.TSMFNA cascade constraints;
drop table STORET1.TSMPSW cascade constraints;
drop table STORET1.TSRDLIN cascade constraints;
drop table STORET1.TSRFACOA cascade constraints;
--
exit

Rem Prerequiste to Reference Table "dmp" file 
exp8i system/sto2et@storet file=refdata.dmp log=refdata.log buffer=409600 owner=storet,storet1 compress=y
Rem 

SQL> spool off
SQL> update storet.tsritisc
  2  set action='APPLY';

111 rows updated.

SQL>
SQL> update storet.tsritisc
  2  set action='DROP'
  3  where comment_name in ('?','Typifies','Suborder', 'Superfamily',
  4  'Group', 'Section',
  5       'Subgenus','Subgunus','Argentina', 'Brazil','Nearctic',
  6  'Neotropical', 'Oriental');

10 rows updated.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select lpad(max(substr(taxon_sort_code,1,1)),3) kng,
  2         lpad(max(substr(taxon_sort_code,2,1)),3) sbk,
  3         lpad(max(substr(taxon_sort_code,3,2)),3) phy,
  4         lpad(max(substr(taxon_sort_code,5,1)),3) sbp,
  5         lpad(max(substr(taxon_sort_code,6,1)),3) spc,
  6         lpad(max(substr(taxon_sort_code,7,2)),3) cls,
  7         lpad(max(substr(taxon_sort_code,9,1)),3) sbc,
  8         lpad(max(substr(taxon_sort_code,10,1)),3) ifc,
  9         lpad(max(substr(taxon_sort_code,11,2)),3) spo,
 10         lpad(max(substr(taxon_sort_code,13,2)),3) ord,
 11         lpad(max(substr(taxon_sort_code,15,2)),3) sbo,
 12         lpad(max(substr(taxon_sort_code,17,1)),3) ifo,
 13         lpad(max(substr(taxon_sort_code,18,2)),3) spf,
 14         lpad(max(substr(taxon_sort_code,20,3)),3) fam,
 15         lpad(max(substr(taxon_sort_code,23,2)),3) sbf,
 16         lpad(max(substr(taxon_sort_code,25,2)),3) tri,
 17         lpad(max(substr(taxon_sort_code,27,1)),3) sbt,
 18         lpad(max(substr(taxon_sort_code,28,3)),3) gen,
 19         lpad(max(substr(taxon_sort_code,31,2)),3) sbg,
 20         lpad(max(substr(taxon_sort_code,33,3)),3) spe,
 21         lpad(max(substr(taxon_sort_code,36,2)),3) ssp,
 22         lpad(max(substr(taxon_sort_code,38,2)),3) var,
 23         lpad(max(substr(taxon_sort_code,40,2)),3) form
 24  from itis.itistaxa
 25
SQL> spool C:\Storet\STUU203\logs\rep_itis_names60.log
SQL> /
from itis.itistaxa
          *
ERROR at line 24:
ORA-00942: table or view does not exist


SQL> spool off
SQL>