PIC S9 issue in oracle

Question

I am assigned the task by manager to create a DDL script. One of the fields that is required is specified as PIC S9 with length 16, but I don't know whether PIC S9 is supported by Oracle.

The DDL script need to be compatible with oracle 10g.

What is this PIC S9 data type?

Is this supported by Oracle? If yes, can i use the following?

create table tablename(srno number, name PIC S9(16) );

Answer 1

PIC S9 is a declaration in Cobol. To the best of my knowledge of Oracle it is not supported. You need to put in a number(16) field.

Answer 2

According to this link, PIC S9 is a COBOL data type:

PIC S9(4) COMP (Integer 16-bit)
    Specifies an integer that is 16 bits, or 2 bytes, in length.
PIC S9(9) COMP (Integer 32-bit)
    Specifies an integer that is 32 bits, or 4 bytes, in length.

According to this link, you could use the OVERPUNCH TRAILING external data type. So your DDL statement would resemble:

CREATE TABLE tablename (
   srno NUMBER,
   name OVERPUNCH TRAILING
);

The alternative would be to use NUMBER, seeing as it is a numeric datatype but that doesn't work with the example you've given - where name would usually be a VARCHAR2.

Answer 3

PIC S9(16) is for sure COBOL, and defines a signed numeric data item that holds 16 digits. The presence or absence of a BINARY, COMP-n, or PACKED DECIMAL modifier denotes how this data is stored on disk. The different storage options are described here.

I suspect your manager doesn't know SQL and is just trying to communicate the requirements to you in "Cobolese". If so, he/she is really saying this needs to be a signed integer of up to 16 digits, and you would use the most natural Oracle type, which is NUMBER(16) or just INTEGER.

The OVERPUNCH reference @OMG Ponies found is in Pro*Cobol, which is a tool that lets you embed Oracle SQL statements in COBOL programs. For this to work, you need to map the Oracle internal types like NUMBER with the Cobol types, like PIC S9(16) PACKED-DECIMAL. In this case, you define the Cobol type as an OVERPUNCH LEADING (not TRAILING).

The term "overpunch" comes from the zoned decimal representation on early mainframes. To save space on punch cards the sign would be "overpunched" over the leading or trailing digit.