How to handle user defined data types, etc. defined at the back-end(ORA) on the Business layer

[Former Member]

SAP BO 4.0
Back-end: Oracle 11g.

Data source: Oracle 11g examples(HR & OE Data Schemas)

Issue description:
How to handle following datatypes

1. Custom Data types defined at the back-end.
2. MDSYS.SDO_GEOMETRY
3. SYS.XMLTYPE
4. "INTERVAL YEAR(2) MONTH" or "Interval Day to Sec"

These are the data type of the columns specified at the back-end.

When  the Business layer's integrity is checked for "Table Structure" and  "Business Objects expression" i get the errors(Image attached) stating  "Undetermined data type" for and "Incorrect Data type".

Has anyone experienced this and how were these data types handled?

[Former Member]

BOXI3 was never keen on user-defined data types and doesn't like varchar(max) either.

It's because it doesn't know how to categorise things that are non-standard.

The only workaround that I'm aware of is to create a view in the database and cast/convert your columns to recognised formats as appropriate.

Regards,

Mark

[Former Member]

Thanks for responding Mark.

What do you say about 4 Platform? I actualy published this Business Layer to the reporsitory and created a report to test if i get an error for the dimensions, mapped with columns having custom data data type defined in the back-end.

Gues what! The report executed successfully . It would raise an error, i thought, but didn't.

With respect to your workaround, could you please give an example?

Were you referring the use of CONVERT() function in ORA database?

Kind Regards,

Sandeep

[Former Member]

Things fail to parse in the universe but work fine with other objects -

Say you have an object called Yesterday defined as trunc(sysdate)-1

It will fail to parse in designer because it is not associated with a table. The common mistake people make is to associate it with a table just so that it will parse but there is no need. Once you use it with at least one table-based object in a report it works fine.

In a similar way, I expect your parsing fails because Designer cannot know all the custom data types and so it errs on the side of caution and errors (obviously there's a bit more to it than that technically speaking!). However, once you run the query and it brings back data correctly as string, number or date then you're fine.

Say you had a table, PRODUCTS of:

ID     integer

Code     special_charactercode <-- your custom data type, length 5 characters

Product_Description varchar2(200)

BO won't know what your special_charactercode is but if it evaluates to a char then you will be fine.

In your view, you may want to try

CREATE OR REPLACE VIEW V_PRODUCTS AS

SELECT

ID,

to_char(Code) "PRODUCT_CODE",

"PRODUCT_DESCRIPTION"

FROM

PRODUCTS;

to see how it handles  Obviously that's not the tidiest code in the world, but an example of what I meant. cast and convert are SQL Server syntax, my apologies for confusing you!

Does that make things a bit clearer for you?

Regards,

Mark