Orafce — Oracle-compatible functions and packages

This QHB extension implements functions and operators that emulate a subset of functions and packages from the Oracle RDBMS.

Note that this orafce version works with QHB version 1.5.1 and later.

Installation

Oracle package and compatibility functions for QHB are available as package qhb-1.5.3-orafce.

Add QHB package repository and install the extension package for chosen platform from the download page.

Oracle Functions and Packages

This module contains some useful functions that can help with porting Oracle application to QHB or that can be generally useful.

Built-in Oracle date functions have been tested against Oracle 10 for conformance. Date ranges from 1960 to 2070 work correctly. Dates before 1582-10-05 with the 'J' format and before 1100-03-01 with other formats cannot be verified due to a bug in Oracle.

All functions are fully compatibles with Oracle and respect all known format strings.

List of Format Strings for trunc, round Functions

Y,YY,YYY,YYYY,SYYY,SYEAR	year
I,IY,IYY,IYYY			        iso year
Q,				                quarter
WW				                week, day as first day of year
IW				                week, beginning Monday
W				                  week, day as first day of month
DAY,DY,D			            first day of week, sunday
MONTH,MON,MM,RM			      month
CC,SCC				            century
DDD,DD,J			            day
HH,HH12,HH24			        hour
MI				                minute

Functions round up. That is, a date of July 1st will be rounded to the next year. The 16th of July will be rounded to August.

Date Functions

add_months ( date, integer ) → date

Returns date plus n months.

Example:

add_months(date '2005-05-31',1) → 2005-06-30

last_day ( date ) → date

Returns last day of the month based on a date value.

Example:

last_day(date '2005-05-24') → 2005-05-31

next_day ( date, text ) → date

Returns the first weekday that is greater than a date value.

Example:

next_day(date '2005-05-24', 'monday') → 2005-05-30

next_day ( date, integer ) → date

Same as above. The second argument should be 1..7 and interpreted as Sunday..Saturday.

Example:

next_day(date '2005-05-24', 1) → 2005-05-30

months_between ( date, date ) → numeric

Returns the number of months between date1 and date2. If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

Example:

months_between(date '1995-02-02', date '1995-01-01') → 1.0322580645161

trunc ( date, text ) → date

Truncates date according to the specified format.

Example:

trunc(date '2005-07-12', 'iw') → 2005-07-11

round ( date, text ) → date

Rounds dates according to the specified format.

Example:

round(date '2005-07-12', 'yyyy') → 2006-01-01

to_date ( text ) → timestamp

Typecasts input text to timestamp. The GUC orafce.nls_date_format is used to specify input text format for this function. If the value is left blank or set as DEFAULT then input text format according to QHB’s datestyle GUC setting.

Examples:

orafce.nls_date_format value to DEFAULT
       to_date('2014-05-19 17:23:53+5:30') -> 2014-05-19 17:23:53

orafce.nls_date_format='YYYY-MMDD HH24:MI:SS'
       to_date('2014-0519 17:23:53+5:30')  -> 2014-05-19 17:23:53

to_date (text, text) → timestamp

Typecasts input text with the specified format to timestamp. The GUC orafce.orafce_emit_error_on_date_bug is used to specify whether an error is reported when the date value hits the Oracle bug on dates. This bug appears with dates before 1582-10-05 when the 'J' format is used ('J2299159') and before 1100-03-01 with other formats. An error is reported by default, to disable this behavior set orafce.orafce_emit_error_on_date_bug to off.

SELECT oracle.to_date('112012', 'J');
ERROR:  Dates before 1582-10-05 ('J2299159') cannot be verified due to a bug in Oracle.

SELECT oracle.to_date('1003-03-15', 'yyyy-mm-dd');
ERROR:  Dates before 1100-03-01 cannot be verified due to a bug in Oracle.

SET orafce.oracle_compatibility_date_limit TO off;

SELECT oracle.to_date('112012', 'J');
	to_date
------------------------
 4407-07-30 00:00:00 BC
(1 row)

SELECT oracle.to_date('1003/03/15', 'yyyy/mm/dd');
       to_date
---------------------
 1003-03-15 00:00:00
(1 row)

oracle.date Data Type

This module contains implementation of oracle compatible date data type oracle.date and functions which are using date data type like oracle.add_months, oracle.last_day(), oracle.next_day(), oracle.months_between() etc.

Example:

SET search_path TO oracle,"$user", public, pg_catalog;
CREATE TABLE oracle_date(col1 date);
INSERT INTO oracle_date values('2014-06-24 12:12:11'::date);
SELECT * FROM oracle_date;
        col1
---------------------
 2014-06-24 12:12:11
(1 row)

oracle.date Functions

oracle.add_months ( timestamp with time zone, integer ) → timestamp with time zone

Returns date and time plus n months

Example:

oracle.add_months(oracle.date'2005-05-31 10:12:12',1) → 2005-06-30 10:12:12

oracle.last_day ( timestamp with time zone ) → timestamp with time zone

Returns last day of the month based on a date value.

Example:

oracle.last_day(oracle.date '2005-05-24 11:12:12') → 2005-05-31 11:12:12

oracle.next_day ( timestamp with time zone, text ) → timestamp with time zone

Returns the first weekday that is greater than a date value.

Example:

oracle.next_day(oracle.date '2005-05-24 10:12:12', 'monday') → 2005-05-30 10:12:12

oracle.next_day ( timestamp with time zone, integer ) → timestamp with time zone

Same as above. The second argument should be 1..7 and interpreted as Sunday..Saturday.

Example:

oracle.next_day(oracle.date '2005-05-24 11:21:12', 1) → 2005-05-29 11:21:12

oracle.months_between ( timestamp with time zone, timestamp with time zone ) → numeric

Returns the number of months between timestamp1 and timestamp2. If a fractional month is calculated, the months_between function calculates the fraction based on a 31-day month.

Example:

oracle.months_between(oracle.date '1995-02-02 10:00:00', oracle.date '1995-01-01 10:21:11') → 1.03225806451613

oracle.to_date ( text, text ) → timestamp without time zone

Returns timestamp without time zone.

Example:

oracle.to_date('02/16/09 04:12:12', 'MM/DD/YY HH24:MI:SS') → 2009-02-16 04:12:12

oracle.to_date ( text ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('16.02.09 04:12:12') → 2009-02-16 04:12:12

oracle.sysdate() → timestamp with time zone

Returns statement timestamp at server timezone (orafce.timezone).

Example:

oracle.sysdate() → 2015-12-09 17:47:56

oracle.dbtimezone() → timezone

Returns server time zone - emulated via orafce.timezone.

Example:

oracle.dbtimezone() → GMT

oracle.sessiontimezone() → timezone

Returns session timezone — current QHB timezone.

Example:

oracle.sessiontimezone() → Europe/Prague

oracle.sys_extract_utc ( timestamp with timezone ) → timestamp with time zone

Returns timestamp in UTC timezone.

Example:

oracle.sys_extract_utc(current_timestamp)

oracle.sys_extract_utc ( oracle.date ) → timestamp with time zone

Returns timestamp in UTC timezone, when time zone is not specified, session (current QHB) timezone is used.

Example:

oracle.sys_extract_utc(oracle.date '2005-05-24 11:21:12', 1) -> 2005-05-24 09:21:12

oracle.to_char ( timestamp ) → nls_date_format

Returns timestamp in nls_date_format.

Examples:

orafce.nls_date_format='YY-MonDD HH24:MI:SS'
       oracle.to_char(to_date('14-Jan08 11:44:49+05:30')) -> 14-Jan08 11:44:49

orafce.nls_date_format='YY-MonDD HH24:MI:SS'
       oracle.to_char(oracle.to_date('21052014 12:13:44+05:30','DDMMYYYY HH24:MI:SS')) -> 14-May21 12:13:44

oracle.date Operators

oracle. + ( oracle.date, smallint ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::smallint → 2014-07-11 10:08:55

oracle. + ( oracle.date, integer ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::integer → 2014-07-11 10:08:55

oracle. + ( oracle.date, bigint ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::bigint → 2014-07-11 10:08:55

oracle. + ( oracle.date, numeric ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') + 9::numeric → 2014-07-11 10:08:55

oracle. - ( oracle.date, smallint ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::smallint → 2014-06-23 10:08:55

oracle. - ( oracle.date, integer ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::integer → 2014-06-23 10:08:55

oracle. - ( oracle.date, bigint ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::bigint → 2014-06-23 10:08:55

oracle. - ( oracle.date, numeric ) → oracle.date

Returns oracle.date.

Example:

oracle.to_date('2014-07-02 10:08:55','YYYY-MM-DD HH:MI:SS') - 9::numeric -> 2014-06-23 10:08:55

oracle. - ( oracle.date, oracle.date ) → double precision

Returns double precision.

Example:

oracle.to_date('2014-07-17 11:10:15', 'yyyy-mm-dd hh24:mi:ss') - oracle.to_date('2014-02-01 10:00:00', 'yyyy-mm-dd hh24:mi:ss') → 166.048785

You need to set search_path to oracle, "$user", public, pg_catalog because functions like oracle.add_months, oracle.last_day, oracle.next_day, and oracle.months_between are installed side-by-side with pg_catalog.add_months, pg_catalog.last_day, pg_catalog.next_day, and pg_catalog.months_between.

Table Dual

QHB does not need Oracle’s table 'dual', but since it is intensively used by Oracle users, it has been added in orafce. This table is in schema oracle. Usually you want allow unqualified access — so you should to add this schema to search_path configuration (like search_path = 'oracle, pg_catalog, "$user", public' in qhb.conf).

Package dbms_output

QHB sends information to the client via RAISE NOTICE. Oracle uses dbms_output.put_line(). This works differently from RAISE NOTICE. Oracle has a session queue, put_line() adds a line to the queue and the function get_line() reads from queue. If flag serveroutput is set, then client over all SQL statements reads queue. You can use:

SELECT dbms_output.enable();
SELECT dbms_output.put_line('first_line');
SELECT dbms_output.put_line('next_line');
SELECT * FROM dbms_output.get_lines(0);

SELECT dbms_output.enable();
SELECT dbms_output.serveroutput('t');
SELECT dbms_output.put_line('first_line');

This package contains the following functions: enable(), disable(), serveroutput(), put(), put_line(), new_line(), get_line(), get_lines(). The package queue is implemented in the session’s local memory.

Package utl_file

This package allows PL/pgSQL programs to read from and write to any files that are accessible from server. Every session can open a maximum of ten files and max line size is 32K. This package contains following functions:

utl_file.fclose ( file utl_file.file_type)

Closes file.

utl_file.fclose_all()

Closes all files.

utl_file.fcopy (_ src_location_ text, src_filename text, dest_location text, dest_filename text [, start_line text ] [, end_line text ] )

Copies text file.

utl_file.fflush ( file utl_file.file_type )

Flushes all data from buffers.

utl_file.fgetattr ( location text, filename text )

Gets file attributes.

utl_file.fopen ( location text, filename text, file_mode text [, maxlinesize int ] [, encoding name ] ) → utl_file.file_type

Opens file.

utl_file.fremove ( location text, filename text )

Removes file.

utl_file.frename ( location text, filename text, dest_dir text, dest_file text [, overwrite bool ] )

Renames file.

utl_file.get_line ( file utl_file.file_type) → text

Reads one line from file.

utl_file.get_nextline ( file utl_file.file_type) → text

Reads one line from file or returns NULL.

utl_file.is_open ( file utl_file.file_type) → bool

Returns true, if file is opened.

utl_file.new_line ( file utl_file.file_type [, rows int ] )

Puts some new line chars to file.

utl_file.put ( file utl_file.file_type, buffer text )

Puts buffer to file.

utl_file.put_line ( file utl_file.file_type, buffer text )

Puts line to file.

utl_file.putf ( file utl_file.file_type, buffer text, format [, arg1 text ] [, arg2 text ] [..] [, arg5 text ] )

Puts formatted text into file.

utl_file.tmpdir()

Gets path of temp directory.

Because QHB doesn’t support call by reference, some functions are slightly different: fclose and get_line.

DO $$
DECLARE f utl_file.file_type;
BEGIN
  f := utl_file.fopen('/tmp', 'sample.txt', 'r');
  <<readl>>
  loop
    BEGIN
      raise notice '%', utl_file.get_line(f);
    exception
      WHEN no_data_found THEN
        exit readl;
    END;
  end loop;
  f := utl_file.fclose(f);
END;
$$;

or second (with QHB specific function get_nextline)

DO $$
DECLARE
  f utl_file.file_type;
  line text;
BEGIN
  f := utl_file.fopen('/tmp', 'sample.txt', 'r');
  loop
    line := utl_file.get_nextline(f);
    exit when line is NULL;
    raise notice '%', line;
  end loop
exception
  WHEN others THEN
  perform utl_file.fclose_all();
END;
$$;

Before using the package you have to set the utl_file.utl_file_dir table. It contains all allowed directories without ending symbol (/ or \). On WinNT platform, the paths have to end with symbol \ every time.

Directory entries can be named (second column in table utl_file.utl_file_dir). The location parameter can be either the directory name or the dictionary path. The location is first interpreted and checked as a directory name. If not found (in 2nd column), then the location is interpreted and checked as a path.

Functions from utl_file package (schema on QHB) requires a access to table utl_file.utl_file_dir. This fact can be used to control what users can use these functions or not. Default setting is READ for public. INSERT, UPDATE can do only privileged user (superuser). So unprivileged user can use functions from this package, but cannot to change list of safe directories (content of utl_file.utl_file_dir table). The content of this table is visible for public (or should be visible for users who uses functions from this package).

Package dbms_sql

This is implementation of Oracle’s API of package DBMS_SQL.

It doesn’t ensure full compatibility, but should to decrease a work necessary for successful migration.

Attention: QHB architecture is different than Oracle’s architecture. PL/pgSQL is executed in same context like SQL engine. Then is not any reason to use Oracle’s patterns like bulk collect and iteration over collection in QHB to get good performance. This code is designed to reduce work related to porting some applications from Oracle to QHB, and it can work well. But there will not be any performance advantage against build-in PL/pgSQL statements. The emulation of Oracle’s API has memory and CPU overhead, that can be significant on bigger data.

Functionality

This extension implements subset of Oracle’s dbms_sql interface. The goal of this extension is not a compatibility with Oracle, it is designed to reduce some work related migration Oracle’s applications to QHB. Some basic bulk DML functionality is supported:

DO $$
DECLARE
  c int;
  a int[];
  b varchar[];
  ca numeric[];
BEGIN
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'insert into foo values(:a, :b, :c)');
  a := ARRAY[1, 2, 3, 4, 5];
  b := ARRAY['Ahoj', 'Nazdar', 'Bazar'];
  ca := ARRAY[3.14, 2.22, 3.8, 4];

  call dbms_sql.bind_array(c, 'a', a, 2, 3);
  call dbms_sql.bind_array(c, 'b', b, 3, 4);
  call dbms_sql.bind_array(c, 'c', ca);
  raise notice 'inserted rows %d', dbms_sql.execute(c);
END;
$$;

DO $$
DECLARE
  c int;
  a int[];
  b varchar[];
  ca numeric[];
BEGIN
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select i, ''Ahoj'' || i, i + 0.003 from generate_series(1, 35) g(i)');
  call dbms_sql.define_array(c, 1, a, 10, 1);
  call dbms_sql.define_array(c, 2, b, 10, 1);
  call dbms_sql.define_array(c, 3, ca, 10, 1);

  perform dbms_sql.execute(c);
  while dbms_sql.fetch_rows(c) > 0
  loop
    call dbms_sql.column_value(c, 1, a);
    call dbms_sql.column_value(c, 2, b);
    call dbms_sql.column_value(c, 3, ca);
    raise notice 'a = %', a;
    raise notice 'b = %', b;
    raise notice 'c = %', ca;
  end loop;
  call dbms_sql.close_cursor(c);
END;
$$;

There is function dbms_sql.describe_columns_f, that is like procedure dbms_sql.describe_columns. Attention, the type IDs are related to QHB type system. The values are not converted to Oracle’s numbers.

DO $$
DECLARE
  c int;
  r record;
  d dbms_sql.desc_rec;
BEGIN
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select * from pg_class');
  r := dbms_sql.describe_columns(c);
  raise notice '%', r.col_cnt;

  foreach d in array r.desc_t
  loop
    raise notice '% %', d.col_name, d.col_type::regtype;
  end loop;

  call dbms_sql.close_cursor(c);
END;
$$;

DO $$
DECLARE
  c int;
  n int;
  d dbms_sql.desc_rec;
  da dbms_sql.desc_rec[];
BEGIN
  c := dbms_sql.open_cursor();
  call dbms_sql.parse(c, 'select * from pg_class');
  call dbms_sql.describe_columns(c, n, da);
  raise notice '%', n;

  foreach d in array da
  loop
    raise notice '% %', d.col_name, d.col_type::regtype;
  end loop;

  call dbms_sql.close_cursor(c);
END;
$$;

Package dbms_pipe

This package is an emulation of dbms_pipe Oracle package. It provides inter-session communication. You can send and read any message with or without waiting; list active pipes; set a pipe as private or public; and, use explicit or implicit pipes.

The maximum number of pipes is 50.

Shared memory is used to send messages.

An example follows:

-- Session A
SELECT dbms_pipe.create_pipe('my_pipe',10,true); -- explicit pipe creating
SELECT dbms_pipe.pack_message('neco je jinak');
SELECT dbms_pipe.pack_message('anything is else');
SELECT dbms_pipe.send_message('my_pipe',20,0); -- change limit and send without waiting
SELECT * FROM dbms_pipe.db_pipes; -- list of current pipes

-- Session B
SELECT dbms_pipe.receive_message('my_pipe',1); -- wait max 1 sec for message
SELECT dbms_pipe.next_item_type(); -- -> 11, text
SELECT dbms_pipe.unpack_message_text();
SELECT dbms_pipe.next_item_type(); -- -> 11, text
SELECT dbms_pipe.unpack_message_text();
SELECT dbms_pipe.next_item_type(); -- -> 0, no more items
SELECT dbms_pipe.remove_pipe('my_pipe');

There are some differences compared to Oracle, however:

limit for pipes isn’t in bytes but in elements in pipe
you can send message without waiting
you can send empty messages
next_item_type knows about timestamp (type 13)
QHB doesn’t know about the raw type, use bytea instead

Package dbms_alert

Another means of inter-process communication.

-- Session A
SELECT dbms_alert.register('boo');
SELECT * FROM dbms_alert.waitany(10);

-- Session B
SELECT dbms_alert.register('boo');
SELECT * FROM dbms_alert.waitany(10);

-- Session C
SELECT dbms_alert.signal('boo','Nice day');

Package PLVdate

This module contains some functions for working with business days from package PLVdate. Detailed documentation can be found in PLVision library. This package is multicultural, but default configurations are only for european countries (see source code).

You should define your own non-business days (max 50 days) and own holidays (max 30 days). A holiday is any non-business day, which is the same every year. For example, Christmas day in Western countries.

Functions

plvdate.add_bizdays ( day date, days int ) → date

Gets the date created by adding n business days to a date.

plvdate.nearest_bizday ( day date ) → date

Gets the nearest business date to a given date, user defined.

plvdate.next_bizday ( day date ) → date

Gets the next business date from a given date, user defined.

plvdate.bizdays_between ( day1 date, day2 date ) → int

Gets the number of business days between two dates.

plvdate.prev_bizday ( day date ) → date

Gets the previous business date from a given date.

plvdate_isbizday ( date ) → bool

Returns true if a date is a business day.

plvdate.set_nonbizday ( dow varchar )

Sets day of week as non business day.

plvdate.unset_nonbizday ( dow varchar )

Unsets day of week as non business day.

plvdate.set_nonbizday ( day date )

Sets day as non business day.

plvdate.unset_nonbizday ( day date )

Unsets day as non business day.

plvdate.set_nonbizday ( day date, repeat bool )

Sets day as non business day, if repeat is true, then day is nonbizday every year.

plvdate.unset_nonbizday ( day date, repeat bool )

Unsets day as non business day, if repeat is true, then day is nonbizday every year

plvdate.use_easter()

Easter Sunday and Easter Monday will be holidays.

plvdate.unuse_easter()

Easter Sunday and Easter Monday will not be holidays.

plvdate.use_easter ( useit bool )

If true then Easter Sunday and Easter Monday will be holidays.

plvdate.using_easter() → bool

Returns true if Easter Sunday and Easter Monday are holidays.

plvdate.use_great_friday()

Easter Great Friday will be holiday.

plvdate.unuse_easter()

Easter Great Friday will not be holiday.

plvdate.use_easter ( useit bool )

If true then Easter Great Friday will be holiday.

plvdate.using_easter() → bool

Returns true if Easter Great Friday is holiday.

plvdate.include_start()

Include starting date in bizdays_between calculation.

plvdate.noinclude_start()

Exclude starting date from bizdays_between calculation.

plvdate.include_start ( include bool )

If true threading starting date will be included in bizdays_between calculation.

plvdate.include_start() → bool

Returns true if starting date is included in bizdays_between calculation.

plvdate.default_holidays ( varchar )

Loads default configurations. You can use the following configurations: Czech, German, Austria, Poland, Slovakia, Russia, GB and USA at this moment. Configuration contains only common holidays for all regions. You can add your own regional holiday with plvdate.set_nonbizday(nonbizday, true).

Example:

qhb=# select plvdate.default_holidays('czech');
 default_holidays
 -----------------

(1 row)
qhb=# select to_char(current_date, 'day'),
           plvdate.next_bizday(current_date),
	   to_char(plvdate.next_bizday(current_date),'day');
  to_char  | next_bizday |  to_char
 ----------+-------------+-----------
 saturday  | 2006-03-13  | monday
(1 row)

Change for non-European environment:

SELECT plvdate.unset_nonbizday('saturday');
SELECT plvdate.unset_nonbizday('sunday');
SELECT plvdate.set_nonbizday('friday');
SELECT plvdate.set_nonbizday('2006-05-19', true);
SELECT plvdate.unuse_easter();

Package PLVstr and PLVchr

This package contains some useful string and character functions. Each function supports positive and negative offsets — i.e., offset from the end of the string. For example:

plvstr.left('abcdef',2) -> ab
plvstr.left('abcdef',-2) -> abcd
plvstr.substr('abcdef',1,1) -> a
plvstr.substr('abcdef',-1,1) -> f
plvstr.substr('abcde',-2,1) -> d

List of functions:

plvstr.normalize ( str text )

Normalizes string — replaces white chars by space, replaces spaces by space.

plvstr.is_prefix ( str text, prefix text, cs bool ) → bool

Returns true, if prefix is prefix of string.

plvstr.is_prefix ( str text, prefix text ) → bool

Returns true, if prefix is prefix of string.

plvstr.is_prefix ( str int, prefix int ) → bool

Returns true, if prefix is prefix of string.

plvstr.is_prefix ( str bigint, prefix bigint ) → bool

Returns true, if prefix is prefix of string.

plvstr.substr ( str text, start int, len int ) → text

Returns the substring of string starting at the start'th character and stopping after len characters.

plvstr.substr ( str text, start int ) → text

Returns substring of string starting at the start'th character and extending to end of the string.

plvstr.instr ( str text, patt text, start int, n int ) → text

Search nth pattern in string starting at the start'th character.

plvstr.instr ( str text, patt text, start int ) → text

Search pattern in string starting at the start'th character.

plvstr.instr ( str text, patt text ) → text

Search pattern in string.

plvstr.lpart ( str text, div text, start int, n int, all_if_notfound bool ) → text

Returns the left part of a string. When all_if_notfound is true, then if specified item is missing, the entire string is returned.

plvstr.lpart ( str text, div text, start int, n int ) → text

Returns the left part of a string.

plvstr.lpart ( str text, div text, start int ) → text

Returns the left part of a string.

plvstr.lpart ( str text, div text ) → text

Returns the left part of a string.

plvstr.rpart ( str text, div text, start int, n int, all_if_notfound bool ) → text

Returns the right part of a string. When all_if_notfound is true, then if specified item is missing, the entire string is returned.

plvstr.rpart ( str text, div text, start int, n int ) → text

Returns the right part of a string.

plvstr.rpart ( str text, div text, start int ) → text

Returns the right part of a string.

plvstr.rpart ( str text, div text ) → text

Returns the right part of a string.

plvstr.lstrip ( str text, substr text, num int ) → text

Removes characters from the beginning.

plvstr.lstrip ( str text, substr text ) → text

Removes characters from the beginning.

plvstr.rstrip ( str text, substr text, num int ) → text

Removes characters from the end.

plvstr.rstrip ( str text, substr text ) → text

Removes characters from the end.

plvstr.rvrs ( str text, start int, end int ) → text

Reverses string or part of string.

plvstr.rvrs ( str text, start int ) → text

Reverses string or part of string.

plvstr.rvrs ( str text ) → text

Reverses string or part of string.

plvstr.left ( str text, n int ) → text

Returns first n characters. You can use negative n.

plvstr.right ( str text, n int ) → text

Returns last n characters. You can use negative n.

plvstr.swap ( str text, replace text, start int, length int ) → text

Replaces the substring of string str that starts at the start'th character and extends for length characters with replace.

plvstr.swap ( str text, replace text ) → text

Replaces the substring of string str with replace.

plvstr.betwn ( str text, start int, end int, inclusive bool ) → text

Finds the substring between start and end locations. If inclusive is true, boundary characters are included in the search.

plvstr.betwn ( str text, start int, end int, startnth int, endnth int, inclusive bool, gotoend bool ) → text

Finds the substring between start and end locations. If inclusive is true, boundary characters are included in the search. If gotoend is true, search continues to the end of the string.

plvstr.betwn ( str text, start int, end int ) → text

Finds the substring between start and end locations.

plvstr.betwn ( str text, start int, end int, startnth int, endnth int ) → text

Finds the substring between start and end locations.

plvchr.nth ( str text, n int ) → text

Returns the nth character in a string.

plvchr.first ( str text ) → text

Returns the first character in a string.

plvchr.last ( str text ) → text

Returns the last character in a string.

plvchr.is_blank ( c int )

It is blank.

plvchr.is_blank ( c text )

It is blank.

plvchr.is_digit ( c int )

It is digit.

plvchr.is_digit ( c text )

It is digit.

plvchr.is_quote ( c int )

It is quote.

plvchr.is_quote ( c text )

It is quote.

plvchr.is_other ( c int )

It is other.

plvchr.is_other ( c text )

It is other.

plvchr.is_letter ( c int )

It is letter.

plvchr.is_letter ( c text )

It is letter.

plvchr.char_name ( c text )

Returns the name of the character to ASCII code as a varchar.

plvchr.quoted1 ( str text )

Quoted text between '.

plvchr.quoted2 ( str int )

Quoted text between ".

plvchr.stripped ( str text, char text )

Strips a string of all instances of the specified characters.

Package PLVsubst

The PLVsubst package performs string substitutions based on a substitution keyword.

plvsubst.string ( template text, vals text[] )

Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list.

plvsubst.string ( template text, vals text[], subst text )

Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list.

plvsubst.string ( template text, vals text[], delim text )

Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list.

plvsubst.string ( template text, vals text[], delim text subst text )

Scans a string for all instances of the substitution keyword and replace it with the next value in the substitution values list.

plvsubst.setsubst ( str text )

Set substitution keyword to default %s.

plvsubst.subst()

Retrieve substitution keyword.

Examples:

SELECT plvsubst.string('My name is %s %s.', ARRAY['Pavel','Stěhule']);
          _`строка`_
 --------------------------
 My name is Pavel Stěhule.
(1 row)

SELECT plvsubst.string('My name is %s %s.', 'Pavel,Stěhule');
          _`строка`_
 --------------------------
 My name is Pavel Stěhule.
(1 row)

SELECT plvsubst.string('My name is $$ $$.', 'Pavel|Stěhule','|','$$');
          _`строка`_
 --------------------------
 My name is Pavel Stěhule.
(1 row)

Package DBMS_utility

dms_utility.format_call_stack()

Returns a formatted string with content of call stack.

qhb=# select foo2();
               foo2
 ---------------------------------
 -----  Call Stack  -----
   line             object
 number  statement  name
      1  return     function foo
      1  return     function foo1
      1  return     function foo2
(1 row)

Package PLVlex

This package isn’t compatible with original PLVlex.

plvlex.tokens ( str text, skip_spaces bool, qualified_names bool )

Returns table of lexical elements in string.

qhb=# select * from
	plvlex.tokens('select * from a.b.c join d ON x=y', true, true);

 pos | token  | code |  class  | separator | mod
 ----+--------+------+---------+-----------+------
   0 | select |  527 | KEYWORD |           |
   7 | *      |   42 | OTHERS  |           | self
   9 | from   |  377 | KEYWORD |           |
  25 | a.b.c  |      | IDENT   |           |
  20 | join   |  418 | KEYWORD |           |
  25 | d      |      | IDENT   |           |
  27 | on     |  473 | KEYWORD |           |
  30 | x      |      | IDENT   |           |
  31 | =      |   61 | OTHERS  |           | self
  32 | y      |      | IDENT   |           |
(10 rows)

WARNING!
Keyword’s codes can be changed between QHB versions!

Package DBMS_ASSERT

This package protects user input against SQL injection.

dbms_assert.enquote_literal ( varchar ) → varchar

Adds leading and trailing quotes, verify that all single quotes are paired with adjacent single quotes.

dbms_assert.enquote_name ( varchar [, boolean ] ) → varchar

Encloses name in double quotes. Optional second parameter ensure lowering of name. Attention — on Oracle is second parameter capitalize.

dbms_assert.noop ( varchar ) → varchar

Returns value without any checking.

dbms_assert.qualified_sql_name ( varchar ) → varchar

This function verifies that the input string is qualified SQL name.

dbms_assert.schema_name ( varchar ) → varchar

This function verifies that input string is an existing schema name.

dbms_assert.simple_sql_name ( varchar ) → varchar

This function verifies that the input string is simple SQL name.

dbms_assert.object_name ( varchar ) → varchar

This function verifies that input string is qualified SQL identifier of an existing SQL object.

Package PLUnit

This unit contains some assert functions.

plunit.assert_true ( bool [, varchar ] )

Asserts that the condition is true.

plunit.assert_false ( bool [, varchar ] )

Asserts that the condition is false.

plunit.assert_null ( anyelement [, varchar ] )

Asserts that the actual is null.

plunit.assert_not_null ( anyelement [, varchar ] )

Asserts that the actual isn’t null.

plunit.assert_equals ( anyelement, anyelement [, double precision ] [, varchar ] )

Asserts that expected and actual are equal.

plunit.assert_not_equals (anyelement, anyelement [, double precision ] [, varchar ] )

Asserts that expected and actual are equal.

plunit.fail ( [ varchar ] )

Fail can be used to cause a test procedure to fail immediately using the supplied message.

Package DBMS_random

dbms_random.initialize(int)

Initializes package with a seed value.

dbms_random.normal()

Returns random numbers in a standard normal distribution.

dbms_random.random()

Returns random number from -2^31 .. 2^31.

dbms_random.seed(int)

Returns seed value.

dbms_random.seed(text)

Resets seed value.

dbms_random.string(opt text(1), _len_ int)

Creates random string.

dbms_random.terminate()

Terminates package (does nothing in QHB).

dbms_random.value()

Returns a random number from [0.0 - 1.0).

dbms_random.value ( low double precision, high double precision )

Returns a random number from [low - high).

Others Functions

This module contains implementation of functions: concat, nvl, nvl2, lnnvl, decode, greatest, least, bitand, nanvl, sinh, cosh, tanh, oracle.substr, and oracle.mod.

oracle.substr ( str text, start int, len int )