Tom,

I am working on an Oracle data extract project. My output file will send to a DB2 database and a mainframe application. In my file, some fields' type is COMP-3. I use Oracle build-in function convert () to convert to EBCDIC. This works fine in SQL*PLUS. But when I use it in my PL/SQL program, I got "ORA-06502: PL/SQL: numeric or value error: character to number conversion error". Here is my program.

FUNCTION get_vd_pro_norm_mth_amt(p_account_rec IN ACCOUNT_ROW) return varchar2
as

v_scaled_amount varchar2(15);
BEGIN
select convert(a.scaled_amount, 'WE8EBCDIC500','US7ASCII')
into v_scaled_amount
from pin61_02.rate_bal_impacts_t a, pin61_02.rate_plan_t b
where b.poid_id0 = a.obj_id0 and
b.account_obj_db = p_account_rec.poid_db and
b.account_obj_id0 = p_account_rec.poid_id0 and
b.account_obj_type = p_account_rec.poid_type and
b.account_obj_rev = p_account_rec.poid_type;
return v_scaled_amount;

EXCEPTION
WHEN OTHERS THEN
RETURN NULL;

END get_vd_pro_norm_mth_amt;

I guess the wrong data type of my variable v_scaled_amount generated the problem. I do not know which data type should I use to store EBCDIC data.

Thanks a lot!

Max

hard to say i don't know what the real type of a.scaled_amount is in the db -- but you might try 

create view v as select convert ....

and a describe on that to see what the SQL engine believes you need as datatypes, eg:

ops$tkyte@ORA920> create or replace view v
  2  as
  3  select ename, convert( ename,  'WE8EBCDIC500','US7ASCII')  ename_ebcdic
  4    from emp
  5   where rownum <= 5 ;
 
View created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> desc v
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ENAME                                              VARCHAR2(10)
 ENAME_EBCDIC                                       VARCHAR2(50)
 
ops$tkyte@ORA920>
 

Tom,

Sorry to waste your time.

I find the reason. It is nothing related this function. It is because of wrong data type of a variable in the calling procedure.

Thanks any way.

Max

Tom,

With reference to:

CONVERT( yourstringgoeshere, 'US7ASCII' )

The manual suggests that it is possible to control the replacement character used for a source character that doesn't exist in the target characterset.

"Replacement characters can be defined as part of a character set definition."

...but doesn't go on to say exactly how this might be done.

Am I misunderstanding, or can you enlighten me?

TOm, I have a mainframe files which have comp-3 and text data. Also the file has occurs class, as an example I have the following layout.

10 field1 pic xx.
10 field2 pic 9999 comp-3.
10 field3 pic 9999 comp-3.
10 field4 occurs 100 times depending on field-3.
15 field5 pic xx.
15 field6 pic 9999 comp-3.

equivalent to this I have two tables in oracle

Table1 has
field1 varchar2(2)
field2 NUMBER

table2 (is more like a detail table) has
field5 varchar2(2)
field6 NUMBER

so I downloaded the file (as binary fixed length eventhough it is variable length becuase of occurs depending on) to unix and I want to write a sql load scrit to load data into table1 and table2, table1 is easier, I can specify record size and ebcdic as it is one record to one row. I need a sqlloader script for the second one. Can you show me how to load data from one record to multiple rows of a detail table?

Hi Tom,

Loading the EBCDIC data is going fine with the method you mentioned in the thread above . But when I have packed decimals in my EBCDIC data its throwing an errors:

Here is how my control file looks like:
LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE 'sampletext.asc'"fix 16 buffers 1024"
BADFILE samplefile.bad'
DISCARDFILE 'samplfile.dsc'
APPEND
INTO TABLE SAMPLETABLE
(
COL1 position(1:5) char,
COL2 position(6:12) char,
COL3 position(13:14) decimal External,
COL4 position(15:16) char)

The col3 should load a packed decimal into the number filed. But its throwing errors in log file like:

Record 1: Rejected - Error on table SAMPLETABLE, column COL3.
ORA-01722: invalid number

This due basically the packed decimal which normally contains alpthbets like '0C' can not load into a number defined column in the table..

Can you please suggest any remedy for this?

Sasanka, when you use packed decimal, you have to say Decimal not decimal external. that should fix your problem.

Tom,
I have a followup question, I have a mainframe ebcdic file where dates are stored in a 9(9) comp-3 in yyyymmdd format. x'000000000F' and x'999999999F' are considered invalid dates, so I want to load nulls into the corresponding table, otherwise I want to load into as date.

I did the following but not seems to be working.

effective_date POSITION(211:215) DECIMAL
"DECODE(CONTRACT_DATE_YMD,x'000000000F',NULL
,x'999999999F',NULL
,TO_DATE(effective_date,'YYYYMMDD') )"

Tom, column name on the table is CONTRACT_DATE_YMD and it appears on the input file at position 211:215 which is a packed decimal and contains date in yyyymmdd format. So, i tried this. Still didn't work.


CONTRACT_DATE_YMD POSITION(211:215) DECIMAL
"decode( to_char( contract_date_ymd, '000000000X' ),
'000000000F', to_date(null),
'999999999F', to_date(null),
to_date(contract_date_ymd,'yyyymmdd') )",

while loading I got the following error

Record 1: Rejected - Error on table FILE_104_AGENT_MASTER, column CONTRACT_DATE_YMD
ORA-00984: column not allowed here

Thanks again,

just to clarify CONTRACT_DATE_YMD is a date field on the table.

Thanks Tom, I think I figured it out.

CONTRACT_DATE_YMD POSITION(211:215) DECIMAL
"DECODE( :CONTRACT_DATE_YMD, 0, TO_DATE(NULL),
999999999, TO_DATE(NULL),
TO_DATE(:CONTRACT_DATE_YMD,'YYYYMMDD') )",

I forgot to put the ":" (colons).

Thanks again.

Tom,
I have a file like the following

field1 field2 field3
010 100 500
013 200 400
... ... ...

I have a oracle table
create table1 (
field3 number,
fieldx number);

I want to load field3 from file to field3 in the table, but I want to load field1 * field2 into fieldx. How can I do that? if field1 and field2 appear I can say
":field1 * :field2"

Thanks again

9.2

I'm facing the same situation as Anand Kothapeta's initial question: a variable length record built by a COBOL copybook with "OCCURS...DEPENDING ON" clauses (back up at
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:259215259051#53048606121275 <code>
). You replied "...use an external table...". Do you mean something like this? Here I'm parsing, for example, values for the 2nd array (POLDRV). Then I'd create another query to parse out the 1st array (CLTREF), etc?

with
-- ######### SIMPLIFIED SAMPLE COPYBOOK ################
--
-- 03 WDS04-TRANTYPE-ID PIC X(01).
-- *ARRAY COUNTS
-- 03 WDS04-CLTREF-DETAIL-COUNT PIC 9(01).
-- 03 WDS04-POLDRV-DETAIL-COUNT PIC 9(01).
--
-- 03 WDS04-POLICY-DETAILS.
-- *POLQTE/POLICY
-- 05 WDS04-COMPANY PIC X(02).
-- *CLTREF SPECIFIC
-- 05 WDS04-REFERENCE-ARRAY OCCURS 0 TO 2 TIMES
-- DEPENDING ON WDS04-CLTREF-DETAIL-COUNT.
-- 07 WDS04-CLTREF-CODE1 PIC X(02).
-- *ITEM SPECIFIC
-- 05 WDS04-ITEM-CODE2 PIC X(02).
-- *POLDRV SPECIFIC
--
-- 05 WDS04-POLDRV-ARRAY OCCURS 0 TO 2 TIMES
-- DEPENDING ON WDS04-POLDRV-DETAIL-COUNT.
-- 07 WDS04-DEFDRVIND PIC X(01).
--
MOCK_EXT_TBL as ( -- parse fixed length elements
select SUBSTR(WDS04,1,1) AS TRANTYPE_ID
, TO_NUMBER(SUBSTR(WDS04,2,1)) AS CLTREF_CNT
, 2 AS CLTREF_LEN
, TO_NUMBER(SUBSTR(WDS04,3,1)) AS POLDRV_CNT
, 1 AS POLDRV_LEN
, 2 AS ITEM_CODE2_LEN
, SUBSTR(WDS04,4,2) AS COMPANY
, WDS04
from ( SELECT 'I22AaBbCcTtYN' AS WDS04 -- dummy data
FROM DUAL ) VAR_REC
)
select 1 AS OCCURRENCE#
, SUBSTR(WDS04,5 -- initial fixed length
+ CLTREF_CNT * CLTREF_LEN -- skip CLTREF data
+ ITEM_CODE2_LEN -- skip ITEM-CODE2
+ 0 * POLDRV_LEN +1 -- 1st occurrence
,POLDRV_LEN ) AS DEFDRVIND
from MOCK_EXT_TBL
where POLDRV_CNT > 0 -- 1st occurrence
UNION
select 2 AS OCCURRENCE#
, SUBSTR(WDS04,5 -- initial fixed length
+ CLTREF_CNT * CLTREF_LEN -- skip CLTREF data
+ ITEM_CODE2_LEN -- skip ITEM-CODE2
+ 1 * POLDRV_LEN +1 -- 2nd occurrence
,POLDRV_LEN ) AS DEFDRVIND
from MOCK_EXT_TBL
where POLDRV_CNT > 1 -- 2nd occurrence
ORDER BY OCCURRENCE#
/
OCCURRENCE# DEFDRVIND
----------- ----------
1 Y
2 N

You recommended "an external table and a multi-table insert statement".  I looked at

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/statements_9014.htm#i2145081

and noticed the WHEN clauses.  So I constructed one INSERT using an external table that loads multiple rows in a child tables and one row in the master:

create table policy ( TRANTYPE_ID char(1)
                    , COMPANY char(2)
                    , ITEM_CODE2 char(2) )
/
create table poldrv ( DEFDRVIND char(1) )
/
INSERT ALL
  WHEN 1=1 THEN
    INTO policy 
    VALUES ( TRANTYPE_ID, COMPANY, 
             SUBSTR(WDS04,
             5 + 1                    -- skip initial fixed data
           + CLTREF_CNT * CLTREF_LEN  -- skip CLTREF data
              ,2 ) 
           )
  WHEN poldrv_cnt > 0 THEN
    INTO poldrv
    VALUES ( SUBSTR(WDS04,
             5 + 1                    -- skip initial fixed data
           + CLTREF_CNT * CLTREF_LEN  -- skip CLTREF data
           + ITEM_CODE2_LEN           -- skip ITEM-CODE2
           + 0 * POLDRV_LEN           -- 1st occurrence
              ,POLDRV_LEN ) 
           )
  WHEN poldrv_cnt > 1 THEN
    INTO poldrv
    VALUES ( SUBSTR(WDS04,
             5 + 1                    -- skip initial fixed data
           + CLTREF_CNT * CLTREF_LEN  -- skip CLTREF data
           + ITEM_CODE2_LEN           -- skip ITEM-CODE2
           + 1 * POLDRV_LEN           -- 2nd occurrence
              ,POLDRV_LEN ) 
           )
with
-- ######### SIMPLIFIED SAMPLE COPYBOOK ################
--
--      03  WDS04-TRANTYPE-ID                  PIC X(01).
-- *ARRAY COUNTS
--      03  WDS04-CLTREF-DETAIL-COUNT          PIC 9(01).
--      03  WDS04-POLDRV-DETAIL-COUNT          PIC 9(01).
--
--      03  WDS04-POLICY-DETAILS.
-- *POLQTE/POLICY
--        05  WDS04-COMPANY                    PIC X(02).
-- *CLTREF SPECIFIC
--        05  WDS04-REFERENCE-ARRAY   OCCURS 0 TO 2 TIMES
--            DEPENDING ON WDS04-CLTREF-DETAIL-COUNT.
--            07  WDS04-CLTREF-CODE1           PIC X(02).
-- *ITEM SPECIFIC
--        05  WDS04-ITEM-CODE2                 PIC X(02).
-- *POLDRV SPECIFIC
--
--        05  WDS04-POLDRV-ARRAY OCCURS 0 TO 2 TIMES
--            DEPENDING ON WDS04-POLDRV-DETAIL-COUNT.
--            07 WDS04-DEFDRVIND               PIC X(01).
--
MOCK_EXT_TBL as ( -- parse fixed length elements
select SUBSTR(WDS04,1,1)            AS TRANTYPE_ID
     , TO_NUMBER(SUBSTR(WDS04,2,1)) AS CLTREF_CNT
     , 2                            AS CLTREF_LEN
     , TO_NUMBER(SUBSTR(WDS04,3,1)) AS POLDRV_CNT
     , 1                            AS POLDRV_LEN
     , 2                            AS ITEM_CODE2_LEN
     , SUBSTR(WDS04,4,2)            AS COMPANY
     , WDS04
  from ( SELECT 'I22AaBbCcTtYN' AS WDS04 -- dummy data
          FROM DUAL  ) VAR_REC
)
select * from MOCK_EXT_TBL
/
select * from policy
/
select * from poldrv
/


3 rows created.

SQL> select * from policy;

T CO IT
- -- --
I Aa Tt

SQL> select * from poldrv;

D
-
Y
N

As usual, you were dead on.  Thank you! 

I would like to achieve the same result in PL SQL as from COBOL (EBCDIC)!

For example:
create table OIT015
(
ENTSTAMP TIMESTAMP(6) not null,
ORDNR NUMBER(11) not null,
ORDERDATEN VARCHAR2(3800) not null
)
tablespace PLINK_HBCI
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);

insert into OIT015 (ENTSTAMP, ORDNR, ORDERDATEN)
values (to_timestamp('11-11-0011 11:11:11.000011', 'dd-mm-yyyy hh24:mi:ss.ff'), 2, 'X');

update oit015 set orderdaten=convert('00000396375160','WE8EBCDIC500') where ordnr=2
commit;

My collegues wrote the same data with COBOL program into test table in EBCIDIC.

There is the result:
select dump(t.orderdaten,16), t.* from oit015 t

0,0,3,96,37,51,60,c 11.11.11 11:11:11.000011 1111111111
f0,f0,f0,f0,f0,f3,f9,f6,f3,f7,f5,f1,f6,f0 11.11.11 11:11:11.000011 2 ?????óuöó÷onö?

My collegues said that 'WE8EBCDIC500' is special display format on mainframe.

It was run on SUN Solaris with Oracle 9.2.0.6

Tom,
I need to display a field defined as COMP-3 in Oracle Database.
The structure of the field is:

03 TA-ALLOWANCES.
05 ALLOW-CODE1 PIC X(3)
05 ALLOW-UNITS1 PIC S9(3)V9(2) COMP-3

I need to display ALLOW-UNITS1 using Select in Oracle.

Thanks,

Juan Ortega

Most charts on web say ASCII hex to EBCDIC hex for cr lf is

chr(13) --> 0D
chr(10) --> 15

select utl_raw.cast_to_raw( convert( chr( 13 )||chr(10),'WE8EBCDIC500') ) from dual;

shows
0D15

but Im having an issue with the EBCDIC system expectin 0D0A.

How do I get around this.

Tom, can you help please?

I'm trying to use an external table over a variable length ebcdic file. It's variable because of an OCCURS 200 DEPENDING clause.

There is no delimiter for the record but the file has a "NUMBER-OF-WORDS" field at position 1:4

How can I make the external table definition understand the "NUMBER-OF-WORDS" variable. THe following script returns error

ORA-29400: data cartridge error
KUP-04019: illegal length found for VAR record in file F:\oracle\input\warehouse\MACF

CREATE TABLE EXT_TAB
(
X clob
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY WAREHOUSE_INPUT_DIR
ACCESS PARAMETERS
(
RECORDS variable 4
CHARACTERSET WE8EBCDIC500
DATA IS BIG ENDIAN
BADFILE 'WAREHOUSE_INPUT_DIR':'dgn.bad'
LOGFILE 'WAREHOUSE_INPUT_DIR':'dgn.log'
READSIZE 1048576
FIELDS LDRTRIM
REJECT ROWS WITH ALL NULL FIELDS
(
X (1:3000) raw
)
)
location
(
'MACF'
)
)REJECT LIMIT 10

Many thanks

Dear All,

Please suggest a SQL level conversion of EBCDIC file to ASCII file.

We are getting files generated from Mainframe system as .ebcdic format. We want to convert it to ASCII format. While using the informatica tool, we are getting the complete readable file from the ebcdic file. But when using varous methods, like
1. SQL Loader
2. convert function ( which is irrelevant in our case, as it reads string by string )
3. and finally, trying to use the java functions.
4. using unix convert utility

In some, we are getting partial data as readable.

Thanks
Saroj Mahanta