Error While Importing Tables to an Oracle ODS

[ankit_sharma88]

Hello Experts,

I am totally new to BODS. We are suing t he 4.1 version. I have created a datastore in one of my Oracle 11g databases. Its an ODS. While i am importing a few table it throws me an error stating "

Cannot Save<Foreign Key List> for repository object <table_name>.<SQL submitted to Oracle Server <BOBJP>

resulted in error<ORA-01400:cannot insert NULL into "AL_FKPKREL"."FKCOLUMN_NAME")>

Only one of the tables selected  was imported. What i understand is that because a FK is declared in my ODS it is throwing an error. Is there a work around to this.

I have no idea about the metadeta tables but as the name goes, AL_FKPKREL, may be it stores the PK-FK relationship. So do i import all the tables that are joined to a particular table?In that way my DS will be populated with many useless tables. I can't literally disable all my FKs in my ODS.

Can anyone please suggest?

Regards,

Ankit

[former_member211387]

Hi,

"AL_FKPKREL"."FKCOLUMN_NAME" seems to be configured as a "NOT NULL" in the repository database. I think you can alter the table by logging into the repository database and alter the column from NOT NULL to NULL so that it can have null values into this column.

There is an OSS Note (1181680) for a similar case involving this table but a different column PKCOLUMN_NAME.

For your issue this might work. If it doesnt, do raise an OSS Note.

kind regards

Raghu

[former_member211387]

The Error ORA-01400 means that there is an issue with data where the target column is defined as not null and data supplid has null for that column. Nothing to do with PK-FK relationship. 

Looks like you have the BODS repository created in ORACLE.

The table AL_FKPKREL has an index AL_FKPKREL_INDX1 that has columns PKTABLE_NAME, PKTABLE_OWNER and PKCOLUMN_NAME in the index definition.

So the table metadata you are importing is not pulling through information the PK-FK relationship information in full. Below is the table and index structure for this table in the BODS repositoriy (Designer version 14.1.x).

Please check the table definition of the table that your trying to import.

kind regards

Raghu

[ankit_sharma88]

" AL_FKPKREL, may be it stores the PK-FK relationship. So do i import all the tables that are joined to a particular table"

As mentioned above i tried this approach,importing all related tables, but it still gives me the same error.