Oracle10g XE: Why PLS_INTEGER cannot be a column type

Question

Hi guys:

An invalid datatype is returned when I issue following command:

CREATE TABLE msg_info (
   msgcode PLS_INTEGER,
   msgtype VARCHAR2(30),
   msgtext VARCHAR2(2000),
   msgname VARCHAR2(30),
   description VARCHAR2(2000)
   );

I discovered it's resulted from msgcode PLS_INTEGER, removing this column will make creation successful.

Is it XE restriction? Thanks.

Answer 1

Try BINARY_INTEGER - it's identical and can be used interchangeably with PLS_INTEGER

link

Answer 2

PLS_INTEGER is a PL/SQL type only. It cannot be used in SQL tables / DDL-at-large.

I think one should declare the column as NUMBER type and, when retrieving data, use the PLS_INTEGER type at the level of PL/SQL.

See Oracle Datatypes