2

In one environment on 11G, I have created a table with one virtual column as follows

create table TEST_VIRTUAL_COL(
  col1 number(5),
  col2 varchar2(10),
  col3 number generated always as((-1)) virtual visible
);

Then insert a row into this

insert into TEST_VIRTUAL_COL (col1,col2) values (1,'Test');

When I select data from this table

select * from TEST_VIRTUAL_COL;

I get the following output

 COL1 | COL2 |   COL3
---------------------------------------------------------
  1   | Test | -1.00020202020065020202005723022430686716

COL3 doesn't show -1

Any idea around this, how to get -1 as output of COL3?

It works perfectly in another environment on Oracle 11g.

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
Rohit G
  • 39
  • 1
  • 5
  • 1
    Are you using the same client to connect to both instances? Or are you using different clients? Can you tell us more about your client and server environment (OS, exact version number of your Oracle DB, ...) ? – Frank Schmitt Feb 17 '16 at 12:29
  • 1
    Not sure how bug 9039716 affects numbers, but might be plausible if the broken environment is on an affected version. Do you always get the same value back? And what does `dump()` show for that column? – Alex Poole Feb 17 '16 at 12:45

1 Answers1

3

I could replicate your issue on my test machine which is running Oracle 11.2.0.1.0 - 64bit on Windows.

At first guess, it appears to be treating the literal -1 as a floating point value and not as a number.

CREATE TABLE TEST_VIRTUAL_COL (
  col1 NUMBER(5),
  col2 VARCHAR2(10),
  col3 NUMBER GENERATED ALWAYS AS (-1) VIRTUAL VISIBLE
);
INSERT INTO TEST_VIRTUAL_COL ( col1, col2 ) VALUES ( 1, 'Test' );
SELECT col3, DUMP(col3) FROM TEST_VIRTUAL_COL;

Gives the output:

COL3        | DUMP(COL3)
-----------------------------------------------------------------------------------------
-1.00020202 | Typ=2 Len=22: 62,100,102,0,0,0,0,0,161,0,0,0,0,143,179,0,168,85,254,28,0,0

Explicitly casting the literal to a number solved the issue:

CREATE TABLE TEST_VIRTUAL_COL (
  col1 NUMBER(5),
  col2 VARCHAR2(10),
  col3 NUMBER GENERATED ALWAYS AS (CAST(-1 AS NUMBER)) VIRTUAL VISIBLE
);
INSERT INTO TEST_VIRTUAL_COL ( col1, col2 ) VALUES ( 1, 'Test' );
SELECT col3, DUMP(col3) FROM TEST_VIRTUAL_COL;

Gives the output:

COL3 | DUMP(COL3)
-------------------------------
-1   | Typ=2 Len=3: 62,100,102
MT0
  • 143,790
  • 11
  • 59
  • 117