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__