How do I get the attribute of my object type in SQL?

This question found its way into my In Box yesterday:

I have a table with an object type column. I want to way to get the value of an attribute of that object type in my query. But Oracle keeps telling me "ORA-00904: invalid identifier". What am I doing wrong?

Almost certainly what you are doing wrong is forgetting to use a table alias. Yeah, it's that simple.
Don't forget the table alias.
Let's take a look.

I create an object type, use that object type as a column in a table, and insert a couple of rows:

CREATE TYPE food_t AS OBJECT (
   NAME         VARCHAR2 (100)
 , food_group   VARCHAR2 (100)
 , grown_in     VARCHAR2 (100)
)
/

CREATE TABLE food_table (id number primary key, my_food food_t)
/

BEGIN
   INSERT INTO food_table
        VALUES (1, NEW food_t ('Mutter Paneer', 'Curry', 'India'));

   INSERT INTO food_table
        VALUES (2, NEW food_t ('Cantaloupe', 'Fruit', 'Backyard'));

   COMMIT;
END;
/

OK, let's query some data. The following output is what you see in SQLcl:

SQL> SELECT * FROM food_table;

        ID
   ID MY_FOOD(NAME, FOOD_GROUP, GROWN_IN)                                             
----- ------------------------------------------
    1 FOOD_T('Mutter Paneer', 'Curry', 'India')                                       
    2 FOOD_T('Cantaloupe', 'Fruit', 'Backyard') 

In SQL Developer, I see:


While the output format varies, the basic idea is the same: I have asked for all columns, one of those columns is an object type, so I see the instance of that type.

What I want, though, is to select the individual attributes, such as the same. OK, here goes:

SQL> SELECT name FROM food_table;

Error starting at line : 1 in command -
SELECT name FROM food_table
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "NAME": invalid identifier

What? No NAME column? Oh, that's right. It's not a column - it's an attribute of a column. OK, let's try that again:

SQL> SELECT my_food.name FROM food_table;
Error starting at line : 1 in command -
SELECT my_food.name FROM food_table
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-00904: "MY_FOOD"."NAME": invalid identifier

Still no good. But, but. OK, so here's the deal: you must provide an alias to the table, prefix the object type column name with the alias, and then you are good to go. You can even reference the attribute in the WHERE clause:

SQL> SELECT ft.my_food.name FROM food_table ft;

MY_FOOD.NAME                                                                    
-----------------------------------------------
Mutter Paneer                                                                   
Cantaloupe                                                                      

SQL> SELECT ft.my_food.name FROM food_table ft
  2   WHERE ft.my_food.name LIKE 'C%';

MY_FOOD.NAME                                                                    
-----------------------------------------------
Cantaloupe         

For more information on working with object types in SQL and PL/SQL, check out the Object-Relational Developer's Guide.

Comments

  1. Buen dia Steven.

    Excelente, casualmente estaba haciendo una practica en mi Gym PL/Sql justo sobre ADJ y tengo un par de preguntas.

    1) Tengo un Type Obj que tiene el objetivo de calcular en una columna secuencial el proximo numero disponible (sin saltos), funciona prefecto pero quiero refinarla. Me gustaria no tener que pasar el parametro de TableName sino poder tomarlo del ambiente, es posible hacerlo sin tener que pasar por la v$sql?

    CREATE OR REPLACE TYPE Sequences
    AUTHID CURRENT_USER
    AS OBJECT (field varchar(30)
    , tablename varchar(30)
    ,member function UnUseValue (id number) return number);

    CREATE OR REPLACE TYPE BODY Sequences
    AS
    MEMBER FUNCTION UnUseValue (id number) return number
    IS
    TYPE CurTyp IS REF CURSOR;
    l_CurTyp CurTyp;
    l_retorna number(36):=0;
    l_query varchar2(4000) := 'select ts.'||field||'+1 from '||tablename||
    ' ts where ts.'||field||' > '||id||' and not exists (select null from '||tablename|| ' td where ts.'||field||'+1 = td.'||field||') order by 1 fetch first 1 rows only';
    BEGIN
    open l_CurTyp for l_query;
    loop
    fetch l_CurTyp into l_retorna;
    exit when l_CurTyp%notfound;
    end loop;
    close l_CurTyp;
    if l_retorna=0
    then return id+1;
    else return l_retorna;
    end if;
    END;
    END;

    CREATE TABLE Tab_example (id number(36) primary key,
    Idref Sequences default Sequences('id','tab_example'),
    names Varchar2(50));

    insert into Tab_example (id) select level from dual connect by level <= 10;
    insert into Tab_example (Id) select 12 from dual;
    insert into Tab_example (Id) select 15 from dual;
    insert into Tab_example (Id) select 16 from dual;
    insert into Tab_example (Id) select 19 from dual;
    commit;

    select tab.id, tab.idRef.UnUseValue(tab.id) from tab_example tab order by 1 desc, 2 desc;

    2) la otra consulta es referente a los querys sobre tablas que tiene campos tipo object porque es necesario usar el alias? que restriccion hay que nos obliga a usar ese metodo.?

    ReplyDelete
  2. First, I will translate the two paragraphs of Spanish from Edgar above:

    1. I have a Type Obj that has the objective of calculating in a sequential column the next available number (without jumps), it works prefect but I want to refine it. I would like not to have to pass the parameter of TableName but to be able to take it from the environment, is it possible to do it without having to go through the v $ sql?

    Edgar, I am a little unclear on what you want to do here. Which sequences do you want to run this code against? Every single sequence in your schema? Only those recently used?

    2. the other query is referring to queries on tables that have object type fields because it is necessary to use the alias? What restriction is there that forces us to use that method?

    "Why" questions are not my strength. I don't have lots of insights into motivations and constraints of developers at Oracle. I believe the alias is necessary to ensure the correct resolution of names in a fully dot-qualified name.

    ReplyDelete
    Replies
    1. perfect, steven, thank you very much! I understand your answer to question 2, about what you do not understand about the sequence, what I need is a death that when invoked fills the spaces or unused numbers of the sequence.

      in my process it is necessary to reuse the deleted ids and that is why it is necessary to locate the id that is unused.

      Delete
    2. Reuse unused values in the sequence? My gosh, why would anyone need to do that? You're not going to run out any time soon are you? What do you mean by "what I need is a death"? :-)

      And still I am wondering: why wouldn't you pass in the table name? Why do you need it to be more dynamic than that?

      Delete
    3. steven, about "i need is death" sorry google translate! the sequences must extricta becouse is request customer.! i dont know why!

      about the parameter "tablename" is only a question, i think about that maybe i will can do it!

      but do you think this rigth?, then i dont change it!
      --
      to end i changed the body by this, for to protect sql injection

      CREATE OR REPLACE TYPE BODY Sequences
      AS
      MEMBER FUNCTION UnUseValue (id number) return number
      IS
      TYPE CurTyp IS REF CURSOR;
      l_CurTyp CurTyp;
      l_retorna number(36):=0;
      l_query varchar2(4000) := 'select ts.'||field||'+1 from '||SYS.DBMS_ASSERT.sql_object_name (tablename) ||' ts where ts.'||field||' >= '||id
      ||' and not exists (select null from '||SYS.DBMS_ASSERT.sql_object_name (tablename) ||' td where ts.'||field||'+1 = td.'||field||') order by 1 fetch first 1 rows only';
      BEGIN
      open l_CurTyp for l_query;
      loop
      fetch l_CurTyp into l_retorna;
      exit when l_CurTyp%notfound;
      end loop;
      close l_CurTyp;
      if l_retorna=0
      then return id+1;
      else return l_retorna;
      end if;
      END;
      END;

      Delete
    4. Yes, the logic looks ok, but a bit overcomplicated. I offer this:

      CREATE OR REPLACE TYPE BODY sequences
      AS
      MEMBER FUNCTION unusevalue (id NUMBER)
      RETURN NUMBER
      IS
      l_curtyp SYS_REFCURSOR;
      l_retorna NUMBER (36);
      l_query VARCHAR2 (4000)
      := 'select ts.'
      || SELF.field
      || '+1 from '
      || sys.DBMS_ASSERT.sql_object_name (SELF.tablename)
      || ' ts where ts.'
      || SELF.field
      || ' >= '
      || id
      || ' and not exists (select null from '
      || sys.DBMS_ASSERT.sql_object_name (SELF.tablename)
      || ' td where ts.'
      || SELF.field
      || '+1 = td.'
      || SELF.field
      || ')';
      BEGIN
      OPEN l_curtyp FOR l_query;
      FETCH l_curtyp INTO l_retorna;
      CLOSE l_curtyp;
      RETURN NVL (l_retorna, SELF.id + 1);
      END;
      END;

      Delete
  3. Perfecto steven, luce mas prolijo asi!

    thank you!

    ReplyDelete
  4. Hello All,

    Edgar, just a short remark, for your attention:

    I guess that your customer's request originates in the need to have a gap-less sequence of values for a column that is used as a PK for your table.

    Though a little bit "old-fashioned" request, it can be implemented by using code like the above one, but, depending on your application's functionality, you should take care of the concurrency issue, or, in other words, of the fact that two separate transactions executing the above code simultaneously might end up in retrieving the *SAME* value for a missing ID.

    This, of course, cannot happen when using sequences for generating PK values.


    Regarding your request of "taking the table from the environment":

    If your transaction is calling the member function repeatedly for the same table and/or field,
    then you can use a package that stores (or caches) an instance of your object:

    CREATE OR REPLACE PACKAGE seq_pkg
    AS
    g_seq sequences;
    END seq_pkg;
    /

    Then, on the first usage, you create a sequence object like this:

    BEGIN
    seq_pkg.g_seq := sequences ('my_table','my_field');
    END;
    /

    then, on the subsequent requests, you can call the method on the stored object,
    by passing only the ID, as long as you want to work with the same table/field.

    DECLARE
    l_new_number NUMBER;
    l_id NUMBER;
    BEGIN
    l_id := 0 -- or any value you need
    l_new_number := g_seq.unusevalue (l_id);
    END;
    /


    You can also store the "starting ID" (l_id in the above sample) in the package.

    You can, of course, change both the table/field of the package object seq_pkg.g_seq at any time,
    to make the same object work for a different table/field.


    Thanks a lot & Best Regards,
    Iudith


    ReplyDelete
  5. Hello All,

    Just a short correction to my post above:

    The last block should be as follows:

    DECLARE
    l_new_number NUMBER;
    l_id NUMBER;
    BEGIN
    l_id := 0 -- or any value you need
    l_new_number := seq_pkg.g_seq.unusevalue (l_id);
    END;
    /

    In my previous post, the package name was missing ...

    Thanks a lot & Best Regards,
    Iudith

    ReplyDelete

Post a Comment

Popular posts from this blog

Table Functions, Part 1: Introduction and Exploration

Recommendations for unit testing PL/SQL programs

The future of Oracle PL/SQL: some thoughts on Sten Vesterli's thoughts