Problem While Enqueuing

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

Problem While Enqueuing

Postby gnkdev » Fri Mar 23, 2012 5:10 pm

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.
gnkdev
Member
 
Posts: 18
Joined: Mon Apr 05, 2010 11:11 am

Re: Problem While Enqueuing

Postby Tim... » Fri Mar 23, 2012 6:47 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Problem While Enqueuing

Postby gnkdev » Sat Mar 24, 2012 4:48 am

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
gnkdev
Member
 
Posts: 18
Joined: Mon Apr 05, 2010 11:11 am

Re: Problem While Enqueuing

Postby Tim... » Sat Mar 24, 2012 8:11 am

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Problem While Enqueuing

Postby gnkdev » Sat Mar 24, 2012 3:10 pm

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.
gnkdev
Member
 
Posts: 18
Joined: Mon Apr 05, 2010 11:11 am

Re: Problem While Enqueuing

Postby Tim... » Sat Mar 24, 2012 3:44 pm

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...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Problem While Enqueuing

Postby Tim... » Sat Mar 24, 2012 3:46 pm

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...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: Problem While Enqueuing

Postby henrycollins512 » Fri May 11, 2012 1:48 pm

thanks for the info tim
_________________
Visit Website
Last edited by henrycollins512 on Tue May 15, 2012 1:10 am, edited 2 times in total.
henrycollins512
Member
 
Posts: 1
Joined: Fri May 11, 2012 1:36 pm

Re: Problem While Enqueuing

Postby Tim... » Sat May 12, 2012 8:30 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17966
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle SQL and PL/SQL Development

Who is online

Users browsing this forum: No registered users and 3 guests

cron