This discussion is archived
3 Replies Latest reply: Mar 18, 2013 9:07 AM by Paul Horth RSS

Impossibility to get wm_valid's fields validfrom and validtill

997545 Newbie
Currently Being Moderated
Hi everybody!
I enabled versionning in a table. As it is said in the section 3.5 of this web page (http://docs.oracle.com/cd/B28359_01/appdev.111/b28396/long_vt.htm#i1010273): it is possible to access to wm_valid fields: validfrom and validtill. However every time I try to do that I get this error :

Select wm_valid.validFrom FROM EMP
Erreur à la ligne de commande : 2, colonne : 8
Rapport d'erreur :
Erreur SQL : ORA-00904: "WM_VALID"."VALIDFROM" : identificateur non valide
00904. 00000 - "%s: invalid identifier"
*Cause:   
*Action:

Thank you for your help
  • 1. Re: Impossibility to get wm_valid's fields validfrom and validtill
    Paul Horth Expert
    Currently Being Moderated
    994542 wrote:
    Hi everybody!
    I enabled versionning in a table. As it is said in the section 3.5 of this web page (http://docs.oracle.com/cd/B28359_01/appdev.111/b28396/long_vt.htm#i1010273): it is possible to access to wm_valid fields: validfrom and validtill. However every time I try to do that I get this error :

    Select wm_valid.validFrom FROM EMP
    Erreur à la ligne de commande : 2, colonne : 8
    Rapport d'erreur :
    Erreur SQL : ORA-00904: "WM_VALID"."VALIDFROM" : identificateur non valide
    00904. 00000 - "%s: invalid identifier"
    *Cause:   
    *Action:

    Thank you for your help
    You're not actually following the documentation
    SELECT * from employees e 
       WHERE e.wm_valid.validFrom <= TO_DATE('01-01-1991', 'MM-DD-YYYY')
         AND e.wm_valid.validTill > TO_DATE('01-03-1991', 'MM-DD-YYYY');
    You are missing the alias for the emp table on the front of wm_valid.
  • 2. Re: Impossibility to get wm_valid's fields validfrom and validtill
    997545 Newbie
    Currently Being Moderated
    Thank you for your answer!
    What is the role of the Alias here?
    Thanks
  • 3. Re: Impossibility to get wm_valid's fields validfrom and validtill
    Paul Horth Expert
    Currently Being Moderated
    994542 wrote:
    Thank you for your answer!
    What is the role of the Alias here?
    Thanks
    My suspicion is that the wm_valid col is actually an object_type (that is it has a structure to it). One of the rules of accessing object_types
    is that you must have a table alias on the front of the column name.
    SQL> create or replace type test_obj as object (
      2  id number(6)
      3  , text_column varchar2(50)
      4  );
      5  /
    
    Type created.
    
    SQL> create table test_table_obj (
      2  col1 number(8)
      3  , col2 test_obj
      4  );
    
    Table created.
    
    SQL> select col1,t.col2.text_column
      2  from test_table_obj t;
    
    no rows selected
    
    SQL> select col1, col2.text_column
      2  from test_table_obj t;
    select col1, col2.text_column
                 *
    ERROR at line 1:
    ORA-00904: "COL2"."TEXT_COLUMN": invalid identifier

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points