VAX float to IEEE float - OO4O and Wizards(Archived)

Hi ,
Did any one of u faced the problem of migrating VAX/VMS files having - float data(8-bytes) to oracle8i(windows NT) platform.
I have the VMS file with some character fields and 8-byte float data.
When using sqlldr for the same, there is a problem that VAX/VMS has different format for storing float data, and sqlldr expects it to be in IEEE format.
any help is appreciated
Thx
Sam

Related

Cobol COMP type

Hello everyone.
I am trying to import data from a cobol file containing ,among others , a field with type "COMP PIC 9(06)v99". If I am correct the field is represented by 4 chars in the file. I have tried defining the field in the sqlloader control file as DECIMAL(8,2), DECIMAL EXTERNAL, INTEGER and INTEGER(4). The only time I get results is with INTEGER(4) but the values are not correct.
Any Ideas?
Thanx 
Hi
First the basic COBOL question. The correct datatype is INTEGER(4) UNSIGNED. Integer defaults to signed, but the pic(ture) clause does not specify a sign. In order to get the correct results UNSIGNED must be specified.
Integers are not considered portable by sqlloader because of byte size, byte order, and sign representation. So there still may be problems loading the data. The byte order may also need to be specified (big endian/little endian ness)
There is a paper on OTN that has some examples also;
http://www.oracle.com/technology/products/warehouse/pdf/COBOL%20Data%20Integration%20with%20OWB10gR2.pdf
Cheers
David

Extended ASCII on Linux issue

Hi folks,
I'm having an issue with some "extended ASCII" characters in the context of our system configuration. We FTP a flat file from our mainframe to a linux box where we then run GenData & GenPrint. We ran into a problem when one of our transactions included a foreign street address ("Västra") that had an "extended ASCII" character (starts as EBCIDIC hex'43'/decimal 67, would convert to ASCII hex'84'/decimal 132). Using a straight IBM FTP from MVS to Linux, it doesn't convert correctly, and GenData bombs trying to read the input file when it comes to that character.
We added the following LocSite command to our MVS FTP to force character conversion from codepage 37 to UTF-8:
locSite EN=MBCS
locSite MB=(IBM-037,UTF-8)
And now it shows up as 2 characters in Linux ("Västra"), which GenData can now handle, but I'm faced with 2 issues as a result:
1. Since it's now represented by 2 bytes, any fields on the same record with offsets after this field are now "off" by a byte
2. GenPrint bombs. I don't know that it's directly tied to the extended ASCII character, but swapping that character out fixes the issue, so off the cuff I presume it is related.
Anybody have any thoughts/suggestions? If it helps, we're running 11.5.
Thanks in advance,
Gregg 
Nevermind, folks, I answered my own question...
Via the use of the Locale variables on the Linux machine, I was able to change my codepage to ISO8859-1, and then use that codepage (rather than UTF-8), which is a single byte character set (instead of a multiple byte characterset), and then it appears my problems were solved :)

Need help filtering non printable characters

Hi all,
I work at a data warehouse and one of our clients has requested we eliminate two non-printable character which are appearing in a field. We have decided to filter it out during the ETL process when loading the data into our database. I'm not sure how to do this and I have come to the community here for help. Does anyone have any ideas what the syntax would look like?
Here is an example similar to mine (fixed text file):
............
SAMPLE_PRODUCT_CODE          POSITION(440:444) CHAR,
SAMPLE_PRODUCT_CODE2          POSITION(445:449) CHAR,
ANOTHER_FIELD      POSITION(485:485) CHAR,
..............
In my case I have one field (SAMPLE_PRODUCT_CODE2) which periodically has two non printable characters (0x0C which is a “form feed” in both EBCDIC and ASCII) separated by several blank characters. I was wondering if it will be possible to handle this in our data load and enter a NULL value through the NULLIF function for these records. Thanks in advance for any help. 
(fixed text file)On Unix/Linux you may filter your file before loading it :$ tr -cd '\11\12\15\40-\176' < your_text_file > clean_file 
Hi Paul,
Thanks for your response. I guess I should have clarified we are running Windows server 2003 r2. Is there an equivalent DOS command that can be used to clean the file? 
Is there an equivalent DOS command that can be used to clean the file?If there is one, I don't know it.
You could use an hexadecimal editor (which I wouldn't do), or, better, install Cygwin (or something similar), which has Unix commands. See Re: script with sqlplus

VAX float to IEEE float

Hi ,
Did any one of u faced the problem of migrating VAX/VMS files having - float data(8-bytes) to oracle8i(windows NT) platform.
I have the VMS file with some character fields and 8-byte float data.
When using sqlldr for the same, there is a problem that VAX/VMS has different format for storing float data, and sqlldr expects it to be in IEEE format.
any help is appreciated
Thx
Sam

VARCHAR2 and UTF-8

My database character set is UTF-8, and I have many VARCHAR2 columns, because the length of the data varies a lot. Now I know that the VARCHAR2 column length is in bytes, and UTF-8 encoded characters often take up more than one byte. Still I don't understand why SQLLDR refuses to insert into a VARCHAR2(1000) column a value that is pure ASCII and only 350 characters in length. Another field is about 460 characters with some requiring more than one byte, and it still doesn't bite. Can anyone explain? I can supply specifics if you need.
Best regards,
Jere
null 
Hi Jere,
Can you please post the foll info too
1. What is the client/server OS? What is the version of Oracle ?
2. What is the NLS_LANG setting on the client?
3. Snippets from the control/data file.
4. How was the data prepared ? Was the data in UTF8/UCS2 format ?
5. What language characters are you trying to insert ? How many bytes does each character take?
Regards,
Shirish 
OK, here goes:
1. Client and server are the same, Microsoft Windows NT 4.0 Workstation with SP4. Oracle version is 8i Enterprise Edition Release 3 (8.1.7).
2. No specific NLS_LANG setting, other UTF-8 data has been previously entered into the database with no problems.
3. Extract from the control file:
LOAD DATA CHARACTERSET UTF8
INFILE *
APPEND INTO TABLE charset
FIELDS TERMINATED BY ";" OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(charset_id SEQUENCE(MAX), charset_name, alt_names,
mime_name, mibenum, encoding, charset_desc, free_text, url, url_title)
BEGINDATA
...and then comes the data.
4. The data was prepared in Microsoft Excel and converted into UTF-8 in the worksheet, then saved into a CSV file.
5. The columns that have caused problems contained just the euro sign (U+20AC) and U+0153, U+0161, U+017E, U+0178. They take mostly two, max three bytes each.
--Jere
null

Categories

Resources