package DBIShell::help::Oracle;

use strict;
use Exporter ();

use vars qw($VERSION @EXPORT @EXPORT_OK %EXPORT_TAGS @ISA %HELP);

@ISA         = qw(Exporter);
@EXPORT      = ();
@EXPORT_OK   = ();
%EXPORT_TAGS = ();
$VERSION     = 0.01_04;

use constant H_ASCII => <<'__ascii__';
ascii(CHR)

return the ascii code of character CHR
__ascii__

use constant H_CHR => <<'__chr__';
chr(INT)

return the character whose ascii code is INT
__chr__

use constant H_CONCAT => <<'__concat__';
concat(STR0, STR1)

returns STR0 and STR1 concatenated.
Normally the concatenation operator || is used instead.
__concat__

use constant H_INITCAP => <<'__initcap__';
initcap(STR)

returns STR with the first letter capitalized.
__initcap__

use constant H_INSTR => <<'__instr__';
instr(STR, SUBSTR[, START, [N]],)

find the Nth occurrence, starting from character no. START, of
the string SUBSTR within the string ST, and return its position.
character positions start from 1, not 0.
__instr__

use constant H_LENGTH => <<'__length__';
length(STR)

returns the length of STR.
__length__

use constant H_LOWER => <<'__lower__';
lower(STR)

returns a downcased copy of STR.
__lower__

use constant H_LPAD => <<'__lpad__';
lpad(STR, LEN, [PADSTR])
rpad(STR, LEN, [PADSTR])

returns a copy of STR, padded out to LEN characters with the characters
in PADSTR repeated as many times as neccessary to achieve this.
PADSTR may be omitted, in which case a default value of ' ' is assumed.
lpad pads on the left, rpad on the right.
__lpad__

use constant H_LTRIM => <<'__ltrim__';
ltrim(STR[, TRIM_CHAR_CLASS_STR])
rtrim(STR[, TRIM_CHAR_CLASS_STR])

returns a copy of STR with all consecutive occurrences of the characters
in TRIM_CHAR_CLASS_STR at the left or right of STR removed. eg:

ltrim('abacbsfwgtwtwaba', 'ab') -> 'cbsfwgtwtwaba'
__ltrim__

use constant H_REPLACE => <<'__replace__';
replace(STR, MATCH_STR[, REPLACE_STR])

returns ST, with each occurrence of MATCH_STR within it replaced
by REPLACE_STR [defaults to '' [the empty string]]
__replace__

use constant H_RPAD  => H_LPAD;
use constant H_RTRIM => H_LTRIM;

use constant H_SOUNDEX => <<'__soundex__';
soundex(STR)

returns the soundex code for STR.
soundex, in case you didn't know, is an algorithm which represents [broadly]
the way a word [it's optimised for English] sounds, and can therefore be use
to compare strings on the basis of how they sound.
__soundex__

use constant H_SUBSTR => <<'__substr__';
substr(STR, START, LEN)

returns the substring of STR, from position START
[CAVEAT: starts from 1, not 0], and LEN characters long
__substr__

use constant H_TRANSLATE => <<'__translate__';
translate(STR, MATCH_CHAR_CLASS_STR, REPLACE_CHAR_CLASS_STR)

translates one character class with another: example:

translate('how now brown cow', 'whn', '_13') -> '1o_ 3o_ bro_3 co_'
if MATCH_CHAR_CLASS_STR is longer than REPLACE_CHAR_CLASS_STR, then the
extra characters in MATCH_CHAR_CLASS_STR are removed from the returned
string altogether.
__translate__

use constant H_UPPER => <<'__upper__';
upper(STR)

returns an upcased copy of STR
__upper__

use constant H_ADD_MONTHS => <<'__add_months__';
add_months(DATE, MONTH_SHIFT)
add_months(MONTH_SHIFT, DATE)

returns the date MONTH_SHIFT months after DATE. negative values of
MONTH_SHIFT are allowed.
__add_months__

use constant H_LAST_DAY => <<'__last_day__';
last_day(DATE)

returns the date of the last day of the month in which DATE occurs.
__last_day__

use constant H_MONTHS_BETWEEN => <<'__months_between__';
months_between(DATE0, DATE1)

returns the (possibly fractional and/or negative) number of months
between two dates.

date1 > date2                                        -> return > 0
date1 < date2                                        -> return < 0
date1 == last_day(date1) && date2 == last_day(date2) -> int(return) == return
date1 != last_day(date1) || date2 != last_day(date2) -> int(return) != return
__months_between__

use constant H_NEW_TIME => <<'__new_time__';
new_time(DATE, ZONE_IN, ZONE_OUT [useless outside the US])

returns a DATE in zone ZONE_OUT, assuming it was in ZONE_IN to start with.
Not useful outside the US, as it understands few zones.

Also, it claims to understand GMT, but it actually understands UTC, not
GMT0BST, which is the correct timezone for the UK.
__new_time__

use constant H_NEXT_DAY => <<'__next_day__';
next_day(DATE, DAY_NAME_STR)

returns the first date after DATE which falls on the day of the week named in
DAY_NAME_STR. DAY_NAME_STR may be any string which is valid according to your
oracle sessions NLS_DATE_LANGUAGE setting.
__next_day__

use constant H_SYSDATE => <<'__sysdate__';
sysdate()

sysdate returns the current date [and time] in the timezone of the current
session, which in turn depends on the time zone of the client connecting to
oracle. There is no way to determine what this timezone is within oracle,
and date values contain no timezone data, so if timezone is liable to be an
issue for you, or you need to know the actual time difference between, say,
two date entries in oracle, you must supply some date value from outside
oracle: I recommend unix time_t values as returned by time(), as these are
as absolute as it is conveniently possible to be.
__sysdate__

use constant H_ABS => <<'__abs__';
abs(NUMBER)

returns the absolute numerical value of its argument
__abs__

use constant H_ACOS => <<'__acos__';
acos(NUMBER)

returns the arc cosine [inverse cosine] of NUMBER
__acos__

use constant H_ASIN => <<'__asin__';
asin(NUMBER)

returns the arc sine [inverse sine] of NUMBER
__asin__

use constant H_ATAN => <<'__atan__';
atan(NUMBER)

returns the arc tan [inverse tan] of NUMBER
__atan__

use constant H_ATAN2 => <<'__atan2__';
atan2(NUMBER, NUMBER)

I used to know a useful definition of this one, but I can't remember it now...
something to do with atan being ambiguous [ie there are 2 values [within the
1st repeat range ] of RAD that give tan(RAD) -> NUMBER, and atan(NUMBER) only
maps back to one of them - it's all a blur... ]
__atan2__

use constant H_CEIL => <<'__ceil__';
ceil(NUMBER)

returns the lowest integer greater than or equal to NUMBER. This is more
complicated than it sounds, if you consider that NUMBER can be -ve

see floor
__ceil__

use constant H_COS => <<'__cos__';
cos(ANGLE)

returns the cosine of ANGLE, where ANGLE is in radians [_not_ degrees]
__cos__

use constant H_COSH => <<'__cosh__';
cosh(NUMBER)

returns the hyperbolic cosine of NUMBER
__cosh__

use constant H_EXP => <<'__exp__';
exp(NUMBER)

returns e^NUMBER
Therefore exp(1) == e
see ln
__exp__

use constant H_FLOOR => <<'__floor__';
floor(NUMBER)

returns the largest integer <= NUMBER
see ceil
__floor__

use constant H_LN => <<'__ln__';
ln(NUMBER)

returns the natural logarithm of NUMBER.

exp(ln(NUMBER)) == NUMBER
__ln__

use constant H_LOG => <<'__log__';
log(NUMBER, BASE)

returns the log of NUMBER in base BASE
see ln, exp, power
__log__

use constant H_MOD => <<'__mod__';
mod(NUMBER, DIVISOR)

returns the remainder of NUMBER ÷ DIVISOR
__mod__

use constant H_POWER => <<'__power__';
power(BASE, POWER )

returns BASE to raised to the POWERth power. If BASE is +ve, POWER
may be fractional, otherwise it must be integral.
__power__

use constant H_ROUND => <<'__round__';
round(NUMBER, N)

returns NUMBER rounded to N decimal places. N may be -ve.
[ie the return value is NUMBER rounded to the nearest power(10, -N)]
__round__

use constant H_SIGN => <<'__sign__';
sign(NUMBER)

returns -1, 0 or +1 depending on whether NUMBER is less than , equal to or
greater than 0
__sign__

use constant H_SIN => <<'__sin__';
sin(ANGLE)

returnd the sine of ANGLE, where ANGLE is expressed in radians.
__sin__

use constant H_SINH => <<'__sinh__';
sinh(NUMBER)

returns the hyperbolic sine of NUMBER.
__sinh__

use constant H_SQRT => <<'__sqrt__';
sqrt(NUMBER)

returns the square root of NUMBER.
__sqrt__

use constant H_TAN => <<'__tan__';
tan(ANGLE)

returns the tangent of ANGLE, where ANGLE is in radians
__tan__

use constant H_TANH => <<'__tanh__';
tanh(NUMBER)

returns the hyperbolic tangent of NUMBER.
__tanh__

use constant H_BFILENAME => <<'__bfilename__';
bfilename(DIR_ALIAS, FILE)

something complicated to do with BFILE columns or variables.
__bfilename__

use constant H_EMPTY_BLOB => <<'__empty_blob__';
empty_blob()

returns an empty BLOB. Think of it as a constructor.
__empty_blob__

use constant H_EMPTY_CLOB => <<'__empty_clob__';
empty_clob()

returns an empty CLOB. Think of it as a constructor.
__empty_clob__

use constant H_DUMP => <<'__dump__';
dump(THING, RET_FMT_CODE, START, LENGTH)

returns a string depicting the internal representaion of THING
starting at byte START, proceeding for LENGTH bytes. The bytes
are numbered from 1, not 0.

RET_FMT_CODE:
8  : return in Octal
10 : return in Decimal
16 : return in Hexadecimal
17 : return as characters.

THING may be a number, string or date.
__dump__

use constant H_GREATEST => <<'__greatest__';
greatest(THING0, THING1[, THING2 ...])

returns the largest of the things passed to it. The things must all be of the
same type, and may be numbers, dates or strings.
__greatest__

use constant H_LEAST => <<'__least__';
least(THING0, THING1[, THING2 ...])

returns the smallest of the things passed to it. The things must all be of the
same type, and may be numbers, dates or strings.
__least__

use constant H_NVL => <<'__nvl__';
nvl(THING, NON_NULL_REPLACEMENT_VALUE)

returns THING if THING IS NOT NULL, or NON_NULL_REPLACEMENT_VALUE otherwise.
__nvl__

use constant H_SQLCODE => <<'__sqlcode__';
sqlcode()

returns the latest[current] SQL exception thrown.
[+1 for user defined, +100 for NO_DATA_FOUND,
 -xxxx for a specific error, 0 for no error]

see sqlerrm
__sqlcode__

use constant H_SQLERRM => <<'__sqlerrm__';
sqlerrm([INTEGER])

returns the error message associated with INTEGER.
INTEGER defaults to sqlcode

see sqlcode
__sqlerrm__

use constant H_UID => <<'__uid__';
uid()

returns the Oracle UID for the current user
__uid__

use constant H_USER => <<'__user__';
user()

returns the current Oracle user
__user__

use constant H_USERENV => <<'__userenv__';
userenv(THING_STR)

returns some data about the current session. Valid THING_STR values are:

ENTRYID
LANGUAGE
SESSIONID
TERMINAL
__userenv__

use constant H_VSIZE => <<'__vsize__';
vsize(THING)

returns the internal representaion size, in bytes, of the supplied THING
__vsize__

use constant H_CHARTOROWID => <<'__chartorowid__';
chartorowid(STRING_ROWID)

convert a string to a ROWID.
__chartorowid__

use constant H_CONVERT => <<'__convert__';
convert(STR, TO_CHARSET_STR[, FROM_CHARSET_STR])

converts a string from one charset to another.
__convert__

use constant H_HEXTORAW => <<'__hextoraw__';
hextoraw(HEX_STRING)

converts from hex format to raw value
__hextoraw__

use constant H_RAWTOHEX => <<'__rawtohex__';
rawtohex(RAW)

converts from raw to hex
__rawtohex__

use constant H_ROWIDTOCHAR => <<'__rowidtochar__';
rowidtochar(ROWID)

converts a ROWID to a string of the form BBBBBBBB.RRRR.FFFF
__rowidtochar__

use constant H_TO_CHAR => <<'__to_char__';
to_char(DATE   [, FMT_STR[, NLS_LANG_STR]])
to_char(NUMBER [, FMT_STR[, NLS_LANG_STR]])
to_date(STR    [, FMT_STR[, NLS_LANG_STR]])
to_number(STR, [, FMT_STR[, NLS_LANG_STR]])

converts a DATE   to a string
or       a NUMBER to a string
or       a string to a DATE
or       a string to a NUMBER

to_date(NUMBER, 'J') may also be used, but 'J' is the only format allowed.

FMT str is composed of any valid combination of the following tokens:

NOTE: In general, the capitalisation follows the form of the format,
      wherever this would be appropriate. For example:

      MONTH => JANUARY
      Month => January
      month => january

DATE format tokens:
Token       | What?
------------+---------------------------------------------------------------
 SCC        | Century. BC dates prefixed with a -
 CC         | Century
 SYYYY      | 4 digit year. BC dates prefixed with -
 YYYY       | 4 digit year
 IYYY       | 4 digit ISO standard year
 YYY        | Last 3 digits of year  
 YY         | Last 2 digits of year  
 Y          | Last 1 digits of year  
 IYY        | Last 3 digits of ISO year  
 IY         | Last 2 digits of ISO year  
 I          | Last 1 digits of ISO year  
 Y,YYY      | 4 digit year w. comma
 SYEAR      | Year, spelled out eg 'NINETEEN EIGHTY FOUR' BC dates - prefixed
 YEAR       | Year, spelled out eg 'NINETEEN EIGHTY FOUR'
 RR         | Er. A thing. I dont know.
 BC         | BC/AD identifier
 AD         | BC/AD identifier
 B.C.       | B.C./A.D. identifier
 A.D.       | B.C./A.D. identifier
 Q          | Quarter of Year
 MM         | Month 01-12
 RM         | Roman numeral of month I-XII
 MONTH      | Month spelled out
 MON        | Month abbreviated
 WW         | Week of year, 1-53
 IW         | ISO standard week in year
 W          | Week in month 1-5. Week 1 starts on day 1 and ends on day 7
 DDD        | Day of Year 1-366
 DD         | Day of Month 1-[28|29|30|31]
 D          | Day in Week, 1-7. Day one varies with NLS_TERRITORY
 DAY        | Day name
 DY         | Abbreviated day name
 J          | Julian day. [Days since 01-Jan-4712 BC]
 AM         | AM/PM indicator
 PM         | AM/PM indicator
 A.M.       | A.M./P.M. indicator
 P.M.       | A.M./P.M. indicator
 HH         | Hour of Day, 1-12
 HH12       | Hour of Day, 1-12
 HH24       | Hour of Day, 0-23
 MI         | Minute of Hour, 0-59
 SS         | Second of Minute, 0-59
 SSSS       | Seconds since midnight. 0-86399
 TH         | Convert ordinal to cardinal. Must be at end of format.
 SP         | Spell out number. Must be at end of format.
 SPTH       | Convert to spelled-out-ordinal. Must be at end of format.
 FX         | Force Exact conformance to format mask
 FM         | Toggle padding of output w. blanks
 "literal"  | anything in double quotes is passed as-is into the output

Number format tokens:
Token       | What?
------------+---------------------------------------------------------------
 9          | represents a digit. leading 0's are suppressed [blanked]
 0          | represents a digit.  
 $          | prefix: puts a $ in front of the number.
 B          | prefix: value of zero returned as blanks 
 MI         | suffix: puts a '-' or ' ' after the number, as appropriate
 S          | prefix: put a '+' or '-' in front, as appropriate
 PR         | suffix: put <> around a negative value
 D          | specifies the location of the decimal point
 G          | specifies the location of the group separator [eg ',']
 C          | specifies the location of the ISO currency symbol
 L          | specifies the location of the currency symbol
 ,          | put a comma here 
 .          | put a dot here 
 V          | multiply number to left by 10 to the N, where N is the number 
            | of 9's to the right in the format 
 EEEE       | suffix: declares that you want scientific notation
 RN         | I want uppercase roman numerals
 rn         | I want lowercase roman numerals
__to_char__

use constant H_TO_DATE   => H_TO_CHAR;

use constant H_TO_NUMBER => H_TO_CHAR;

%HELP =
  (
   ascii           => H_ASCII,
   chr             => H_CHR,
   concat          => H_CONCAT,
   initcap         => H_INITCAP,
   instr           => H_INSTR,
   length          => H_LENGTH,
   lower           => H_LOWER,
   lpad            => H_LPAD,
   ltrim           => H_LTRIM,
   replace         => H_REPLACE,
   soundex         => H_SOUNDEX,
   substr          => H_SUBSTR,
   translate       => H_TRANSLATE,
   upper           => H_UPPER,
   add_months      => H_ADD_MONTHS,
   last_day        => H_LAST_DAY,
   months_between  => H_MONTHS_BETWEEN,
   new_time        => H_NEW_TIME,
   next_day        => H_NEXT_DAY,
   sysdate         => H_SYSDATE,
   abs             => H_ABS,
   acos            => H_ACOS,
   asin            => H_ASIN,
   atan            => H_ATAN,
   atan2           => H_ATAN2,
   ceil            => H_CEIL,
   cos             => H_COS,
   cosh            => H_COSH,
   exp             => H_EXP,
   floor           => H_FLOOR,
   ln              => H_LN,
   log             => H_LOG,
   mod             => H_MOD,
   power           => H_POWER,
   round           => H_ROUND,
   sign            => H_SIGN,
   sin             => H_SIN,
   sinh            => H_SINH,
   sqrt            => H_SQRT,
   tan             => H_TAN,
   tanh            => H_TANH,
   bfilename       => H_BFILENAME,
   empty_blob      => H_EMPTY_BLOB,
   empty_clob      => H_EMPTY_CLOB,
   dump            => H_DUMP,
   greatest        => H_GREATEST,
   least           => H_LEAST,
   nvl             => H_NVL,
   sqlcode         => H_SQLCODE,
   sqlerrm         => H_SQLERRM,
   uid             => H_UID,
   user            => H_USER,
   userenv         => H_USERENV,
   vsize           => H_VSIZE,
   chartorowid     => H_CHARTOROWID,
   convert         => H_CONVERT,
   hextoraw        => H_HEXTORAW,
   rawtohex        => H_RAWTOHEX,
   rowidtochar     => H_ROWIDTOCHAR,
   to_char         => H_TO_CHAR,
   rpad            => H_LPAD,
   rtrim           => H_LTRIM,
   to_date         => H_TO_CHAR,
   to_number       => H_TO_CHAR
  );

sub help_map ($) { return \%HELP }



__END__