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.
I create an object type, use that object type as a column in a table, and insert a couple of rows:
OK, let's query some data. The following output is what you see in SQLcl:
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:
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:
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:
For more information on working with object types in SQL and PL/SQL, check out the Object-Relational Developer's Guide.
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.
Buen dia Steven.
ReplyDeleteExcelente, 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.?
First, I will translate the two paragraphs of Spanish from Edgar above:
ReplyDelete1. 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.
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.
Deletein my process it is necessary to reuse the deleted ids and that is why it is necessary to locate the id that is unused.
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"? :-)
DeleteAnd still I am wondering: why wouldn't you pass in the table name? Why do you need it to be more dynamic than that?
steven, about "i need is death" sorry google translate! the sequences must extricta becouse is request customer.! i dont know why!
Deleteabout 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;
Yes, the logic looks ok, but a bit overcomplicated. I offer this:
DeleteCREATE 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;
Perfecto steven, luce mas prolijo asi!
ReplyDeletethank you!
Hello All,
ReplyDeleteEdgar, 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
Hello All,
ReplyDeleteJust 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
That is really helpful. In fact, use of OOPS concept makes it so realistic and related to real life issue.
ReplyDelete