Good Stuff
Ashraf Kazi, October 16, 2003 - 7:43 am UTC
Tom,
I like the stuff you did !! Excellent.
Would you please let us know, how to convert a number with decimal Points eg. -100.25
If I try converting this it gives Error. Numeric or value error.
Thanks & Regards,
Ashraf
October 16, 2003 - 10:38 am UTC
comp-3's don't have decimal places
but anyway -- you would do what I described I did
research the format (no clue what format you are wanting) and then write a snippet of code to do it.....
I will use your package in my project
Max Yao, November 21, 2003 - 9:31 am UTC
Tom,
I am working on one project to extract Oracle data to a flat text file for mainframe. In the data file there are three COMP-3 fields. I am going to modify your package for my data extract.
I have a question for you. There is a column in the extract data file user requirement. Its field size (format) is (09)v99 and its position is 203-208. The user wants the field to be a COMP-3 field. F appears in the rightmost byte indicating an absolute value. (09)v99 means there will have as many as 11 digits. But position 203-208 just can hold 6 digits.
I know in COMP-3 data type, one byte can store 2 digits. When I try to convert an 11 digits number, your function returns 12 digits. In other words, the COMP-3 data will use 12 positions.
select comp3.convert( '12345678901' ) a from dual;
A
------------------------------------------------------------
12345678901C
Please help me to resovle the above format vs. positions problem.
Thanks a lot!
Max
November 21, 2003 - 4:55 pm UTC
(09)v99 means 11?? i see 4 digits, an implied decimal and a sign? but it's been since high school that I did cobol so.....
all i did was take the definition (url referenced up there) and codify the definition. I don't really know that much about comp3 beyond that.
comp-3 fields
a reader, November 22, 2003 - 3:31 am UTC
Hi Max,
In the packed format, each byte contains two decimal digits, except for the rightmost byte, which contains the sign code in the right half.
Prabably you meant your field size (format) to be S9(09)v99 and its position is 203-208. The equivalent field size in oracle would be number(11,2). Which means your data in ascii would look something like this.
-999999999.99 to +999999999.99
position (203-208) holds six bytes - each byte contains two decimal digits, except the right most byte, which contain the sign code in the right half. That means you can have 2n-1 digits plus the sign bit. Here n=6 (for the field positins 203-208).
To fit in your example below your field size format should have been s9(11) and not S9(09)v99
select comp3.convert( '12345678901' ) a from dual;
Thanks,
COMP-3
Fabio Pasqualetti, February 22, 2004 - 5:22 pm UTC
Hi Max & Tom,
when Max tries to convert an 11 digits number, Tom's function returns a raw but SQL*Plus converts this raw data in a 12 digits VARCHAR2 ( raw data are rappresented in hex notation).
Max, try this:
SQL> select utl_raw.cast_to_varchar2(comp3.convert('12345678901' ) ) from dual;
UTL_RAW.CAST_TO_VARCHAR2(COMP3.CONVERT('12345678901'))
--------------------------------------------------------------------------------
4Vx
These 6 bytes are your COMP-3 field (with 3 non-printable characters)in position 203-208
Loading COMP-3 using SQL*Loader
Subbaraman, Kailasanathan, April 22, 2004 - 5:02 pm UTC
Tom
We have a EBCDIC file with 2 COMP-3 fields. We need to load this file into a ORACLE TABLE.
Could you give us an example of the Control File.
Thanks
April 23, 2004 - 9:51 am UTC
I don't have one -- but maybe someone reading this does....... (and will share)
comp-3 conversion
Mani, September 08, 2004 - 12:58 pm UTC
Hi Tom,
I am looking for some help to load comp-3 fields in Oracle tables. Do you have some conversion script for the same? It would be of great help to me..Thanks
Mani
September 08, 2004 - 2:24 pm UTC
in sqlldr, you would just use:
field POSITION (N:M) DECIMAL (5,2)
comp-3 conversions
Mani, September 08, 2004 - 5:29 pm UTC
I was getting an ORA-01488: invalid nibble or byte in the input data error..while using this
amount POSITION(22:31) DECIMAL(10,2),
I tried with
amount POSITION(22:31) ZONED(10,2) and it went thro.
Thx,
Mani
Fixed width files
A reader, May 24, 2006 - 10:58 pm UTC
You know how Cobol has these field definitions for numbers like PIC 9999v99 and stuff.
In other words, a field has a implicit format associated with it, any value you assign to that field will get that format.
I have always wondered if there was a way to do this in PL/SQL. For example, if I have
l_number number;
and I need to create a fixed width layout using this field, I would need to go
l_char := to_char(l_number,'<some number format>')
and use l_char in my output record.
Is there an easier way to "attach" a format specification to a numeric variable so that it is used whenever a value is assigned to that variable?
Thanks
May 25, 2006 - 1:34 pm UTC
no, they are just numbers. you would have to use "strings" to have some sort of formatting.
convert Decimal to Packed-decimal
ron, November 03, 2006 - 11:33 am UTC
tom...we have a requirement where i need to convert my decimal data in Oracle column into Packed-decimal for the mainframe Folks...
how do we do it ?...could'nt find it anywhere...
any help ?
thanks
Ron
November 03, 2006 - 12:15 pm UTC
do you have the specification for how the packed decimal data should be ENCODED
you have "number" data in Oracle, not really "decimal" data, but if you find the SPECIFICATION for your packed decimal, you should be able to generate a program that can take a number and output it that way.
(seems the mainframe guys should be able to accept "PIC" data as well - you know, strings - that would be easiest)
Follow up !!
ron, November 07, 2006 - 4:08 pm UTC
tom ..
------------------------------
you said : if you find the SPECIFICATION for your packed decimal, you should be able to generate a program that can take a number and output it that way.
------------------------------
Well..here are the requirements that they are looking at.
there is a column in Oracle called amount that is NUMBER..and it has lot of +ve and -ve values.
for eg. it has a value -23879.21.We would want this value converted to the packed equivalent which is 00000238792J
you said "you should be able to generate a program "..and iam curious, generate what kind of program...
can you help me in this please ?..
thanks
Ron
November 07, 2006 - 4:46 pm UTC
you cannot write a function that
a) takes -23878.21 as inputs
b) returns 00000238792J
(and a single example of one number does not a specification even BEGIN to make)
For Ron in Raligh
Andrew, November 07, 2006 - 6:03 pm UTC
Here is a starting place for what you want -- via sql. No need to create a function...
Maybe
create table t (num_col number(12,2));
insert into t values (-23878.21);
insert into t values (23878.21);
commit;
SELECT num_col,
CASE SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),1,1)
WHEN '-' THEN
CASE SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),-1,1)
WHEN '0' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'}'
WHEN '1' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'J'
WHEN '2' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'K'
WHEN '3' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'L'
WHEN '4' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'M'
WHEN '5' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'N'
WHEN '6' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'O'
WHEN '7' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'P'
WHEN '8' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'Q'
WHEN '9' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'R'
END
ELSE
CASE SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),-1,1)
WHEN '0' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'{'
WHEN '1' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'A'
WHEN '2' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'B'
WHEN '3' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'C'
WHEN '4' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'D'
WHEN '5' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'E'
WHEN '6' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'F'
WHEN '7' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'G'
WHEN '8' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'H'
WHEN '9' THEN SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,13)||'I'
END
END AS EBCDIC_NUM
from t;
NUM_COL EBCIC_NUM
========== ==================
-23878.21 0000002387821J
23878.21 0000002387821A
2 rows selected.
oops.
Andrew, November 08, 2006 - 9:51 am UTC
SELECT num_col,
CASE SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),1,1)
WHEN '-' THEN
CASE SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),-1,1)
WHEN '0' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'}'
WHEN '1' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'J'
WHEN '2' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'K'
WHEN '3' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'L'
WHEN '4' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'M'
WHEN '5' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'N'
WHEN '6' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'O'
WHEN '7' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'P'
WHEN '8' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'Q'
WHEN '9' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'R'
END
ELSE
CASE SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),-1,1)
WHEN '0' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'{'
WHEN '1' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'A'
WHEN '2' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'B'
WHEN '3' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'C'
WHEN '4' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'D'
WHEN '5' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'E'
WHEN '6' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'F'
WHEN '7' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'G'
WHEN '8' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'H'
WHEN '9' THEN
SUBSTR(TO_CHAR(num_col*100,'s0000000000009'),2,12)||'I'
END
END AS EBCDIC_NUM
from t;
web page correction
Linda Fisher, January 19, 2007 - 3:29 pm UTC
sql loader problem
eita, February 08, 2007 - 7:24 am UTC
hi..
i have .dat file which extract from mainframe in EBCDIC format n i have to load in table using sqlloader.
here, my .ctl sample:
AMTV2_RESERVE08 POSITION(857:861) DECIMAL(9,2),
AMTV2_RESERVE09 POSITION(862:866) DECIMAL(9,2)
here, my record layout
10 IN4331A-AMTV2-RESERVE08
PICTURE IS S9(7)V99
USAGE IS COMP-3
ELEMENT LENGTH IS 5
POSITION IS 857
10 IN4331A-AMTV2-RESERVE09
PICTURE IS S9(7)V99
USAGE IS COMP-3
ELEMENT LENGTH IS 5
POSITION IS 862
when i run the sqlloader, i got this error:
Invalid packed decimal nibble.
what must i do?
February 08, 2007 - 8:32 am UTC
if the data is in ebcdic, are you specifying that characterset in your NLS_LANG and/or in the control file
sqlloader problem
eita, February 08, 2007 - 8:29 pm UTC
i'm using this in my .ctl file :
load data
characterset we8ebcdic500
infile '/xxxxx/abc.dat
"fix 866"
replace
into table A
TRAILING NULLCOLS
(
AMTV2_RESERVE08 POSITION(857:861) DECIMAL(9,2),
AMTV2_RESERVE09 POSITION(862:866) DECIMAL(9,2)
)
but, i still have "Invalid packed decimal nibble" errors.. whats the prob with my .ctl file actually? i have search the solutions in internet, but still have no ideas. plz help me
thanks
create tables with numeric datatype
wawan, May 02, 2007 - 10:27 pm UTC
Tom,
when create a table with numeric datatypes,
for example to save rate, which user can input
fixed amount, or just %.
3,000,000 or .005.
I use create table XXX ( rate_ number(12,4));
does it effective enough , or any better creation ?
regards
May 03, 2007 - 7:59 am UTC
number(x,y)
the x and y are integrity constraints - 12 digits, four after the decimal.
If you have the need to
a) store a number
b) that cannot exceed 12 digits of precision
c) and four of them are on the right of the decimal place
then number(12,4) is the best datatype for you
novice
Sammy, May 10, 2007 - 8:11 am UTC
Hi Tom,
I have a column defined as a number(6 bytes). I need to convert it to a character type because the leading zeros are needed.
I created a new column and defined it varchar(6) , when I update the column using set new_col = to_char(old_col,'000000') the result is ######.
Please tell me what is the best way to accomplish this.
I need to do this on 3 tables with 50+ mil recs.
May 11, 2007 - 10:47 am UTC
if leading zeroes are needed, why would the client application just not
select ... to_char(x,'000000') x from ....
???????? then, you are done.
or create a view that does that.
then, you are done.
(hint - to_char(x,'000000') is seven characters long, there is a leading space for the SIGN, use 'fm000000' to get rid of that.
but the best way to accomplish this is
TO NOT DO IT!!!! it is a huge mistake to do this.
sammy, May 14, 2007 - 7:00 am UTC
Hi Tom,
Thank you for your reply.
Just out of curiosity...
Why do you consider it a huge mistake to do this?
May 14, 2007 - 2:07 pm UTC
because you don't need to - putting leading zeroes (formatting a number) is trivial, you don't want to convert a number into a string for FORMATTING.
else, you'll end up with:
hello world
as one of your numbers someday. You want to use the correct datatype.
May 15, 2009
john w, May 15, 2009 - 10:46 am UTC
You can simplify a function that takes an integer and returns a 13 char 0 padded ebcdic string.
create or replace
function gldep.convert_to_ebcdic (number_in in number)
return varchar2
is
ebcdic_string varchar2(13);
temp_string_12 varchar2(12);
begin
select substr(lpad(to_char(abs(nvl(number_in, 0))), 13, '0'), 1, 12) into temp_string_12 from dual;
if number_in > 0
then
select temp_string_12 || decode(substr(to_char(number_in), -1, 1), '0', '{', chr(ascii(substr(to_char(number_in), -1, 1)) + 16))
into ebcdic_string from dual;
elsif number_in < 0
then
select temp_string_12 || decode(substr(to_char(number_in), -1, 1), '0', '}', chr(ascii(substr(to_char(number_in), -1, 1)) + 25))
into ebcdic_string from dual;
else
ebcdic_string := '0000000000000';
end if;
RETURN(ebcdic_string);
end;
/
Use of UTL_PG
Ashim Basak, September 17, 2009 - 12:57 am UTC
Can anybody tell me how we can use UTL_PG to cnvert oracle number into cobol format...
Ashim, September 17, 2009 - 10:08 am UTC
I am using
select UTL_PG.NUMBER_TO_RAW(1234,'COMP-3',NULL,NULL,'IBMVSCOBOLII',NULL,'WE8EBCDIC37C',0,null) from dual;
to convert the oracle number data into raw format.
but it is not working: Giving error
ORA-06553: PLS-306: wrong number or types of arguments in call to 'NUMBER_TO_RAW'..
September 17, 2009 - 1:25 pm UTC
did you read the documentation?
http://docs.oracle.com/docs/cd/B19306_01/gateways.102/b16212/apc.htm#sthref1598 function NUMBER_TO_RAW (n IN NUMBER,
mask IN VARCHAR2,
maskopts IN VARCHAR2,
envrnmnt IN VARCHAR2,
compname IN VARCHAR2,
compopts IN VARCHAR2,
nlslang IN VARCHAR2,
wind IN BOOLEAN,
<b> wmsgbsiz IN BINARY_INTEGER,</b>
wmsgblk OUT RAW) RETURN RAW;
that function has an out parameter, you'll NEVER call that from sql.
that function has a plsql boolean as an in parameter, you'll NEVER call that from sql.
your call is supplying the wrong number of inputs even if the prior two things wouldn't stop you (in short, did you really look at the specification?)
please
a) read the documentation
b) use the values provided in the table
http://docs.oracle.com/docs/cd/B19306_01/gateways.102/b16212/apc.htm#i635625 (eg: your mask, well, it isn't a valid mask - it could be a maskopts....)
Please - slow down, take time to read - it is all there.
A reader, September 18, 2009 - 2:41 am UTC
Hi Tom,
Thank you very much fr your advise. But i am still not able to do the conversion. I am trying to convert oracle number into cobol numeric data (Packed Dacimal).
It is showing below error.
Looking forward for ur help
1 DECLARE
2 v_line RAW(2048);
3 c RAW(2048);
4 BEGIN
5 v_line := UTL_PG.NUMBER_TO_RAW (
6 78,
7 '999',
8 null,
9 NULL,
10 'IBMVSCOBOLII',
11 NULL,
12 'WE8EBCDIC37C',
13 0,
14 00000200,
15 c
16 ) ;
17 DBMS_OUTPUT.PUT_LINE(v_line);
18 DBMS_OUTPUT.PUT_LINE(c);
19* END;
SQL> /
v_line := UTL_PG.NUMBER_TO_RAW (
*
ERROR at line 5:
ORA-06550: line 5, column 17:
PLS-00306: wrong number or types of arguments in call to 'NUMBER_TO_RAW'
ORA-06550: line 5, column 7:
PL/SQL: Statement ignored
September 18, 2009 - 10:45 am UTC
who is UR and why would UR be helping you here, I'm very sure UR doesn't have a logon to this application - UR cannot possibly answer you.
0 is NOT a boolean -
booleans are TRUE and FALSE in plsql. the number 0 is not a boolean.
ops$tkyte%ORA10GR2> DECLARE
2 v_line RAW(2048);
3 c RAW(2048);
4 BEGIN
5 v_line := UTL_PG.NUMBER_TO_RAW (
6 78,
7 '999',
8 null,
9 NULL,
10 'IBMVSCOBOLII',
11 NULL,
12 'WE8EBCDIC37C',
13 false,
14 00000200,
15 c
16 ) ;
17 DBMS_OUTPUT.PUT_LINE(v_line);
18 DBMS_OUTPUT.PUT_LINE(c);
19 END;
20 /
303738
PL/SQL procedure successfully completed.
about conversion comp-3 via external table
Ottavio Vicini, January 11, 2010 - 4:20 am UTC
Hi Tom,
I read your post.
I have quite the same problem.
I am trying to load data from a file via external table.
Some fields are in comp-3, and I need to convert in number.
The error is
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04018: partial record at end of file /oracle-bin/ISP/extdir/ALM_ANAGR.txt
ORA-06512: at "SYS.ORACLE_LOADER", line 52
My external table:
CREATE TABLE OWBOWN.ALM_ANAGR5
(
DTSITZ DATE,
IDOPER VARCHAR2(42 BYTE),
CDFILE VARCHAR2(3 BYTE),
CDBRMS VARCHAR2(6 BYTE),
CDALMS VARCHAR2(12 BYTE),
CDOPER VARCHAR2(17 BYTE),
NUPIAN VARCHAR2(2 BYTE),
DTEROG DATE,
CDCOMP VARCHAR2(2 BYTE),
SEGECO VARCHAR2(4 BYTE),
VALUTA VARCHAR2(3 BYTE),
IMPRAC VARCHAR2(1 BYTE),
TIPTAS VARCHAR2(1 BYTE),
DURATA VARCHAR2(1 BYTE),
CDALGO VARCHAR2(4 BYTE),
SPRAPP NUMBER(9,7),
SPRCON NUMBER(9,5),
PERSPR VARCHAR2(3 BYTE),
TRATAS VARCHAR2(4 BYTE),
RCALGG VARCHAR2(1 BYTE),
PERATA VARCHAR2(3 BYTE),
DTFINE DATE,
DURMAC NUMBER(9,5),
DEBRES NUMBER(15,0),
RATEOI NUMBER(15,0),
VALMER NUMBER(15,0),
DERBRE NUMBER(15,0),
DERLUN NUMBER(15,0),
CDALGOTTI VARCHAR2(4 BYTE),
SPRCONTTI NUMBER(9,5),
TRATASTTI VARCHAR2(4 BYTE),
RCALGGTTI VARCHAR2(1 BYTE),
VALMER2 NUMBER(15,0),
DERBRE2 NUMBER(15,0),
DERLUN2 NUMBER(15,0),
DURMAC2 NUMBER(15,0),
DEALIN VARCHAR2(2 BYTE),
PROVV VARCHAR2(2 BYTE),
AGGREG1 VARCHAR2(6 BYTE),
AGGREG2 VARCHAR2(6 BYTE),
AGGREG3 VARCHAR2(6 BYTE),
AGGREG4 VARCHAR2(6 BYTE),
AGGREG5 VARCHAR2(6 BYTE),
SOCIETA VARCHAR2(5 BYTE),
GRUPPO VARCHAR2(10 BYTE),
BANCA VARCHAR2(1 BYTE),
POLICY VARCHAR2(1 BYTE),
NSG VARCHAR2(16 BYTE),
TIR NUMBER(5,3),
CONTAB VARCHAR2(25 BYTE),
SIST_ALIM VARCHAR2(3 BYTE),
COD_DESK VARCHAR2(12 BYTE),
TIRTTI NUMBER(5,3),
BANK_TRADE VARCHAR2(10 BYTE),
HLID VARCHAR2(10 BYTE),
CLASS_IAS VARCHAR2(5 BYTE),
TIP_COP VARCHAR2(5 BYTE),
SUB_TIP_COP VARCHAR2(5 BYTE),
LIV_COP VARCHAR2(1 BYTE),
PV_RF_B NUMBER(15,2),
PV_RFS_B NUMBER(15,2),
PV_RA_B NUMBER(15,2),
PV_RAS_B NUMBER(15,2),
DELTA_PV_RF_B NUMBER(15,2),
DELTA_PV_RA_B NUMBER(15,2),
PV_RF_TTI NUMBER(15,2),
PV_RFS_TTI NUMBER(15,2),
DELTA_PV_RF_TTI NUMBER(15,2),
BREVE VARCHAR2(1 BYTE),
RATEO_MKT NUMBER(15,2),
RATEO_TTI_MKT NUMBER(15,2),
KFIT VARCHAR2(17 BYTE),
ABI VARCHAR2(5 BYTE),
FILLER VARCHAR2(53 BYTE)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY EXTDIR
ACCESS PARAMETERS
( records
fixed 550
SKIP 1
badfile EXTDIR:'ALM_ANAGR5.bad'
logfile EXTDIR:'ALM_ANAGR5.log'
fields
missing field values are null
(
DTSITZ position(1:10) DATE 'YYYY-MM-DD',
IDOPER position(11:52),
CDFILE position(53:55),
CDBRMS position(56:61),
CDALMS position(62:73),
CDOPER position(74:90),
NUPIAN position(91:92),
DTEROG position(93:102) DATE 'YYYY-MM-DD',
CDCOMP position(103:104),
SEGECO position(105:108),
VALUTA position(109:111),
IMPRAC position(112:112),
TIPTAS position(113:113),
DURATA position(114:114),
CDALGO position(115:118),
SPRAPP position(119:123) DECIMAL(9,7),
SPRCON position(124:128) DECIMAL(9,5),
PERSPR position(129:131),
TRATAS position(132:135),
RCALGG position(136:136),
PERATA position(137:139),
DTFINE position(140:149) DATE 'YYYY-MM-DD',
DURMAC position(150:154) DECIMAL(9,5),
DEBRES position(155:162) DECIMAL(15,0),
RATEOI position(163:170) DECIMAL(15,0),
VALMER position(171:178) DECIMAL(15,0),
DERBRE position(179:186) DECIMAL(15,0),
DERLUN position(187:194) DECIMAL(15,0),
CDALGOTTI position(195:198),
SPRCONTTI position(199:203) DECIMAL(9,5),
TRATASTTI position(204:207),
RCALGGTTI position(208:208),
VALMER2 position(209:216) DECIMAL(15,0),
DERBRE2 position(217:224) DECIMAL(15,0),
DERLUN2 position(225:232) DECIMAL(15,0),
DURMAC2 position(233:237) DECIMAL(9,5),
DEALIN position(238:239),
PROVV position(240:241),
AGGREG1 position(242:247),
AGGREG2 position(248:253),
AGGREG3 position(254:259),
AGGREG4 position(260:265),
AGGREG5 position(266:271),
SOCIETA position(272:276),
GRUPPO position(277:286),
BANCA position(287:287),
POLICY position(288:288),
NSG position(289:304),
TIR position(305:307) DECIMAL(5,3),
CONTAB position(308:332),
SIST_ALIM position(333:335),
COD_DESK position(336:347),
TIRTTI position(348:350) DECIMAL(5,3),
BANK_TRADE position(351:360),
HLID position(361:370),
CLASS_IAS position(371:375),
TIP_COP position(376:380),
SUB_TIP_COP position(381:385),
LIV_COP position(386:386),
PV_RF_B position(387:394) DECIMAL(15,2),
PV_RFS_B position(395:402) DECIMAL(15,2),
PV_RA_B position(403:410) DECIMAL(15,2),
PV_RAS_B position(411:418) DECIMAL(15,2),
DELTA_PV_RF_B position(419:426) DECIMAL(15,2),
DELTA_PV_RA_B position(427:434) DECIMAL(15,2),
PV_RF_TTI position(435:442) DECIMAL(15,2),
PV_RFS_TTI position(443:450) DECIMAL(15,2),
DELTA_PV_RF_TTI position(451:458),
BREVE position(459:459),
RATEO_MKT position(460:467) DECIMAL(15,2),
RATEO_TTI_MKT position(468:475) DECIMAL(15,2),
KFIT position(476:492),
ABI position(493:497),
FILLER position(498:550)
)
)
LOCATION (EXTDIR:'ALM_ANAGR.txt')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;
Thank you very much for your help
defined readsize
Ottavio Vicini, January 14, 2010 - 4:00 pm UTC
Hi Tom,
I found a workaround for this problem, setting readsize to a particular value (100MB).
I tried the same external table with a default readsize,in an other pc, with 8 processors, and an other architecture, and it works!
I though the default value for readsize at 512kb.
Should be possible that it is dependent on the structure and caracteristics of machine?
Thank you very much
Ottavio
Conversion
Abhisek, March 09, 2011 - 9:55 am UTC
hi tom,
How can we convert a number with decimals..
I mean the input is 98666698 and the output should be 986666.98
I dont want to make it char.
I have done the same by doing a substring and concatenating with a decimal in between, like in ctl file
INTEGER EXTERNAL "to_number(substr(:sal,1,4) || '.' || substr(:sal,5,2))",
But I would prefer if any other idea to achieve this.
March 09, 2011 - 12:40 pm UTC
how about dividing by 100???
use this in your control file:
sal char ":sal/100"
or better yet, use an external table - map the element as a number, and select sal/100 from it.
UNPK
umencsv, April 26, 2012 - 7:29 pm UTC
Hello to everybody,
Apologies for maybe not fully following the forum's posting/other standards, it's my first ever contribution to here. I am not an Oracle guy either - rather a mainframer. Just doing some bits for mainframe to Oracle data movement/transformation at the moment. Came across this thread here (and some others elsewhere) and thought I should share the results of my own tests in this area.
So I think the packed decimal (in its raw IBM format) to number conversion can be done with as simple function as the one below. Just note, for simplicity I am allowing only for the "F" (in the rightmost nibble) being the "positive sign"; if you'd need a production ready version of this function then first read relevant topics in the POPs (the (z/OS) "Principles of Operation" in mainframers' jargon) and of course you would need some extra logic dealing with the implied decimal point if your decimals are not whole integers.
CREATE OR REPLACE FUNCTION UNPK (R RAW) RETURN NUMBER AS
VC VARCHAR2(16) := RAWTOHEX(R);
BEGIN
RETURN to_number(case substr(VC,-1,1) when 'F' then NULL else '-' end||substr(VC,1,length(VC)-1));
END UNPK;
And you'd probably want to have it in-line to gain somewhat better performance. In my case (XE 11 on a laptop with 1.8GHz processor) the dirreference is about 4-fold.
For your convenience below is a procedure to demonstrate what feeds into the function.
CREATE OR REPLACE PROCEDURE UNPK_TEST1 AS
n1 number;
raw1 raw(8) := hextoraw('123456789012345D'); /* note, the "D" is just to get UNPK thinking it's a negative packed decimal */
raw2 raw(4) := hextoraw('1234567D');
t1 timestamp; t2 timestamp;
BEGIN
t1 := current_timestamp;
for i in 1..100000 loop
-- n1 := UNPK(raw1);
n1 := UNPK(raw2);
end loop;
t2 := current_timestamp;
dbms_output.put_line(' n1='||to_char(n1)||' Elapsed:'||to_char(t2-t1));
END UNPK_TEST1;