SAP HANA: exception Handling

I think that the exception handling in SAP Hana is a bit "non-intuitive" (to be nice…). 

I invest a bit of time in order to understand how does it exactly works. 

I prepared a small tutorial and summerized here below. 

Theory: 

In java

 public class myClass() {  
      ...  
      public void myMethod() {  
           ...  
           try {  
                //code to be executed causing exception  
           }  
           catch(Exception e) {  
                //log the exception  
           }  
           ...  
      }  
      ...  
 }  

Equivalent in SQLScript

 CREATE PROCEDURE myproc AS  
 BEGIN  
      ...  
   DECLARE EXIT HANDLER FOR SQL_ERROR_CODE MY_SQL_ERROR_CODE  
   BEGIN  
           -- log the exception  
   END;  
      -- code to be executed causing exception  
 end;  

Practical example

copy and paste on SAP HANA Studio. First prepare something: 

 CREATE TABLE MYTAB (I INTEGER PRIMARY KEY);  
 drop PROCEDURE myproc;  

Then create this procedure: 

 CREATE PROCEDURE myproc AS  
 BEGIN  
      declare myvar int;  
   DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 1299  
     BEGIN  
         write_debug_log('MARCO TEST',   
                                  'Handler of the NO_DATA_FOUND exception of the select below (after the following begin end block)',   
                                  'SQL_ERROR_CODE = ' || ::SQL_ERROR_CODE || '; SQL_ERROR_MESSAGE = ' || ::SQL_ERROR_MESSAGE);   
     END;  
           begin   
                declare my_test int;  
                --CASE GENERIC FOR ANY POSSIBLE EXCEPTION  
                  DECLARE EXIT HANDLER FOR SQLEXCEPTION  
                  begin   
                             write_debug_log('MARCO TEST',   
                                                 'separate handler for division by 0',   
                                                 'SQL_ERROR_CODE = ' || ::SQL_ERROR_CODE || '; SQL_ERROR_MESSAGE = ' || ::SQL_ERROR_MESSAGE);   
                end;  
                my_test := 1/0;  
           end;  
   SELECT I INTO myVar FROM MYTAB; --NO_DATA_FOUND exception  
   SELECT 'NeverReached_noContinueOnErrorSemantics' FROM DUMMY;  
 END;  

Please, notice that I am using my classing logging procedure.
Just substitute this procedure with any other suitable to your environment for logging...

Then let it run: 

 --execute the code and raise the exceptions  
 call myproc;  
 --check the situation in the log table  
 select * from log   
 order by id desc;  

Again, here is the code for accessing my log table...
Just use the one of your environment.

Kill session in Sap Hana

In Oracle an easy trick to kill a session is to run the following query:

 SELECT 'exec sys.kill_session(' || a.sid || ',' || a.serial# || ');'  
    ,osuser  
    ,terminal  
    ,machine  
    ,program  
    ,a.*  
  FROM v$session a  
 WHERE status = 'ACTIVE' AND username = USER;  

Of course you have to be careful on the way you use it... You should be sure about what is running in the correspondent session.
You can use for example the tool offered by SQL Developer (you need to have the rights on the table v$session).

What about SAP HANA? The basic command you need is:

   ALTER SYSTEM CANCEL SESSION 'conn_id';  

Where conn_id is in the table M_CONNECTIONS, column CONNECTION_ID.
You can retrieve the complete command with the following query:

 SELECT   
    'alter system cancel session ''' || C.CONNECTION_ID || ''';' command,   
    C.CONNECTION_ID,   
    PS.STATEMENT_STRING  
 FROM M_CONNECTIONS C JOIN M_PREPARED_STATEMENTS PS  
     ON C.CONNECTION_ID = PS.CONNECTION_ID   
     AND C.CURRENT_STATEMENT_ID = PS.STATEMENT_ID  
 WHERE C.CONNECTION_STATUS = 'RUNNING'   
  AND C.CONNECTION_TYPE = 'Remote';  

    

Execution plan in SAP HANA

Do you have a slow query in Hana?
Do you want to improve the performances of a query?

First step is to retrieve the execution plan:

 DELETE FROM explain_plan_table WHERE statement_name = 'marco test nr 1';  
 DELETE FROM explain_plan_table WHERE statement_name = 'marco test nr 1';  
 EXPLAIN PLAN SET STATEMENT_NAME = 'marco test nr 1' FOR  
 select * from dummy; --put here your query!!!  
   
   
   
 SELECT 
   table_size,   
   to_number(output_size, 10, 2) as output_size,   
   to_number(subtree_cost, 10, 2) cost,   
   operator_name,   
   operator_details,   
   table_name  
 FROM explain_plan_table  
 WHERE statement_name = 'marco test nr 1'  
 order by operator_id;    

The output of the last select will look like this in your SAP HANA STUDIO:

In order to read the execution plan, I believe that the most important column is the "Operator Name". 
This is the column telling you how the DBMS is approaching the problem. 
Here you will see if it is doing something stupid. 
The question is: what is the meaning of the content of this column? Here is the set of possible values: 

Operator Name	Description
ROW SEARCH	Starting position of row engine operators. OPERATOR_DETAILS lists projected columns.
LIMIT	Operator for limiting number of output rows
ORDER BY	Operator for sorting output rows
HAVING	Operator for filtering with predicates on top of grouping and aggregation
GROUP BY	Operator for grouping and aggregation
MERGE AGGREGATION	Operator for merging the results of multiple parallel grouping and aggregations
DISTINCT	Operator for duplicate elimination
FILTER	Operator for filtering with predicates
UNION ALL	Operator for producing union-all of input relations
MATERIALIZED UNION ALL	Operator for producing union-all of input relations with intermediate result materialization
BTREE INDEX JOIN	Operator for joining input relations through B-tree index searches. Join type suffix can be added. For example, B-tree index join for left outer join is shown as BTREE INDEX JOIN (LEFT OUTER). Join without join type suffix means inner join.
CPBTREE INDEX JOIN	Operator for joining input relations through CPB-tree index searches. Join type suffix can be added.
HASH JOIN	Operator for joining input relations through probing hash table built on the fly. Join type suffix can be added.
NESTED LOOP JOIN	Operator for joining input relations through nested looping. Join type suffix can be added.
MIXED INVERTED INDEX JOIN	Operator for joining an input relation of row store format with a column table without format conversion using an inverted index of the column table. Join type suffix can be added.
BTREE INDEX SEARCH	Table access through B-tree index search
CPBTREE INDEX SEARCH	Table access through CPB-tree index search
TABLE SCAN	Table access through scanning
AGGR TABLE	Operator for aggregating base table directly
MONITOR SEARCH	Monitoring view access through search
MONITOR SCAN	Monitoring view access through scanning

Source of information: 
https://help.sap.com/hana/html/sql_explain_plan.html

Connect By in SAP HANA

As you know SAP HANA does not offer the feature: CONNECT BY.

"Connect by" is a feature which can be used in order to get full path over tables. 

Ex: Suppose you have a table made of arcs, you want to have all the paths inside the table. 

In Oracle exists a feature called "connect by" which makes all the computation for you. 

Probably I will not be able to make a generic code like in Oracle (like simply call a function). 

Anyway, I have got some good result on a simple test case. 

Example and Theory documentation

 create table arc (  
    node_father int,   
    node_child int  
 );   
   
 insert into arc values(1, 2);  
 insert into arc values(1, 3);  
 insert into arc values(1, 4);  
 insert into arc values(2, 5);  
 insert into arc values(2, 6);  
 insert into arc values(6, 8);  
 insert into arc values(6, 9);  
 insert into arc values(4, 7);  

The graph looks like this: 

                

              1

             /|\

           /  |  \

         2   3    4

        /\            \

      /    \            \

     5      6          7

             /\

           /    \

         8      9     

Set of Path starting from the root: 

1-3

1-4-7

1-2-5

1-2-6-8

1-2-6-9

Set of Sub path

2-5

6-8

6-9

4-7

2-6-8

2-6-9

 create table paths(  
    node_father int,   
    node_child int,   
    length int,   
    path varchar(100),   
    root int  
 );  

Expected result: 

F    = father

C    = Child

L    = Length (of the path)

R    = Root

Path = Path

The first loop is simply moving the arc table in the path. 

All the other step join the current paths p to the ars a on the condition 

     p.child = a.father and p.length = current_iteration - 1

The values for each path are given like: 

     father   = a.father

     child    = a.child

     length  = p.length + 1 (or current_iteration number)

     root     = p.root

     path     = p.path - a.node_child

F C L R   Path

--------------------

1 2 0 1   1-2         1st Loop (length 1)

1 3 0 1   1-3

1 4 0 1   1-4

2 5 0 2   2-5

2 6 0 2   2-6

6 8 0 6   6-8

6 9 0 6   6-9

4 7 0 4   4-7

-----

2 5 1 1   1-2-5       2nd Loop (length 2)

2 6 1 1   1-2-6

4 7 1 1   1-4-7

6 8 1 1   2-6-8

6 9 1 1   2-6-9

-----

6 8 2 1   1-2-6-8     3rd Loop (length 3)

6 9 2 1   1-2-6-9

To remove the sub path, remove each entry where of the previous iteration which matched in the current iteration.

This at the end of each loop. 

Following this variation then it will look like: 

F C L R   Path

--------------------

1 2 0 1   1-2    --> deleted after iteration 2     

1 3 0 1   1-3

1 4 0 1   1-4    --> deleted after iteration 2

2 5 0 2   2-5    --> deleted after iteration 2

2 6 0 2   2-6    --> deleted after iteration 2

6 8 0 6   6-8    --> deleted after iteration 2

6 9 0 6   6-9    --> deleted after iteration 2

4 7 0 4   4-7    --> deleted after iteration 2

--------------------

2 5 1 1   1-2-5       

2 6 1 1   1-2-6  --> deleted after iteration 3

4 7 1 1   1-4-7

6 8 1 1   2-6-8  --> deleted after iteration 3

6 9 1 1   2-6-9  --> deleted after iteration 3

--------------------

6 8 2 1   1-2-6-8     

6 9 2 1   1-2-6-9

WATCH OUT!!!! Oracle decided to remove the sub patch using the concept of STARTING!
I used a different approach! The algorithm try to create the longest path and remove the path included in another which is longer.
This works only if you are using trees or generically acyclic graph (an by definition a tree is a direct acyclic graph)!!!

Code: 

 create procedure connect_by_test(in p_mode varchar(10) default 'no_sub') as  
   v_length integer;  
   v_count integer;  
   v_boolean varchar(5);  
   c_procedure varchar(30) := 'connect_by_test';  
   v_sql varchar(5000);  
   
 begin  
   
   -- put a clean table on the beginning  
   v_sql := 'delete from paths';  
   write_debug_log(c_procedure, 'truncate table paths', v_sql);  
   execute immediate v_sql;  
   
   
   -- initialization of the control variables used for the loop  
   v_boolean := 'true';  
   v_length := 1;  
   
   
   while v_boolean = 'true' do  
     -- it is needed to distinguish the first iteration with the n-th generic one  
     if (v_length = 1) then  
       -- simply insert all the arcs into the final table  
       v_sql := 'insert into paths(node_father,  node_child,       length,    root,                   path)  
        select a.node_father, a.node_child, ' || v_length || ' , a.node_father, a.node_father || '' - '' || a.node_child  
        from  arc a';  
       write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);  
       execute immediate v_sql;  
   
     else  
       -- join the arcs with the path known till now  
       v_sql := 'insert into paths(node_father,  node_child,       length,  root,               path)  
        select a.node_father, a.node_child, ' || v_length || ' , p.root, p.path || '' - '' || a.node_child  
        from  arc a join paths p on ( a.node_father = p.node_child )  
        where  p.length = ' || v_length || '-1' ;  
       write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);  
       execute immediate v_sql;  
   
       if p_mode = 'no_sub' then  
         -- delete the known path joined one more time (I want to keep only the full paths (no subpath)  
         v_sql := 'delete from paths where (node_father, node_child, length) in ( ' ||  
          -- this first query is to get the arcs joined in front of the SUB path  
           ' select a.node_father, a.node_child, ' || v_length - 1 || '  
          from  arc a join paths p on ( a.node_father = p.node_child )  
          where  p.length = ' || v_length || '-1  
          union all ' ||  
           -- this second query is to get the arcs joined after the SUB path  
         ' select p.node_father, p.node_child, ' || v_length - 1 || '  
          from  arc a join paths p on ( a.node_father = p.node_child )  
          where  p.length = ' || v_length || '-1 )' ;  
         write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);  
         execute immediate v_sql;  
         /**/  
       end if;  
     end if;  
   
     -- check how many records have I inserted in the last iteration  
     write_debug_log(c_procedure, 'perform the count for length: ' || v_length, 'select count(*) from paths where length = ' || v_length);  
     select count(*) into v_count from paths where length = v_length;  
     write_debug_log(c_procedure, 'performed count for length: ' || v_length, v_count);  
   
     -- exit condition: if in the last iteration I did not generated any entry, then I have found already every possible path.  
     if v_count = 0 then  
       -- no other loop, change the while condition  
       v_boolean := 'false';  
     else  
       --there will be another loop  
       --increment the level to be used for the next loop  
       v_length  := v_length + 1;  
     end if;  
   
   
   
     -- this is just an emergency break: after 10 iteration just stop.  
     if (v_length > 10) then  
       v_boolean := 'false';  
     end if;  
   
   
     write_debug_log(c_procedure, 'variable loop status: v_length = ' || v_length || '; v_boolean = ' || v_boolean, '');  
   end while;  
   
   end;  
   

After performing a test, here is the output example: 

(SUB mode = with sub path)

NODE_FATHER;NODE_CHILD;LENGTH;ROOT;PATH

1;2;1;1;1 - 2

1;3;1;1;1 - 3

1;4;1;1;1 - 4

2;5;1;2;2 - 5

2;6;1;2;2 - 6

4;7;1;4;4 - 7

6;8;1;6;6 - 8

6;9;1;6;6 - 9

2;5;2;1;1 - 2 - 5

2;6;2;1;1 - 2 - 6

4;7;2;1;1 - 4 - 7

6;8;2;2;2 - 6 - 8

6;9;2;2;2 - 6 - 9

6;8;3;1;1 - 2 - 6 - 8

6;9;3;1;1 - 2 - 6 - 9

(No SUB mode = without sub path)

NODE_FATHER;NODE_CHILD;LENGTH;ROOT;PATH

1;3;1;1;1 - 3

2;5;2;1;1 - 2 - 5

4;7;2;1;1 - 4 - 7

6;8;3;1;1 - 2 - 6 - 8

6;9;3;1;1 - 2 - 6 - 9

Cool! Very nice! Now I understood!
But what does it means? Of course I am working here! I am not playing with the concept of table Arc and Paths... What can I do now in order to use on my real table?
I am a lazy guy! I want to do just a copy and paste!

Since you need to have totally dynamic code, Hana is having some issue with retrieving the result from a dynamic query.

You need to create first a table:

 table.schemaName = "My Schema";  
   
 table.tableType = COLUMNSTORE;  
 table.columns = [  
   {name = "ID"; sqlType = INTEGER; nullable = false;},  
   {name = "SQL_TEXT"; sqlType = NVARCHAR; nullable = true; length = 5000;},  
   {name = "NUM_RESULT"; sqlType = INTEGER; nullable = true; },  
   {name = "CHR_RESULT"; sqlType = NVARCHAR; nullable = true; length = 5000;}  
 ];  
 table.primaryKey.pkcolumns = ["ID"];  
   

Which simply generate the following DDL:

 CREATE COLUMN TABLE TEMP_RESULTS (  
           "ID" INTEGER CS_INT NOT NULL ,   
           "SQL_TEXT" NVARCHAR(5000),   
           "NUM_RESULT" INTEGER CS_INT,   
           "CHR_RESULT" NVARCHAR(5000),   
           PRIMARY KEY ("ID")  
 ) ;  

create sequence temp_result_id starting with 1 ;

Ok, then I can create the following code:

 PROCEDURE dynamic_connect_by (   
      in p_source_schema_name varchar(100),   
      in p_source_table_name varchar(100),   
      in p_paths_schema_name varchar(100),   
      in p_paths_table_name varchar(100),   
      in p_field_father varchar(100),   
      in p_field_child varchar(100),   
      in p_list_of_fields varchar(1000),  
      in p_where varchar(1000),  
      in p_sub_path varchar(10),   
      in p_start_with varchar(100)       
 )   
 LANGUAGE SQLSCRIPT AS  
   
      v_length integer;  
      v_count integer;  
      v_boolean varchar(5);  
      c_procedure varchar(30) := 'connect_by_test_dynamic';  
      v_sql varchar(5000);  
      v_id integer;   
      v_list_of_fields varchar(1000);  
      v_alias_list_of_fields varchar(1000);  
        
 begin   
        
      call write_debug_log(c_procedure, 'start the procedure', '');  
   
      -- put a clean table on the beginning  
      v_sql := 'delete from ' || p_paths_schema_name || '.' || p_paths_table_name;  
      call write_debug_log(c_procedure, 'truncate table paths', v_sql);       
      execute immediate v_sql;  
        
      v_list_of_fields := replace(p_list_of_fields, ' ', '');
      if v_list_of_fields != '' then 
 v_list_of_fields := v_list_of_fields || ','; 
 v_alias_list_of_fields := 'a.' || replace(p_list_of_fields,   ',',   ',a.') || ',';
      else
 v_list_of_fields := '';
 v_alias_list_of_fields := ''; 
      end if;
        

      -- initialization of the control variables used for the loop  
      v_boolean := 'true';  
      v_length := 1;  
        
      while v_boolean = 'true' do  
             
           -- it is needed to distinguish the first iteration with the n-th generic one  
           if (v_length = 1) then   
                     -- simply insert all the arcs into the final table  
                     v_sql := 'insert into ' || p_paths_schema_name || '.' || p_paths_table_name || ' (' || v_list_of_fields || p_field_father || ',  ' || p_field_child || ',       length,         root,                   path)   
                                          select ' || v_alias_list_of_fields || ' a.' || p_field_father || ', a.' || p_field_child || ', ' || v_length || ' , a.' || p_field_father || ', a.' || p_field_father || ' || '' - '' || a.' || p_field_child || '  
                                          from  ' || p_source_schema_name || '.' || p_source_table_name || ' a';  
                       
                     if p_start_with != '' then   
                           v_sql := v_sql || ' where ' || p_field_father || ' = ''' || p_start_with || '''';  
                                 
                     if P_WHERE != '' then   
                      V_SQL := V_SQL || ' and ' || P_WHERE;  
                     end if;  
             
               else  
             
                     if P_WHERE != '' then   
                      V_SQL := V_SQL || ' where ' || P_WHERE;  
                     end if;  
                 
               end if;   
                 
                     call write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);       
                     execute immediate v_sql;  
   
           else   
                     -- join the arcs with the path known till now  
                     v_sql := 'insert into ' || p_paths_schema_name || '.' || p_paths_table_name || '(' || v_list_of_fields || p_field_father || ',  ' || p_field_child || ',       length,  root,               path)  
                                           select ' || v_alias_list_of_fields || 'a.' || p_field_father || ', a.' || p_field_child || ', ' || v_length || ' , p.root, p.path || '' - '' || a.' || p_field_child || '  
                                          from  ' || p_source_schema_name || '.' || p_source_table_name || ' a join ' || p_paths_schema_name || '.' || p_paths_table_name || ' p on ( a.' || p_field_father || ' = p.' || p_field_child || ' )  
                                          where  p.length = ' || v_length || '-1' ;  
                     call write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);       
                     execute immediate v_sql;  
   
                     if p_sub_path = 'false' then   
                          /**/  
                          -- delete the known path joined one more time (I want to keep only the full paths (no subpath)  
                          v_sql := 'delete from ' || p_paths_schema_name || '.' || p_paths_table_name || ' where (' || p_field_father || ', ' || p_field_child || ', length) in ( ' ||  
                                               -- this first query is to get the arcs joined in front of the SUB path  
                                           ' select a.' || p_field_father || ', a.' || p_field_child || ', ' || v_length - 1 || '  
                                               from  ' || p_source_schema_name || '.' || p_source_table_name || ' a join ' || p_paths_schema_name || '.' || p_paths_table_name || ' p on ( a.' || p_field_father || ' = p.' || p_field_child || ' )  
                                               where  p.length = ' || v_length || '-1   
                                                    union all ' ||  
                                                -- this second query is to get the arcs joined after the SUB path  
                                              ' select p.' || p_field_father || ', p.' || p_field_child || ', ' || v_length - 1 || '  
                                               from  ' || p_source_schema_name || '.' || p_source_table_name || ' a join ' || p_paths_schema_name || '.' || p_paths_table_name || ' p on ( a.' || p_field_father || ' = p.' || p_field_child || ' )  
                                               where  p.length = ' || v_length || '-1 )' ;  
                          call write_debug_log(c_procedure, 'perform insert nr: ' || v_length, v_sql);       
                          execute immediate v_sql;  
                          /**/  
                     end if;  
           end if;  
             
           -- check how many records have I inserted in the last iteration  
           select temp_result_id.nextval into v_id from dummy;   
           v_sql := 'insert into TEMP_RESULTS (id, num_result) select ' || v_id || ', count(*) from ' || p_paths_schema_name || '.' || p_paths_table_name || ' where length = ' || v_length;  
           call write_debug_log(c_procedure, 'perform the count for length: ' || v_length, v_sql);       
           execute immediate v_sql;  
             
           select num_result into v_count from TEMP_RESULTS where id = v_id;  
           call write_debug_log(c_procedure, 'performed count for length: ' || v_length, v_count);       
             
   
   
                  
           -- exit condition: if in the last iteration I did not generated any entry, then I have found already every possible path.   
           if v_count = 0 then   
                -- no other loop, change the while condition  
                v_boolean := 'false';  
           else   
                --there will be another loop  
                --increment the level to be used for the next loop  
                v_length  := v_length + 1;       
           end if;  
   
   
   
           -- this is just an emergency break: after 10 iteration just stop.   
           if (v_length > 10) then   
       v_boolean := 'false';  
           end if;  
             
           call write_debug_log(c_procedure, 'variable loop status: v_length = ' || v_length || '; v_boolean = ' || v_boolean, '');       
       end while;  
   
 end;  

It is almost impossible to read.
If you want to understand the code, just remain on the basic approach I put previously...
For your generic usage, you can just create this function and then invoke it in the following way:

 -- be sure you have a proper table:  
 create table ['Schema name where the output table is'].['Output table name'] (   
                ['Additional fields in the Select statement'],   
                ['Father Field'],   
                ['Chield Field'],   
                "ROOT_PROF" VARCHAR(36) ,   
                "HIERACHY" NUMBER,   
                "LENGTH" NUMBER,   
                "PATH" VARCHAR(3000),   
                "ROOT" VARCHAR(3000)   
           )  
   
   
      call dynamic_connect_by(  
           'Schema name where the input table is',   
           'Input table name',   
           'Schema name where the output table is',   
           'Output table name',   
           'Father Field',   
           'Child Field',   
           'Additional fields in the Select statement',   
           'condition for the where statement',   
           'TRUE' if you want also the sub-paths, 'FALSE' if you want only the complete paths   
           '' if you want to comunicate which is the starting point (make the algorithm faster)  
      );  
   

The equivalent in Oracle is:

 SELECT employee_id, last_name, manager_id  
 FROM employees  
 CONNECT BY PRIOR employee_id = manager_id;  
   
 ...  
   
 SELECT ['Additional fields in the Select statement'], ['Father Field'], ['Chield Field']  
 FROM ['Schema name where the output table is'].['Output table name']  
 where ['condition for the where statement']  
 CONNECT BY PRIOR employee_id = manager_id;  

Retrieve result from dynamic execution in SAP HANA

In oracle I have a great tool which is retrieving result from dynamic executions.

 declare        
      v_sql varchar2(100);  
      v_count number;   
 begin   
      v_sql := 'select count(*) from dual';  
      EXECUTE IMMEDIATE v_sql INTO v_count;  
 end;  
   

In SAP HANA such feature does not exist.
What can we do?

 CREATE COLUMN TABLE TEMP_RESULTS (  
           "ID" INTEGER CS_INT NOT NULL ,   
           "SQL_TEXT" NVARCHAR(5000),   
           "NUM_RESULT" INTEGER CS_INT,   
           "CHR_RESULT" NVARCHAR(5000),   
           PRIMARY KEY ("ID")  
 ) ;  

create sequence temp_result_id starting with 1 ;

Then the usage code

   
 begin   
      declare v_id integer;   
      declare v_sql varchar(100);  
        
      select temp_result_id.nextval into v_id from dummy;   
      v_sql := 'insert into TEMP_RESULTS(id, num_result) select ' || v_id || ', count(*) from dummy';  
      execute immediate v_sql;  
             
      select num_result into v_count from TEMP_RESULTS where id = v_id;  
 end;