My Tech Learnings: September 2015

Monday, September 21, 2015

Oracle Making Key Value Pairs

Returning a 'table' from a PL/SQL function

With collections, it is possible to return a table from a pl/sql function.
First, we need to create a new object type that contains the fields that are going to be returned:

create or replace type t_col as object (
i number,
n varchar2(30)
);
/

Then, out of this new type, a nested table type must be created.

create or replace type t_nested_table as table of t_col;
/

Now, we're ready to actually create the function:

create or replace function return_table return t_nested_table as
v_ret   t_nested_table;
begin
v_ret := t_nested_table();

v_ret.extend;
v_ret(v_ret.count) := t_col(1, 'one');

v_ret.extend;
v_ret(v_ret.count) := t_col(2, 'two');

v_ret.extend;
v_ret(v_ret.count) := t_col(3, 'three');

return v_ret;
end return_table;
/

Here's how the function is used:

select * from table(return_table);

     1 one
     2 two
     3 three

Returning a dynamic set

Now, the function is extended so as to return a dynamic set.
The function will return the object_name and the object_id from user_objects whose object_id is in the range that is passed to the function.

create or replace function return_objects(
p_min_id in number,
p_max_id in number
)
return t_nested_table as
v_ret   t_nested_table;
begin
select
cast(
multiset(
    select
      object_id, object_name
    from
      user_objects
    where
      object_id between p_min_id and p_max_id)
      as t_nested_table)
    into
      v_ret
    from
      dual;

return v_ret;

end return_objects;
/

And here's how the function is called.

select * from table(return_objects(37900,38000));

Sunday, September 20, 2015

Oracle Case In-Sensitive like

Since 10gR2, Oracle allows to fine-tune the behaviour of string comparisons by setting the NLS_COMP and NLS_SORT session parameters:

SQL> SET HEADING OFF
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY

NLS_COMP
BINARY


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         0

SQL>
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

Session altered.

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

SQL>
SQL> SELECT *
  2  FROM NLS_SESSION_PARAMETERS
  3  WHERE PARAMETER IN ('NLS_COMP', 'NLS_SORT');

NLS_SORT
BINARY_CI

NLS_COMP
LINGUISTIC


SQL>
SQL> SELECT CASE WHEN 'abc'='ABC' THEN 1 ELSE 0 END AS GOT_MATCH
  2  FROM DUAL;

         1

You can also create case insensitive indexes:

create index
   nlsci1_gen_person
on
   MY_PERSON
   (NLSSORT
      (PERSON_LAST_NAME, 'NLS_SORT=BINARY_CI')
   )
;

Saturday, September 19, 2015

Understanding Eclipse Files

Faced this issue today

Some more info