Oracle and TimesTen – Can you bind it

You can use DDL triggers in Oracle.

I haven't actually done this myself, so here are some links:

• http://www.dba-oracle.com/t_ddl_triggers.htm
• http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2153503
• http://justoracle.blogspot.com/2006/10/ddl-triggers.html

One way of doing this on a low level basis is by reading the USER_SOURCE for the corresponding package. In PL/SQL you could loop over the lines and add each line to an array (/table) of type DBMS_SQL.VARCHAR2A, lets call this variable 'sqlTable'.

The first line of your sqlTable contains the package name, with a little String manipulation you should be able to change the name from 'PACKAGE_NAME' to 'SCHEMA'.'PACKAGE_NAME'.

Then using DBMS_SQL package perform the following steps:

1. cur = DBMS_SQL.OPEN_CURSOR; -- cur is of type pls_integer
2. DBMS_SQL.PARSE(cur, sqlTable, sqlTable.first, sqlTable.last, false, DBMS_SQL.NATIVE);
3. DBMS_SQL.execute(cur)

Another way is to spool the output of the USER_SOURCE to a file, connect to the other schema and load / execute the file there. See this.