PL/SQL convert DB decimal value to implied decimal right overpunch 1

[babsjoy]

We are currently converting an old COBOL based application to ASP.NET. My task is to re-write a COBOL unload program using PL/SQL.
The program extracts text as well as numeric fields from the database and writes several fixed length files. Everything seems to be working well except to convert the database numeric values for example NUMBER(18,2) to what was previously described in the COBOL programs as S9(9)V99 (implied decimal/right overpunched). Any ideas? the resulting output value must also have leading zeros.

Thank you inadvance...

 

[SantaMufasa]

Babs,

Before I started working for Oracle in 1988, my car's license plate/tag was "CPN COBOL" (Captain COBOL), so a little tear of reminiscence came to my eyes as I read your thread.

Is your incoming data in Extended Binary Coded Decimal Interchange Code (EBCDIC) or American Standard Code for Information Interchange (ASCII)? As I recall, the overpunch was to indicate "signed negative" or "signed positive" numbers (No overpunch was "unsigned, assumed positive&quot.

Also, are the COBOL numeric fields to be in COMP, COMP-3, or DISPLAY format?

Depending upon the answers to my questions, above, I'm pretty sure that we can create the fixed-length, leading-zero COBOL output you are looking for.

To assure quality, could you also please post here some EXACT [bit-for-bit] replicas of the COBOL data you want from replicas of Oracle [number (18,2)] sample data. Be sure to include examples of positive, negative, zero, and null contents of your Oracle source data.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:18 (25Nov03) GMT, 16:18 (25Nov03) Mountain Time)

 

[babsjoy]

Thanks for your quick reply. I am a contractor for the government and they are still using COBOL...
The data coming into my PL/SQL procedure will be from an Oracle 9i database. I think the answer you are looking for is ASCII since thats all we use here.
You are correct when saying:
As I recall, the overpunch was to indicate "signed negative" or "signed positive" numbers (No overpunch was "unsigned, assumed positive&quot.
In my case all fields will be signed.
For your question:
Also, are the COBOL numeric fields to be in COMP, COMP-3, or DISPLAY format? Answer: no

I will have to work on [bit-for-bit] replicas of the COBOL data you want from replicas of Oracle [number (18,2)] data and provide examples of positive, negative, zero, and null contents of your Oracle source data. I will get to this tomorrow if thats o.k.

Thanks again.

 

[SantaMufasa]

Babs,

Tomorrow is fine. On the "format" question, COMP = binary, COMP-3 = packed decimal, DISPLAY = standard text mode; I presume from your answer that you want DISPLAY format.

Looking forward to you post tomorrow.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:57 (25Nov03) GMT, 16:57 (25Nov03) Mountain Time)

 

[babsjoy]

Mufasa,
Since the original (and currently executing)COBOL program that I am trying to convert does not contain COMP or COMP-3 I assume DISPLAY is what I am looking for. Below are my examples:


ORACLE (18,2) COBOL S9(9)V99
POSITIVE 1000.55 0000010005E
NEGATIVE -1000.55 0000010005N
ZERO 0 0000000000{
NULL NULL 0000000000{

Hope these are good examples....

 

[SantaMufasa]

Babs,

Sorry for one more request: Could you please confirm a couple of more items for me?

1) Are you wanting a function that converts Oracle data to COBOL, or from COBOL to Oracle?
2) To ensure my algorithm correctness, could you please confirm the "over-punched" values for this sequence of Oracle values:
Oracle
-------
1000.49
1000.50
1000.51
1000.52
1000.53
1000.54
1000.55 0000010005E
1000.56
1000.57
1000.58
1000.59
-1000.49
-1000.50
-1000.51
-1000.52
-1000.53
-1000.54
-1000.55 0000010005N
-1000.56
-1000.57
-1000.58
-1000.59

Following answers to the above questions, I should be able to post your function in a few minutes.

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:39 (26Nov03) GMT, 09:39 (26Nov03) Mountain Time)

 

[babsjoy]

Is a function necessary? I am new to PL/SQL would the function reside in my current PL/SQL procedure or would the function have to be called ?
Oracle
-------
1000.49 0000010004I
1000.50 0000010005{
1000.51 0000010005A
1000.52 0000010005B
1000.53 0000010005C
1000.54 0000010005D
1000.55 0000010005E
1000.56 0000010005F
1000.57 0000010005G
1000.58 0000010005H
1000.59 0000010005I
-1000.49 0000010004R
-1000.50 0000010005}
-1000.51 0000010005J
-1000.52 0000010005K
-1000.53 0000010005L
-1000.54 0000010005M
-1000.55 0000010005N
-1000.56 0000010005O
-1000.57 0000010005P
-1000.58 0000010005Q
-1000.59 0000010005R

Hope this is what you where looking for.

 

[SantaMufasa]

Babs,

Your last post answered my Question #2 just fine.

But per my Question #1, which direction are you going: Are you wanting to translate over-punched values to Oracle values, or are you wanting Oracle values translated to over-punched values?

Per your question (basically, How will I [Babs] use the conversion algorithm?), I presume you have an existing PL/SQL block that processes a flat file, correct? If you excerpt here the relevant part(s) of your existing PL/SQL code, I can modify your code to accommodate the PL/SQL function.

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:13 (26Nov03) GMT, 10:13 (26Nov03) Mountain Time)

 

[babsjoy]

I am trying to go from an ORACLE value of (18,2) to an overpunched values of S9(9)V99. The amount field defined below is what I am trying to convert

CREATE OR REPLACE PROCEDURE req_plant_unload
-- 11/2003-for now hard code directory path
-- (p_filedir in varchar2)

-- output file names will be hard coded since these are DIFMS files
-- and names will not change
IS
v_filehandle_log UTL_FILE.file_type;
v_filehandle UTL_FILE.file_type;
BEGIN

v_filehandle_log := UTL_FILE.fopen ('e:\tasi\data','apr_req_plant4235_unload.log', 'w');
UTL_FILE.put_line (v_filehandle_log, 'Begin REQ UNLOAD Process');

DECLARE


v_errortext varchar2(200);
v_errornumber number;


v_count_4235 INTEGER := 0;
expense_element_pos1 VARCHAR2(1);
expense_element_pos2_4 VARCHAR2(3);
requisition_number VARCHAR2(13);
nomenclature VARCHAR2(28);
originator VARCHAR2(12);
pcc VARCHAR2(6);
deliver_to_bldg VARCHAR2(10);
total_amount NUMBER(9,2);


TYPE plant_4235_rec IS RECORD (
plant_4235_requisition_number VARCHAR2 (13),
plant_4235_expense_element FINANCE.EXPENSE_ELEMENT_TABLE.EET_EXPENSE_ELEMENT%TYPE,
plant_4235_nomenclature VARCHAR2 (28),
plant_4235_originator VARCHAR2 (12),
plant_4235_pcc VARCHAR2 (6),
plant_4235_deliver_to_bldg VARCHAR2 (10),
plant_4235_jon VARCHAR2 (6),
plant_4235_fiscal_year ASIDBA.APR_JON_TABLE.AJT_FY%TYPE,
plant_4235_total_amount NUMBER (9,2)
);

plant_4235_out_rec plant_4235_rec;

--CURSOR
CURSOR unload_plant_4235
IS
SELECT
NVL(SUBSTR (art_requisition_number, 2, 13),13,' '),
art_expense_element,
NVL(SUBSTR(art_nomenclature,1,28),' '),
NVL(SUBSTR(art_originator,1,12),' '),
NVL(SUBSTR(art_pcc,1,6),6' '),
NVL(SUBSTR(art_deliver_to_bldg,1,10),10,' '),
NVL(SUBSTR(ajt_jon,1,6),6' '),
art_fy,
art_total_document
FROM joyceba.v_apr_unload_req
WHERE art_plant_flag = 'Y'
AND art_fy_type = 'C'
AND art_requisition_type = '2';
BEGIN


v_filehandle :=
UTL_FILE.fopen ('e:\tasi\data','apr_unload_plant.dat',
'w'
);

OPEN unload_plant_4235;

UTL_FILE.put_line (v_filehandle_log, 'Begin PLANT unload Process');

LOOP
FETCH unload_plant_4235 INTO plant_4235_out_rec;

EXIT WHEN unload_plant_4235%NOTFOUND;


expense_element_pos1 :=
SUBSTR (plant_4235_out_rec.plant_4235_jon, 1, 1);

IF SUBSTR (plant_4235_out_rec.plant_4235_jon, 1, 1) >= 'A'
THEN
expense_element_pos1 := '5';
END IF;

IF SUBSTR (plant_4235_out_rec.plant_4235_jon, 1, 1) = 'I'
THEN
expense_element_pos1 := '8';
END IF;

expense_element_pos2_4 := substr(plant_4235_out_rec.plant_4235_expense_element,1,3);


plant_4235_out_rec.plant_4235_expense_element := expense_element_pos1||expense_element_pos2_4;


-- realize now this can be done in the PUT_LINE logic!!!

requisition_number :=RPAD(plant_4235_out_rec.plant_4235_requisition_number,13,' ');
nomenclature:=RPAD(plant_4235_out_rec.plant_4235_nomenclature,28,' ');
originator :=RPAD(plant_4235_out_rec.plant_4235_originator,12,' ');
pcc := RPAD(plant_4235_out_rec.plant_4235_pcc,6,' ');
deliver_to_bldg := RPAD(plant_4235_out_rec.plant_4235_deliver_to_bldg,10,' ');

--****** total amount should contain leading zeros
--****** stored as signed ASCII number RIGHT OVERPUNCHED
---******
----*******
total_amount :=(plant_4235_out_rec.plant_4235_total_amount);



-- WRITE RECORD

UTL_FILE.put_line
(v_filehandle,
requisition_number
|| total_amount
|| plant_4235_out_rec.plant_4235_expense_element
|| nomenclature
|| originator
|| pcc
|| deliver_to_bldg
|| plant_4235_out_rec.plant_4235_jon
|| plant_4235_out_rec.plant_4235_fiscal_year);

v_count_4235 := v_count_4235 + 1;


END LOOP;


UTL_FILE.put_line (v_filehandle_log,
'Plant 4235 records written to output file: '
|| TO_CHAR (v_count_4235));

CLOSE unload_plant_4235;

UTL_FILE.put_line (v_filehandle_log, 'PLANT 4235 process has completed');


EXCEPTION

WHEN OTHERS
THEN
IF SQL%NOTFOUND
THEN
UTL_FILE.put_line (v_filehandle_log,
'No PLANT data found, file created' );
END IF;

v_errornumber:= sqlcode;
v_errortext := substr(sqlerrm,1,200);
UTL_FILE.PUT_LINE(v_filehandle_log, 'Unhandled exception occurred');
UTL_FILE.PUT_LINE(v_filehandle_log,v_errornumber||v_errortext);
UTL_FILE.fclose (v_filehandle_log);



END;
UTL_FILE.fclose (v_filehandle);
UTL_FILE.fclose (v_filehandle_log);

END;

/

 

[SantaMufasa]

Babs,

My resolution is in three sections:
Section 1: The Function - You may copy and paste the PL/SQL code for the function, &quot;COBOL_VAL&quot;, either directly to your SQL*Plus &quot;SQL>&quot; prompt, or to a script file which you then &quot;@<script_file>&quot; at your SQL*Plus prompt.

Section 2: The Test - This section contains a PL/SQL block that exercises the COBOL_VAL(), showing results of the test-case values you posted, above.

Section 3: The Implementation - This section shows the pertinent excerpt of your, modified to include the COBOL_VAL function invocation.

=====================================
Section 1: The Function
=====================================
create or replace function cobol_val (ora_val in varchar2) return varchar2 is
	result_hold	varchar2(11);
	rightmost_digit varchar2(1);
	overpunch_char	varchar2(1);
begin
	result_hold	:=
		ltrim(translate(to_char(trunc(abs(nvl(ora_val,0)),1),'000000000.9'),'^.','^'));
	rightmost_digit	:=
		substr(to_char(to_number(nvl(ora_val,0)),'9999999999999999.99'),-1);
	if ora_val < 0 then
		if rightmost_digit = 0 then
			overpunch_char := '}';
		else
			overpunch_char := chr(ascii(rightmost_digit)+25);
		end if;
	else
		if rightmost_digit = 0 then
			overpunch_char := '{';
		else
			overpunch_char := chr(ascii(rightmost_digit)+16);
		end if;
	end if;
	return result_hold||overpunch_char;
end;
/

Function created.

=====================================
Section 2: The Test
=====================================
set serveroutput on
declare
	procedure prt (x in varchar2) is
	begin
		dbms_output.put_line(x);
	end;
	procedure show_conv (val_in in number) is
	begin
		prt('Oracle value: ['||val_in||']. Overpunched value: ['||
			COBOL_val(val_in)||'].');
	end;
begin
	show_conv (0);
	show_conv (null);
	show_conv (1);
	for i in 100049..100059 loop
		show_conv(i/100);
	end loop;
	for i in 100049..100059 loop
		show_conv((i/100)*-1);
	end loop;
end;
/

Oracle value: [0]. Overpunched value: [0000000000{].
Oracle value: []. Overpunched value: [0000000000{].
Oracle value: [1]. Overpunched value: [0000000010{].
Oracle value: [1000.49]. Overpunched value: [0000010004I].
Oracle value: [1000.5]. Overpunched value: [0000010005{].
Oracle value: [1000.51]. Overpunched value: [0000010005A].
Oracle value: [1000.52]. Overpunched value: [0000010005B].
Oracle value: [1000.53]. Overpunched value: [0000010005C].
Oracle value: [1000.54]. Overpunched value: [0000010005D].
Oracle value: [1000.55]. Overpunched value: [0000010005E].
Oracle value: [1000.56]. Overpunched value: [0000010005F].
Oracle value: [1000.57]. Overpunched value: [0000010005G].
Oracle value: [1000.58]. Overpunched value: [0000010005H].
Oracle value: [1000.59]. Overpunched value: [0000010005I].
Oracle value: [-1000.49]. Overpunched value: [0000010004R].
Oracle value: [-1000.5]. Overpunched value: [0000010005}].
Oracle value: [-1000.51]. Overpunched value: [0000010005J].
Oracle value: [-1000.52]. Overpunched value: [0000010005K].
Oracle value: [-1000.53]. Overpunched value: [0000010005L].
Oracle value: [-1000.54]. Overpunched value: [0000010005M].
Oracle value: [-1000.55]. Overpunched value: [0000010005N].
Oracle value: [-1000.56]. Overpunched value: [0000010005O].
Oracle value: [-1000.57]. Overpunched value: [0000010005P].
Oracle value: [-1000.58]. Overpunched value: [0000010005Q].
Oracle value: [-1000.59]. Overpunched value: [0000010005R].

PL/SQL procedure successfully completed.

(Notice that for values where Oracle has removed any insignificant, trailing zero(s), the function ensures that the result still represents two decimal places (examples: &quot;Oracle value: [1]. Overpunched value: [0000000010{].&quot; and &quot;Oracle value: [-1000.5]. Overpunched value: [0000010005}].&quot;)

=====================================
Section 3: The Implementation
=====================================

total_amount :=COBOL_VAL(plant_4235_out_rec.plant_4235_total_amount);

=====================================
End of Resolution
=====================================

Let me know if this adequately meets your needs.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:12 (26Nov03) GMT, 12:12 (26Nov03) Mountain Time)

 

[babsjoy]

WOW! the above is great. Will try it and let you know on Monday. Leaving early today for the Thanksgiving Holiday!
Hope you have a great one !!! Thanks again for all your help would have never figured it out myself. I will let you know how things work out!

 

[babsjoy]

Mufasa - everything seems to work great for S9(9)V99 fields!
I will probably have to customize the function so others in my group can use it to convert other database numeric field lengths to other COBOL numeric fields of different lengths. How easy would it be to code so that the function is flexible in the length coming in and going out. For example:

Oracle NUMBER(10,2) COBOL OUTPUT S9(7)V99

NUMBER(12,2) COBOL OUTPUT S9(6)V99

variable length input and output fields.

 

[SantaMufasa]

We can generalize (ise-UK) the function to handle overpunching for any-length numeric field. Do you want to do it or do you want me to do it? The main adjustment would be an additional incoming argument to the function to specify the target length of the RETURN value.

Let me know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:31 (01Dec03) GMT, 11:31 (01Dec03) Mountain Time)

 

[babsjoy]

I would like to give it a try. Just wanted to make sure that as a beginner the generalization of the function would not be beyond my knowledge. If I need help can I contact you again ?

 

[SantaMufasa]

Babs,

I'd be pleased to help. A hint is to use &quot;substr(string,-n)&quot; in the function body, where 'string' is 'result_hold' and 'n' is the new incoming argument that specifies the target length.

Let us know how you fare.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:46 (01Dec03) GMT, 12:46 (01Dec03) Mountain Time)

 

[babsjoy]

I think we have success !!! Thank you... for all your help. I could not have done it with out you. Your site is terrific !