0

I am running the below query in Oracle SQL. Table has 2 columns, first is rowid type and second is a varchar. 

INSERT INTO MYTABLE SELECT 'AAAAAAAAAAAAAAAAAA' RID, NULL FROM DUAL;

The result is adding null in both columns.

Why is this happening?

Shouldn't 'AAAAAAAAAAAAAAAAAA' be added to the first column of the table?

Dchris
  • 2,867
  • 10
  • 42
  • 73
  • 2
    Why are you trying to insert constant values into a `rowid` type column? That should be for database addresses. – Gordon Linoff May 28 '19 at 13:59
  • 1
    Well the `ROWID` type is not a text column, [as this SO question discusses](https://stackoverflow.com/questions/11612268/datatype-of-rowid-in-oracle-sql). Rather than explaining why what you are doing is wrong, maybe the first thing to ask is why you are inserting text into a `ROWID` column? – Tim Biegeleisen May 28 '19 at 14:00
  • can you post the table structure.It seems you dont have something that accepts 'AAAAAAAAAAAAAA' in that column – Ess Kay May 28 '19 at 14:00
  • The insert query is working, if we change "AAAAAAAAAAAAAAAAAA" to "AAAAAAAAAAAAAAAAAB". Probably it has something to do with the validity of the rowid value, you can make use of dbms_rowid.rowid_info to check the same. – Krishna May 28 '19 at 14:22

2 Answers2

1

When you are adding 'AAAAAAAAAAAAAAAAAA' into rowid datatype column then oracle converts string to rowid and store it there.

select chartorowid('AAAAAAAAAAAAAAAAAA') from dual; -- Result: NULL

'AAAAAAAAAAAAAAAAAA' rowid do not represent any physical location. In fact, Oracle uses 'AAAAAAAAAAAAAAAAAA' rowid to represent rows which are currently processed but do not exist physically. e.g. In trigger (new row).

Consider mentioned rowid as a special rowid used by Oracle which is allocated to all the rows which exist logically, not physically.

Hope, This is the answer you are looking for.

Popeye
  • 35,427
  • 4
  • 10
  • 31
0

ROWID is a pseudocolumn that you don't store, and can only select from. So your RID is not a row ID.

Without any other details, it seems like you are using EXTENDED ROWID instead:

Extended rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, and the plus sign (+) and forward slash (/). Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid as well as information specific to extended rowids.

As noted in documentation, you can't just simply select it, you must use DBMS_ROWID to store, or get them.

DVK
  • 126,886
  • 32
  • 213
  • 327