Problem While Enqueuing

[gnkdev]

While calling DBMS_AQ.enqueue by giving suitable values to the parameters

the following error occured.

ORA-00604: error occurred at recursive SQL level 1
ORA-00918: column ambiguously defined

But, the same working fine in other databases.
If it is a problem in administration level could anyone point exact place and solution for it.

Note: from internet source got some solution to set false the undocumented parameter, which i could not do it.

[Tim...]

Hi.

That message usually means the SQL statement is referring to a column that could be in more than one table referenced in the SQL statement. Since this is recursive SQL, that makes it a bit harder to track down.

Questions:

1) Does the queue table exist in more than one schema in the database?
2) Does the payload object have a name that could be a reserved world?
3) Do any attributes of the payload object have names that could collide with reserved words?

Cheers

Tim...

[gnkdev]

Answers:

1.No
2.No
3.No

If it is a problem in sql then this may occur in other database also, but it doesn't.
Today we tried after databse down and up activity, Still problem exists. will see, Any other solutions

[Tim...]

Hi.

No point being in denial. It is definitely a problem with SQL (recursive) because the message says so. The question is what is different between these two systems that causes it.

I think in this position I would raise an SR with Oracle support and get them to check it out.

Cheers

Tim...

[gnkdev]

Hi,

your view on following passage,

Internet Source Follows

(The ORA-604 error (ORA-00604: error occurred at recursive SQL level 1 ORA-00918: column ambiguously defined) can occur for the internal SQL created for temporary tables if an index join access path has been chosen. This is most likely to happen in star transformation. This problem can occur on any platform.

The work around is, to set the initialization parameter star_transformation_enabled=temp_disable. Or install 9.2.0.5 which brings it's own set of problems.

Also refer the Oracle bug ID: 268920.1 in metalink.)

But i made false the parameter _Index_join at the session level and tried of it, but still problem exists.

[Tim...]

Hi.

Every SQL statement has the potential to produce this error. That fact that you've picked a bug about star transformation seems a bit odd, since I would not expect the resursive SQL involved in queuing and dequeuing messages on a queue to perform star transformations. I think you've got to be a bit more selective about the bugs you look at.

Have you raised an SR for your particular issue?

Cheers

Tim...

[Tim...]

Hi.

I just want to ram home the point that this can truely be any SQL statement. Look at this:

Code: Select all

CREATE TABLE tab1 (
  id          NUMBER,
  description VARCHAR2(50),
  CONSTRAINT tab1_pk PRIMARY KEY (id)
);

CREATE TABLE tab2 (
  id          NUMBER,
  tab1_id     NUMBER,
  description VARCHAR2(50),
  CONSTRAINT tab2_pk PRIMARY KEY (id),
  CONSTRAINT tab2_tab1_fk FOREIGN KEY (tab1_id) REFERENCES tab1(id)
);

SELECT description
FROM   tab1
       JOIN tab2 ON tab2.tab1_id = tab1.id;

When you run the query you get this:

Code: Select all

SELECT description
       *
ERROR at line 1:
ORA-00918: column ambiguously defined

SQL>

Why? Because the optimizer can't tell which DESCRIPTION column you are asking for.

Now, something in your system is causing the recursive SQL to run into this problem. It could be a bug, or be something perfectly normal. You really need to raise an SR to get to the bottom of it.

Cheers

Tim...

[henrycollins512]

thanks for the info tim
_________________
Visit Website

[Tim...]