Sybernet / Supplied Packages Reference Release 3.00 May 13, 2006

HTTP.JAVASCRIPT

The JAVASCRIPT package provides subprograms for creating commonly used JavaScript functions. Some functions are common to both Oracle and Sybase (like rtrim), some functions are patterned after similar functions in C (like isdigit), and some functions are real subroutines that do something useful (like openWindow).

These are JavaScript functions so they must be bound by <script> and </script> tags if you want to use them in your JavaScript code. The one exception is the subprogram script (which you can use to create your script tags).

Many of these functions rely on other functions in this package; for example, if you want to verify a date field with the validateDate() function, you must also invoke trim() and to_char(). All of the examples in this package also include the subprograms that must be invoked if you want to use that function. Try to avoid including all subprograms because you think this will make your coding easier, and although JavaScript is forgiving if you define the same function more than once, you should take care that you don't do this. Take a minute or two, and do a view source to make sure you are not invoking validateEmployee() in a while loop (for example).

Summary of Subprograms

SCRIPT

ALERT

ASCII

ASCIITOEBCDIC

AVG

CALENDAR

CHR

CONSTANTS

CONVERT

DATENAME

DATEPART

DECODE

EBCDICTOASCII

EXECUTEIMMEDIATE

GETDATE

GOTO

INITCAP

ISALNUM

ISALPHA

ISDIGIT

ISLOWER

ISSPACE

ISUPPER

ISXDIGIT

LTRIM

LPAD

MAX

MIN

ONES

OPENWINDOW

PATINDEX

REPLACE

ROUND

RPAD

RTRIM

SIGN

SORTTABLE

STD

SUBSTR

SYSDATE

TO_CHAR

TO_DATE

TRANSPOSE

TRUNC

TRIM

VALIDATECOUNTRY

VALIDATEDATE

VALIDATEEMPLOYEE

VALIDATELANGUAGE

VALIDATENUMBER

VALIDATESTATE

VALIDATETIME

Subprogram SCRIPT (VALUE)

The subprogram SCRIPT creates your beginning or ending script tag. The Boolean parameter value determines if you are opening this tag (when true) or ending this tag (when false).

Syntax

Procedure SCRIPT(value in Boolean);

Example

The following example illustrates how to call SCRIPT:

http.javascript.script(true);
http.writeln('window.document.writeln("Hello World");');
http.javascript.script(false);

Subprogram ALERT (TEXT)

The subprogram ALERT calls the JavaScript function alert() from your PL/SQL block. Like the latter it accepts a single string parameter

• text

that is automatically escaped or encoded so that your text is actually displayed without causing a JavaScript error.

Syntax

Procedure ALERT(text in varchar2);

Example

The following example illustrates how to call ALERT:

begin
    execute immediate('update dual set dummy = ''X''');
    http.javascript.script(true);
    http.javascript.alert('Success!');
    http.javascript.script(false);
exception when others then
    http.javascript.script(true);
    http.javascript.alert('Oops!'||chr(10)||' '||chr(10)||SQLERRM);
    http.javascript.script(false);
end;

Subprogram ASCII

The subprogram ASCII creates the function ascii() which accepts the following parameters:

• c

where c is the ascii character whose decimal value is returned.

Syntax

Procedure ASCII;

Example

The following example illustrates how to call ASCII:

http.javascript.script(true);
http.javascript.ascii;
http.writeln('window.document.writeln(ascii("a"));');
http.javascript.script(false);

Subprogram ASCIITOEBCDIC

The subprogram ASCIITOEBCDIC creates a JavaScript array called ASCIITOEBCDIC[] which allows you to translate Ascii to EBCDIC.

Syntax

Procedure ASCIITOEBCDIC;

Example

The following example illustrates how to call ASCIITOEBCDIC:

http.javascript.script(true);
http.javascript.asciitoebcdic;
http.writeln('var a=ASCIITOEBCDIC[97];');
http.javascript.script(false);

Subprogram AVG

The subprogram AVG creates the function avg() which accepts one or more numeric values.

Syntax

Procedure AVG;

Example

The following example illustrates how to call AVG:

http.javascript.script(true);
http.javascript.avg;
http.writeln('window.document.writeln(avg(1,2,3,4,5,6,7,8,9));');
http.javascript.script(false);

Subprogram CALENDAR (NLS_DATE_FORMAT)

The subprogram CALENDAR creates the function calendar() which accepts the following parameters:

• cell
• nls_date_format

where cell is the cell on your form that is be updated and nls_date_format is the format that is used to format this cell. If nls_date_format is null, your date cell is populated with the NLS_DATE_FORMAT that is passed to this procedure.

Syntax

Procedure CALENDAR (NLS_DATE_FORMAT IN VARCHAR2:='Mon DD YYYY HH12:MIAM');

Example

The following example illustrates how to call CALENDAR:

http.javascript.script(true);
http.javascript.calendar;
http.javascript.script(false);
http.writeln('<form name=form>');
http.writeln('<input type=text name=datetime>');
http.javascript.calendarlink('window.document.form.datetime');
http.writeln('</form>');

The subprogram calendarlink() can be used to invoke this function if you are satisfied with the default image. To use this you must pass a fully qualified path to this cell's window, form and cell as illustrated above. If you wish to use something else, then you must create your own href tag and image. You also need to call this manually if the nls_date_format can be different between two or more cells. The call to calendarlink() looks something like this:

http.writeln('<a href="" onClick="return calendar(window.document.form.datetime);"><img src="/images/calendar.gif" border=0></a>');

Example

In conjunction with the validateDate() function provided in this package (which you should also use), this subprogram makes it possible to enter a valid date manually by entering that date in this cell or selecting that date from an HTML calendar. The calendar() function obviously updates the year, month and date, but attempts to leave the hours, minutes and seconds untouched. Here is how the two are used together:

declare
    nls_date_format varchar(30) := 'Mon DD YYYY HH12:MIAM'
    ;
begin
    http.javascript.script(true);
    http.javascript.ltrim;
    http.javascript.rtrim;
    http.javascript.trim;
    http.javascript.to_char;
    http.javascript.validatedate(nls_date_format);
    http.javascript.calendar(nls_date_format);
    http.javascript.script(false);
    http.writeln('<form name=form>');
    http.writeln('<input type="text" name="datetime" onChange="validateDate(this);">');
    http.javascript.calendarlink('window.document.form.datetime');
    http.writeln('</form>');
end;

Subprogram CHR

The subprogram CHR creates the function chr() which accepts the following parameters:

• x

where x the numeric or ascii value of the character you wish returned.

Syntax

Procedure CHR;

Example

The following example illustrates how to call CHR:

http.javascript.script(true);
http.javascript.chr;
http.writeln('window.document.writeln(chr(10));');
http.javascript.script(false);

Subprogram CONSTANTS

The subprogram CONSTANTS defines the following constants:

var
    ASCII_NUL              = 0X00       // Null.
,   ASCII_SOH              = 0X01       // Start of heading.
,   ASCII_STX              = 0X02       // Start of text.
,   ASCII_ETX              = 0X03       // End of text.
,   ASCII_EOT              = 0X04       // End of transmission.
,   ASCII_ENQ              = 0X05       // Enquire.
,   ASCII_ACK              = 0X06       // Acknowledge.
,   ASCII_BEL              = 0X07       // Bell (Master Reset on IBM).
,   ASCII_BS               = 0X08       // Back space.
,   ASCII_HT               = 0X09       // Horizontal tab.
,   ASCII_LF               = 0X0A       // Line feed.
,   ASCII_VT               = 0X0B       // Vertical tab.
,   ASCII_FF               = 0X0C       // Form feed.
,   ASCII_CR               = 0X0D       // Carriage return.
,   ASCII_SO               = 0X0E       // Shift out.
,   ASCII_SI               = 0X0F       // Shift in.
,   ASCII_DLE              = 0X10       // Data link escape.
,   ASCII_DC1              = 0X11       // Device control 1.
,   ASCII_DC2              = 0X12       // Device control 2.
,   ASCII_DC3              = 0X13       // Device control 3.
,   ASCII_DC4              = 0X14       // Device control 4.
,   ASCII_NAK              = 0X15       // Negative acknowledge.
,   ASCII_SYN              = 0X16       // Synchronize.
,   ASCII_ETB              = 0X17       // End of transmission block.
,   ASCII_CAN              = 0X18       // Cancel.
,   ASCII_EM               = 0X19       // End of medium.
,   ASCII_SUB              = 0X1A       // Substitute.
,   ASCII_ESC              = 0X1B       // Escape.
,   ASCII_FS               = 0X1C       // File separator.
,   ASCII_GS               = 0X1D       // Group separator.
,   ASCII_RS               = 0X1E       // Record separator.
,   ASCII_US               = 0X1F       // Unit separator.
,   ASCII_SPACE            = 0X20       // Space.
,   ASCII_DOUBLE_QUOTE     = 0X22       // double-quote.
,   ASCII_SINGLE_QUOTE     = 0X27       // single-quote.
,   ASCII_DEL              = 0X7F       // Roubout (delete).
    ;
var
    EBCDIC_NUL             = 0X00       // Null.
,   EBCDIC_SOH             = 0X01       // Start of heading.
,   EBCDIC_STX             = 0X02       // Start of text.
,   EBCDIC_ETX             = 0X03       // End of text.
,   EBCDIC_HT              = 0X05       // Horizontal tab.
,   EBCDIC_DEL             = 0X07       // Rubout (delete).
,   EBCDIC_VT              = 0X0B       // Vertical tab.
,   EBCDIC_FF              = 0X0C       // Form feed.
,   EBCDIC_CR              = 0X0D       // Carriage return.
,   EBCDIC_SO              = 0X0E       // Shift out..
,   EBCDIC_SI              = 0X0F       // Shift in.
,   EBCDIC_DLE             = 0X10       // Data link escape.
,   EBCDIC_DC1             = 0X11       // Device control 1.
,   EBCDIC_DC2             = 0X12       // Device control 2.
,   EBCDIC_DC3             = 0X13       // Device control 3.
,   EBCDIC_NL              = 0X15       // New Line?
,   EBCDIC_BS              = 0X16       // Back space.
,   EBCDIC_CAN             = 0X18       // Cancel.
,   EBCDIC_EM              = 0X19       // End of medium.
,   EBCDIC_FS              = 0X1C       // File separator.
,   EBCDIC_GS              = 0X1D       // Group separator.
,   EBCDIC_RS              = 0X1E       // Record separator
,   EBCDIC_US              = 0X1F       // Unit separator.
,   EBCDIC_LF              = 0X25       // Line feed.
,   EBCDIC_ETB             = 0X26       // End of transmission block.
,   EBCDIC_ESC             = 0X27       // Escape.
,   EBCDIC_ENQ             = 0X2D       // Enquire.
,   EBCDIC_ACK             = 0X2E       // Acknowledge.
,   EBCDIC_BEL             = 0X2F       // Bell (Master Reset on IBM).
,   EBCDIC_SYN             = 0X32       // Synchronize.
,   EBCDIC_EOT             = 0X37       // End of transmission.
,   EBCDIC_DC4             = 0X3C       // Device control 4.
,   EBCDIC_NAK             = 0X3D       // Negative acknowledge.
,   EBCDIC_SUB             = 0X3F       // Substitute.
,   EBCDIC_SPACE           = 0X40       // Space.
,   ECBDIC_SINGLE_QUOTE    = 0X7D       // single-quote.
,   EBCDIC_DOUBLE_QUOTE    = 0X7F       // double-quote.
    ;

Syntax

Procedure CONSTANTS;

Example

The following example illustrates how to call CONSTANTS:

http.javascript.script(true);
http.javascript.constants;
http.javascript.chr;
http.writeln('window.document.writeln(chr(ASCII_LF));');
http.javascript.script(false);

Subprogram CONVERT

The subprogram CONVERT creates the function convert() which accepts the following parameters:

• datatype
• date
• style

Syntax

Procedure CONVERT;

Example

The following example illustrates how to call CONVERT:

http.javascript.script(true);
http.javascript.getdate;
http.javascript.rpad;
http.javascript.to_char;
http.javascript.convert;
http.writeln('window.document.writeln("<pre>");');
http.writeln('for (var i=0;i<13;i++)');
http.writeln('window.document.writeln(convert(varchar(30),getdate(),i));');
http.writeln('window.document.writeln("</pre>");');
http.javascript.script(false);

Subprogram DATENAME

The subprogram DATENAME creates the function datename() which accepts the following parameters:

• datepart
• date

Syntax

Procedure DATENAME;

Example

The following example illustrates how to call DATENAME:

http.javascript.script(true);
http.javascript.getdate;
http.javascript.datename;
http.writeln('window.document.writeln("<pre>");');
http.writeln('window.document.writeln(datename(year,getdate()));');
http.writeln('window.document.writeln(datename(month,getdate()));');
http.writeln('window.document.writeln(datename(day,getdate()));');
http.writeln('window.document.writeln(datename(weekday,getdate()));');
http.writeln('window.document.writeln(datename(hour,getdate()));');
http.writeln('window.document.writeln(datename(minute,getdate()));');
http.writeln('window.document.writeln(datename(second,getdate()));');
http.writeln('window.document.writeln("</pre>");');
http.javascript.script(false);

Subprogram DATEPART

The subprogram DATEPART creates the function datepart() which accepts the following parameters:

• datepart
• date

Syntax

Procedure DATEPART;

Example

The following example illustrates how to call DATEPART:

http.javascript.script(true);
http.javascript.getdate;
http.javascript.datepart;
http.writeln('window.document.writeln("<pre>");');
http.writeln('window.document.writeln(datepart(year,getdate()));');
http.writeln('window.document.writeln(datepart(month,getdate()));');
http.writeln('window.document.writeln(datepart(day,getdate()));');
http.writeln('window.document.writeln(datepart(weekday,getdate()));');
http.writeln('window.document.writeln(datepart(hour,getdate()));');
http.writeln('window.document.writeln(datepart(minute,getdate()));');
http.writeln('window.document.writeln(datepart(second,getdate()));');
http.writeln('window.document.writeln("</pre>");');
http.javascript.script(false);

Subprogram DECODE

The subprogram DECODE creates the function decode() which accepts one or more numeric or string values.

Syntax

Procedure DECODE;

Example

The following example illustrates how to call DECODE:

http.javascript.script(true);
http.javascript.decode;
http.writeln('window.document.writeln(decode(2,1,"Hello",2,"World","undefined"));');
http.javascript.script(false);

Subprogram EBCDICTOASCII

The subprogram EBCDICTOASCII creates a JavaScript array called ECBDCITOASCII[] which allows you to translate EBCDIC to Ascii.

Syntax

Procedure EBCDICTOASCII;

Example

The following example illustrates how to call EBCDICTOASCII:

http.javascript.script(true);
http.javascript.ebcdictoascii;
http.writeln('var a=EBCDICTOASCII[97];');
http.javascript.script(false);

Subprogram EXECUTEIMMEDIATE

The subprogram EXECUTEIMMEDIATE creates the function executeImmediate() which accepts the following parameter:

• SQLTEXT

where SQLTEXT is your PL/SQL text that is to be executed. This function assumes you are calling sp_html_frame so that it can use (and rebuild) your NORTH frame to execute your PL/SQL block.

Syntax

Procedure EXECUTEIMMEDIATE;

Example

The following example illustrates how to call EXECUTEIMMEDIATE:

create or replace procedure executeImmediate(button in varchar2:=null)
is
begin
    if (button is null) then
        sp_html_frame
        (
            procname    => http.myself.name
        ,   title       => http.myself.title
        ,   version     => http.myself.version
        ,   center      => '*'
        )    ;
        return;
    end if;
    if (button = 'CENTER') then
        http.javascript.script(true);
        http.javascript.executeImmediate;
        http.writeln('executeImmediate("begin http.javascript.script(true);http.javascript.alert(http.myself.nls_date_format);http.javascript.script(false);end;");');
        http.javascript.script(false);
        return;
    end if;
end executeImmediate;

Example

The above example is a little bizarre because you are executing PL/SQL code from JavaScript which then invokes a JavaScript alert. The fact that we are executing an Oracle stored procedure directly from JavaScript, however, should illustrate the power of this facility. The following example illustrates how you can use this subprogram to populate fields on your form screen:

create or replace procedure validateNationalID(button in varchar2:=null,national_id in varchar2:=null)
is
begin
    if (button is null) then
        sp_html_frame
        (
            procname    => http.myself.name
        ,   title       => http.myself.title
        ,   version     => http.myself.version
        ,   center      => '*'
        )    ;
        return;
    end if;
    if (button = 'CENTER') then
        http.javascript.script(true);
        http.javascript.executeImmediate;
        http.writeln('function validateNationalID(CELL)');
        http.writeln('{');
        http.writeln('    var sqltext = "begin ' || http.myself.name || '(button=>''validateNationalID'',national_id=>''" + CELL.value + "''); END;";');
        http.writeln('    executeImmediate(sqltext);');
        http.writeln('    return;');
        http.writeln('}');
        http.javascript.script(false);
        http.writeln('<form name=form>');
        http.writeln('<input type=text name=emplid>');
        http.writeln('<input type=text name=name>');
        http.writeln('<input type=text name=national_id onChange="validateNationalID(this);">');
        http.writeln('</form>');
        return;
    end if;
    if (button = 'validateNationalID') then
    declare
        emplid ps_employees.emplid%type;
        name ps_employees.name%type;
    begin
        select emplid
        ,      name
        into   emplid
        ,      name
        from   ps_employees
        where  national_id = validateNationalID.national_id;
        http.javascript.script(true);
        http.writeln('parent.CENTER.document.form.emplid.value="' || emplid || '";');
        http.writeln('parent.CENTER.document.form.name.value="' || name || '";');
        http.javascript.script(false);
        return;
    exception when no_data_found then
        http.javascript.script(true);
        http.javascript.alert(national_id || ' (not found)');
        http.javascript.script(false);
        return;
    end;
    end if;
end validateNationalID;

Subprogram GETDATE

The subprogram GETDATE creates the function getdate() which returns the current date and time as a JavaScript variable.

Syntax

Procedure GETDATE;

Example

The following example illustrates how to call GETDATE:

http.javascript.script(true);
http.javascript.getdate;
http.writeln('window.document.writeln(getdate());');
http.javascript.script(false);

Subprogram GOTO

The subprogram GOTO creates the function GOTO() which accepts the following parameters:

• CELL

where CELL is the form element that is to receive focus.

Syntax

Procedure GOTO;

Example

The following example illustrates how to call GOTO:

http.javascript.script(true);
http.javascript."goto";
http.writeln('function validate(FORM)                                   ');
http.writeln('{                                                         ');
http.writeln('    if (FORM.NAME.length==0)                              ');
http.writeln('    {                                                     ');
http.writeln('        alert("Oops!\n \nName is required on this form.");');
http.writeln('        GOTO(FORM.NAME);                                  ');
http.writeln('        return false;                                     ');
http.writeln('    }                                                     ');
http.writeln('    return true;                                          ');
http.writeln('}                                                         ');
http.javascript.script(false);
http.writeln('<form onSubmit="return validate(this);">');
http.writeln('<input type="text" name=NAME>');
http.writeln('</form>');

Subprogram INITCAP

The subprogram INITCAP creates the function initcap() which accepts the following parameters:

• string

where string is the value to be operated on. The first character in each word is set to uppercase and the rest are set to lowercase

Syntax

Procedure INITCAP;

Example

The following example illustrates how to call INITCAP:

http.javascript.script(true);
http.javascript.islower;
http.javascript.isupper;
http.javascript.isspace;
http.javascript.initcap;
http.writeln('window.document.writeln(initcap("hello world"));');
http.javascript.script(false);

Subprogram ISALNUM

The subprogram ISALNUM creates the function isalnum() which accepts the following parameters:

• c

where c is the character to test for alpha numeric.

Syntax

Procedure ISALNUM;

Example

The following example illustrates how to call ISALNUM:

http.javascript.script(true);
http.javascript.isdigit;
http.javascript.islower;
http.javascript.isupper;
http.javascript.isalpha;
http.javascript.isalnum;
http.writeln('if (isalnum("a"))');
http.writeln('    window.document.writeln("true");');
http.writeln('else');
http.writeln('    window.document.writeln("false");');
http.javascript.script(false);

Subprogram ISALPHA

The subprogram ISALPHA creates the function isalpha() which accepts the following parameters:

• c

where c is the character to test for alpha.

Syntax

Procedure ISALPHA;

Example

The following example illustrates how to call ISALPHA:

http.javascript.script(true);
http.javascript.islower;
http.javascript.isupper;
http.javascript.isalpha;
http.writeln('if (isalpha("a"))');
http.writeln('    window.document.writeln("true");');
http.writeln('else');
http.writeln('    window.document.writeln("false");');
http.javascript.script(false);

Subprogram ISDIGIT

The subprogram ISDIGIT creates the function isdigit() which accepts the following parameters:

• c

where c is the character to test for digit.

Syntax

Procedure ISDIGIT;

Example

The following example illustrates how to call ISDIGIT:

http.javascript.script(true);
http.javascript.isdigit;
http.writeln('if (isdigit("a"))');
http.writeln('    window.document.writeln("true");');
http.writeln('else');
http.writeln('    window.document.writeln("false");');
http.javascript.script(false);

Subprogram ISLOWER

The subprogram ISLOWER creates the function islower() which accepts the following parameters:

• c

where c is the character to test for lower-case.

Syntax

Procedure ISLOWER;

Example

The following example illustrates how to call ISLOWER:

http.javascript.script(true);
http.javascript.islower;
http.writeln('if (islower("a"))');
http.writeln('    window.document.writeln("true");');
http.writeln('else');
http.writeln('    window.document.writeln("false");');
http.javascript.script(false);

Subprogram ISSPACE

The subprogram ISSPACE creates the function isspace() which accepts the following parameters:

• c

where c is the character to test for white space.

Syntax

Procedure ISSPACE;

Example

The following example illustrates how to call ISSPACE:

http.javascript.script(true);
http.javascript.isspace;
http.writeln('if (isspace("a"))');
http.writeln('    window.document.writeln("true");');
http.writeln('else');
http.writeln('    window.document.writeln("false");');
http.javascript.script(false);

Subprogram ISUPPER

The subprogram ISUPPER creates the function isupper() which accepts the following parameters:

• c

where c is the character to test for upper-case.

Syntax

Procedure ISUPPER;

Example

The following example illustrates how to call ISUPPER:

http.javascript.script(true);
http.javascript.isupper;
http.writeln('if (isupper("a"))');
http.writeln('    window.document.writeln("true");');
http.writeln('else');
http.writeln('    window.document.writeln("false");');
http.javascript.script(false);

Subprogram ISXDIGIT

The subprogram ISXDIGIT creates the function isxdigit() which accepts the following parameters:

• c

where c is the character to test for hexadecimal.

Syntax

Procedure ISXDIGIT;

Example

The following example illustrates how to call ISXDIGIT:

http.javascript.script(true);
http.javascript.isdigit;
http.javascript.isxdigit;
http.writeln('if (isxdigit("a"))');
http.writeln('    window.document.writeln("true");');
http.writeln('else');
http.writeln('    window.document.writeln("false");');
http.javascript.script(false);

Subprogram LTRIM

The subprogram LTRIM creates the function ltrim() which accepts the following parameters:

• string

where string is the string to be trimmed.

Syntax

Procedure LTRIM;

Example

The following example illustrates how to call LTRIM:

http.javascript.script(true);
http.javascript.ltrim;
http.writeln('window.document.writeln(ltrim("    Hello World"));');
http.javascript.script(false);

Subprogram LPAD

The subprogram LPAD creates the function lpad() which accepts the following parameters:

• string
• n
• string_pad

where string is left padded to length n with string_pad. If string_pad is omitted, a space will be used as default. RPAD is similar, but pads right instead of left.

Syntax

Procedure LPAD;

Example

The following example illustrates how to call LPAD:

http.javascript.script(true);
http.javascript.lpad;
http.writeln('window.document.writeln("<pre>");');
http.writeln('for (var i=1;i<15;i++)');
http.writeln(    'window.document.writeln(lpad("string",i),"<");');
http.writeln('window.document.writeln("</pre>");');
http.javascript.script(false);

Subprogram MAX

The subprogram MAX creates the function max() which accepts one or more numeric values.

Syntax

Procedure MAX;

Example

The following example illustrates how to call MAX:

http.javascript.script(true);
http.javascript.max;
http.writeln('window.document.writeln(max(1,2,3,4,5,6,7,8,9));');
http.javascript.script(false);

Subprogram MIN

The subprogram MIN creates the function min() which accepts one or more numeric values.

Syntax

Procedure MIN;

Example

The following example illustrates how to call MIN:

http.javascript.script(true);
http.javascript.min;
http.writeln('window.document.writeln(min(1,2,3,4,5,6,7,8,9));');
http.javascript.script(false);

Subprogram ONES

The subprogram ONES creates the function ones() which accepts the following parameters:

• x

where x is the numeric value whose number of bits are returned.

Syntax

Procedure ONES;

Example

The following example illustrates how to call ONES:

http.javascript.script(true);
http.javascript.ones;
http.writeln('window.document.writeln(ones(10));');
http.javascript.script(false);

Subprogram PATINDEX

The subprogram PATINDEX creates the function patindex() which accepts the following parameters:

• pattern
• string
• selectionStart

where pattern is the pattern to search for, string is the string to search, and selectionStart is the starting position in string to start searching.

Syntax

Procedure PATINDEX;

Example

The following example illustrates how to call PATINDEX:

http.javascript.script(true);
http.javascript.patindex;
http.writeln('window.document.writeln(patindex("%o%","Hello World",0));');
http.javascript.script(false);

Subprogram OPENWINDOW

The subprogram OPENWINOW creates the function openWindow() which accepts the following parameters:

• WindowURL
• WindowName
• width
• height
• scrollbars
• resizable
• toolbar
• location
• directories
• status
• menubar

WindowURL is the URL this window is suppose to invoke. WindowName is the name of this window. width is the width of this window in pixels. height is the height of this window in pixels. scrollbars, resizable, toolbar, location, directories, status, and menubar are Boolean values (1 or 0) which determine whether or not this option is enabled.

Syntax

Procedure OPENWINDOW;

Example

The following example illustrates how to call OPENWINDOW:

http.javascript.script(true);
http.javascript.openwindow;
http.writeln('openWindow("http://Sybernet.sri.com","Sybernet",300,300,1,0,0,0,0,0,0);');
http.javascript.script(false);

Subprogram REPLACE

The function REPLACE creates the function replace() which accepts the following parameters:

• string
• search_string
• replacement_string

string is the string to be searched. search_string is the string to be found. replacement_string is the string that replaces all occurrences of search_string.

Syntax

Procedure REPLACE;

Example

The following example illustrates how to call REPLACE:

http.javascript.script(true);
http.javascript.replace;
http.writeln('window.document.writeln(replace("Hello World","o","i"));');
http.javascript.script(false);

Subprogram ROUND

The subprogram ROUND creates the function round() which accepts the following parameters:

• value
• scale

Syntax

Procedure ROUND;

Example

The following example illustrates how to call ROUND:

http.javascript.script(true);
http.javascript.round;
http.writeln('window.document.writeln(round(123.456,1));');
http.javascript.script(false);

Subprogram RPAD

The subprogram RPAD creates the function rpad() which accepts the following parameters:

• string
• n
• string_pad

where string is right padded to length n with string_pad. If string_pad is omitted, a space will be used as default. LPAD is similar, but pads left instead of right.

Syntax

Procedure RPAD;

Example

The following example illustrates how to call RPAD:

http.javascript.script(true);
http.javascript.rpad;
http.writeln('window.document.writeln("<pre>");');
http.writeln('for (var i=1;i<15;i++)');
http.writeln('    window.document.writeln(rpad("string",i),"<");');
http.writeln('window.document.writeln("</pre>");');
http.javascript.script(false);

Subprogram RTRIM

The subprogram RTRIM creates the function rtrim() which accepts the following parameters:

• string

where string is the string to be trimmed.

Syntax

Procedure RTRIM;

Example

The following example illustrates how to call RTRIM:

http.javascript.script(true);
http.javascript.rtrim;
http.writeln('window.document.writeln(rtrim("Hello World    "));');
http.javascript.script(false);

Subprogram SIGN

The subprogram SIGN creates the function sign() which accepts the following parameters:

• x

where x is a numeric value whose sign is returned. 1 is returned for positive values; -1 is returned for negative values; 0 is returned for zero.

Syntax

Procedure SIGN;

Example

The following example illustrates how to call SIGN:

http.javascript.script(true);
http.javascript.sign;
http.writeln('window.document.writeln(sign(123));');
http.javascript.script(false);

Subprogram SORTTABLE

The subprogram SORTTABLE creates the function sortTable() which accepts the following parameters:

• id
• col

where id is the ID of your table's <tbody> tag and col is the column number to sort.

This function allows you to sort an HTML table from the client. Inspired by code written by Mark Hill at brainjar, this subprogram has been optimized to sort more than a few hundred rows. Clicking a column heading sorts your table in ascending order. Clicking a column heading again sorts your table in descending order. A table of 1600 rows and 6 columns can be sorted in about 10 seconds.

Syntax

Procedure SORTTABLE;

Example

The following example illustrates how to call SORTTABLE:

http.javascript.script(true);
http.javascript.sortTable;
http.javascript.script(false);
http.writeln('<table border=1>');
http.writeln('<thead>');
http.writeln('<tr bgcolor=lightgrey>');
http.writeln('<th><a href="" onClick="return sortTable(''id'',0);">Name</a></th>');
http.writeln('<th><a href="" onClick="return sortTable(''id'',1);">Color</a></th>');
http.writeln('</tr>');
http.writeln('</thead>');
http.writeln('<tbody id="id">');
for r in (select * from http.http_colors) loop
    http.writeln('<tr>');
    http.writeln('<td>');
    http.writeln(r.name);
    http.writeln('</td>');
    http.writeln('<td>');
    http.writeln(r.color);
    http.writeln('</td>');
    http.writeln('</tr>');
end loop;
http.writeln('</tbody>');
http.writeln('</table>');

Subprogram STD

The subprogram STD creates the function std() which accepts one or more numeric values.

Syntax

Procedure STD;

Example

The following example illustrates how to call STD:

http.javascript.script(true);
http.javascript.std;
http.writeln('window.document.writeln(std(1,2,3,4,5,6,7,8,9));');
http.javascript.script(false);

Subprogram SUBSTR

The subprogram SUBSTR creates the function substr() which accepts the following parameters:

• string
• start_position
• length

where string is the source string, start_position is the position for extraction, and length is the number of characters to extract. If lenth is omitted, the function substr() returns the entire string extracted from start_position. If start_position is 0, start_position is assumed to be 1. This is different from JavaScript's substring() function because that function is zero-relative and length is the zero-relative column that is to be extracted last.

Syntax

Procedure SUBSTR;

Example

The following example illustrates how to call SUBSTR:

http.javascript.script(true);
http.javascript."substr";
http.writeln('window.document.writeln(substr("Hello World",2,3));');
http.javascript.script(false);

Subprogram SYSDATE

The subprogram SYSDATE creates the function sysdate() which returns the current date and time as a JavaScript variable.

Syntax

Procedure SYSDATE;

Example

The following example illustrates how to call SYSDATE:

http.javascript.script(true);
http.javascript.to_char;
http.javascript.sysdate;
http.writeln('window.document.writeln(to_char(sysdate(),"MON DD YYYY"));');
http.javascript.script(false);

Subprogram TO_DATE (NLS_DATE_FORMAT)

The subprogram TO_DATE creates the function to_date() which accepts the following parameters:

• value
• nls_date_format

Syntax

Procedure TO_DATE (NLS_DATE_FORMAT IN VARCHAR2:=HTTP.MYSELF.NLS_DATE_FORMAT);

Example

The following example illustrates how to call TO_DATE:

http.javascript.script(true);
http.javascript.isdigit;
http.javascript.to_date;
http.writeln('window.document.writeln(to_date("11/25/2006","MM/DD/YYYY"));');
http.javascript.script(false);

Subprogram TRANSPOSE

The subprogram TRANSPOSE creates the function transpose() which accepts the following parameters:

• string

where string is the string to be reversed.

Syntax

Procedure TRANSPOSE;

Example

The following example illustrates how to call TRANSPOSE:

http.javascript.script(true);
http.javascript.transpose;
http.writeln('window.document.writeln(transpose("Hello World"));');
http.javascript.script(false);

Subprogram TO_CHAR

The subprogram TO_CHAR creates the function to_char() which accepts the following parameters:

• value
• format_mask

value can be either a JavaScript number or a JavaScript date that will be converted to a string. The format_mask is optional, but causes this function to call the method toString() which is of little use.

Syntax

Procedure TO_CHAR;

Example

The following example illustrates how to call TO_CHAR:

http.javascript.script(true);
http.javascript.lpad;
http.javascript.rpad;
http.javascript.ltrim;
http.javascript.rtrim;
http.javascript.trim;
http.javascript.to_char;
http.writeln('window.document.writeln("<pre>")');
http.writeln('window.document.writeln(to_char(new Date(),"Mon DD YYYY HH12:MIAM"));');
http.writeln('window.document.writeln(to_char(1234567.9,"fm99999999999999"));');
http.writeln('window.document.writeln("</pre>")');
http.javascript.script(false);

Subprogram TRUNC

The subprogram TRUNC creates the function trunc() which accepts the following parameters:

• value
• scale

Syntax

Procedure TRUNC;

Example

The following example illustrates how to call TRUNC:

http.javascript.script(true);
http.javascript."trunc";
http.writeln('window.document.writeln(trunc(123.456,1));');
http.javascript.script(false);

Subprogram TRIM

The subprogram TRIM creates the function trim() which accepts the following parameters:

• string

where string is the string to be trimmed of spaces.

Syntax

Procedure TRIM;

Example

The following example illustrates how to call TRIM:

http.javascript.script(true);
http.javascript.ltrim;
http.javascript.rtrim;
http.javascript.trim;
http.writeln('window.document.writeln(trim("  Hello World  "));');
http.javascript.script(false);

Subprogram VALIDATECOUNTRY

The subprogram VALIDATECOUNTRY creates functions for looking up country names and country codes. This subprogram creates the following javascript functions:

• validateCountryCode
• validateCountryName

Both functions accept the following parameters:

• CODE
• NAME

All parameters are optional (if that makes sense). Pass a null value for parameters that don't interest you; for example, if all you are interested in is looking up a country name, pass a "null" value for CODE.

Example

The following example illustrates how to call VALIDATECOUNTRY:

http.javascript.script(true);
http.javascript."goto";
http.javascript.openwindow;
http.javascript.validateCountry;
http.javascript.script(false);
http.writeln('<form name=form>');
http.writeln('<input type=text name=code onChange="validateCountryCode(this,this.form.name);">');
http.writeln('<input type=text name=name onChange="validateCountryName(this.form.code,this);">');
http.writeln('</form>');

Subprogram VALIDATEDATE (NLS_DATE_FORMAT)

The subprogram VALIDATEDATE creates the function validateDate() which accepts the following parameters:

• date
• nls_date_format

where date is the cell on your form that is to be validated. If valid, your cell is formatted with the format (NLS_DATE_FORMAT) that is passed to this subprogram or the format (nls_date_format) that is passed to this function. You can use the former if all date fields should have the same format. Use the latter if the date format is cell specific.

Syntax 1

Procedure VALIDATEDATE
(
    NLS_DATE_FORMAT := 'Mon DD YYYY'
)   ;

Example 1

The following example illustrates how to call VALIDATEDATE:

declare
    nls_date_format varchar(30) := 'Mon DD YYYY HH12:MIAM'
    ;
begin
    http.javascript.script(true);
    http.javascript.ltrim;
    http.javascript.rtrim;
    http.javascript.trim;
    http.javascript.to_char;
    http.javascript.validatedate(nls_date_format);
    http.javascript.script(false);
    http.writeln('<form>');
    http.writeln('<input type="text" onChange="validateDate(this);">');
    http.writeln('<input type="text" onChange="validateDate(this,''YYYY-MM-DD'');">');
    http.writeln('<input type="text" onChange="validateDate(this,'''|| NLS_DATE_FORMAT || ''');">');
    http.writeln('<input type="text" onChange="validateDate(this,'''|| HTTP.MYSELF.NLS_DATE_FORMAT || ''');">');
    http.writeln('</form>');
end;

See Also

CALENDAR to attach an HTML calendar to your date field.

Subprogram VALIDATEDATE (STYLE)

The subprogram VALIDATEDATE creates the function validateDate() which accepts the following parameters:

• date
• style

where date is the cell on your form that is to be validated. If valid, your cell if formatted with the format (STYLE) that is passed to this subprogram or the format (style) that is passed to this function. You can use the former if all date fields should have the same format. Use the latter if the date format is cell specific.

Syntax 2

Procedure VALIDATEDATE
(
    STYLE := 107
)   ;

Example 2

The following example illustrates how to call VALIDATEDATE:

declare
    style number := 107
    ;
begin
    http.javascript.script(true);
    http.javascript.ltrim;
    http.javascript.rtrim;
    http.javascript.trim;
    http.javascript.to_char;
    http.javascript.convert;
    http.javascript.validateDate(style);
    http.javascript.script(false);
    http.writeln('<form>');
    http.writeln('<input type="text" onChange="validateDate(this);">');
    http.writeln('<input type="text" onChange="validateDate(this,100);">');
    http.writeln('<input type="text" onChange="validateDate(this,'|| STYLE || ');">');
    http.writeln('</form>');
end;

See Also

CALENDAR to attach an HTML calendar to your date field.

Subprogram VALIDATEEMPLOYEE (OFFLINE)

The subprogram VALIDATEEMPLOYEE creates functions for looking up employees based on their employee ID (EMPLID), name, location (MAIL_DROP), and department ID (DEPTID).

This subprogram creates the following javascript functions:

• validateEmployeeID
• validateEmployeeName
• validateMailDrop
• validateDeptID

All of these functions accept the following parameters:

• EMPLID
• NAME
• MAIL_DROP
• DEPTID

All parameters are optional (if that makes sense). You should pass a "null" value for parameters that don't interest you; for example, if all you are interested in is looking up employee ID and employee name, MAIL_DROP and DEPTID can be omitted or explicitly set to null.

The purpose here is to allow your users to specify an employee ID or name. Usually, it is the employee ID that you are interested in because you can map an employee ID to a name, location, or department ID. When searching for a name, location, or Department ID that is not unique, your users will be prompted with a list of employees (their ID and Name) that match to this value. From this list they can select the employee ID. What is updated on your form screen depends on the cells you passed that are not null.

Syntax

Procedure VALIDATEEMPLOYEE (OFFLINE BOOLEAN:=FALSE);

Example

The following example illustrates how to call VALIDATEEMPLOYEE:

http.javascript.script(true);
http.javascript.openwindow;
http.javascript.validateEmployee;
http.javascript.script(false);
http.writeln('<form>');
http.writeln('<input type=text name=employeeID   onChange="validateEmployeeID  (this.form.employeeID,this.form.employeeName);">');
http.writeln('<input type=text name=employeeName onChange="validateEmployeeName(this.form.employeeID,this.form.employeeName);">');
http.writeln('</form>');

Example

The problem with the above example is that it takes longer to load this function than it does to actually validate an employee. It might be simpler to just ask someone their employee number. To use this function correctly, it should be loaded once and once only, and you should expect it to be used to validate several employees. Frames make this possible. The following example illustrates the correct way to invoke this subprogram. Note that we qualify the reference to validateEmployeeID() and validateEmployeeName() so that they refer to the parent window.

create or replace procedure test(button in varchar2:=null)
is
begin
    if button is null then
        http.javascript.script(true);
        http.javascript.openwindow;
        http.javascript.validateEmployee;
        http.javascript.script(false);
        http.sp_html_frame(procname=>http.myself.name,center=>'*');
        return;
    end if;
    if button = 'CENTER' then
        http.writeln('<form>');
        http.writeln('<input type=text name=employeeID   onChange="parent.validateEmployeeID  (this.form.employeeID,this.form.employeeName);">');
        http.writeln('<input type=text name=employeeName onChange="parent.validateEmployeeName(this.form.employeeID,this.form.employeeName);">');
        http.writeln('</form>');
        return;
    end if;
end test;

Example

If you pass TRUE to this procedure (different from the default), you can validate employees on the server side instead of loading all employees to the client. The interface is almost the same, but it does require that you call sp_html_frame to intialize these functions. The effect is the same except that it takes longer to validate this on the server. If you are attempting to validate only one employee, however, this is the preferred interface.

create or replace procedure test(button in varchar2:=null)
is
begin
    if button is null then
        http.javascript.script(true);
        http.javascript.openwindow;
        http.javascript.validateEmployee(true);
        http.javascript.script(false);
        http.sp_html_frame(procname=>http.myself.name,center=>'*');
        return;
    end if;
    if button = 'CENTER' then
        http.writeln('<form>');
        http.writeln('<input type=text name=employeeID   onChange="parent.validateEmployeeID  (this.form.employeeID,this.form.employeeName);">');
        http.writeln('<input type=text name=employeeName onChange="parent.validateEmployeeName(this.form.employeeID,this.form.employeeName);">');
        http.writeln('</form>');
        return;
    end if;
end test;

Example

The following example illustrates how you can invoke these functions if there are multiple occurences of the same field:

http.writeln('<form>');
for i in 0..14 loop
    http.writeln('<input type=text name=employeeID   onChange="parent.validateEmployeeID  (this.form.employeeID['||i||'],this.form.employeeName['||i||']);">');
    http.writeln('<input type=text name=employeeName onChange="parent.validateEmployeeName(this.form.employeeID['||i||'],this.form.employeeName['||i||']);">');
end loop;
http.writeln('</form>');

Subprogram VALIDATELANGUAGE

The subprogram VALIDATELANGUAGE creates functions for looking up languages and language codes.

This subprogram creates the following javascript functions:

• validateLanguageCode
• validateLanguageName

Both functions accept the following parameters:

• CODE
• NAME

All parameters are optional (if that makes sense). Pass a null value for parameters that don't interest you; for example, if all you are interested in is looking up a language, pass a "null" value for CODE.

Example

The following example illustrates how to call VALIDATELANGUAGE:

http.javascript.script(true);
http.javascript."goto";
http.javascript.openwindow;
http.javascript.validateLanguage;
http.javascript.script(false);
http.writeln('<form name=form>');
http.writeln('<input type=text name=code onChange="validateLanguageCode(this,this.form.name);">');
http.writeln('<input type=text name=name onChange="validateLanguageName(this.form.code,this);">');
http.writeln('</form>');

Subprogram VALIDATENUMBER (PICTURE)

The subprogram VALIDATENUMBER creates the function validateNumber() which accepts the following parameters:

• value
• picture

where value is the cell on your form that is to be validated. If valid, your cell is formatted with the format (PICTURE) that is passed to this subprogram or the format (picture) that is passed to this function. You can use the former if all numeric fields should have the same format. Use the latter if the picuture clause is cell specific.

Syntax

Procedure VALIDATENUMBER(PICTURE varchar2 := 'fm99999999999999999999');

Example

The following example illustrates how to call VALIDATENUMBER:

declare
    picture varchar(30) := 'fm999'
    ;
begin
    http.javascript.script(true);
    http.javascript.ltrim;
    http.javascript.rtrim;
    http.javascript.trim;
    http.javascript.isdigit;
    http.javascript.to_char;
    http.javascript.lpad;
    http.javascript.rpad;
    http.javascript.validatenumber(picture);
    http.javascript.script(false);
    http.writeln('<form>');
    http.writeln('<input type="text" onChange="validateNumber(this);">');
    http.writeln('<input type="text" onChange="validateNumber(this,''999,999,999.00'');">');
    http.writeln('<input type="text" onChange="validateNumber(this,'''|| PICTURE || ''');">');
    http.writeln('</form>');
end;

Subprogram VALIDATESTATE

The subprogram VALIDATESTATE creates functions for looking up state names and state codes. If you're wondering why you wouldn't just create a drop-down list using a <select> tag, by all means that is what you should use and I encourage you to do so.

This subprogram creates the following javascript functions:

• validateStateCode
• validateStateName

Both functions accept the following parameters:

• CODE
• NAME

All parameters are optional (if that makes sense). Pass a null value for parameters that don't interest you; for example, if all you are interested in is looking up a state name, pass a "null" value for CODE.

Example

The following example illustrates how to call VALIDATESTATE:

http.javascript.script(true);
http.javascript."goto";
http.javascript.openwindow;
http.javascript.validateState;
http.javascript.script(false);
http.writeln('<form name=form>');
http.writeln('<input type=text name=code onChange="validateStateCode(this,this.form.name);">');
http.writeln('<input type=text name=name onChange="validateStateName(this.form.code,this);">');
http.writeln('</form>');

Subprogram VALIDATETIME (NLS_DATE_FORMAT)

This subprogram creates the function validateTime() which accepts the following parameters:

• cell
• nls_date_format

Syntax

Procedure VALIDATETIME (NLS_DATE_FORMAT IN VARCHAR2:='HH:MI:SSAM');

Example

The following example illustrates how to call VALIDATETIME:

http.javascript.script(true);
http.javascript.isdigit;
http.javascript.ltrim;
http.javascript.rtrim;
http.javascript.trim;
http.javascript.isspace;
http.javascript.to_char;
http.javascript.validateTime;
http.javascript.script(false);
http.writeln('<form>');
http.writeln('<input type=text name=time onChange="validateTime(this);">');
http.writeln('</form>');

Your NLS_DATE_FORMAT should reference hours, minutes and seconds only.

See Also