cancel
Showing results for 
Search instead for 
Did you mean: 

can we use oracles nested tables in designer Xi r2

Former Member
0 Kudos

HI

Can we use oracle nested tables throgh designer

If so how?

Thank you

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Kultar,

given the complexity required in querying an Oracle nested table, it might be best to construct a view then submit that view to Designer. Though looking at the syntax of querying a nested table makes it look like an "alias" process, this "alias" is much more complex, thus making it difficult to replicate in a Designer environment. By performing the Oracle "alias" via the view process, the complexity is maintained in an Oracle environment and Designer just executes the view, which in Designer terms, a view and a table appear as the same to Designer. Here is an example of querying on an Oracle Nested Table and why you'll want to do this as a view:

create view Authors_and_Books as
select Auth.Auth_Name, B.* as The_Books_Written
 from Authors Auth, table(Auth.Books_Written) B;

So, you'll get a list of Auth_Name and for each of those the books written from the nested table, but to Designer, it will all look like a table.

Thanks,

John