Oracle PL/SQL code execution from Timesten

As you know, TimesTen has opportunity to execute SQL statements directly from Oracle DB (using Passthrough). What about PL/SQL? Unfortunately, documentation says:

"A PL/SQL block cannot be passed through to the Oracle database for execution.
Also, you cannot pass through to Oracle for execution a reference to a stored procedure or function that is
defined in the Oracle database but not in the TimesTen database."

It is true, you can not use passthrough feature for PL/SQL, but you can use it for SQL. It means that you can execute PL/SQL from Oracle DB which could be executed from SQL. For example:

Suppose, we have a function in Oracle which we would like to execute:

SQL> create function test_r return number
is
begin
  return 1;
end;
/

Function created.

SQL> select test_r from dual;

    TEST_R
----------
         1

SQL>

Create one table which doesn't exist in TimesTen side.

SQL> create table tt (id number);

Table created.

SQL> select * from tt;

no rows selected

SQL> insert into tt values (3);

1 row created.

SQL> commit;

Commit complete.

SQL>

In TimesTen:
 
Command> select test_r from dual;
 2211: Referenced column TEST_R not found
The command failed.
Command> 
Command> set autocommit 0;
Command> call ttOptSetFlag('PassThrough', 1);
Command> select * from oratt.tt;
< 3 >
1 row found.
Command> select oratt.test_r from oratt.tt;
< 1 >
1 row found.

This method, of course, has some restrictions for PL/SQL execution, but in any case, it is better than nothing :)

3 комментария:

Анонимный комментирует...

I'm so happy to find the good inofrmation On PL/SQL thanks for your effort .friends learn Oracle PLSQL e-learning By 8 years experienced trainer

sqlcode комментирует...

Im glad you had post that awesome info about SQL, i usually use this sqlcodes to answer another questions about SQL programming

Raju Kumar комментирует...

I am really impressed with your writing skills and also with the structure in your weblog. Is that this a paid subject or did you modify it your self? Anyway keep up the excellent high quality writing, it is rare to look a great blog like this one these days..
Hadoop Online Training