CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Davor Josipovic
Davor Josipovic

Reputation: 5504

Store ROWID in Oracle OBJECT type

I am trying the following:

CREATE TYPE T_TEST AS OBJECT (
  TEST_ROWID                ROWID,
  TEST_DATA                 NUMBER(12)
);
/

But I get an error:

ORA-24344: success with compilation error
PLS-00530: Illegal type used for object type attribute: 'ROWID'.

I want to store the rowid's because they are faster than index lookups.

What is a good way for achieving the above? Casting to and from VARCHAR2 will probably introduce as much overhead as using the index?

Upvotes: 1

Views: 1149

Answers (1)

Multisync
Multisync

Reputation: 8787

There are at least two errors in your type definition.

Nonquoted identifiers cannot be Oracle SQL reserved words. Quoted identifiers can be reserved words, although this is not recommended.

Note: The reserved word ROWID is an exception to this rule. You cannot use the uppercase word ROWID, either quoted or nonquoted, as a column name. However, you can use the uppercase word as a quoted identifier that is not a column name, and you can use the word with one or more lowercase letters (for example, "Rowid" or "rowid") as any quoted identifier, including a column name.

So you cannot use ROWID as the variable name.

The second one is that you cannot use ROWID type. If you try you'll get PLS-00530

As I know there are CHARTOROWID / ROWIDTOCHAR functions which may help.

Upvotes: 1

Related Questions