Operations Guide

10.2
- 10.5 HotFix 2
- 10.5 HotFix 1
- 10.5
- 10.2 HotFix 1
- 10.1 HotFix 1
- 10.1
- 10.0 HotFix 1
- 10.0

Back Next

Miscellaneous Tips

Loading User Source Tables - Oracle

When loading data to User Source Tables, make sure that the input file contains fixed length records and instruct the DBMS loader to read the file in binary mode.

For Oracle this is done with the

FIX

option. For example, if the input file contains fixed length records 16 bytes long encoded as UTF-8, the following control file could be used:


LOAD DATA
CHARACTERSET AL32UTF8
LENGTH SEMANTICS BYTE
INFILE ’testx182.ut8’ "FIX 16"
REPLACE
INTO TABLE TESTX182A
(
						NAME 		POSITION( 1: 16) CHAR
)

If the input file contains fixed length records 16 bytes long encoded as UTF-16, the following control file could be used.

The byte order must be specified so that SQL*Loader can convert it to match the DBMS Server machine’s byte order, if necessary.

LOAD DATA
CHARACTERSET UTF16
LENGTH SEMANTICS BYTE
BYTEORDER LITTLE
INFILE ’testx182.u6l’ "FIX 16"
REPLACE
INTO TABLE TESTX182B
(
						NAME 		POSITION( 1: 16) CHAR
)

Validating Loaded Data

Binary data stored in

CHAR

VARCHAR2

columns may be displayed using a number of methods:

It is easiest to work in hexadecimal format by converting the

CHAR

data to RAW, as SQL*Plus automatically displays RAW data in hexadecimal format. A sample package called

ids_conv

is provided that will convert CHAR to RAW and vice versa. For example, the following script installs the package and calls it:

@%SSABIN%\idsconv9.sql
select row_id, ids_conv.chartoraw(name) from T106;

It produces output similar to this:


ROW_ID IDS_CONV.CHARTORAW(NAME)
------ -------------------------------------------------------------------------
					1 28C3C0B9FA29CFD6B4FAB6ABCEF7B7BDB9ABCBBE202020202020202020202020202020202
					2 28C3C0B9FA29CFD6B4FAB6ABCEF7D1D4B9ABCBBE202020202020202020202020202020202
					3 B0AED2C0CBB9D6D0B9FAB7A2B5E7D3D0CFDEB9ABCBBE20202020202020202020202020202
					4 B0AED2C0CBB9D6D0B9FAD3D0CFDEB9ABCBBE2020202020202020202020202020202020202
					5 B0D7B5C3B2B9B1A6B8D6D3D0CFDEB9ABCBBEC9CFBAA3C1AAC2E7B4A620202020202020202
					6 B0B2B4EFD0C528C9CFBAA329C6F3D2B5D7C9D1AFD3D0CFDEB9ABCBBE20202020202020202
					7 B0B2B4EFD0C52EBBAAC7BFBBE1BCC6CAA6CAC2CEF1CBF9202020202020202020202020202
					8 B0B2B4EFD0C5BCC6CBE3BBFAD0C5CFA2CFB5CDB3B1B1BEA9D3D0CFDEB9ABCBBE202020202
					9 B0B2B4EFD0C5C7E5BBAAB4F3D1A7D0C5CFA2CFB5CDB328B1B1BEA929D3D0CFDEB9ABCBBE2
				   10 B0B2B5C2C0FBD3D0CFDEB9ABCBBE202020202020202020202020202020202020202020202

An alternative is to use the

DUMP

function to display table data in hexadecimal format. This also displays the name of the database character set used to store the column. For example,


select dump(ids_name,1016) from idt182;

DUMP(IDS_NAME,1016)
-----------------------------------------------------
Typ=1 Len=6 CharacterSet=AL16UTF16: 30,a0,30,a1,30,a2
Typ=1 Len=6 CharacterSet=AL16UTF16: 30,f0,30,f1,30,f2

dd

The

utility can be used to add newlines to a file containing fixed length records. Use

Rename Saved Search

Table of Contents

Operations Guide

Operations Guide

Miscellaneous Tips

Miscellaneous Tips

Loading User Source Tables - Oracle

Validating Loaded Data

dd