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')
   )
;