/*****************************************************************************
* 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;
pelliott_at_isd.net (Patrick Elliott) wrote in message news:<32d5cd1e.0_at_usamrid.isd.net>...
> I have an interesting problem. I have to send some data in flat file
> form to an old computer that still uses signed overpunch numbers where
> the high order bit of the last digit contains a 1 if the number is
> negative. Does anyone have any ideas of how to do this. The only
> thing I can think of is making a pl/sql procedure to convert the
> number to a string. It would also save me some time if anyone has
> already written such a procedure. Thanks for your help!!
Received on Wed Mar 24 2004 - 12:58:51 CST
