[Oracle] Search in sources

In Oracle it's possible to search for text in source files with a query on the all_source object. Of course the resulting data will only include objects that are accessible by your current user.

For example, to search in sources with owner myOwner that include the myString string you can run:

SELECT *
FROM all_source
WHERE owner = 'myOwner'
AND LOWER(text) like '%myString%' --case insensitive search

[Avaloq] Get task parameter value in datamart and use it in layout

When creating a new report in Avaloq, you will typically create/edit a datamart (REP DTM source) and a layout (eg: REP SCREEN scource).

If you wish to use a task parameter to enhance the functionality offered by your report, first store the value in a variable inside the DTM (it will be at TOP level):

 [Report 2.0]  
   
 report some_task.some_dtm  
   
  datamart  
   on init  
    --get the parameter passed to the task  
    my_task_param   some_datatype   assign   [task_exec.param("my_task_param").nr_val(1)] --remember to check the DDIC for more appropriate methods other than nr_val. Also pay attention to the sequence number!  
   
   connect some_ddic as dm  
        
      ...  

then access it from your layout with:

top.my_task_param

[PLSQL] Split string by delimiter and store it in VARRAY

Here is some sample Oracle PL/SQL code to split a string using a specific delimiter and storing the result pieces in a VARRAY.

 DECLARE  
   
 TYPE t_varchar2_varray IS TABLE OF VARCHAR2(4000);  
 list_val t_varchar2_varray := t_varchar2_varray();  
 l_explode_at PLS_INTEGER;  
   
 l_string VARCHAR2(4000) := your_string;  
 l_separator VARCHAR2(1) := your_separator;  
   
 BEGIN  
   
 l_explode_at := INSTR (l_string, l_separator); -- if the separator is not in the string, it returns 0   
   
 IF l_explode_at != 0 THEN   
        -- split all the values in the list and store them in our temp variable   
      LOOP   
      EXIT WHEN l_string IS NULL; -- keep going as long as there are values in the list   
        
       l_explode_at := INSTR (l_string, l_separator);   
       list_val.EXTEND;   
         
       -- if we have other values after this one   
       IF l_explode_at !=0 THEN   
         
            -- store it and keep going   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, l_explode_at - 1)); --get the current value   
            l_string := SUBSTR (l_string, l_explode_at + 1); -- and move on the the next one   
              
       -- if there are no more values after me   
       ELSE   
            -- store the last one and quit   
            list_val(list_val.COUNT) := TRIM (SUBSTR (l_string, 1, LENGTH(l_string)));   
            l_string := NULL;   
       END IF;   
         
      END LOOP;   
        
 END IF;  
   
 END;  

Additionally, if you want to remove "empty" values (eg: tabs or spaces) from the string, you can add this code initially:

l_string := regexp_replace(l_string, '[[:space:]]*','');

[SQL] Oracle count elements in nested table

When working with collections in Oracle, you might need to count the number of elements they contain.

In the case of Nested Tables, you can either COUNT(*) after you unnest them:

SELECT COUNT(nt.*)
FROM myTable t, TABLE(t.nested_table) nt

or use the far simpler CARDINALITY function (returns NULL in case of empty list):

SELECT NVL(CARDINALITY(t.nested_table), 0)
FROM myTable t

In case of VARRAYs instead, you must still COUNT the elements but if you're working in PL/SQL though, you can go with:

myVarray.COUNT