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 :)

Calling Java code from PLSQL

Create a simple java class.

[oracle@db ~]$ sqlplus oratt/oracle

SQL*Plus: Release 11.2.0.3.0 Production on Thu Feb 7 18:16:27 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> ! cat Factorial.java
public class Factorial {
  public static int getFactorialValue(int a) {
    if (a == 1) return 1;
    else return a * getFactorialValue(a - 1);
  }
}

SQL>

There are a lot of options for loading the java class (I'll describe only two of them).

1. Load the file by using loadjava utility

SQL> ! loadjava -user oratt/oracle Factorial.java

SQL>

select object_name, object_type from user_objects where object_type like 'J%';

SQL> select object_name, object_type from user_objects where object_type like 'J%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
Factorial                      JAVA SOURCE
Factorial                      JAVA CLASS

SQL>

2. Create java statement Before that let's delete Factorial class from database

SQL> !dropjava -user oratt/oracle Factorial.java

SQL> select object_name, object_type from user_objects where object_type like 'J%';

no rows selected

SQL> CREATE JAVA SOURCE NAMED "Factorial" AS
   2   public class Factorial {
   3     public static int getFactorialValue(int a) {
   4       if (a == 1) return 1;
   5       else return a * getFactorialValue(a - 1);
   6     }
   7   }
   8 /  

Java created.

SQL> select object_name, object_type from user_objects where object_type like 'J%';

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
Factorial                      JAVA SOURCE
Factorial                      JAVA CLASS

SQL>

Now, create a package.

SQL> CREATE OR REPLACE PACKAGE java_pack AS
   2   function factorial_func(x in pls_integer) return pls_integer;
   3 END java_pack;
   4 /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY java_pack AS
   2
   3   function factorial_func(x in pls_integer)
   4    return  pls_integer
   5   as language java
   6   name 'Factorial.getFactorialValue (int) return int';
   7
   8 END java_pack;
   9 /

Package body created.

SQL>

SQL> exec dbms_output.put_line(java_pack.factorial_func(15));
2004310016

PL/SQL procedure successfully completed.

SQL> select java_pack.factorial_func(10) from dual;

JAVA_PACK.FACTORIAL_FUNC(10)
----------------------------
                     3628800

SQL>

Is you can see, its very easy to use Java code in PLSQL. There is only one thing you should remember - it is a Oracle Database JVM version (DB Version 11.2 - Java 1.5.0 (1.5.0_01)) I would recommend you to read the following notes:

• What Version of Java is Compatible With The Database JVM? [ID 438294.1] 
• How to identify JDK version running in Oracle Database JVM [ID 331673.1]

Calling external C function from PLSQL

In this post I'll describe the process of calling  external C function from PLSQL.

1. Create a simple C function. (in this example I use basic factorial function)

[oracle@db /]$ cd /u01/app/oracle/product/
[oracle@db product]$ ls
11.2.0
[oracle@db product]$ mkdir c_lib
[oracle@db product]$ ls
11.2.0  c_lib
[oracle@db product]$ cd c_lib
[oracle@db c_lib]$ touch factorial.c
[oracle@db c_lib]$ cat factorial.c
int getVal (int a){
  if(a!=1){
    return(a * getVal(a-1));
    }
  else return 1;
}
[oracle@db c_lib]$

1.1 Compile it and create a shared library.

[oracle@db c_lib]$ gcc -fPIC -c factorial.c
[oracle@db c_lib]$ gcc -shared -o factorial.so factorial.o
[oracle@db c_lib]$ ls
factorial.c  factorial.o  factorial.so
[oracle@db c_lib]$

Now we have a shared library. Lets set up the listener.
2. Modify the listener.ora.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = IPC)(KEY = extproc)) 
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = PLSExtProc)
     (ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
     (PROGRAM = extproc)
     (ENVS = "EXTPROC_DLLS=/u01/app/oracle/product/c_lib/factorial.so")
    )
  )

ADR_BASE_LISTENER = /u01/app/oracle

Restart listener.

[oracle@db admin]$ lsnrctl stop listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:31:01

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
The command completed successfully
[oracle@db admin]$ lsnrctl start listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:31:09

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 11.2.0.3.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                05-FEB-2013 15:31:09
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@db admin]$

Check the listener status:

[oracle@db admin]$ lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 05-FEB-2013 15:32:13

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                05-FEB-2013 15:31:09
Uptime                    0 days 0 hr. 1 min. 3 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=extproc)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully

3. Create a library (as sysdba)

[oracle@db c_lib]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 4 18:01:02 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning and Real Application Testing options

SQL> create or replace library c_factorial as '/u01/app/oracle/product/c_lib/factorial.so' 
   2 /

Library created.

SQL> select owner, library_name, file_spec, status from all_libraries where lower(library_name) = 'c_factorial';

OWNER     LIBRARY_NAME    FILE_SPEC                                  STATUS
--------- --------------- ------------------------------------------ -------
SYS       C_FACTORIAL     /u01/app/oracle/product/c_lib/factorial.so VALID

SQL> grant execute on c_factorial to oratt;

Grant succeeded.

SQL>

4. Create a package (oratt user).

SQL> CREATE OR REPLACE PACKAGE c_pack AS
   2   function factorial_func(x in integer) return pls_integer;
   3 END;
   4 / 
 
Package created.
 
SQL> CREATE OR REPLACE PACKAGE BODY c_pack AS 
   2   function factorial_func(x in integer)
   3     return  pls_integer
   4   as language c
   5   library sys.c_factorial
   6   name "getVal"; 
   7 END;
   8 / 
 
Package Body created.

5. Execute factorial_func function

SQL> set serveroutput on
SQL> begin
   2   dbms_output.put_line(c_pack.factorial_func(5));
   3 end;
   4 /
120

PL/SQL procedure successfully completed.

SQL> select c_pack.factorial_func(5) from dual;

C_PACK.FACTORIAL_FUNC(5)
------------------------
                     120

SQL>

Restrictions on Calling Functions from SQL expressions

Recently, I've decided to start preparation for 1Z0-146 exam and the first exam topic is 'List restrictions on calling functions from SQL expressions'. Let's start from basic and obvious restrictions:.

1. Function should accept only IN parameters (not OUT, IN OUT).
2. Function should accent and return only valid SQL data types (not PL/SQL specific types like boolean, record and etc.).
3. Parameters must be specified with positional notation (not named notation '=>').
4. User should have a Execute privilege.

The above restrictions are quite simple and I can't add something to it. I would like to create a couple of examples about 'DML' and 'Select' restrictions.  
There is an easy test case:

SQL> create table test1 (id number, name varchar2(100));

Table created.

SQL> insert into test1 select level, 'name'||level from dual connect by level <= 10;

10 rows created.

SQL> commit;

Commit complete.

SQL> select * from test1;

        ID NAME
---------- -----------------------------------------
         1 name1
         2 name2
         3 name3
         4 name4
         5 name5
         6 name6
         7 name7
         8 name8
         9 name9
        10 name10

10 rows selected.

SQL> create table test2 (id number);

Table created.

SQL>

1. Functions called from select statement can't contain DML statements.

SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
   2   return test1.name%type
   3 is
   4   v_name test1.name%type;
   5 begin
   6   insert into test1 (id, name) values (-1,'test'); -- DML 
   7   return 'test';
   8 end func_sql_run_test;
   9 /

Function created.

SQL> select func_sql_run_test(1) from test1 where id =1;
select func_sql_run_test(1) from test1 where id =1
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6

SQL>

2. Functions called from update, delete and insert .. select statement can't query or modify the same table.

2.1. Example 1.

SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
   2 return test1.name%type
   3 is
   4   v_name test1.name%type;
   5 begin
   6   select name into v_name from test1 where id = p_id;
   7   return v_name;
   8 end func_sql_run_test;
   9 /

Function created.

SQL> update test1 set name = func_sql_run_test(1) where id = 10;
update test1 set name = func_sql_run_test(1) where id = 10
                        *
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6

SQL> delete from test1 where name = func_sql_run_test(1);
delete from test1 where name = func_sql_run_test(1)
                               *
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6

SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;
insert into test1 (id, name) select 11, func_sql_run_test(1) from dual
                                        *
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6

SQL> -- insert works fine
SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));

1 row created.

SQL> rollback;

Rollback complete.

SQL>

2.2. Example 2.

SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
   2   return test1.name%type
   3 is
   4   v_name test1.name%type;
   5 begin
   6   insert into test1 (id, name) values (-1,'test'); -- DML 
   7   return 'test';
   8 end func_sql_run_test;
   9 /

Function created.

SQL> update test1 set name = func_sql_run_test(1) where id = 10;
update test1 set name = func_sql_run_test(1) where id = 10
                        *
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6

SQL> delete from test1 where name = func_sql_run_test(1);
delete from test1 where name = func_sql_run_test(1)
                               *
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6

SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;
insert into test1 (id, name) select 11, func_sql_run_test(1) from dual
                                        *
ERROR at line 1:
ORA-04091: table ORATT.TEST1 is mutating, trigger/function may not see it
ORA-06512: at "ORATT.FUNC_SQL_RUN_TEST", line 6

SQL> -- insert works fine
SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));

1 row created.

SQL> rollback;

Rollback complete.

SQL>

If I change the table name in DML statement, the function can be invoked through DML.

SQL> create or replace function func_sql_run_test (p_id in test1.id%type)
   2 return test1.name%type
   3 is
   4   v_name test1.name%type;
   5 begin
   6   insert into test2 (id) values (-1); 
   7   return 'test';
   8 end func_sql_run_test;
   9 /

Function created.

SQL> update test1 t set name = func_sql_run_test(1) where id = 10;

1 row updated.

SQL>  select * from test2;

        ID
----------
        -1

SQL> delete from test1 where name = func_sql_run_test(1);

1 row deleted.

SQL> rollback;

Rollback complete.

SQL> insert into test1 (id, name) select 11, func_sql_run_test(1) from dual;

1 row created.

SQL> insert into test1 (id, name) values (11, func_sql_run_test(1));

1 row created.

SQL> rollback;

Rollback complete.

Additionally, there are some additional restrictions related with transactions (commit, rollback, DDL,DCL and etc.) Of course, all these restrictions are obvious, but I still decided to write about it because it's quite important and I can use this post as a hint :). 

ORA-01795 - maximum number of expressions in a list is 1000 error

Recently, while loading a lot of data into DWH, I've faced with the ORA-01795 error. Some of my colleagues wrote the following code:

declare 
  v_user_name clob;
begin
    ...
    select wm_concat('''' ||username) || ''''),
      into v_user_name
      from customers;
    ...
    execute immediate
            'insert into temp_customer (user_name)
             select username
               from  tab_1
              where username in (' || v_user_name || ')';
    ...
end;

As you can see, v_user_name variable is populated by the first select statement. It contains number of customers divided by comma (why has my colleague decided to use undocumented "wm_concat" function - its another question, I hope there was a reason for that :) ) and after that, we use v_user_name variable into dynamic sql. This code had been working fine for years, but recently we had to load a lot of data (a lot of customers) into DWH and we've faced with the ORA-01795. I found a topic in OTN forum about this issue.

First of all, I decided to use select * from table (sys.odcivarchar2list ... ) construction (I am a very lazy person :), and didn't want to rewrite this code), but when I tried to use the above construction I received another error - ORA-00939: too many arguments for function. This approach didn't work. After that, I decided to divide the code into small parts - I had to rewrite the package a bit and it works fine. This solution works fine.
Of course, there is one more approach - create a temp table and just join it to the query. I think it is the best option.

PLSQL Server Pages

Recently, I’ve received a very interesting task at my job, I have to create a colourful report which uses different colours depending on values (if value < 5 then cell should has a green colour, if value between 5 and 9 then yellow, else red).

There are a lot of methods to create a report like this. First approach based on using any BI tool ( like SAS, Oracle BI, BO and etc.). These tools allow you to create a report and upload all data into Excel file. There is only one disadvantage – they cost money, so in my case I can’t use it because I don’t have a budget for that.

Second approach based on uploading a CSV file from Oracle DB by using PL/SQL, but in this case there is no opportunity to control the cells’ colours, so I’ve decided using PL/SQL server pages feature.

Let’s create a  simple example. First of all, create test tablespace and objects owner user.

SQL> create tablespace gena_tbls datafile '/u01/app/oracle/oradata/orcl/gena.dbf' size 100m autoextend on;

Tablespace created.

SQL> create user gena identified by gena default tablespace gena_tbls;

User created.

SQL> grant connect, resource to gena;

Grant succeeded.

Ensure that the database account ANONYMOUS is unlocked.

SQL> alter user anonymous account unlock;

User altered.

Create a simple table.

SQL> connect gena/gena
Connected.

SQL> create table t1 ( indicator_name varchar2(50), value number);

Table created.

SQL> insert into t1 values ('INDIC 1', 1);

1 row created.

SQL> insert into t1 values ('INDIC 2', 6);

1 row created.

SQL> insert into t1 values ('INDIC 3', 10);

1 row created.

SQL> insert into t1 values ('INDIC 4', 4);

1 row created.

SQL> insert into t1 values ('INDIC 5', 8);

1 row created.

SQL> commit;

Commit complete.

SQL>

Log on to the database as an XML DB administrator (SYS in this case), that is a user with the XDBADMIN role and create the DAD.

SQL> connect / as sysdba
Connected.
SQL> exec dbms_epg.create_dad('gena_dad', '/gena_report/*');

PL/SQL procedure successfully completed.

SQL>

Set the DAD attribute database-username to the database user whose privileges must be used by the DAD.

SQL> exec dbms_epg.set_dad_attribute('gena_dad', 'database-username', 'gena');

PL/SQL procedure successfully completed.

SQL>

Grant EXECUTE privilege to the database user GENA whose privileges must be used by the DAD.

SQL> grant execute on dbms_epg to gena;

Grant succeeded.

SQL>

Log on to the database as the database user whose privileges must be used by the DAD and authorize the embedded PL/SQL gateway to invoke procedures and access document tables through the DAD.

SQL> connect gena/gena
Connected.
SQL> exec dbms_epg.authorize_dad('gena_dad');

PL/SQL procedure successfully completed.

SQL>

Create a sample PL/SQL stored procedure. This procedure creates an HTML page that includes the result set of a query of gena.t1.

Ensure that the listener is able to handle HTTP requests.

[oracle@db ~]$ lsnrctl status listener

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 31-JUL-2012 12:24:24

Copyright (c) 1991, 2011, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=db)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date                31-JUL-2012 12:24:00
Uptime                    0 days 0 hr. 0 min. 24 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/db/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=db)(PORT=8080))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "orcl" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
  Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@db ~]$

Run a web browser and put the following address: http://db:8080/gena_report/print_indicators

Перенос PL/SQL в TimesTen и ttSrcScan

В версии 11g, TimesTen стал поддерживать PL/SQL, а точнее, движок PL/SQL Oracle Database 11g, был портирован в TimesTen, т.е., теперь, приложения, использующие PL/SQL могут работать и в TimesTen. Но, вы прекрасно понимаете, что TtimesTen не поддерживает ВСЕГО функционала Oracle Database, например нет триггеров, нельзя создавать типы (CREATE TYPE) и т.д. Кроме того, как показывает практика, приложения содержат огромное количество кода (сотни тысяч или миллионы строк) и анализ кода на проверку работоспособности в TimesTen может занять огромное количество времени, поэтому разработчики создали специальную утилиту (ttSrcScan), которая должна автоматизировать данный процесс.

Данная утилита доступна после установки TimesTen 11.2.1 в каталоге $TIMESTEN_HOME/quickstart/sample_util (правда нужно не забыть поставить quickstart), но если не хочется ставить лишнее программное обеспечение, то можно обратиться в Oracle и получить ее отдельно.

Итак, данная утилита принимает на вход директории в которых содержатся файлы с кодом и анализирует их, после чего выдает отчеты (HTML и текстовый) об обнаруженных ошибках. Очень удобная утилита, но, конечно существует ряд нюансов.
Например, попробуем создать пакет.

create or replace package test_1 as

  procedure p_test;

end test_1;
/
create or replace package body test_1 as

  procedure p_test
  is
     v_rec number (10);  
  begin
    for v_rec in ( select level from dual connect by level <=10 ) loop
      dbms_output.put_line ('1');
    end loop;
  end p_test;   
end test_1;
/

Т.е. я явно указываю SQL конструкцию, которую TimesTen не поддерживает.

Command> select level from dual connect by level <=10;
 1001: Syntax error in SQL statement before or at: "by", character position: 32
select level from dual connect by level <=10
                               ^^
The command failed.
Command>

Проверим данный пакет с помощью ttSrcScan.

[oracle@tt1 ~]$ cd /u01/app/oracle/product/11.2.1.8/TimesTen/tt2/quickstart/sample_util/
[oracle@tt1 sample_util]$ ./ttSrcScan -i /home/oracle/source -o /home/oracle/result


  ***************************************
  * Oracle TimesTen Source Code Scanner *
  ***************************************

  Options used:
  =============
  -input           = /home/oracle/source
  -output          = /home/oracle/result
  -nestedDir       = TRUE
  -version         = 11.2.1.8.0
  -summaryPrefix   = ttSrcScan
  -maxRows         = 15


  Summary Statistics:
  ===================

  Files Processed:
  Input files and sub-directories processed :         1
  Sub-directories processed                 :         0
  Unsupported file types                    :         0
  Files Scanned                             :         1

  Files Scanned:
  Scanned files with no source code issues  :         1
  Scanned files with source code issues     :         0

  Lines Of Code Scanned:
  Lines of code                             :        19
  Lines of code with issues                 :         0
  Percentage of lines of code with issues   :         0.00%


  Detail Statistics:
  ==================
  Summary Report           /home/oracle/result/ttSrcScan_summary.html
  Files Processed Report   /home/oracle/result/ttSrcScan_all_input_files.html
  Files With Issues Report /home/oracle/result/ttSrcScan_issue_files.html
  Log File                 /home/oracle/result/ttSrcScan_log_file.log


[oracle@tt1 sample_util]$

Следовательно, ошибок нет. Скомпилируем данный пакет в TimesTen.

Command> create or replace package test_1 as
       >
       >   procedure p_test;
       >
       > end test_1;
       > /

Package created.

Command> create or replace package body test_1 as
       >
       >   procedure p_test
       >   is
       >      v_rec number (10);
       >   begin
       >     for v_rec in ( select level from dual connect by level <=10 ) loop
       >       dbms_output.put_line ('1');
       >     end loop;
       >   end p_test;
       >
       > end test_1;
       > /

Package body created.

Что удивительно, здесь также ошибок нет и только при выполнении получаем

Command> exec test_1.p_test;
 1001: Syntax error in SQL statement before or at: "BY", character position: 32
SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=10
                               ^^
 8507: ORA-06512: at "ORACLE.TEST_1", line 7
 8507: ORA-06512: at line 1
The command failed.
Command>

Следовательно, ошибки, связанные с неподдерживаемыми SQL операторами, получим только в runtime, это нужно учитывать. Кроме того, TimesTen поддерживает работу с некоторыми системными пакетыми (UTL_FILE, DBMS_LOCK и др.), полный список можно посмотреть в документации. Но, содержание пакетов может быть разным. Например:

Command> # TimesTen
Command> desc dbms_lock
       > ;

Package SYS.DBMS_LOCK:

  Procedure SLEEP:
    Arguments:
      SECONDS                         IN     NUMBER

1 PL/SQL object found.

и Oracle Database.

SQL> desc dbms_lock;

PROCEDURE ALLOCATE_UNIQUE

FUNCTION CONVERT RETURNS NUMBER(38)

FUNCTION CONVERT RETURNS NUMBER(38)

FUNCTION RELEASE RETURNS NUMBER(38)

FUNCTION RELEASE RETURNS NUMBER(38)

FUNCTION REQUEST RETURNS NUMBER(38)

FUNCTION REQUEST RETURNS NUMBER(38)

PROCEDURE SLEEP
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SECONDS                        NUMBER                  IN

Поэтому, при анализе кода, ttSrcScan добавляет строки содержащие системные пакеты, как "потенциально ошибочные". Например, проанализируем данный код:

create or replace package test_1 as

  procedure p_test;

end test_1;
/
create or replace package body test_1 as

  procedure p_test
  is
  begin
    dbms_lock.sleep(10);
  end p_test;

end test_1;
/

[oracle@tt1 sample_util]$ ./ttSrcScan -i /home/oracle/source -o /home/oracle/result


  ***************************************
  * Oracle TimesTen Source Code Scanner *
  ***************************************

  Options used:
  =============
  -input           = /home/oracle/source
  -output          = /home/oracle/result
  -nestedDir       = TRUE
  -version         = 11.2.1.8.0
  -summaryPrefix   = ttSrcScan
  -maxRows         = 15


  Summary Statistics:
  ===================

  Files Processed:
  Input files and sub-directories processed :         1
  Sub-directories processed                 :         0
  Unsupported file types                    :         0
  Files Scanned                             :         1

  Files Scanned:
  Scanned files with no source code issues  :         0
  Scanned files with source code issues     :         1

  Lines Of Code Scanned:
  Lines of code                             :        16
  Lines of code with issues                 :         1
  Percentage of lines of code with issues   :         6.25%


  Detail Statistics:
  ==================
  Summary Report           /home/oracle/result/ttSrcScan_summary.html
  Files Processed Report   /home/oracle/result/ttSrcScan_all_input_files.html
  Files With Issues Report /home/oracle/result/ttSrcScan_issue_files.html
  Log File                 /home/oracle/result/ttSrcScan_log_file.log


[oracle@tt1 sample_util]$ 

А в файле test.sql__source_issues.html видим.

Собственно, нужно также быть очень осторожным при переносе системных пакетов.

Конечно, данная утилита имеет свои ограничения, но все же она позволяет очень быстро проверить основные конструкции кода. Игорь Мельников обещал написать (если будет время конечно :) ) свою утилиту ttChecker, аналогичную RacChecker.