sql

sql does not like tabs!
comments are /* this is commented */
a blank line terminates a SQL command just like a ; does.
Spool someName..log will use log as the extension instead of the default lst.
spool someName..log APPEND will append.

select all jwars tables

select distinct table_name from all_tab_columns where owner in ('JWARS') order by table_name;
quit;

select all jwars tables with BLOBs

SELECT DISTINCT table_name FROM user_tab_cols WHERE  data_Type IN ('CLOB', 'LOB', 'BLOB');
quit;

does not work 5/23.24

print all jwars tables using a refcursor

SET AUTOPRINT ON
VARIABLE a REFCURSOR
BEGIN
 OPEN :a FOR select distinct table_name from all_tab_columns 
 where owner in ('JWARS') order by table_name;
END;
/

drop all jwars tables

DROP TABLESPACE jwars_primary INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE jwars_SCENARIOCOMPONENT INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE jwars_Jar INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE jwars_INDEX INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE jwars_ENVIRONMENT INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
DROP TABLESPACE jwars_INSTRUMENTS INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
exit;

primaryKey.sql

select dbms_metadata.get_ddl('CONSTRAINT', c.constraint_name) 
from user_constraints c where c.constraint_type = 'P'; 
quit;

iteration example

DECLARE CURSOR
all_my_tables
IS SELECT COLUMN_NAME, TABLE_NAME, DATA_TYPE from USER_COLUMNS
order by column_name;
varchar_count integer;
BEGIN
   FOR my_list IN all_my_tables LOOP
     IF column_type in (VARCHAR, VARCHAR2) then varchar_count:=varchar_count+1 ;
     execute immediate . . .
END LOOP;

 utl_file.put_ine(varchar_count);
END;
/

unlock an account.sql

ALTER USER username IDENTIFIED BY password ACCOUNT UNLOCK;
quit;

fix unusable indexes after ora-01502

-- this will create sql statements for every table that has an unusable index
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;'
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';
quit;

read first 100 rows

select * from (select * from SCENARIO_COMPONENT) where rownum  < 101

alterTbSize.sql

ALTER DATABASE DATAFILE 'C:\app\Jas\oradata\Jas\JAS_ENVIRONMENT001.dbf'
   RESIZE 12000M;
quit;

tbusage.sql

SELECT df.tablespace_name "Tablespace",
  totalusedspace "Used MB",
  (df.totalspace - tu.totalusedspace) "Free MB",
  df.totalspace "Total MB",
  ROUND(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "% Free"
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes) / 1048576) TotalSpace
  FROM dba_data_files
  GROUP BY tablespace_name
  ) df,
  (SELECT ROUND(SUM(bytes)/(1024*1024)) totalusedspace,
    tablespace_name
  FROM dba_segments
  GROUP BY tablespace_name
  ) tu
WHERE df.tablespace_name = tu.tablespace_name;
quit;

JWARS_INSTRUMENTS		      634	3366	   4000 	84
JWARS_PRIMARY			      107	1893	   2000 	95
JWARS_SCENARIOCOMPONENT 	      500	1500	   2000 	75
JWARS_INDEX			       76	 924	   1000 	92
JWARS_ENVIRONMENT		     6851	3149	  10000 	31

fixblob.sql

/*
. . imported "JWARS"."ENV_SCENARIO_GRID"                 4.764 GB  364599 rows
. . imported "JWARS"."TL_LOCATION_TO_TRACK_GRID"         653.1 MB     535 rows
*/


PROMPT AB_OMNI_NOISE ;   
ALTER TABLE AB_OMNI_NOISE  modify (OMNI_NOISE blob);
PROMPT AB_SHIPPING_NOISE_GRID ;   
ALTER TABLE AB_SHIPPING_NOISE_GRID  modify (SHIPPING_NOISE_GRID blob);
PROMPT AB_SPECTRAL_CURVE ;   
ALTER TABLE AB_SPECTRAL_CURVE  modify (SPECTRAL_CURVE blob);
PROMPT CHEMICAL_CLOUDS_table ;   
ALTER TABLE CHEMICAL_CLOUDS_table  modify (CHEMICAL_CLOUDS blob);
PROMPT ENV_PROVINCE_GRID ;   
ALTER TABLE ENV_PROVINCE_GRID  modify (Province_grid blob);
/*
PROMPT Env_Scenario_Grid ;   
ALTER TABLE Env_Scenario_Grid  modify (SCENARIO_GRID blob);
*/
PROMPT Env_Seasonal_Grid ;   
ALTER TABLE Env_Seasonal_Grid  modify (Seasonal_Grid blob);
PROMPT ENV_SHIPPING_NOISE_LEVEL ;   
ALTER TABLE ENV_SHIPPING_NOISE_LEVEL  modify (grid blob);
PROMPT ENV_SS_Volume ;   
ALTER TABLE ENV_SS_Volume  modify (VSS_GRID blob);
PROMPT Env_Static_Grid ;   
ALTER TABLE Env_Static_Grid  modify (STATIC_GRID blob);
PROMPT EXCURSION_SCENARIO ;   
ALTER TABLE EXCURSION_SCENARIO  modify (Scenario_Object blob);
PROMPT Excursion_Scenario_Component ;   
ALTER TABLE Excursion_Scenario_Component  modify (Scenario_Component blob);
PROMPT GEO_AREA_TRIANGULATION    
ALTER TABLE GEO_AREA_TRIANGULATION  modify (spatial_region blob);
PROMPT GEO_AREA_TRIANGULATION_NEW    
ALTER TABLE GEO_AREA_TRIANGULATION_NEW  modify (Triangle_Data blob);
PROMPT JWARS_Preferences    
ALTER TABLE JWARS_Preferences  modify (Pref_Object blob);
PROMPT JWARS_ReportDefinitions    
ALTER TABLE JWARS_ReportDefinitions  modify (Definition_Object blob);
PROMPT JWARS_SimRepInfo    
ALTER TABLE JWARS_SimRepInfo  modify (Replication_Object blob);
PROMPT JWARS_SimRunInfo    
ALTER TABLE JWARS_SimRunInfo  modify (Run_Object blob);
PROMPT Run_Setup_Components ;   
ALTER TABLE Run_Setup_Components  modify (RunSetup_Object blob);
PROMPT scenario ;   
ALTER TABLE scenario  modify (Scenario_Object blob);
PROMPT scenario_component ;   
ALTER TABLE scenario_component  modify (Scenario_Component blob);

PROMPT TL_LOCATION_TO_TRACK_GRID ;   
ALTER TABLE TL_LOCATION_TO_TRACK_GRID  modify (LOC_TO_TRACK_GRID blob);

PROMPT TL_LOCATION_TO_TRACK_ID_GRID ;   
ALTER TABLE TL_LOCATION_TO_TRACK_ID_GRID  modify (LOC_TO_TRACK_ID_GRID blob);
PROMPT TL_TRACK_TO_LOSS_CURVE ;   
ALTER TABLE TL_TRACK_TO_LOSS_CURVE  modify (TRACK_TO_LOSS_CURVE blob);
PROMPT TRANMISSION_LOSS ;   
ALTER TABLE TRANMISSION_LOSS  modify (TRANMISSION_LOSS blob);
quit;

descJwarsTables.sql

sqlplus jwars/password@jas @descAllTables.sql > descAllTables.log

prompt ' AB_OMNI_NOISE ';
desc  AB_OMNI_NOISE;
prompt ' AB_SHIPPING_NOISE_GRID ';
desc AB_SHIPPING_NOISE_GRID ;
prompt ' AB_SPECTRAL_CURVE ';
desc AB_SPECTRAL_CURVE ;
prompt ' AMBIENT_NOISE_AREA ';
desc AMBIENT_NOISE_AREA ;
prompt ' CHEM_CLOUDS_MUNITION_AGENT ';
desc CHEM_CLOUDS_MUNITION_AGENT ;
prompt ' CHEMICAL_CLOUDS_TABLE ';
desc CHEMICAL_CLOUDS_TABLE ;
prompt ' COMM_MSG_TYPE ';
desc COMM_MSG_TYPE ;
prompt ' COMM_MSG_TYPE_ASSOC ';
desc COMM_MSG_TYPE_ASSOC ;
prompt ' descRIPTOR_ASSOCIATIONS ';
desc descRIPTOR_ASSOCIATIONS ;
prompt ' descRIPTOR_CLASSIFICATION ';
desc descRIPTOR_CLASSIFICATION ;
prompt ' descRIPTOR_DEFINITION ';
desc descRIPTOR_DEFINITION ;
prompt ' descRIPTOR_RELATIONSHIPS ';
desc descRIPTOR_RELATIONSHIPS ;
prompt ' ENV_AREA ';
desc ENV_AREA ;
prompt ' ENV_LVL ';
desc ENV_LVL ;
prompt ' ENV_PARAM ';
desc ENV_PARAM ;
prompt ' ENV_PROVINCE_GRID ';
desc ENV_PROVINCE_GRID ;
prompt ' ENV_REVERBERATION_AREA ';
desc ENV_REVERBERATION_AREA ;
prompt ' ENV_SCENARIO ';
desc ENV_SCENARIO ;
prompt ' ENV_SCENARIO_GRID ';
desc ENV_SCENARIO_GRID ;
prompt ' ENV_SCENARIO_PARAM ';
desc ENV_SCENARIO_PARAM ;
prompt ' ENV_SCENARIO_PARAM_LAYER ';
desc ENV_SCENARIO_PARAM_LAYER ;
prompt ' ENV_SEASON ';
desc ENV_SEASON ;
prompt ' ENV_SEASONAL_GRID ';
desc ENV_SEASONAL_GRID ;
prompt ' ENV_SHIPPING_NOISE_LEVEL ';
desc ENV_SHIPPING_NOISE_LEVEL ;
prompt ' ENV_SHIPPING_NOISE_LEVEL_AREA ';
desc ENV_SHIPPING_NOISE_LEVEL_AREA ;
prompt ' ENV_SS_VOLUME ';
desc ENV_SS_VOLUME ;
prompt ' ENV_STATIC_GRID ';
desc ENV_STATIC_GRID ;
prompt ' EXCURSION_SCENARIO ';
desc EXCURSION_SCENARIO ;
prompt ' EXCURSION_SCENARIO_COMPONENT ';
desc EXCURSION_SCENARIO_COMPONENT ;
prompt ' GEO_AREA_TRIANGULATION ';
desc GEO_AREA_TRIANGULATION ;
prompt ' GEO_AREA_TRIANGULATION_NEW ';
desc GEO_AREA_TRIANGULATION_NEW ;
prompt ' GEO_AREA_TRIANGULATION_REF ';
desc GEO_AREA_TRIANGULATION_REF ;
prompt ' GEO_AREA_TRIANGULATION_REF_NEW ';
desc GEO_AREA_TRIANGULATION_REF_NEW ;
prompt ' JAR_TABLE ';
desc JAR_TABLE ;
prompt ' JAR_TABLE_COLUMN ';
desc JAR_TABLE_COLUMN ;
prompt ' JAR_TABLE_STRUCTURE ';
desc JAR_TABLE_STRUCTURE ;
prompt ' JAREXT_TABLE_ALTERED ';
desc JAREXT_TABLE_ALTERED ;
prompt ' JAREXT_TABLE_COLUMNS ';
desc JAREXT_TABLE_COLUMNS ;
prompt ' JAREXT_TABLE_COMPONENT ';
desc JAREXT_TABLE_COMPONENT ;
prompt ' JAREXT_TABLE_PK ';
desc JAREXT_TABLE_PK ;
prompt ' JWARS_COMPONENTS ';
desc JWARS_COMPONENTS ;
prompt ' JWARS_DB_INSTALL ';
desc JWARS_DB_INSTALL ;
prompt ' JWARS_PREFERENCES ';
desc JWARS_PREFERENCES ;
prompt ' JWARS_REPORTDEFINITIONS ';
desc JWARS_REPORTDEFINITIONS ;
prompt ' JWARS_SIMREPINFO ';
desc JWARS_SIMREPINFO ;
prompt ' JWARS_SIMREPINFO_HISTORY ';
desc JWARS_SIMREPINFO_HISTORY ;
prompt ' JWARS_SIMRUNINFO ';
desc JWARS_SIMRUNINFO ;
prompt ' JWARS_SIMRUNINFO_HISTORY ';
desc JWARS_SIMRUNINFO_HISTORY ;
prompt ' MOB_BSE_LIST ';
desc MOB_BSE_LIST ;
prompt ' MOB_CNTRY ';
desc MOB_CNTRY ;
prompt ' MOB_EQUIPMENT_CHAR ';
desc MOB_EQUIPMENT_CHAR ;
prompt ' MOB_FORCE ';
desc MOB_FORCE ;
prompt ' MOB_GEO_LOC_TYP ';
desc MOB_GEO_LOC_TYP ;
prompt ' MOB_GEOFILE ';
desc MOB_GEOFILE ;
prompt ' MOB_RESUPPLY ';
desc MOB_RESUPPLY ;
prompt ' MOB_SEALIFT_MRS_SHIPS ';
desc MOB_SEALIFT_MRS_SHIPS ;
prompt ' MOB_SRFDETAIL ';
desc MOB_SRFDETAIL ;
prompt ' MOB_SRFFORCECAT ';
desc MOB_SRFFORCECAT ;
prompt ' MOB_STATE ';
desc MOB_STATE ;
prompt ' MOB_TRUCK_CLASSIFICATION ';
desc MOB_TRUCK_CLASSIFICATION ;
prompt ' MOB_TUCHAF2 ';
desc MOB_TUCHAF2 ;
prompt ' MOB_TUCHAF3 ';
desc MOB_TUCHAF3 ;
prompt ' RUN_SETUP_COMPONENTS ';
desc RUN_SETUP_COMPONENTS ;
prompt ' SCENARIO ';
desc SCENARIO ;
prompt ' SCENARIO_COMPONENT ';
desc SCENARIO_COMPONENT ;
prompt ' SCENARIO_COMPONENT_LOG ';
desc SCENARIO_COMPONENT_LOG ;
prompt ' SCENARIO_COMPONENT_REF ';
desc SCENARIO_COMPONENT_REF ;
prompt ' SCENARIO_LOG ';
desc SCENARIO_LOG ;
prompt ' TL_AREA ';
desc TL_AREA ;
prompt ' TL_LOC_TO_TRACK_TIME_INDEX ';
desc TL_LOC_TO_TRACK_TIME_INDEX ;
prompt ' TL_LOCATION_TO_TRACK_GRID ';
desc TL_LOCATION_TO_TRACK_GRID ;
prompt ' TL_LOCATION_TO_TRACK_ID_GRID ';
desc TL_LOCATION_TO_TRACK_ID_GRID ;
prompt ' TL_PARAMS_TO_TL_TIME_INDEX ';
desc TL_PARAMS_TO_TL_TIME_INDEX ;
prompt ' TL_SCENARIO ';
desc TL_SCENARIO ;
prompt ' TL_TRACK_TO_LOSS_CURVE ';
desc TL_TRACK_TO_LOSS_CURVE ;
prompt ' TRANMISSION_LOSS ';
desc TRANMISSION_LOSS ;
prompt ' US_JWARS_LINS ';
desc US_JWARS_LINS ;
prompt ' VIDEO_PLAYBACK_VIEW ';
desc VIDEO_PLAYBACK_VIEW ;
quit;

xyzzy.sql

create or replace directory xyzzy as 'c:\temp\xyzzy';
grant read, write on directory xyzzy to public;
quit;

importDumpFile.bat

impdp system/jwars@jas table_exists_action=append schemas=JWARS directory=xyzzy dumpfile=expdatOracle11LongRaw.dmp logfile=importExpdatOracle11LongRaw.log

exportOneTable.bat

expdp jwars/password@jas tables=(jwars_preferences) directory=xyzzy dumpfile=jwars_preferences.dmp logfile=jwars_preferences.log

importOneTableFromDumpFile.bat

-- note tables=
impdp system/jwars@jas table_exists_action=append tables=jwars.jwars_preferences directory=xyzzy dumpfile=expdatOracle11LongRaw.dmp logfile=importExpdatOracle11LongRaw.log

Create Long raw table

-- you cannot create a table with a long raw in Oracle 11
create table abc (SCENARIO_COMPONENT                                 LONG RAW,
 SCENARIO_NAME                             NOT NULL VARCHAR2(255),
 DATE_TIME                                 NOT NULL CHAR(20),
 COMPONENT_TYPE                            NOT NULL VARCHAR2(255),
 CREATED_BY                                NOT NULL VARCHAR2(30),
 LONG_RAW_SIZE                             NOT NULL NUMBER,
 ID                                                 VARCHAR2(500)
);

createallIndexes.sql

THIS DOES NOT CREATE ALL INDEXES! 2022 04 27

CREATE INDEX envscenComp_excursionName_key  
   ON Excursion_Scenario_Component (Excursion_Name)
   TABLESPACE jwars_index
   STORAGE (INITIAL        50K
            NEXT           50K
            MINEXTENTS      1
            MAXEXTENTS    512
            PCTINCREASE     0);
			
REM -- Create additional indexes on the table to speed up retrievals.
CREATE UNIQUE INDEX descDef_nameType_ukey 
   ON descriptor_definition (Descriptor_Name, Descriptor_Type)
   TABLESPACE jwars_index
   STORAGE (INITIAL      10K
            NEXT         10K
            MINEXTENTS    1
            MAXEXTENTS  121
            PCTINCREASE   0);

REM -- Create additional indexes on the table to speed up retrievals.
CREATE INDEX descDef_type_key ON descriptor_definition (Descriptor_Type)
   TABLESPACE jwars_index
   STORAGE (INITIAL      10K
            NEXT         10K
            MINEXTENTS    1
            MAXEXTENTS  121
            PCTINCREASE   0);
			
CREATE INDEX scenComp_scenarioName_key  
   ON scenario_component (Scenario_Name)
   TABLESPACE jwars_index
   STORAGE (INITIAL            50K
            NEXT               50K
            MINEXTENTS          1
            MAXEXTENTS        121
            PCTINCREASE         0);

CREATE INDEX scenComp_scenarioComp_key
   ON scenario_component (Component_Type)
   TABLESPACE jwars_index
   STORAGE (INITIAL            50K
            NEXT               50K
            MINEXTENTS          1
            MAXEXTENTS        121
            PCTINCREASE         0);
			
REM -- Create additional indexes on the table to speed up retrievals.
CREATE INDEX descRel_child_key ON descriptor_relationships (Child_Type)
   TABLESPACE jwars_index
   STORAGE (INITIAL      10K
            NEXT         10K
            MINEXTENTS    1
            MAXEXTENTS  121
            PCTINCREASE   0);
			
REM -- Create additional indexes on the table to speed up retrievals.
CREATE INDEX descAssc_parent_key ON descriptor_associations (Descriptor_Parent_Id, Descriptor_Parent_Type)
   TABLESPACE jwars_index
   STORAGE (INITIAL      10K
            NEXT         10K
            MINEXTENTS    1
            MAXEXTENTS  512
            PCTINCREASE   0);

CREATE INDEX descAssc_child_key ON descriptor_associations (Descriptor_Child_Id, Descriptor_Child_Type)
   TABLESPACE jwars_index
   STORAGE (INITIAL      10K
            NEXT         10K
            MINEXTENTS    1
            MAXEXTENTS  512
            PCTINCREASE   0);
			
		REM -- Create additional indexes on the table to speed up retrievals.
CREATE INDEX simRunInfo_runOwner_key ON JWARS_SimRunInfo (Run_Owner)
   TABLESPACE jwars_index
   STORAGE (INITIAL      50K
            NEXT         50K
            MINEXTENTS    1
            MAXEXTENTS  512
            PCTINCREASE   0);

CREATE INDEX simRunInfo_runBaseName_key ON JWARS_SimRunInfo (Run_Base_Name)
   TABLESPACE jwars_index
   STORAGE (INITIAL      50K
            NEXT         50K
            MINEXTENTS    1
            MAXEXTENTS  512
            PCTINCREASE   0);

CREATE INDEX simRunInfo_runExcurName_key ON JWARS_SimRunInfo (Run_Excursion_Name)
   TABLESPACE jwars_index
   STORAGE (INITIAL        50K
            NEXT           50K
            MINEXTENTS      1
            MAXEXTENTS    512 
            PCTINCREASE     0);
			
REM -- Create additional indexes on the table to speed up retrievals.
CREATE INDEX chemCloud_data_set_key ON CHEMICAL_CLOUDS_TABLE (DATA_SET_NM)
   TABLESPACE jwars_index
   STORAGE (INITIAL       1M
            NEXT          1M
            MINEXTENTS     1
            MAXEXTENTS   121
            PCTINCREASE    0);
			

REM -- Create additional indexes on the table to speed up retrievals.
CREATE INDEX jar_table_table_key ON jar_table_column (JAR_TABLE_NAME, RELEASE_VER)
   TABLESPACE jwars_index
   STORAGE (INITIAL      100K
            NEXT         100K
            MINEXTENTS     1
            MAXEXTENTS   121
            PCTINCREASE    0);

CREATE INDEX jar_tc_rv_key ON jar_table_column (RELEASE_VER)
   TABLESPACE jwars_index
   STORAGE (INITIAL      100K
            NEXT         100K
            MINEXTENTS     1
            MAXEXTENTS   121
            PCTINCREASE    0);
			
REM -- Create additional indexes on the table to speed up retrievals.
CREATE INDEX mobCntry_cntry_nm_key ON mob_cntry (Cntry_Nm)
   TABLESPACE jwars_index
   STORAGE (INITIAL         100K
            NEXT            100K
            MINEXTENTS        1
            MAXEXTENTS      121
            PCTINCREASE       0);
			
REM -- Create additional indexes on the table to speed up retrievals.
CREATE INDEX mobState_stateJcsCD_key ON mob_state (State_Jcs_CD)
   TABLESPACE jwars_index
   STORAGE (INITIAL            1K
            NEXT               1K
            MINEXTENTS         1
            MAXEXTENTS       121
            PCTINCREASE        0);

countRecsInAllTables.sql

sqlplus jwars/password@jas @countRecsInAllTables.sql > countRecsInAllTables.log

prompt 	AB_OMNI_NOISE		AB_OMNI_NOISE	;
select	count(*)	from	AB_OMNI_NOISE	;
prompt 	AB_SHIPPING_NOISE_GRID		AB_SHIPPING_NOISE_GRID	;
select	count(*)	from	AB_SHIPPING_NOISE_GRID	;
prompt 	AB_SPECTRAL_CURVE		AB_SPECTRAL_CURVE	;
select	count(*)	from	AB_SPECTRAL_CURVE	;
prompt 	AMBIENT_NOISE_AREA		AMBIENT_NOISE_AREA	;
select	count(*)	from	AMBIENT_NOISE_AREA	;
prompt 	CHEM_CLOUDS_MUNITION_AGENT;		CHEM_CLOUDS_MUNITION_AGENT;	
select	count(*)	from	CHEM_CLOUDS_MUNITION_AGENT;	
prompt 	CHEMICAL_CLOUDS_TABLE		CHEMICAL_CLOUDS_TABLE	;
select	count(*)	from	CHEMICAL_CLOUDS_TABLE	;
prompt 	COMM_MSG_TYPE		COMM_MSG_TYPE	;
select	count(*)	from	COMM_MSG_TYPE	;
prompt 	COMM_MSG_TYPE_ASSOC		COMM_MSG_TYPE_ASSOC	;
select	count(*)	from	COMM_MSG_TYPE_ASSOC	;
prompt 	DESCRIPTOR_ASSOCIATIONS;		DESCRIPTOR_ASSOCIATIONS;	
select	count(*)	from	DESCRIPTOR_ASSOCIATIONS;	
prompt 	DESCRIPTOR_CLASSIFICATION;		DESCRIPTOR_CLASSIFICATION;	
select	count(*)	from	DESCRIPTOR_CLASSIFICATION;	
prompt 	DESCRIPTOR_DEFINITION		DESCRIPTOR_DEFINITION	;
select	count(*)	from	DESCRIPTOR_DEFINITION	;
prompt 	DESCRIPTOR_RELATIONSHIPS;		DESCRIPTOR_RELATIONSHIPS;	
select	count(*)	from	DESCRIPTOR_RELATIONSHIPS;	
prompt 	ENV_AREA		ENV_AREA	;
select	count(*)	from	ENV_AREA	;
prompt 	ENV_LVL		ENV_LVL	;
select	count(*)	from	ENV_LVL	;
prompt 	ENV_PARAM		ENV_PARAM	;
select	count(*)	from	ENV_PARAM	;
prompt 	ENV_PROVINCE_GRID		ENV_PROVINCE_GRID	;
select	count(*)	from	ENV_PROVINCE_GRID	;
prompt 	ENV_REVERBERATION_AREA		ENV_REVERBERATION_AREA	;
select	count(*)	from	ENV_REVERBERATION_AREA	;
prompt 	ENV_SCENARIO		ENV_SCENARIO	;
select	count(*)	from	ENV_SCENARIO	;
prompt 	ENV_SCENARIO_GRID		ENV_SCENARIO_GRID	;
select	count(*)	from	ENV_SCENARIO_GRID	;
prompt 	ENV_SCENARIO_PARAM		ENV_SCENARIO_PARAM	;
select	count(*)	from	ENV_SCENARIO_PARAM	;
prompt 	ENV_SCENARIO_PARAM_LAYER;		ENV_SCENARIO_PARAM_LAYER;	
select	count(*)	from	ENV_SCENARIO_PARAM_LAYER;	
prompt 	ENV_SEASON		ENV_SEASON	;
select	count(*)	from	ENV_SEASON	;
prompt 	ENV_SEASONAL_GRID		ENV_SEASONAL_GRID	;
select	count(*)	from	ENV_SEASONAL_GRID	;
prompt 	ENV_SHIPPING_NOISE_LEVEL_AREA		ENV_SHIPPING_NOISE_LEVEL_AREA	;
select	count(*)	from	ENV_SHIPPING_NOISE_LEVEL_AREA	;
prompt 	ENV_SHIPPING_NOISE_LEVEL;		ENV_SHIPPING_NOISE_LEVEL;	
select	count(*)	from	ENV_SHIPPING_NOISE_LEVEL;	
prompt 	ENV_SS_VOLUME		ENV_SS_VOLUME	;
select	count(*)	from	ENV_SS_VOLUME	;
prompt 	ENV_STATIC_GRID		ENV_STATIC_GRID	;
select	count(*)	from	ENV_STATIC_GRID	;
prompt 	EXCURSION_SCENARIO		EXCURSION_SCENARIO	;
select	count(*)	from	EXCURSION_SCENARIO	;
prompt 	EXCURSION_SCENARIO_COMPONENT		EXCURSION_SCENARIO_COMPONENT	;
select	count(*)	from	EXCURSION_SCENARIO_COMPONENT	;
prompt 	GEO_AREA_TRIANGULATION_NEW;		GEO_AREA_TRIANGULATION_NEW;	
select	count(*)	from	GEO_AREA_TRIANGULATION_NEW;	
prompt 	GEO_AREA_TRIANGULATION_REF_NEW		GEO_AREA_TRIANGULATION_REF_NEW	;
select	count(*)	from	GEO_AREA_TRIANGULATION_REF_NEW	;
prompt 	JAR_TABLE		JAR_TABLE	;
select	count(*)	from	JAR_TABLE	;
prompt 	JAR_TABLE_COLUMN		JAR_TABLE_COLUMN	;
select	count(*)	from	JAR_TABLE_COLUMN	;
prompt 	JAR_TABLE_STRUCTURE		JAR_TABLE_STRUCTURE	;
select	count(*)	from	JAR_TABLE_STRUCTURE	;
prompt 	JAREXT_TABLE_ALTERED		JAREXT_TABLE_ALTERED	;
select	count(*)	from	JAREXT_TABLE_ALTERED	;
prompt 	JAREXT_TABLE_COLUMNS		JAREXT_TABLE_COLUMNS	;
select	count(*)	from	JAREXT_TABLE_COLUMNS	;
prompt 	JAREXT_TABLE_COMPONENT		JAREXT_TABLE_COMPONENT	;
select	count(*)	from	JAREXT_TABLE_COMPONENT	;
prompt 	JAREXT_TABLE_PK		JAREXT_TABLE_PK	;
select	count(*)	from	JAREXT_TABLE_PK	;
prompt 	JWARS_DB_INSTALL		JWARS_DB_INSTALL	;
select	count(*)	from	JWARS_DB_INSTALL	;
prompt 	JWARS_PREFERENCES		JWARS_PREFERENCES	;
select	count(*)	from	JWARS_PREFERENCES	;
prompt 	JWARS_REPORTDEFINITIONS;		JWARS_REPORTDEFINITIONS;	
select	count(*)	from	JWARS_REPORTDEFINITIONS;	
prompt 	JWARS_SIMREPINFO		JWARS_SIMREPINFO	;
select	count(*)	from	JWARS_SIMREPINFO	;
prompt 	JWARS_SIMRUNINFO		JWARS_SIMRUNINFO	;
select	count(*)	from	JWARS_SIMRUNINFO	;
prompt 	MOB_BSE_LIST		MOB_BSE_LIST	;
select	count(*)	from	MOB_BSE_LIST	;
prompt 	MOB_CNTRY		MOB_CNTRY	;
select	count(*)	from	MOB_CNTRY	;
prompt 	MOB_EQUIPMENT_CHAR		MOB_EQUIPMENT_CHAR	;
select	count(*)	from	MOB_EQUIPMENT_CHAR	;
prompt 	MOB_FORCE		MOB_FORCE	;
select	count(*)	from	MOB_FORCE	;
prompt 	MOB_GEO_LOC_TYP		MOB_GEO_LOC_TYP	;
select	count(*)	from	MOB_GEO_LOC_TYP	;
prompt 	MOB_GEOFILE		MOB_GEOFILE	;
select	count(*)	from	MOB_GEOFILE	;
prompt 	MOB_RESUPPLY		MOB_RESUPPLY	;
select	count(*)	from	MOB_RESUPPLY	;
prompt 	MOB_SEALIFT_MRS_SHIPS		MOB_SEALIFT_MRS_SHIPS	;
select	count(*)	from	MOB_SEALIFT_MRS_SHIPS	;
prompt 	MOB_SRFDETAIL		MOB_SRFDETAIL	;
select	count(*)	from	MOB_SRFDETAIL	;
prompt 	MOB_SRFFORCECAT		MOB_SRFFORCECAT	;
select	count(*)	from	MOB_SRFFORCECAT	;
prompt 	MOB_STATE		MOB_STATE	;
select	count(*)	from	MOB_STATE	;
prompt 	MOB_TRUCK_CLASSIFICATION;		MOB_TRUCK_CLASSIFICATION;	
select	count(*)	from	MOB_TRUCK_CLASSIFICATION;	
prompt 	MOB_TUCHAF2		MOB_TUCHAF2	;
select	count(*)	from	MOB_TUCHAF2	;
prompt 	MOB_TUCHAF3		MOB_TUCHAF3	;
select	count(*)	from	MOB_TUCHAF3	;
prompt 	RUN_SETUP_COMPONENTS		RUN_SETUP_COMPONENTS	;
select	count(*)	from	RUN_SETUP_COMPONENTS	;
prompt 	SCENARIO		SCENARIO	;
select	count(*)	from	SCENARIO	;
prompt 	SCENARIO_COMPONENT		SCENARIO_COMPONENT	;
select	count(*)	from	SCENARIO_COMPONENT	;
prompt 	SCENARIO_COMPONENT_LOG		SCENARIO_COMPONENT_LOG	;
select	count(*)	from	SCENARIO_COMPONENT_LOG	;
prompt 	SCENARIO_COMPONENT_REF		SCENARIO_COMPONENT_REF	;
select	count(*)	from	SCENARIO_COMPONENT_REF	;
prompt 	SCENARIO_LOG		SCENARIO_LOG	;
select	count(*)	from	SCENARIO_LOG	;
prompt 	TL_AREA		TL_AREA	;
select	count(*)	from	TL_AREA	;
prompt 	TL_LOC_TO_TRACK_TIME_INDEX;		TL_LOC_TO_TRACK_TIME_INDEX;	
select	count(*)	from	TL_LOC_TO_TRACK_TIME_INDEX;	
prompt 	TL_LOCATION_TO_TRACK_GRID;		TL_LOCATION_TO_TRACK_GRID;	
select	count(*)	from	TL_LOCATION_TO_TRACK_GRID;	
prompt 	TL_LOCATION_TO_TRACK_ID_GRID;		TL_LOCATION_TO_TRACK_ID_GRID;	
select	count(*)	from	TL_LOCATION_TO_TRACK_ID_GRID;	
prompt 	TL_PARAMS_TO_TL_TIME_INDEX;		TL_PARAMS_TO_TL_TIME_INDEX;	
select	count(*)	from	TL_PARAMS_TO_TL_TIME_INDEX;	
prompt 	TL_SCENARIO		TL_SCENARIO	;
select	count(*)	from	TL_SCENARIO	;
prompt 	TL_TRACK_TO_LOSS_CURVE		TL_TRACK_TO_LOSS_CURVE	;
select	count(*)	from	TL_TRACK_TO_LOSS_CURVE	;
prompt 	TRANMISSION_LOSS		TRANMISSION_LOSS	;
select	count(*)	from	TRANMISSION_LOSS	;
prompt 	US_JWARS_LINS		US_JWARS_LINS	;
select	count(*)	from	US_JWARS_LINS	;
quit;

convert JWARS_Preferences from long raw to blob

REM use NUMBER NOT INTEGER!
CREATE TABLE JWARS_PreferencesNew (
        Pref_Object     BLOB,
        Owner           VarChar(20)    not null,
        Window          VarChar(50)    not null,
        Category        VarChar(20)    not null,
        PrefLevel       NUMBER,
   CONSTRAINT preference_pkeyNEW PRIMARY KEY (Owner, Window, Category)
      USING INDEX TABLESPACE jwars_index
      STORAGE (INITIAL      50K
               NEXT         50K
               MINEXTENTS    1
               MAXEXTENTS  121
               PCTINCREASE   0))
   TABLESPACE jwars_primary
   STORAGE (INITIAL      100K
            NEXT         100K
            MINEXTENTS     1
            MAXEXTENTS   512
            PCTINCREASE    0);

REM -- Grant all privileges to the specified role for this table.
GRANT ALL ON JWARS_PreferencesNew TO jwarsdb_role;

REM -- Add comments about the table.
COMMENT ON TABLE JWARS_PreferencesNew IS
  'Holds information about preferences to be used by browsers.';


insert into JWARS_PreferencesNew select to_lob(Pref_Object),   
        Owner,
        Window,
        Category,
        PrefLevel from JWARS_Preferences;
select count(*) from JWARS_PreferencesNew;

rename jwars_preferences after to_lob conversion

drop table JWARS_Preferences;
rename JWARS_PreferencesNew to JWARS_Preferences;
drop index preference_pkey;
alter index preference_pkeyNEW rename to preference_pkey;

create blob table using rows from scenario_component.

* show sequence and rename

drop table abc;
CREATE TABLE ABC (
SCENARIO_COMPONENT   BLOB,
LONG_RAW_SIZE     NUMBER NOT NULL
);
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 4100;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 31736;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 34336;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 43904;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 53644;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 62712;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 74008;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 81360;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 95776;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 111672;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 232960;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 571416;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 849952;
SELECT COUNT(*) FROM ABC;
INSERT INTO ABC SELECT TO_LOB(SCENARIO_COMPONENT),   LONG_RAW_SIZE  FROM SCENARIO_COMPONENT WHERE LONG_RAW_SIZE = 1997314;
SELECT COUNT(*) FROM ABC;
alter table abc add (id integer);
drop sequence abc_seq;
create sequence abc_seq;
update abc set id = abc_seq.nextval;
desc abc;
alter table abc rename column SCENARIO_COMPONENT to blobData;
alter table abc rename column long_raw_size to blobSize;
desc abc;
select blobSize, id from abc;
QUIT;

read blob table abc

"read blob table abc"
| anAbtQuerySpec resultTable conn  logonSpec results stmt  saveBufferSize connSpec |

logonSpec := AbtDatabaseLogonSpec id: 'jwars' password: 'password' server: 'jas'.
connSpec := AbtDatabaseConnectionSpec 
	forDbmClass: #AbtOracle10DatabaseManager
	databaseName: 'Jmmas'. "databaseName does not matter for Oracle. Probably the DSN name"
conn := connSpec connectUsingAlias: 'fred' logonSpec: logonSpec ifError: [:e| self halt]. 
"alias does not matter here ^^^"

results := OrderedCollection new.
stmt := 'SELECT * FROM ABC'. 
anAbtQuerySpec := 	AbtQuerySpec new statement: stmt; 	yourself.
				
resultTable := conn
				resultTableFromQuerySpec: anAbtQuerySpec
				withValues: #()
				ifError: [:error | error].
				
resultTable do: [:row | results add: row].
results inspect

Table of Contents

sql