/*****************************************************************************
* overpunch.fnc
*
- Description: Takes the number p_number and converts it to signed overpunch
- in COBOL style PIC S9(p_length) by returning a varchar value
- of length p_length.
*
- Tables Accessed: None
*
- Parameters:
*
- Name Type Description
- p_number NUMBER value of the number to convert to overpunch
- format.
- p_length NUMBER length of the overpunched return value
*
- Returns: VARCHAR2
*
- Error codes: p_length can only be between the values 3 and 6.
*
- Calls: None
*
- Created By: Brian Goldberg
- Date Created: 03/24/2004
- Project: 11553
- Note: Initial creation.
*
*****************************************************************************/
CREATE OR REPLACE FUNCTION overpunch (
p_number IN NUMBER,
p_length IN NUMBER)
RETURN VARCHAR2
IS
retval VARCHAR2(10);
l_signed_value VARCHAR(10);
l_p_length_out_range EXCEPTION;
l_body VARCHAR(10);
l_sign_end VARCHAR(10);
l_last_digit VARCHAR(1);
BEGIN
IF p_length = 3 THEN
l_signed_value := TO_CHAR(p_number, 'S000');
ELSIF p_length = 4 THEN
l_signed_value := TO_CHAR(p_number, 'S0000');
ELSIF p_length = 5 THEN
l_signed_value := TO_CHAR(p_number, 'S00000');
ELSIF p_length = 6 THEN
l_signed_value := TO_CHAR(p_number, 'S000000');
ELSE
RAISE l_p_length_out_range;
END IF; -- p_length
- l_body is the number without the last digit
- l_sign_end is the sign and the last digit
l_body := SUBSTR(l_signed_value, 2, p_length - 1);
l_sign_end := SUBSTR(l_signed_value, 1, 1) ||
SUBSTR(l_signed_value, p_length + 1, 1);
SELECT DECODE(l_sign_end, '+0', '{', '+1', 'A', '+2', 'B', '+3', 'C',
'+4', 'D', '+5', 'E', '+6', 'F', '+7', 'G', '+8', 'H',
'+9', 'I', '-0', '}', '-1', 'J', '-2', 'K', '-3', 'L',
'-4', 'M', '-5', 'N', '-6', 'O', '-7', 'P', '-8', 'Q',
'-9', 'R') INTO l_last_digit FROM DUAL;
retval := l_body || l_last_digit;
RETURN retval;
EXCEPTION
WHEN l_p_length_out_range THEN
DBMS_OUTPUT.PUT_LINE('The length of the overpunch function is out of ' ||
'bounds');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('overpunch.fnc has an error: ' || SQLERRM);
END;
/
EXIT;
Steve Butler <sbut-is_at_seatimes.com> wrote in message news:<Pine.SUN.3.91.950407084852.26428A-100000_at_seatimes>...
> On Thu, 6 Apr 1995, Spencer H Moore wrote:
> > mainframe system. Several of the fields are "signed numeric". I believe this
> > means that the last byte of the field is an alphanumeric character such as
> > "A", "J", "}", etc., depending upon the sign and value.
> >
> > Does anyone have an algorithm to convert a number to a "signed numeric"
> > string in PL/SQL? I searched the FAQ and couldn't find anything (probably
>
> You will have better luck fixing things up on the COBOL side. COBOL
> allows the use of SIGN IS SEPERATE [LEADING/TRAILING] so that the fields
> can be signed without having the sign overpunch the last digit [harking
> back to punched card terminology].
>
> --Steve
>
> +----------------------------------------------------+
> | Steve Butler Voice: 206-464-2998 |
> | The Seattle Times Fax: 206-382-8898 |
> | PO Box 70 Internet: sbut-is_at_seatimes.com |
> | Seattle, WA 98111 Packet: KG7JE_at_N6EQZ.WA |
> +----------------------------------------------------+
> All standard and non-standard disclaimers apply.
> All other sources are annonymous.
Received on Wed Mar 24 2004 - 12:56:36 CST
