OraFAQ Forum: Server Utilities » PL/SQL output COBOL signed overpunch

Home » RDBMS Server » Server Utilities » PL/SQL output COBOL signed overpunch

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

PL/SQL output COBOL signed overpunch [message #73319]

Tue, 23 March 2004 05:36

Brian
Messages: 38
Registered: October 1999

Member

Hi,

First I searched for the word overpunch, so hopefully the answer is not already available.

Second I am aware the reverse of my problem is possible in SQL*loader so this is not the answer.

Description of the problem. We have data being produced on the HP3000 using COBOL. Signed overpunch numbers come out.

For example the number 14 is rendered 1D and -14 is something like 1M. The goal back in the 70's I guess was to save space.

Now we want to use HP9000 and PL/SQL to create the same file. I have no idea how to use utl_pg so there goes that idea. Writing� a package/procedure/function is tempting.

Thanks,

Brian

Report message to a moderator

Re: PL/SQL output COBOL signed overpunch [message #73336 is a reply to message #73319]

Mon, 29 March 2004 04:18

Frank Naude
Messages: 4587
Registered: April 1998

Senior Member

Hi,

You can do this with an SQL function. Here is an example you can extend to match your requirements:

SQL> ----------------------------------------------------------------------
SQL> -- Convert an Oracle number to a Cobol Signed Trailing Overpunch value
SQL> ----------------------------------------------------------------------
SQL> create or replace function overpunch (ora_val in varchar2) return varchar2 is
  2    result_hold        varchar2(12);
  3    rightmost_digit varchar2(1);
  4    overpunch_char  varchar2(1);
  5  begin
  6    Result_hold        := to_char( to_number( abs( nvl(ora_val,0) ) ), '000000000009');
  7    rightmost_digit := substr(result_hold, -1);
  8    result_hold        := substr(result_hold, 1, length(result_hold)-1);
  9
 10    if ora_val < 0 then
 11       if rightmost_digit = 0 then
 12          overpunch_char := '}';
 13       else
 14          overpunch_char := chr(ascii(rightmost_digit)+25);
 15       end if;
 16    else
 17       if rightmost_digit = 0 then
 18          overpunch_char := '{';
 19       else
 20          overpunch_char := chr(ascii(rightmost_digit)+16);
 21      end if;
 22    end if;
 23
 24    return result_hold||overpunch_char;
 25  end;
 26  /

Function created.

SQL> select overpunch(14), overpunch(-14) from dual;

OVERPUNCH(14)
--------------------------------------------------------------------------------
OVERPUNCH(-14)
--------------------------------------------------------------------------------
 0000000001D
 0000000001M

Best regards.

Frank

Report message to a moderator

Previous Topic:	sql-loader an indexes
Next Topic:	logfile not generating

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 24 19:51:27 CST 2024