3. Run the SQL Load script.
■ On Windows, enter:
prompt> oracle_ctl.bat
■ On Linux or UNIX, enter:
prompt> ./oracle_ctl.sh
For Microsoft SQL Server and Sybase migrations, if you are inserting into BLOB fields with SQL*Loader, you will receive the following error:
SQL*Loader-309: No SQL string allowed as part of LARGEOBJECT field specification
To handle situations indicated by this error, you can use either one of the following options:
■ Enable the Generate Stored Procedure for Migrate Blobs Offline SQL Developer
preference (see Migration: Generation Options).
■ Use the following Workaround.
Workaround
The workaround is to load the data (which is in hex format) into an additional CLOB field and then convert the CLOB to a BLOB through a PL/SQL procedure.
The only way to export binary data properly through the Microsoft SQL Server or Sybase Adaptive Server BCP is to export it in a hexadecimal (hex) format; however, to get the hex values into Oracle, save them in a CLOB (holds text) column, and then convert the hex values to binary values and insert them into the BLOB column. The problem here is that the HEXTORAW function in Oracle only converts a maximum of 2000 hex pairs. Consequently, write your own procedure that will convert (piece by piece) your hex data to binary. (In the following steps and examples, modify the START.SQL and FINISH.SQL to reflect your environment.
The following shows the code for two scripts, start.sql and finish.sql, that implement this workaround. Read the comments in the code, and modify any SQL statements as needed to reflect your environment and your needs.
-- START.SQL
-- Modify this for your environment.
-- This should be executed in the user schema in Oracle that contains the table. -- DESCRIPTION:
-- ALTERS THE OFFENDING TABLE SO THAT THE DATA MOVE CAN BE EXECUTED -- DISABLES TRIGGERS, INDEXES AND SEQUENCES ON THE OFFENDING TABLE
-- 1) Add an extra column to hold the hex string; alter table <tablename> add (<blob_column>_CLOB CLOB);
-- 2) Allow the BLOB column to accept NULLS alter table <tablename> MODIFY <blob_column> NULL;
-- 3) Disable triggers and sequences on <tablename> alter trigger <triggername> disable;
alter table <tablename> drop primary key cascade;
drop index <indexname>;
-- 4) Allow the table to use the tablespace
alter table <tablename> move lob (<blob_column>) store as (tablespace lob_ tablespace);
alter table <tablename> move lob (<blob_column>_clob) store as (tablespace lob_ tablespace);
COMMIT;
-- END OF FILE
-- FINISH.SQL
-- Modify this for your enironment.
-- This should be executed in the table schema in Oracle. -- DESCRIPTION:
-- MOVES THE DATA FROM CLOB TO BLOB
-- MODIFIES THE TABLE BACK TO ITS ORIGINAL SPEC (without a clob) -- THEN ENABLES THE SEQUENCES, TRIGGERS AND INDEXES AGAIN
-- Currently we have the hex values saved as -- text in the <blob_column>_CLOB column
-- And we have NULL in all rows for the <blob_column> column. -- We have to get BLOB locators for each row in the BLOB column
-- put empty blobs in the blob column
UPDATE <tablename> SET <blob_column>=EMPTY_BLOB();
Note: After you run start.sql and before you run finish.sql, run BCP; and before you run BCP, change the relevant line in the .ctl file from:
<blob_column> CHAR(2000000) "HEXTORAW (:<blob_column>)"
to:
COMMIT;
-- create the following procedure in your table schema CREATE OR REPLACE PROCEDURE CLOBTOBLOB
AS
inputLength NUMBER; -- size of input CLOB offSet NUMBER := 1;
pieceMaxSize NUMBER := 2000; -- the max size of each peice
piece VARCHAR2(2000); -- these pieces will make up the entire CLOB currentPlace NUMBER := 1; -- this is where were up to in the CLOB blobLoc BLOB; -- blob locator in the table
clobLoc CLOB; -- clob locator pointsthis is the value from the dat file
-- THIS HAS TO BE CHANGED FOR SPECIFIC CUSTOMER TABLE -- AND COLUMN NAMES
CURSOR cur IS SELECT <blob_column>_clob clob_column , <blob_column> blob_column FROM /*table*/<tablename> FOR UPDATE;
cur_rec cur%ROWTYPE;
BEGIN
OPEN cur;
FETCH cur INTO cur_rec;
WHILE cur%FOUND LOOP
--RETRIVE THE clobLoc and blobLoc clobLoc := cur_rec.clob_column; blobLoc := cur_rec.blob_column;
currentPlace := 1; -- reset evertime -- find the lenght of the clob
inputLength := DBMS_LOB.getLength(clobLoc);
-- loop through each peice LOOP
-- get the next piece and add it to the clob
piece := DBMS_LOB.subStr(clobLoc,pieceMaxSize,currentPlace);
-- append this piece to the BLOB
DBMS_LOB.WRITEAPPEND(blobLoc, LENGTH(piece)/2, HEXTORAW(piece));
currentPlace := currentPlace + pieceMaxSize ;
EXIT WHEN inputLength < currentplace; END LOOP;
FETCH cur INTO cur_rec; END LOOP;
END CLOBtoBLOB; /
-- now run the procedure
-- It will update the blob column with the correct binary representation -- of the clob column
-- drop the extra clob cloumn
alter table <tablename> drop column <blob_column>_clob;
-- 2) apply the constraint we removed during the data load alter table <tablename> MODIFY FILEBINARY NOT NULL;
-- Now re enable the triggers, indexes and primary keys alter trigger <triggername> enable;
ALTER TABLE <tablename> ADD ( CONSTRAINT <pkname> PRIMARY KEY ( <column>) ) ;
CREATE INDEX <index_name> ON <tablename>( <column> );
COMMIT;
-- END OF FILE
2.2.9 Making Queries Case Insensitive
With several third-party databases, it is common for queries to be case insensitive. For example, in such cases the following queries return the same results:
SELECT * FROM orders WHERE sales_rep = 'Oracle'; SELECT * FROM orders WHERE sales_rep = 'oracle'; SELECT * FROM orders WHERE sales_rep = 'OrAcLe';
If you want queries to be case insensitive for a user in the Oracle database, you can create an AFTER LOGON ON DATABASE trigger, in which you set, for that database user, the NLS_SORT session parameter to an Oracle sort name with _CI (for "case insensitive") appended.
The following example causes queries for user SMITH to use the German sort order and to be case insensitive:
CREATE OR REPLACE TRIGGER set_sort_order AFTER LOGON ON DATABASE DECLARE
username VARCHAR2(30); BEGIN
username:=SYS_CONTEXT('USERENV','SESSION_USER'); IF username LIKE 'SMITH' then
execute immediate 'alter session set NLS_COMP=LINGUISTIC'; execute immediate 'alter session set NLS_SORT=GERMAN_CI'; END IF;
END;
2.2.10 Testing the Oracle Database
During the testing phase, you test the application and Oracle database to make sure that the:
■ Migrated data is complete and accurate
■ Applications function in the same way as the source database ■ Oracle database produces the same results as the source database
■ Applications and Oracle database meet the operational and performance
requirements
You may already have a collection of unit tests and system tests from the original application that you can use to test the Oracle database. You should run these tests in the same way that you ran tests against the source database. However, regardless of
added features, you should ensure that the application connects to the Oracle database and that the SQL statements it issues produces the correct results.
See also the following:
■ Section 2.2.10.1, "Testing Methodology" ■ Section 2.2.10.2, "Testing the Oracle Database"
2.2.10.1 Testing Methodology
Many constraints shape the style and amount of testing that you perform on a database. Testing can contain one or all of the following:
■ Simple data validation
■ Full life cycle of testing addressing individual unit tests ■ System and acceptance testing
You should follow a strategy for testing that suits your organization and
circumstances. Your strategy should define the process by which you test the migrated application and Oracle database. A typical test method is the V-model, which is a staged approach where each feature of the database creation is mirrored with a testing phase.
Figure 2–2, "V-model with a Database Migration" shows an example of the V-model with a database migration scenario:
Figure 2–2 V-model with a Database Migration
There are several types of tests that you use during the migration process. During the testing stage, you go through several cycles of testing to enhance the quality of the database. The test cases you use should make sure that any issues encountered in a previous version of the Oracle database are not introduced again.
For example, if you have to make changes to the migrated schema based on test results, you may need to create a new version of the Oracle database schema. In practice, you use SQL Developer to create a base-line Oracle schema at the start of testing, and then edit this schema as you progress with testing.
Note: The tests that you run against the application vary
depending on the scope of the application. Oracle recommends that you thoroughly test each SQL statement that is changed in the application. You should also test the system to make sure that the application functions the same way as in the third-party database.
Database Migration Requirements Integration Testing Database/Application Design Database Object Testing System Acceptance Testing
2.2.10.2 Testing the Oracle Database
Use the test cases to verify that the Oracle database provides the same business logic results as the source database.
This procedure explains one way of testing the migrated database. Other methods are available and may be more appropriate to your business requirements.
To test the Oracle database: