Absent Member.. t_nguyen Absent Member..
Absent Member..
160 views

DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workbench

Hi,

I'm getting DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workbench.

As background, I refreshed the DEV instance from the Prod instance version 6.0. When I started the Dev instance and checked the workbench it opens fine. When I try to modify an environment or object type, I get the error.

 

For Environments:

 

DB Package error:

ORA-00918: column ambiguously defined {KNTA_LOCK-200}

SELECT t1.ENVIRONMENT_ID, t1.entity_last_update_date FROM KENV_ENVIRONMENTS t1, KENV_ENVIRONMENTS t1 WHERE t1.ENVIRONMENT_ID = 30002  (KNTA-10498)

 

For Object Types:

 

DB Package error:

ORA-00918: column ambiguously defined {KNTA_LOCK-200}

SELECT t1.OBJECT_TYPE_ID, t1.entity_last_update_date FROM KDLV_OBJECT_TYPES t1, KDLV_OBJECT_TYPES t1 WHERE t1.OBJECT_TYPE_ID = 10001  (KNTA-10498)

 

During the ITG start, I noticed:

 

DB Package error:

ORA-00918: column ambiguously defined {KNTA_LOCK-200}

SELECT t1.REPORT_SUBMISSION_ID, t1.entity_last_update_date FROM KNTA_REPORT_SUBMISSIONS t1, KNTA_REPORT_SUBMISSIONS t1 WHERE t1.REPORT_SUBMISSION_ID = 265267  (KNTA-10498)

 

Any help is apprciated.

 

Thanks

0 Likes
8 Replies
CGut Absent Member.
Absent Member.

Re: DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workben

Hi t_nguyen,

Hi could you tell me wich is your PPM version?

Found this KM regarding the error:

http://support.openview.hp.com/selfsolve/document/KM203652

Hope it helps!
Carolina

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Absent Member.. t_nguyen Absent Member..
Absent Member..

Re: DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workben

Hi,

 

Version 6.0.0 SP9_R1.

 

The article is about Portlet Full Results.

 

I can give it a try and see if it does anything.

 

0 Likes
Absent Member.. t_nguyen Absent Member..
Absent Member..

Re: DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workben

Hi, I actually tried the update in the server.conf file but it did not work. 

 

Any other suggestions?

 

Thanks!

0 Likes
Outstanding Contributor.. Loc_Nguyen_PPM Outstanding Contributor..
Outstanding Contributor..

Re: DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workben

Hi t_nguyen,

 

Did you run kUpdateHtml.sh after made the changes in the server.conf file ?

 

Make sure alway run kUpdateHtml.sh after changes something in server.conf file.

 

About error 
ORA-00918: column ambiguously defined

 

Please take a look this suggestion.

 

ORA-00918 column ambiguously defined

Cause: A column name used in a join exists in more than one table and is thus referenced ambiguously. In a join, any column name that occurs in more than one of the tables must be prefixed by its table name when referenced. The column should be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUMN. For example, if tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.

Action: Prefix references to column names that exist in multiple tables with either the table name or a table alias and a period (.), as in the examples above.

When ORA-00918 is thrown, you have a column which has been ambiguously defined.  If a column name in a join is referenced ambiguously, it exists in multiple tables. 
Column names which occur in multiple tables should be prefixed when it is referenced by its table name.
Columns must be referenced as TABLE.COLUMN or TABLE_ALIAS.COLUM .  Oracle documentation which reference ORA-00918 give the following example:
      - If tables EMP and DEPT are being joined and both contain the column DEPTNO, then all references to DEPTNO should be prefixed with the table name, as in EMP.DEPTNO or E.DEPTNO.
To correct ORA-00918, references should be prefixed to column names existing in multiple tables (either with the table name or table alias and a period)

 

Hope this help.

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
pepdwill Honored Contributor.
Honored Contributor.

Re: DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workben

Not sure if you wrote the SQL above or if HP did, but the issue/error is very obvious.

 

In the query examples above you are pulling the same table into your FROM clause twice, and using the same alias of "t1" for both.  If you really need to bring in the same table twice, each should have a unique alias, and you'd most likely want to have a join of some sort between the two (which I don't see).

 

If this is your SQL I'd say to just remove the extra redundant table from the FROM clause and it should work.

 

If HP wrote this, then I'm not sure what to tell you - but the reason you're getting the error is obvious.

 

Example:

SQL that gives the error:

SELECT t1.ENVIRONMENT_ID, t1.entity_last_update_date FROM KENV_ENVIRONMENTS t1, KENV_ENVIRONMENTS t1 WHERE t1.ENVIRONMENT_ID = 30002

 

Change to:

SELECT t1.ENVIRONMENT_ID, t1.entity_last_update_date FROM KENV_ENVIRONMENTS t1 WHERE t1.ENVIRONMENT_ID = 30002

 

0 Likes
Absent Member.. t_nguyen Absent Member..
Absent Member..

Re: DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workben

Hi vinhloc81,

 

I did run sh kUpdateHtml.sh after updates to the server.conf file were made.

 

Hi pepdwill,

 

The queries are strangely errors that come out of the PPM application and was not written by me.  Anything I try to update has two of the same tables and alias names.  I do not know why the application would add the same table alias in every query for each object I am trying to update.

 

Thanks

 

 

0 Likes
Outstanding Contributor.. Loc_Nguyen_PPM Outstanding Contributor..
Outstanding Contributor..

Re: DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workben

Hi   t_nguyen,

 

Please provide kSupport log file from both environments DEV and PROD for troubleshooting.

 

 

Thanks.

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
Outstanding Contributor.. Loc_Nguyen_PPM Outstanding Contributor..
Outstanding Contributor..

Re: DB PAckage Error: Column ambiguously defined any time i try to configure anything in the workben

Hi t_nguyen,

 

Could you tell me which version oracle on PRO and DEV ?

It looks like the DBA did not recompile all the schemas.
Even though none of the objects are showing up as invalid, they all still needed to be recompiled. After recompiling all the objects in the schema.

 

Make sure you remove extra redundant table from the FROM clause as Pepdwill said before. Please edit your sql command.

 

 

Hope this helps.

“HP Support
If you find that this or any post resolves your issue, please be sure to mark it as an accepted solution.”
0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.