Problem with Select Statement on Queue-Table

jfudickar · Post by **jfudickar** » Wed 20 Jun 2007 09:23

The following Select statement

SELECT TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd"T"hh24:mi:ss.ff5TZH:TZM') timestamp_iso8601, ait.enq_time,
       ait.user_data.originator, ait.user_data.interface_version, ait.user_data.xml_data,
       ait.*
  FROM /*opm_asap.*/aq$asap_in_table ait
 WHERE 1 = 1
   AND enq_time > TO_DATE('14/11/2006 09:15:00', 'dd/mm/yyyy hh24:mi:ss')  --TRUNC(SYSDATE) - 30
 ORDER BY ait.enq_time DESC

results in

Duplicate field name 'USER_DATA.ORIGINATOR' at line 4 and position 21.

Why ? This should be valid!

The statements to create are:

Code: Select all

CREATE TYPE &OPM_USER..soc_msg_type AS OBJECT 
(
  originator        VARCHAR2(10),
  interface_version VARCHAR2(10),
  xml_data          CLOB   
);
/
BEGIN
   DBMS_AQADM.CREATE_QUEUE_TABLE(queue_table        => '&OPM_USER..asap_in_table', 
                                 sort_list          => 'priority', 
                                 multiple_consumers => TRUE, 
                                 queue_payload_type => '&OPM_USER..soc_msg_type');
END;
/
PROMPT .. CREATE_QUEUE &OPM_USER..asap_in_queue
BEGIN
   DBMS_AQADM.CREATE_QUEUE(queue_name  => '&OPM_USER..asap_in_queue',
                           queue_table => '&OPM_USER..asap_in_table',
                           retry_delay => 3600,
                           max_retries => 2147483647 );
END;
/

Any ideas?

Greetings and thanks
Jens

Plash · Post by **Plash** » Wed 20 Jun 2007 14:07

The problem is that when you include an object field in the SELECT list, a dataset creates fields for each attribute of the object.
Names for these fields are generated using the following schema:
+ '.' +

A dataset cannot have fields with duplicate names. Fields created for USER_DATA object in your SQL query have the same names as other fields in this query:
user_data.originator, user_data.interface_version, user_data.xml_data

You can open this query if you set the ObjectView property of TOraQuery to True.

jfudickar · Post by **jfudickar** » Wed 20 Jun 2007 14:17

But in sql*plus it's an valid statement.

And the following statement works also:

Code: Select all

SELECT owner, o.*
FROM all_objects o

Here you have also an duplicate column name, and here fixed this by adding a "_1". Why not doing the same for object columns?

Greetings
Jens

Plash · Post by **Plash** » Thu 21 Jun 2007 11:09

If some field is found twice in the query SELECT clause, Oracle adds '1' to the field name. The following query can be opened without problems because Oracle adds '1' to the second USER_DATA field:

Code: Select all

SELECT TO_CHAR(SYSTIMESTAMP, 'yyyy-mm-dd"T"hh24:mi:ss.ff5TZH:TZM') timestamp_iso8601,
  ait.enq_time,
  ait.user_data, ait.* 
  FROM /*opm_asap.*/aq$asap_in_table ait 
 WHERE 1 = 1 
   AND enq_time > TO_DATE('14/11/2006 09:15:00', 'dd/mm/yyyy hh24:mi:ss') 
 ORDER BY ait.enq_time DESC

In the original query there are no duplicate fields. But these fields are created when the program creates fields for attributes of the USER_DATA field.

jfudickar · Post by **jfudickar** » Mon 25 Jun 2007 08:11

But the statement is executed without problems in sql*plus.

Isn't there a way to adapt the field mechanism of addind "_1" also to objects fields?

Greetings
Jens

Challenger · Post by **Challenger** » Wed 27 Jun 2007 12:18

We have implemented functionality of adding prefix to the object field names. Now such type of SELECT SQL statements will be opened without errors. This fix will be available in the next build of ODAC.

jfudickar · Post by **jfudickar** » Wed 27 Jun 2007 16:06

Is it possible to get the changes via source to my private email account.

Greetings
Jens

Challenger · Post by **Challenger** » Mon 02 Jul 2007 09:08

To jfudickar:
We have sent you changes by email.