2

I've got the following objects:

CREATE FUNCTION CONSTFUNC RETURN INT
DETERMINISTIC
AS
BEGIN
RETURN 1;
END;

CREATE TABLE "FUNCTABLE" (
  "ID" NUMBER(*,0) NOT NULL,
  "VIRT" NUMBER GENERATED ALWAYS AS ("CONSTFUNC"()) NULL
);

however, the functable => constfunc dependency is not listed in all_ or user_ dependencies. Is there anywhere I can access this dependency information in the dictionary?

thecoop
  • 45,220
  • 19
  • 132
  • 189

2 Answers2

1

I just created your function and table in 11G (11.1) and can confirm your findings. I couldn't find anything in the Oracle docs either.

If you drop the function, the table status remains "VALID", but when you select from the table you get ORA-00904: "CHAMP"."CONSTFUNC": invalid identifier. This suggests that Oracle itself isn't aware of the dependency.

It might be worth asking this question on asktom.oracle.com, because Tom Kyte will have access to more information - he may even raise a bug about it if need be.

Tony Andrews
  • 129,880
  • 21
  • 220
  • 259
  • 1
    It looks like Oracle just hasn't fully baked this new feature. I had a thought that perhaps a TABLE couldn't be a referencing object type, but I have databases where tables have columns defined as TYPE(s) and these do show the dependencies correctly – dpbradley Nov 13 '09 at 13:41
0

The expression used to generate the virtual column is listed in the DATA_DEFAULT column of the [DBA|ALL|USER]_TAB_COLUMNS views.

Rene
  • 10,391
  • 5
  • 33
  • 46
  • But I want the actual dependency information of the expression, not the expression text. I don't want to parse the text and lookup the object identifiers myself! – thecoop Nov 13 '09 at 12:47
  • 1
    I'm not sure why I get negative points for this. It may not be the answer you are looking for but it is a correct answer to your question. – Rene Nov 13 '09 at 15:17