Hi, I'm trying to retrieve an Oracle catalog, but I'm getting the folowing error:

"CatalogRetriever::The following table(s) do not have a primary key present:"

I'm getting this issue with every table in my schema.

I've verified that the FK's exist.

TIA, Gastón.-

P.S.: My DBMS is Oracle 9i, and I'm using LBL v1.0.2004 Final.

Well, I think I found the error. The query which findout if a table have a PK, is not taking in acount the double cuotes in the name of my tables. So, the query return no rows when it try to obtain the FKs from the ALL_CONSTRAINTS view (or wathever it use)

Is there a way I could configure my LLBL, in order to take in consideration that my table name is "MyTableName" and not MYTABLENAME ???

Thanks in advance, Gastón.-

Tongas wrote:

Well, I think I found the error. The query which findout if a table have a PK, is not taking in acount the double cuotes in the name of my tables. So, the query return no rows when it try to obtain the FKs from the ALL_CONSTRAINTS view (or wathever it use)

Is there a way I could configure my LLBL, in order to take in consideration that my table name is "MyTableName" and not MYTABLENAME ???

This is the query which determines the PK's:

 SELECT ALL_CONS_COLUMNS.TABLE_NAME, 
       ALL_CONS_COLUMNS.COLUMN_NAME, 
       ALL_CONS_COLUMNS.POSITION 
 FROM  ALL_CONS_COLUMNS, ALL_CONSTRAINTS 
 WHERE
 ALL_CONS_COLUMNS.CONSTRAINT_NAME = ALL_CONSTRAINTS.CONSTRAINT_NAME 
 AND  ALL_CONS_COLUMNS.OWNER = ALL_CONSTRAINTS.OWNER 
 AND ALL_CONSTRAINTS.CONSTRAINT_TYPE='P' 
 AND ALL_CONSTRAINTS.OWNER='schema' 
 ORDER BY ALL_CONS_COLUMNS.TABLE_NAME, ALL_CONS_COLUMNS.COLUMN_NAME, ALL_CONS_COLUMNS.POSITION

so 'Schema' can be 'SCOTT' for example.

The name is then read directly from the datatable filled with the result: currentTableName = pkFields.Rows[ i]["TABLE_NAME"].ToString().Trim();

So, IF there are quote's, they're there.

When you execute the query in TOAD or other query tool, you get a name surrounded with quotes ?

The table fields retrieval routine also uses a similar query:

SELECT  COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE, NULLABLE, DATA_DEFAULT  FROM ALL_TAB_COLUMNS WHERE OWNER='schema' AND 
TABLE_NAME = :tableName ORDER BY TABLE_NAME ASC, COLUMN_ID ASC

where 'schema' is 'SCOTT' for example and :tableName is a parameter, filled with the table name read earlier by the table retrieval routine.

So I'm a bit lost where the problem is as I know for a fact several customers with case sensitive names for tables, created with quotes use it succesfully.

Well, First of all, just in case, I decided to install the v1.0.2004.2 Final version (as I said, I was using v1.0.2004.2). I refreshed the schema, and PKs where succesfully retrieved!! .

In the other hand, I ran the queries you send me, and worked great. I' think there is a problem joining by table name (I guess that you make a join later), and maybe the Oracle SQLCASE variable is involved (maybe I'm wrong ). I have also tried something interesting that may help you to understand what was happening to me. I have created a table, indentical to an existing one, but with different name (the same one, but in upper case). Then I refreshed the catalog, and the PK appear in both tables.

Well, thanks anyway for the quickly help.

Cheers, Gastón.-

Tongas wrote:

Well, First of all, just in case, I decided to install the v1.0.2004.2 Final version (as I said, I was using v1.0.2004.2). I refreshed the schema, and PKs where succesfully retrieved!! .

A miracle!

In the other hand, I ran the queries you send me, and worked great. I' think there is a problem joining by table name (I guess that you make a join later), and maybe the Oracle SQLCASE variable is involved (maybe I'm wrong ). I have also tried something interesting that may help you to understand what was happening to me. I have created a table, indentical to an existing one, but with different name (the same one, but in upper case). Then I refreshed the catalog, and the PK appear in both tables.

So, the PK defined on 1 table, ended up on BOTH tables? Interesting, as the name check to find back the table is case sensitive...

Nevertheless, it's good to know you can proceed now