sqloracle-databaseoracle19c

ORA-00904 when querying a sys.anydata column

Why am I getting a ORA-00904: invalid identifier error? How can I select the value of a sys.anydata column?

CREATE TABLE temp (
  id NUMBER PRIMARY KEY,
  val sys.anydata
);

INSERT INTO temp (ID, val) VALUES (1, sys.anydata.convertnumber(123));

SELECT ID, temp.val.accessnumber() AS val FROM temp

I tried with getnumber() instead of accessnumber() but I get the same error.

Solution

  • Why you get ORA-00904: invalid identifier

    Your query:

    SELECT ID, temp.val.accessnumber() AS val FROM temp;

    fails because:

    1. ACCESSNUMBER is a method, not a column — in Oracle SQL, you cannot use dot-method syntax with parentheses like temp.val.accessnumber().

    2. In Oracle SQL, the correct syntax is without parentheses:

    SYS.ANYDATA.ACCESSNUMBER(column_name)

    So writing temp.val.accessnumber() triggers ORA-00904: invalid identifier.

    1. Similarly, GETNUMBER() is a PL/SQL-only procedure, not a SQL function, so using it in such a query also fails.

    Correct SQL to select the number from a SYS.ANYDATA column

    SELECT id, SYS.ANYDATA.ACCESSNUMBER(val) AS val_extracted FROM temp;

    Key points:

    If you don't know the data type of the value in this column, you can try to cast it to varchar with a CASE expression using some types and return "unsupported type" if this fails.

    SELECT id,
       CASE SYS.ANYDATA.GETTYPENAME(val)
         WHEN 'SYS.NUMBER' THEN TO_CHAR(SYS.ANYDATA.ACCESSNUMBER(val))
         WHEN 'SYS.VARCHAR2' THEN SYS.ANYDATA.ACCESSVARCHAR2(val)
         WHEN 'SYS.DATE' THEN TO_CHAR(SYS.ANYDATA.ACCESSDATE(val), 
                                      'YYYY-MM-DD HH24:MI:SS')
         ELSE 'unsupported type'
       END AS val_extracted
FROM temp;

    Or you can check the data type in a separate query before going on (which will return SYS.NUMBER in your case):

    SELECT id, SYS.ANYDATA.GETTYPENAME(val) AS type_name FROM temp;

    See this db<>fiddle