OOS_UTIL_STRING

• Constants

• TO_CHAR Function

• TO_CHAR-1 Function
• TO_CHAR-2 Function
• TO_CHAR-3 Function
• TO_CHAR-4 Function
• TO_CHAR-5 Function
• TRUNCATE Function
• SPRINTF Function
• STRING_TO_TABLE Function
• STRING_TO_TABLE-1 Function
• LISTUNAGG Function
• LISTUNAGG-1 Function
• REVERSE Function
• ORDINAL Function

Constants

gc_default_delimiter constant varchar2(1) := ',';

gc_cr constant varchar2(1) := chr(13);

gc_lf constant varchar2(1) := chr(10);

gc_crlf constant varchar2(2) := gc_cr

TO_CHAR Function

Converts parameter to varchar2

Notes:

• Code copied from Logger: https://github.com/OraOpenSource/Logger

Syntax

function to_char(
  p_val in number)
  return varchar2
  deterministic

Parameters

Example

select oos_util_string.to_char(123)
from dual;

OOS_UTIL_STRING.TO_CHAR(123)---
123

TO_CHAR-1 Function

See first to_char

Syntax

function to_char(
  p_val in date)
  return varchar2
  deterministic

Parameters

Example

select oos_util_string.to_char(sysdate)
from dual;

OOS_UTIL_STRING.TO_CHAR(SYSDATE)---
26-APR-2016 13:57:51

TO_CHAR-2 Function

See first to_char

Syntax

function to_char(
  p_val in timestamp)
  return varchar2
  deterministic

Parameters

Example

select oos_util_string.to_char(systimestamp)
from dual;

OOS_UTIL_STRING.TO_CHAR(SYSTIMESTAMP)---
26-APR-2016 13:58:24:851908000 -06:00

TO_CHAR-3 Function

See first to_char

Syntax

function to_char(
  p_val in timestamp with time zone)
  return varchar2
  deterministic

Parameters

Example

TODO

TO_CHAR-4 Function

See first to_char

Syntax

function to_char(
  p_val in timestamp with local time zone)
  return varchar2

Parameters

Example

TODO

TO_CHAR-5 Function

See first to_char

Syntax

function to_char(
  p_val in boolean)
  return varchar2
  deterministic

Parameters

Example

begin
  dbms_output.put_line(oos_util_string.to_char(true));
  dbms_output.put_line(oos_util_string.to_char(false));
end;
/

TRUE
FALSE

TRUNCATE Function

Truncates a string to ensure that it is not longer than p_length
If length of p_str is greater than p_length then an ellipsis (...) will be appended to string

Supports following modes:

• By length (default): Will perform a hard parse at p_length
• By word: Will truncate at logical word break

Syntax

function truncate(
  p_str in varchar2,
  p_length in pls_integer,
  p_by_word in varchar2 default 'N',
  p_ellipsis in varchar2 default '...')
  return varchar2

Parameters

Example

select
  oos_util_string.truncate(
    p_str => comments,
    p_length => 20,
    p_by_word => 'N'
  ) by_word_n,
  oos_util_string.truncate(
    p_str => comments,
    p_length => 20,
    p_by_word => 'Y'
  ) by_word_y
from apex_dictionary
where 1=1
  and rownum <= 5
;

BY_WORD_N            BY_WORD_Y
-------------------- --------------------
List of APEX buil... List of APEX...
Identifies the th... Identifies the...
Identifies the na... Identifies the...
Identifies the th... Identifies the...
Identifies a work... Identifies a...

SPRINTF Function

Does string replacement similar to C's sprintf

Notes:

• Uses the following replacement algorithm (in following order)
  • Replaces %s<n> with p_s<n>
  • Occurrences of %s (no number) are replaced with p_s1..p_s10 in order that they appear in text
  • %% is escaped to %

Syntax

function sprintf(
  p_str in varchar2,
  p_s1 in varchar2 default null,
  p_s2 in varchar2 default null,
  p_s3 in varchar2 default null,
  p_s4 in varchar2 default null,
  p_s5 in varchar2 default null,
  p_s6 in varchar2 default null,
  p_s7 in varchar2 default null,
  p_s8 in varchar2 default null,
  p_s9 in varchar2 default null,
  p_s10 in varchar2 default null)
  return varchar2

Parameters

Example

select oos_util_string.sprintf('hello %s', 'martin') demo
from dual;

DEMO
------------------------------
hello martin

select oos_util_string.sprintf('%s2, %s1', 'Firstname', 'Lastname') demo
from dual;

DEMO
------------------------------
Lastname, Firstname

STRING_TO_TABLE Function

Converts delimited string to array

Notes:

• Similar to apex_util.string_to_table but handles clobs

Syntax

function string_to_table(
  p_str in clob,
  p_delim in varchar2 default gc_default_delimiter)
  return oos_util.tab_vc2_arr

Parameters

Example

declare
  l_str clob := 'abc,def,ghi';
  l_arr oos_util.tab_vc2_arr;
begin
  l_arr := oos_util_string.string_to_table(p_str => l_str);

  for i in 1..l_arr.count loop
    dbms_output.put_line('i: ' || i || ' ' || l_arr(i));
  end loop;
end;
/

i: 1 abc
i: 2 def
i: 3 ghi

STRING_TO_TABLE-1 Function

See string_to_table (p_str clob) for notes

Syntax

function string_to_table(
  p_str in varchar2,
  p_delim in varchar2 default gc_default_delimiter)
  return oos_util.tab_vc2_arr

Parameters

Example

-- See previous example

LISTUNAGG Function

Converts delimited string to queriable table

Notes:

• Text between delimiters must be <= 4000 characters

Syntax

function listunagg(
  p_str in varchar2,
  p_delim in varchar2 default gc_default_delimiter)
  return oos_util.tab_vc2 pipelined

Parameters

Example

 select rownum, column_value
 from table(oos_util_string.listunagg('abc,def'));

     ROWNUM COLUMN_VAL
---------- ----------
         1 abc
         2 def

LISTUNAGG-1 Function

Converts delimited string to queriable table

Syntax

function listunagg(
  p_str in clob,
  p_delim in varchar2 default gc_default_delimiter)
  return oos_util.tab_vc2 pipelined

Parameters

Example

See previous example

REVERSE Function

Returns the input string in its reverse order

Syntax

function reverse(
  p_str in varchar2)
  return varchar2

Parameters

Example

begin
  dbms_output.put_line(oos_util_string.reverse('OraOpenSource'));
end;
/

ecruoSnepOarO

ORDINAL Function

Returns the input number with the ordinal attached, in english.
e.g. 1st, 2nd, 3rd, 4th, etc

Notes:

• Logic taken from: http://stackoverflow.com/a/13627586/3476713

Syntax

function ordinal(
  p_num in number)
  return varchar2

Parameters

Example

select oos_util_string.ordinal(level)
from dual
connect by level <= 10;