Importing database Packages/Functions/Triggers in Teiid

[neetacomp]

I have imported all the tables from my datasource to Teiid and have defined row based security on some of my tables using some virtual procedures.

But if my actual database package/procedure try to access these tables, How can I implement security in those package/procedure?

In that case do I have to import all the database objects in Teiid?

I can not see any option to import Packages/Functions/Triggers in Teiid. Please let me know how can I achieve it.

Whatever Oracle/SQL stored procedures can do, can it be done using virtual procedures?? (including dynamic queries, cursor, sequence etc)

Thanks in advance,

Neeta

[shawkins]

> I can not see any option to import Packages/Functions/Triggers in Teiid. Please let me know how can I achieve it.

Not all source constructs are importable or representable in Teiid.  Functions are representable, but typically do not have an import option.  You can log an enhancement to either update the oracle translator with additional oracle system functions or add import options for udfs exposed through JDBC metadata.  There is no direct concept of a package either.  Our data roles can approximate a package in that you can grant execute access to only a subset of your procedures - but unless you group the other procedures in their own hidden model they will still be visible in the metadata.  Teiid also only has the concept of instead of triggers to handle insert/update/delete handling against views and there is no ability to import triggers.  Importing triggers in any case would only make sense if you are trying to pull functionality off of your source.  The typical approach is for Teiid to act as a cooperative integration layer that still relies on the sources to maintain relational integrity, existing lower level functionality, etc.

> Whatever Oracle/SQL stored procedures can do, can it be done using virtual procedures?? (including dynamic queries, cursor, sequence etc)

Not entirely.  You'll want to check the reference https://docs.jboss.org/author/display/TEIID/Procedures for all functionality.  Dynamic queries are supported via executed immediate and cursoring is implicit in any iterative statement or procedure resultset - however currently there is no explicit mechanism to reference a cursor by name.  Sequencing can be delegated to Oracle via a table with special metadata:

create foreign table seq (nextval integer OPTIONS (nameinsource 'seq.nextval'), currentval (nameinsource 'seq.currentval') ) OPTIONS (nameinsource 'DUAL')

or you can use the native procedure to call whatever source sql you want.