- E

- Oracle Power Objects Programming Reference

- Overview of Oracle Basic
- Supported Data Types
- Variables
- Constants and Reserved Words
- Operators
- Expressions
- Statements
- Commands

- Database Interaction Commands
- Definition and Manipulation of Variables or Constants
- Definition of Functions and Subroutines
- Directory and File Management Commands
- File Input and Output Commands
- Miscellaneous Commands
- Program Flow Commands

- Functions

- Aggregation Functions
- Array and Subscript Functions
- Data Type Conversion Functions
- Date Functions
- File Input and Output Functions
- Financial Functions
- General Functions
- Mathematical Functions
- Selection Functions
- SQL Functions
- String Functions
- Test Functions
- User Dialog Functions

- Summary

#
E

## Oracle Power Objects Programming Reference

Power Objects uses Oracle Basic as its programming language. Oracle Basic is fully compatible with ANSI BASIC with the additions of object concept extensions and Structured Query Language (SQL) extensions. Oracle chose Basic as the programming language in order to appeal to the widest developer experience and level. This chapter discusses the majority of all commands and functions comprising Oracle Basic in order to provide you with a reference on syntax and purpose.

### Overview of Oracle Basic

Oracle Basic is fully compatible with ANSI BASIC and is therefore compatible with Microsoft Visual Basic. You are not required to write Basic code in order to use Power Objects effectively. If you find that the default functionality of a method or process does not exactly meet your requirements, then you can overwrite, modify, or augment the default behavior using Oracle Basic code.

Using Oracle Basic you can create user-defined functions and methods. You can declare variables and external functions. You can obtain direct access to the data contained in visual and non-visual objects. Oracle Basic provides the developer with the capability to create the features in the Power Objects product that he finds are missing or incomplete.

### Supported Data Types

Power Objects provides support for two different data type concepts: database-related data types and Oracle Basic-related data types. Database-related data types are the values that you specify when you are defining a new table in the Table Editor or when using SQL statements such as CREATE TABLE. These data types specify the type of data that is stored in the table column. Database data types vary depending on the database you use. The following lists show the data types for three different types of databases.

    Blaze
    Oracle7
    SQL Server
CHAR | CHAR | BINARY
DATE | DATE | BIT
FLOAT | FLOAT | CHAR
INTEGER | LONG | DATETIME
LONG | LONG RAW | FLOAT
LONG RAW | MLSLABEL | IMAGE
MLSLABEL | NUMBER | INT
NUMBER | ROWID | MONEY
ROWID | VARCHAR | REAL
VARCHAR | VARCHAR2 | SMALLDATETIME
VARCHAR2 |  | SMALLINT
|  | SYSDATE
|  | TEXT
|  | TIMESTAMP
|  | TINYINT
|  | VARBINARY
|  | VARCHAR

Oracle Basic-related data types are the values that you specify when you are choosing a DataType property for a data-bound object in the Power Objects design environment. These values are independent of the database-related data types. There are four possible values: Long Integer, Double, Date, and String. Another data type is available for use with the picture and OCX objects. These two objects rely on the Long Raw data type implicitly (you cannot change the data type of the object). There are eight types of Oracle Basic variables that you can declare: Integer, Long, Single, Double, Date, String, Object, and Variant.

For a more in-depth understanding of data types, please refer to the Oracle Power Objects online help under the following search concepts: data types – Oracle Basic, column data types, and column data types by database.

### Variables

Oracle Basic variables are names you use to hold values or indirect references to objects. Variables are constructed according to a strict naming convention. A variable must start with a letter, and it can include numbers, letters, and the underscore character. A variable cannot have more than 39 characters. Variables in Oracle Basic are not case sensitive.

You can declare variables and their respective data types explicitly and implicitly. Explicitly declaring variables involves using Oracle Basic commands such as DIM, REDIM, GLOBAL, and STATIC. You implicitly declare variables through simply using a variable name without an explicit declaration (this is also referred to as declaration by reference). You can also declare the data type of variables explicitly and implicitly. You explicitly declare a data type by declaring the variable explicitly with a data type or by using a data type suffix. An example of explicit declaration is DIM MyVar AS Integer. The variable MyVar is of data type Integer. Data type suffixes include: % for Integer, & for Long, ! for Single, # for Double, and $ for String. Another example of an explicit data type suffix variable is MyVar%. The variable MyVar% is of data type Integer. This declaration is similar to the first explicit declaration, but they are not completely interchangeable. Once you've declared a variable in an explicit fashion, you must always reference it in the same explicit declaration naming convention; for example, MyVar% must always be MyVar%, and MyVar must always be MyVar. You can also set a variable's data type implicitly through the type of data that is placed in it. For example, you can set the variable MyText to a data type of String with the following statement: MyText = "This is a literal text string". For a more extensive look at variables in Oracle Basic, refer to the online help under the search word variables. Each topic under this search word is well written.

### Constants and Reserved Words

Power Objects has a large number of constants predefined to increase developer productivity. Constants are not case sensitive. The online help has references to constants but doesn't always present the value and description; many of the constants listed here are not documented in any obvious way in the online help. The tables that follow group the Oracle Power Objects constants by functional areas, indicating their value and a description of the purpose of the constant.

Table E.1. Constants for counters.

Value | Constant | Description
CounterType Property
1 | CounterType_None | The field is not a counter (default).
2 | CounterType_Sequence | New values are automatically selected from the sequence object specified in the CounterSeq property.
3 | CounterType_Table_MaxCounterIncBy | New values are determined by taking the current maximum value in the column and adding the value of the CounterIncBy property.
4 | CounterType_User Generated | New values are determined by the code supplied in the CounterGenKey() method.
CounterTiming Property
0 | CounterTiming Immediate | New values are generated when a row is inserted into the record set containing the field (default).
1 | CounterTiming Deferred | New values are generated when a new row is flushed to the database.

Table E.2. Constants for control data types.

    Value
    Constant
    Description
DataType Property
0 | DataType_Integer | The control contains integer data.
0 | DataType_Long_Integer | The control contains long integer data (default).
1 | DataType_Float | The control contains float data.
1 | DataType_Double | The control contains double data.
2 | DataType_String | The control contains string data.
3 | DataType_Date | The control contains date data.

Although constants are supplied for data type values, their merit is questionable. The values of the constants do not map to the values returned when text field objects of the four primary data types are queried for their data type value. A Long Integer data type returns a value of 2. A Double data type returns a value of 4. A String data type returns a value of 1. A Date data type returns a value of 6.
I should also note that these data type constants will have considerably more value in future versions of Power Objects when a developer can programmatically set the DataType property of a control.

Table E.3. Constants for line controls.

    Value
    Constant
    Description
Direction Property
0 | Direction_Upper_Left_To_Lower_Right | The control draws the line from the upper-left corner down to the lower-right corner (default).
1 | Direction_Lower_Left_To_Upper_Right | The control draws the line from the lower-left corner up to the upper-right corner.

Table E.4. Constants for special values.

    Value
    Constant
    Description
Booleans
0 | FALSE | Value is False or 0.
1 | TRUE | Value is True or 1.
| NULL | Value is 0 or <empty>. You use this constant for initializing variables and controls to have an understood value of Null. Null is a concept perhaps considered unique to the database environment.
| ArgCount | Returns the number of arguments or parameters for the current function or subroutine method in which the semi-constant is referenced.

Table E.5. Constants for database operations and referential integrity.

    Value
    Constant
    Description
LinkMasterDel Property
0 | LinkMasterDel_Refuse | You cannot delete the master record if children exist (default).
1 | LinkMasterDel_Cascade | Deleting the master record causes all children to also be deleted.
2 | LinkMasterDel_Orphan | Deleting the master record does not delete the children.
LinkMasterUpd Property
0 | LinkMasterUpd_Refuse | You cannot update the master record if children exist (default).
1 | LinkMasterUpd_Cascade | Updating the master record causes all children to also be updated.
2 | LinkMasterUpd_Orphan | Updating the master record does not update the children.
LinkPrimaryKey Property
0 | PrimaryKey_On_Master | The primary key column resides on the master object (default).
1 | PrimaryKey_Here | The primary key column resides on the detail object.
RowFetchMode Property
0 | RowFetchMode_Fetch_As_Needed | Fetch as many rows as are needed to meet display requirements, from one to many rows.
1 | RowFetchMode_Fetch_Count_First | Fetch the count of all rows that are selected and then fetch as many rows as are needed to meet display requirements, from one to many rows.
2 | RowFetchMode_Fetch_All_Immediately | Fetch all rows from the database; cache to disk as necessary.

Table E.6. Constants for text justification.

    Value
    Constant
    Description
TextJustHoriz Property
0 | TextJustHoriz_Left | Text is aligned on the left of the object.
1 | TextJustHoriz_Center | Text is aligned on the center of the object.
2 | TextJustHoriz_Right | Text is aligned on the right of the object.
TextJustVert Property
0 | TextJustVert_Top | Text is aligned at the top of the object.
1 | TextJustVert_Center | Text is aligned at the center of the object.
2 | TextJustVert_Bottom | Text is aligned at the bottom of the object.

Table E.7. Constants for windows.

    Value
    Constant
    Description
WindowInitPos Property (No predefined constants exist)
0 |  | Automatic—the form is placed according to an indexed offset scheme used for positioning windows. This does not always result in the best window placement (default).
1 |  | WinPositionX/Y—the form is placed according to the offsets specified in the WinPosX and WinPosY properties.
2 |  | Center—the form is displayed in the center of the displayable region.
3 |  | Dialog Position—the form is horizontally centered within the displayable region and one third of the vertical distance between the application menu bar and bottom of the displayable region.
4 |  | Maximize—the form is expanded to fill all of the available display region.
WindowStyle Property
0 | WindowStyle_Standard_Document | The form is displayed as a standard window that is moveable and resizeable and can be minimized and maximized (default).
1 | WindowStyle_Fixed | The form is displayed in its exact dimensions as a non-standard window that is moveable only.
2 | WindowStyle_Document_Without_Maximize | The form is displayed as a standard window that is moveable and resizeable.
3 | WindowStyle_Standard_Dialog | A non-resizeable, unmovable dialog box with no title bar and a thick border.
4 | WindowStyle_Plain_Dialog | Same as a standard dialog box except on Macintosh, it has no border.
5 | WindowStyle_Alternate_Dialog | Same as a standard dialog box except on Macintosh, it has a shadow beneath it.
6 | WindowStyle_Movable_Dialog | A moveable, non-resizeable dialog box with a title bar.

Table E.8. Constants for system, form, and report menu bars, toolbars, and status lines.

    Value
    Constant
    Description
Menu and Toolbar Command Codes
1 | Cmd_About | cmdCode for Help menu About Oracle Power Objects menu item.
2 | Cmd_New | cmdCode for File menu New menu item.
3 | Cmd_Open | cmdCode for File menu Open menu item.
4 | Cmd_Save | cmdCode for File menu Save menu item.
5 | Cmd_SaveAs | cmdCode for File menu Save As menu item.
6 | Cmd_Close | cmdCode for File menu Close menu item.
9 | Cmd_PrintSetup | cmdCode for File menu Print Setup menu item.
10 | Cmd_Print | cmdCode for File menu Print menu item.
11 | Cmd_Quit | cmdCode for File menu Exit menu item.
20 | Cmd_Undo | cmdCode for Edit menu Undo menu item.
21 | Cmd_Cut | cmdCode for Edit menu Cut menu item.
22 | Cmd_Copy | cmdCode for Edit menu Copy menu item.
23 | Cmd_Paste | cmdCode for Edit menu Paste menu item.
24 | Cmd_Clear | cmdCode for Edit menu Clear menu item.
28 | Cmd_Redo | cmdCode for Edit menu Redo menu item.
29 | Cmd_Help | cmdCode for Help menu Contents menu item.
31 | Cmd_HelpOnHelp | cmdCode for Help menu Using Help menu item.
32 | Cmd_PrintPreview | cmdCode for File menu Print Preview menu item.
4127 | Cmd_RunStop | cmdCode for the Stop toolbar icon.
4128 | Cmd_FullPage | cmdCode for Preview menu Full Page menu item.
4133 | Cmd_PasteSpecial | cmdCode for Edit menu Paste Special menu item.
4134 | Cmd_InsertObject | cmdCode for Edit menu Insert Object menu item.
5120 | Cmd_InsertRow | cmdCode for Database menu Insert Row menu item.
5121 | Cmd_DeleteRow | cmdCode for Database menu Delete Row menu item.
5122 | Cmd_Commit | cmdCode for Database menu Commit Work menu item.
5123 | Cmd_Rollback | cmdCode for Database menu Rollback Work menu item.
5124 | Cmd_AppQuery | cmdCode for Database menu Requery menu item.
5125 | Cmd_QBF | cmdCode for Database menu Query By Form menu item.
5126 | Cmd_QBFRun | cmdCode for Database menu Apply Form Query menu item.
5200 | Cmd_NextPage | cmdCode for Preview menu Next Page menu item.
5201 | Cmd_PrevPage | cmdCode for Preview menu Previous Page menu item.
10000 | Cmd_FirstUserCommand | The cmdCode for the first available user-defined command codes that are guaranteed not to overlap with system or application-defined command codes. A developer typically uses the Cmd_FirstUserCommand+i syntax for defining their own command code constants where i >= 0.
TestCommand() Method Constants
65536 | TestCommand_Enabled | The command is enabled.
65537 | TestCommand_Checked | The command is enabled and checked.
65538 | TestCommand_Disabled | The command is disabled.
65539 | TestCommand_Disabled_Checked | The command is disabled and checked.
Status Line Constants
0 | StatusLinePart_Text | Returns a string with the text currently display in the panel.
1 | StatusLinePart_Width | Returns the width of the panel in pixels.
2 | StatusLinePart_Command | Returns the command code of the panel (integer).
3 | StatusLinePart_Msg_Enabled | Returns the "enabled" message string.
4 | StatusLinePart_Msg_Disabled | Returns the "disabled" message string.
5 | StatusLinePart_Msg_Checked | Returns the "checked" message string.
6 | StatusLinePart_Msg_Disabled_Checked | Returns the "disabled-checked" message string.
Toolbar Constants
0 | ToolbarPart_Command | Refers to the command code of the toolbar.
1 | ToolbarPart_Bitmap | Refers to the icon image to display on the toolbar.
2 | ToolbarPart_Help | Refers to the help context ID of the toolbar.
3 | ToolbarPart_Style | Refers to the display style of the toolbar.
Toolbar Style Constants
0 | ToolbarStyle_PushBtn | A standard pushbutton style.
1 | ToolbarStyle_Toggle | An on/off toggle button style.
2 | ToolbarStyle_Separator | A space or gap between two buttons on the toolbar.
Menu Bar Constants
0 | MenuPart_Label | Refers to the label of the menu or menu item.
1 | MenuPart_Command | Refers to the command code of the menu or menu item.
2 | MenuPart_Help | Refers to the help context code of the menu or menu item.
3 | MenuPart_Accel | Refers to the hot key or accelerator key of the menu or menu item.
Additional Data Type Constants
1 | RecDty_String | String data type.
2 | RecDty_Integer | Integer data type.
3 | RecDty_Long | Long data type.
4 | RecDty_Double | Double data type.
6 | RecDty_Date | Date data type.

Power Objects also has a large number of reserved words. Reserved words are not case sensitive. Reserved words include words from ANSI standards, Oracle SQL standards, and Oracle Basic. Each of the lists of reserved words is well documented under the search concepts in the online help as follows: reserved words – Oracle and reserved words – Oracle Power Objects.

### Operators

Operators are the verbs in the Oracle Basic statements that enable a developer to create and evaluate expressions involving variables of various data types. Oracle Basic operators include arithmetic, string, comparison, logical, date, assignment, and object operators.

Arithmetic operators include exponentiation (^}, negation (-), multiplication (*), division (/), integer division (\), modulo arithmetic (MOD), addition (+), and subtraction (-). String operators include concatenation with implicit data type conversion to string (&) and concatenation without data type conversion (+). Comparison operators for all data types include equality (=) and inequality (<>). Comparison operators for all data types except Object include less than (<), greater than (>), less than or equal (<=), and greater than or equal (>=). Logical operators include negation (NOT), conjunction (AND), disjunction (OR), exclusive disjunction (XOR), equivalence (EQV), and implication (IMP). Date operators include subtraction (-) and addition (+). Subtraction of one date from another returns the number of days between the two dates. Subtraction of a number x from a date d returns a date x days before date d. Addition of a number x to a date d returns a date x days after date d. The assignment operator is the equals mark (=). The object operator is the NEW command. For more information on Oracle Basic operators, refer to the search concept operators – Oracle Basic in the online help.

Operators are also key to the formation of valid SQL statements. SQL operators include arithmetic, character, comparison, logical, set, and others. For more information on SQL operators, refer to the search concept operators – SQL in the online help.

### Expressions

An expression is the right side of an assignment statement using Oracle Basic operators to group one or more of the following elements. Valid elements in an expression include literals, constants, built-in functions, object methods, object properties, and variables. Every valid expression has a data type and can return only one value. Each element in an expression also has a data type. If various elements in a given expression are of different types, you must be able to convert them to meaningful data types either implicitly by Oracle Power Objects or explicitly with type conversion functions.

Number elements are represented in decimal notation by default. To represent numbers in octal format, precede each number with &O. Remember that octal numbers use digits 0–7. &O11 is 9 in decimal notation. To represent numbers in hexadecimal notation, precede each number with &H. Remember that hexadecimal numbers use digits 0–9 and letters A–F. &H2F is 47 in decimal notation. For more information on Oracle Basic expression, refer to the search concept expressions and the subtopic Expressions in Oracle Basic in the online help.

### Statements

The three types of Oracle Basic statements are comment, assignment, and command. The comment statement begins either with a single apostrophe (') followed by text or with the Oracle Basic reserved word REM (stands for remark) followed by at least one space and then other text. An assignment statement has a variable or object property followed by an equal sign (=) and an expression. The command statement is any valid use of an Oracle Basic command or reference to an object method, subroutine, or function. A function returns a value, but the value does not have to be assigned.

Most Oracle Basic statements are represented one statement per line; however, this is not always the case. You can have multiple statements per line, and you can continue a statement across multiple lines. One form of multiple statements on a line uses the colon (:) operator between statements. For example, a = 1 : b = 2 assigns the value 1 to variable a and value 2 to variable b. A second form of multiple statements on a line involves the comment statement. You can add a comment statement to the right of any valid Oracle Basic single statement or multiple statements. You do not have to begin the comment with a colon (:) operator. The only exception to this form of multiple-statements-per-line option occurs when the last statement in the line has a line continuation operator.

To continue a statement across multiple lines, you use the line continuation character (&). One of the most often encountered problems with line continuation occurs when the developer does not include a space before the line continuation character. A similar problem exists when string data types are concatenated, but you actually wanted a multiline statement. Remember that the line continuation character (&) is also a type conversion suffix and the string concatenation operator.

For more information on Oracle Basic statements, refer to the search concept statements – Oracle Basic in the online help.

### Commands

A command is a type of Oracle Basic statement that performs implicit and explicit operations. Commands typically are explicit in that they are responsible for actions that occur only within the context of the command itself. Some commands are implicit and might execute one or more other commands as a result of the original command's implicit or inherited behavior. You can group Oracle Basic commands into the following categories: database interaction, definition and manipulation of variables or constants, definition of functions and subroutines, directory and file management, file input and output, miscellaneous, and program flow.

The notation used in the syntax for commands is that a vertical bar (|) indicates that you should make a choice between the values. A bracket pair ([]) indicates that the contents are optional. Braces ({}) are used to group arguments.

For more information on command statements, refer to the search concept commands with the subtopic List of Oracle Basic Commands in the online help.

#### Database Interaction Commands

Database interaction commands are limited to the single EXEC SQL Oracle Basic command. You can handle most database interactions, if not all, through the various record managers within the Power Objects application. When you need to perform a custom database operation, the EXEC SQL statement enables you to bypass the local record manager structure and update the database directly. The following code outlines the syntax for the EXEC SQL statement:

    EXEC SQL [ AT { session | :variable }] {sql_statement | :SQL_string_var}

Argument | Description
AT | Specifies that Oracle Power Objects will direct the SQL statement to a session other than the default session for the application.
session | The name of a database session as an object reference. This cannot be a string data type.
:variable | A variable of type object or variant that refers to a database session object. The colon (:) is not optional when using a variable reference.
sql_statement | Any valid SQL statement.
:SQL_string_var | An Oracle Basic string variable containing a valid SQL statement and preceded by a colon.

For more information on the EXEC SQL statement, refer to the search concepts EXEC SQL – using with the subtopic Using Exec SQL and the topic EXEC SQL command in the online help.

#### Definition and Manipulation of Variables or Constants

You use the CONST command to declare symbolic constants, usually within the Declarations section of your application. In naming your constants, you should prefix the constant literal with a lowercase c. CONST cMyVal = 1 specifies that cMyVal is an Integer constant with value 1. The following code segment outlines the syntax.

    CONST identifier = const_expression [, identifier = const_expression ...]

identifier | The name of the constant.
const_expression | A valid Oracle Basic expression that is permanently assigned to the identifier.

You use the DELETE command to delete variables of the object data type that was created dynamically with the NEW operator command. These variables are in memory. Once the variable is deleted, you cannot reference its methods.

    DELETE object_expr [, object_expr ... ]

object_expr | A variable or expression of data type object.

You use the NEW command to dynamically create variables of the object data type. Variables include record sets, menus, menu bars, toolbars, and status lines. These variables are in memory and must be deleted in order to recover the memory.

    NEW object_expr

object_expr | A variable or expression of data type object.

You use the DIM command to declare a variable and its data type. You can use this command in the application's Declaration section and in the method code for any object's methods. Variables have a scope local to the method in which they are declared. To declare a variable that is global in scope, you must use the GLOBAL command. The following line shows the syntax for the DIM command.

    DIM vname [(subscripts)] [AS vtype][,vname [(subscripts)] [AS vtype]...]

vname | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.
subscripts | The dimensions of an array variable.
vtype | The data type of the variable. Must be one of the following: Integer, Long, Single, Double, String, Date, Variant, or Object.

The GLOBAL command is entirely similar to the DIM command. You can declare variables and their respective data types. GLOBAL is different from DIM in two distinct ways. First, you can only use GLOBAL in the Declarations section of the application. Lastly, the scope of variables declared with GLOBAL is global with respect to the application and all its contained objects—versus a local scope when declared using the DIM command. The following line shows the syntax of the GLOBAL command.

    GLOBAL vname [(subscripts)] [AS vtype][,vname [(subscripts)] [AS vtype]...]

vname | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.
subscripts | The dimensions of an array variable.
vtype | The data type of the variable. Must be one of the following: Integer, Long, Single, Double, String, Date, Variant, or Object.

You use the ERASE command to delete the elements of one or more static arrays or to completely remove one or more dynamic arrays. With static arrays, the elements are reset to their default values based on their data type. For dynamic arrays, the memory allocated for the array is released. To reuse the erased dynamic array, you must use the REDIM command to allocate memory for the array.

    ERASE array_name [, array_name ... ]

array_name | The name of the array variable to be erased or whose array elements are to be reset.

You use the REDIM command to declare dynamic or static arrays and allocate the memory necessary to support them. You cannot use REDIM in the application's Declarations section. REDIM cannot redimension a variable unless that variable was declared using the GLOBAL or DIM commands.

    REDIM [PRESERVE] aname [([subs])] [AS dtype] [,aname [([subs])] [AS dtype] ...]

PRESERVE | Preserves the contents of an existing array when it is resized.
aname | The name of the array variable to be erased or whose array elements are to be reset.
subs | The dimensions of an array variable.
dtype | The data type of the variable. Must be one of the following: Integer, Long, Single, Double, String, Date, Variant, or Object.

You use the LET command for assigning an object reference or the value of an expression to a variable or property. The LET command is entirely optional and rarely used. Its primary function is for compatibility with ANSI BASIC.

    LET var_name = expression

var_name | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.
expression | A valid Oracle Basic expression or object reference that is assigned to the identifier.

The STATIC command is entirely similar to the DIM and GLOBAL commands. You can declare variables and their respective data types. STATIC is different from these commands in the following ways. First, the memory associated with the variables is preserved as long as the application is running. You can use STATIC everywhere that the DIM command is available. The scope of a STATIC variable is based on where the variable is declared. It is local to the procedure or method in which it is declared, and global to the application if declared in the application's Declarations section. The following line shows the syntax for the STATIC command.

    STATIC vname [(subscripts)] [AS vtype][,vname [(subscripts)] [AS vtype]...]

vname | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.
subscripts | The dimensions of an array variable.
vtype | The data type of the variable. Must be one of the following: Integer, Long, Single, Double, String, Date, Variant, or Object.

#### Definition of Functions and Subroutines

You use the DECLARE command to declare or define a procedure from a dynamic link library in the Microsoft Windows environment. This enables a developer to use functions and subroutines developed by third-party vendors within the method code of Power Objects applications and objects. You can only use this command in the Declarations section of an application or class. The following lines show the syntax for the DECLARE command.

    DECLARE SUB proc_name LIB lib_name [ALIAS alias_name]

    [ic:ccc][[BYVAL] (param_list)]

or

    DECLARE FUNCTION proc_name LIB lib_name [ALIAS alias_name]

    [ic:ccc][[BYVAL] (param_list)] [AS datatype | CSTRING]

SUB | Indicates that the procedure is a subroutine and does not return a value.
FUNCTION | Indicates that the procedure is a function and does return a value.
proc_name | The name that you want for the subroutine or function in your application. The name must conform to the naming conventions for Oracle Basic names.
LIB | Indicates that the procedure resides in an external library.
lib_name | The name of the dynamic link library (DLL) where the procedure is defined.
ALIAS | Indicates that the procedure's real name is different from the proc_name you are using.
alias_name | The name of the procedure as defined in the DLL.
BYVAL | Indicates that the parameter is passed by value (a copy of the variable) versus the default mechanism of passing by reference (a pointer to the variable).
param_list | A comma-separated list of parameters passed as arguments to the procedure.
datatype | The data type of the variable. Must be one of the following: Integer, Long, Single, Double, String, Date, Variant, or Object.
CSTRING | A special return type for those functions that return a null-terminated string.

You use the END command to indicate the termination of a related block of code. It is required in some formats of the IF...THEN...ELSE construct and in all references to the SELECT CASE construct. END is optional for function and subroutine methods because Oracle Basic automatically provides it for you.

    END { IF | SELECT | FUNCTION | SUB }

IF | Indicates that the block of code is an IF...THEN...ELSE construct.
SELECT | Indicates that the block of code is a SELECT CASE construct.
FUNCTION | Indicates that the procedure is a function and does return a value.
SUB | Indicates that the procedure is a subroutine and does not return a value.

#### Directory and File Management Commands

You use the CHDIR command to specify a new working directory on the current drive. The new directory must already exist or an error occurs. The following is the syntax:

    CHDIR dir_path

dir_path | A string specifying an existing directory path on the current drive to change the working directory to.

You use the CURDIR command to return the current default working directory or the default directory for the specified drive or volume. If Power Objects cannot determine the drive or volume for any reason (they don' t exist, for example), then the return value is null.

    CURDIR [[(] drive [)]]

drive | A string specifying a particular drive or volume to determine the current working directory for.

You use the CHDRIVE command to change the current drive or volume to a new drive or volume.

    CHDRIVE drive

drive | A string specifying a particular drive or volume to change the current working directory to.

You use the KILL command to delete disk files. The filename parameter does not accept wildcard characters.

    FILL filename

filename | The fully qualified name of disk file to be deleted.

You use the LOCK command to prevent other users or applications from modifying the specified file.

    LOCK [#]filenum [, lock_begin [TO lock_end]]

filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
lock_begin | An integer or numeric expression between 1 and 2,147,483,647 that represents the byte or record to be locked. (A record can be up to 64KB long.)
lock_end | An integer or expression that represents the upper limit on the range of bytes or records to be locked.

You use the UNLOCK command to release a lock previously placed on the specified file.

    UNLOCK [#]filenum [, lock_begin [TO lock_end]]

filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
lock_begin | An integer or numeric expression between 1 and 2,147,483,647 that represents the byte or record to be locked. (A record can be up to 64KB long.)
lock_end | An integer or expression that represents the upper limit on the range of bytes or records to be locked.

You use the MKDIR command to create a directory on the current drive or volume. If the specified directory path does not exist and would require the creation of multiple levels of directories, then the command fails.

    MKDIR dir_path

dir_path | A string specifying a valid directory path on the current drive to create.

You use the NAME...AS... command to rename files or move files to new directories. If the current filename and the proposed filename are in the same directory, the file is renamed; otherwise, the file is moved. If no directory path is specified, the current drive and directory path are assumed. The following line shows the syntax.

    NAME cur_name AS new_name

cur_name | The current name of the file to be renamed or moved including all relevant path information.
new_name | The new name to give to the file whether it is moved or simply renamed. May or may not include path information.

You use the RMDIR command to remove or delete the specified directory. This only works if the specified directory is empty.

    RMDIR dir_path

dir_path | A string specifying an existing directory path on the current drive to remove or delete.

#### File Input and Output Commands

You use the CLOSE command to close open file handles, releasing all buffer space associated with each open file handle. If no open file handles are specified, all the open files are closed. If the file handle specified is not open, Oracle Power Objects stops execution of your application after generating the following error: BAS-01066: File is not open. The following line shows the syntax of the CLOSE command.

    CLOSE [ [#] filenum [, [#] filenum ...] ]

# | Optional parameter used to provide a more visual cue that the filenum is an open file handle.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.

You use the GET command to read data from a disk file and assign it to a variable. The variable cannot be an object reference. Do not be mislead by the syntax for this command; it is actually correct. You cannot use a variable for rec_num if you intend a variable value of NULL to indicate omission of the rec_num. rec_num must be an integer or not supplied as a parameter. If rec_num is a variant variable data type and not initialized to an integer value, then the Oracle Power Objects error is BAS-01003: Numeric value required. If rec_num is declared as an integer variable data type and not initialized to an integer value, the error is BAS-01068: Value out of range. Either of these errors stops execution of your application.

The following line outlines the syntax for the GET command:

    GET [#] filenum, [rec_num], var_name

# | Optional parameter used to provide a more visual cue that the filenum is an open file handle.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
rec_num | If the file was opened in RANDOM mode, the rec_num is the number of the record to read starting at 1. If the file was opened in BINARY mode, the rec_num is the byte offset into the file to begin reading, starting at 1. If rec_num is omitted, the read begins at the current file pointer position.
var_name | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.

You use the INPUT # command to read data from a sequential file into a list of variables. The data types of the variables must correspond to the data stored in the file. The var_name cannot refer to an Oracle Power Objects object. If an input reaches the end-of-file marker before filling the variable list, execution of your application stops and an error occurs, BAS-01074: End of file on input.

    INPUT # filenum, var_name [, var_name ...]

filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
var_name | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.

You use the LINE INPUT # command to read one line of text, up to the end-of-line character, from a sequential disk file into a variable. The variable must be of data type String or Variant.

    LINE INPUT # filenum, var_name

filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
var_name | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.

You use the OPEN command to open a disk file for read or write operations, establish the access mode for those I/O operations, conditionally specify the record size, conditionally specify the locking method, allocate buffer space for the file handle, and return the open file handle. If the file handle you specify is already in use, your application stops execution and Oracle Power Objects returns an error, BAS-01063: File number already in use. If you attempt to open the same file with multiple file handles and do not have file sharing turned on, your application stops and Power Objects returns the following error: BAS-01067: Error opening file.

The following line shows the syntax for the OPEN command:

    OPEN filename [FOR mode] [ACCESS operations] [lock_method]

    [ic:ccc]AS [#] filenum [LEN=record_len]

filename | The fully qualified name of disk file to be opened.
mode | One of the following input/output choices: Append, Binary, Input, Output, or Random.
operations | One of the following file access operations: Read, Write, or Read Write.
lock_method | One of the following choices for determining locking and sharing operations: Shared, Lock Read, Lock Write, or Lock Read Write.
# | Optional parameter used to provide a more visual cue that the filenum is an open file handle.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
record_len | An integer or numeric expression in the range 1 to 32,767 indicating the length of a record. Random files have a default of 128 bytes. Sequential files have a default of 512 bytes.

You use the PRINT # command to write values including formatting information to a sequential file. Each PRINT # command appends an end-of-line character appropriate to the operating system to the output stream.

    PRINT #filenum,[SPC(n)]var_name[{;|,}[SPC(n)]var_name ...]

# | Optional parameter used to provide a more visual cue that the filenum is an open file handle.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
SPC(n) | Print the number n spaces before printing the next variable.
var_name | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.
; (semicolon) | Print the next variable immediately without any print zone alignment.
, (comma) | Print the next variable aligning on the next print zone boundary. Print zones are 14 characters wide.

You use the PUT command to write data from a variable to a disk file. The variable cannot be an object reference.

    PUT [#] filenum, [rec_num], var_name

# | Optional parameter used to provide a more visual cue that the filenum is an open file handle.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
rec_num | If the file was opened in RANDOM mode, the rec_num is the number of the record to read starting at 1. If the file was opened in BINARY mode, the rec_num is the byte offset into the file to begin reading, starting at 1. If rec_num is omitted, the read begins at the current file pointer position.
var_name | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.

You use the RESET command to flush all file buffers to disk and then close all open disk files. No arguments are required.

    RESET

The SEEK command is used to set the file pointer position in an open file for the next input/output operation.

    SEEK [#] filenum, seek_position

# | Optional parameter used to provide a more visual cue that the filenum is an open file handle.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
seek_position | Specifies the numeric offset into the file. Must be a value in the range 1 to 2,147,483,647.

You use the WIDTH # command to establish or change the output line length for the specified file handle.

    WIDTH # filenum, line_width

# | Optional parameter used to provide a more visual cue that the filenum is an open file handle.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
line_width | A numeric expression or integer in the range 0 to 255 that indicates the maximum length of the line before an end-of-line character is appended to the output stream. A value of 0 indicates that no explicit line length applies.

You use the WRITE # command to write data to a sequential file. String data is enclosed within double quotation marks (" "). Null data is displayed as #NULL#. Date data is written as #yyyy-mm-dd hh:mm:ss#.

    WRITE # filenum [, var_name ...]

# | Optional parameter used to provide a more visual cue that the filenum is an open file handle.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.
var_name | The name of a variable. The name must conform to the Oracle Basic variable naming conventions.

#### Miscellaneous Commands

You use the BEEP command to sound a tone through the computer speaker. No arguments are required.

    BEEP

You use the REM command to indicate the beginning of a user comment in the current source code text line. You can substitute an apostrophe (') for REM.

    REM comment

comment | Any sequence of ASCII characters. All characters are treated as a comment until the end-of-line character is encountered. The comment ends at the end-of-line character and cannot span multiple lines without restating the REM command.

You use the RANDOMIZE command to initialize the internal random number generator with a starting or seed value.

    RANDOMIZE [numeric_expression]

numeric_expression | Can be any valid Oracle Basic numeric expression.

You use the MSGBOX command to display a dialog box containing the specified message, using the optional dlg_options parameters to specify characteristics of the dialog box and displaying the optional dlg_title.

    MSGBOX [(] message [, dlg_options] [, dlg_title] [)]

message | An expression that evaluates to a string to be displayed in the dialog box.
dlg_options | A numeric expression that indicates the number and type of buttons displayed, the type of icon to display, which button is the default button, and whether the dialog box is application or system modal. Your button choices include the following:
| O—OK button only (default)
| 1—OK and Cancel
| 2—Abort, Retry, and Ignore
| 3—Yes, No, and Cancel
| 4—Yes and No
| 5—Retry and Cancel
| Your icon choices include the following:
| 0—No icon (default)
| 16—Stop sign (Hand on Mac)
| 32—Question mark
| 48—Exclamation point
| 64—Information "I"
| The default button definitions include the following:
| 0—First button (default)
| 256—Second button
| 512—Third button
| The modal window choices include the following:
| 0—Application (default)
| 4096—System
dlg_title | A string to place as the title of the dialog box window. If omitted, "Oracle Power Objects" is displayed.
( and ) | Parentheses are not optional if you specify either the dlg_options or dlg_title.

#### Program Flow Commands

You use the CALL command to invoke an Oracle Basic subroutine or function or a DLL procedure and pass it the argument specified. CALL is an optional command and used primarily to maintain consistency with ANSI BASIC. You pass parameters through the arg_list by reference (default) or by value using the ByVal option in the declaration of the function or subroutine.

    CALL proc_name [ ( arg_list ) ]

proc_name | The name of the Oracle Basic or DLL function or subroutine to be executed.
arg_list | A list of variables or object references to be passed as arguments to the function or subroutine to be executed.

You use the DO command to repeat a series of Oracle Basic commands while a test condition remains true or until it becomes false. There are two available syntaxes for the DO command.

The DO command in the following syntax might not ever execute the embedded statements:

    DO [{WHILE | UNTIL} condition]

    [statements]

    LOOP

The DO command in the following syntax always executes the embedded statements at least once:

    DO

    [statements]

    LOOP [{WHILE | UNTIL} condition]

condition | A string or numeric expression that evaluates to true (a non-zero number) or false (0). If the expression evaluates to NULL, you should use the ISNULL() function to return the expected values for true and false.

You use the EXIT command to terminate execution within the loop or procedure being processed. For the DO and FOR commands, EXIT returns control to the statement immediately following the corresponding LOOP or NEXT statement. For subroutines and functions, EXIT returns control to the statement immediately following the statement that called the procedure initially.

    EXIT { DO | FOR | FUNCTION | SUB }

DO | Transfers control from a DO command construct.
FOR | Transfers control from a FOR command construct.
FUNCTION | Transfers control from a function procedure.
SUB | Transfers control from a subroutine procedure.

You use the FOR command to execute a given block of Oracle Basic commands a specified number of times.

    FOR counter = first_val TO last_val [STEP increment]

    [statements]

    NEXT counter

counter | A numeric variable used as the incremental counter for loop iterations.
first_val | A numeric expression that is the first value assigned to counter.
last_val | A numeric expression that is the highest value that counter reaches before execution control transfers to the statement after the NEXT command.
increment | A numeric expression that is added to counter each time execution reaches the NEXT command. The default value is 1.

You use the GOSUB command to branch within a procedure and transfer execution control until control is returned with the RETURN command.

    GOSUB { line_num | label: }

line_num | The line number to which execution control is transferred. The line number must contain no more than 40 digits using 0-9 and ending with a space.
label | The line label to which execution control is transferred. The label must contain no more than 40 characters using a-z, A-Z, 0-9, and underscores (_). The label must begin with a letter and end with a colon (:).

You use the GOTO command to unconditionally transfer execution control within a procedure. Unlike the GOSUB command, GOTO cannot be returned to continue processing.

    GOTO { line_num | label: }

line_num | The line number to which execution control is transferred. The line number must contain no more than 40 digits using 0-9 and ending with a space.
label | The line label to which execution control is transferred. The label must contain no more than 40 characters using a-z, A-Z, 0-9, and underscores (_). The label must begin with a letter and end with a colon (:).

You use the IF...THEN...ELSE command to transfer execution control based on the evaluation of a specified condition. The IF command has three supported syntaxes.

The first two examples are each one continuous line.

    IF condition THEN {statements|line|label} [ELSE {statements|line|label}]

or

    IF condition GOTO {line|label} [ELSE {statements|line|label}]

The third syntax example contains multiple lines.

    IF condition_1 THEN

      [statements]

    [ELSEIF condition_2 THEN

      [statements]]

    [ELSE

      [statements]]

    END IF

condition | A string or numeric expression that evaluates to true (a non-zero number) or false (0). If the expression evaluates to NULL, you should use the ISNULL() function to return the expected values for true and false.
line | The line number to which execution control is transferred. The line number must contain no more than 40 digits using 0-9 and ending with a space.
label | The line label to which execution control is transferred. The label must contain no more than 40 characters using a-z, A-Z, 0-9, and underscores (_). The label must begin with a letter and end with a colon (:).

You use the ON command to transfer control within a procedure to a specified line number or label when a specified condition is met. This command functions identically to the GOTO and GOSUB commands after the target_condition is evaluated. There are two syntaxes for the ON command.

    ON target_condition GOTO target_list

or

    ON target_condition GOSUB target_list

target_condition | An expression that evaluates to 0 to 255 inclusive, indicating which of the target_list line numbers or labels to transfer execution control to.
target_list | A list of line numbers or labels to transfer execution control to based on the evaluation of the target_condition.

You use the ON ERROR command to trap run-time errors with Oracle Power Objects.

    ON ERROR { GOTO { line_num | label | 0 } | RESUME NEXT }

line_num | The line number to which execution control is transferred. The line number must contain no more than 40 digits using 0-9 and ending with a space.
label | The line label to which execution control is transferred. The label must contain no more than 40 characters using a-z, A-Z, 0-9, and underscores (_). The label must begin with a letter and end with a colon (:).
0 | Disables any enabled run-time error-handling routine in the current procedure.
RESUME NEXT | Specifies that if a run-time error occurs, execution should continue with the next statement after the one that caused the error.

You use the RESUME command to return execution control to the normal processing sequence of events after handling an error condition.

    RESUME { [0] | NEXT | line_num | label }

[0] | Zero (0) is optional. Supplying no parameter to the RESUME command has the same effect as supplying 0. Execution control restarts at the statement that caused the current run-time error condition.
NEXT | Execution control is transferred to the next statement after the one that caused the current run-time error condition.
line_num | The line number to which execution control is transferred. The line number must contain no more than 40 digits using 0-9 and ending with a space.
label | The line label to which execution control is transferred. The label must contain no more than 40 characters using a-z, A-Z, 0-9, and underscores (_). The label must begin with a letter and end with a colon (:).

You use the RETURN command to transfer execution control back to the next statement following the GOSUB command that initiated the transfer. No arguments are required.

    RETURN

You use the SELECT CASE command to execute a set of Oracle Basic statements based on evaluating a test_expression.

    SELECT CASE test_expression

      [CASE case_expression

        [statements]]

      [CASE case_expression

        [statements]]

      ...

      [CASE ELSE

        [statements]]

    END SELECT

test_expression | Any numeric or string expression whose value is to be compared with the case_expression associated with each CASE statement. Execution is transferred to the first CASE statement whose case_expression matches the test_expression.
case_expression | One or more numeric or string expressions separated by commas. A range can be expressed using the case_expression TO case_expression syntax. A second type of range can be expressed using IS relational_operator case_expression.

You use the STOP command to activate the design-time debugger. The only problem is that it generates a run-time error, BAS-00322: General syntax error. No arguments are required.

    STOP

You use the WHILE command to execute a list of statements as long as a test condition remains true.

    WHILE condition

      [statements]

    WEND

condition | A string or numeric expression that evaluates to true (a non-zero number) or false (0). If the expression evaluates to NULL, you should use the ISNULL() function to return the expected values for true and false.

### Functions

A function is a type of Oracle Basic statement that performs implicit and explicit operations with a resulting return value. Functions typically are explicit in that they are responsible for actions that occur only within the context of the function itself. Some functions are implicit and might execute one or more other functions or commands as a result of the original function's implicit or inherited behavior. You can group Oracle Basic functions into the following categories: aggregation, array and subscript, data type conversion, date, file input and output, financial, general, mathematical, selection, SQL, string, test, and user dialog.

The notation used in the syntax for functions is that a vertical bar (|) indicates that you should make a choice between the values. A bracket pair ([]) indicates that the contents are optional. Braces ({}) are used to group arguments.

For more information on function statements, refer to the search concept functions with the subtopic Oracle Basic Functions in the online help.

#### Aggregation Functions

You use the AVG function to return the arithmetic average (mean) of a set of values.

    AVG ( value_expr )

value_expr | An expression that must contain at least one reference to a bound control. It cannot refer to controls across multiple containers. The control must be in a repeater or in the detail group of a report.

The return value is of the Double data type.

You use the COUNT function to return the number of records in a set.

    COUNT ( value_expr )

value_expr | An expression that must contain at least one reference to a bound control. It cannot refer to controls across multiple containers. The control must be in a repeater or in the detail group of a report.

The return value is of the Double data type.

You use the MAX function to return the maximum value in a set of values.

    MAX ( value_expr )

value_expr | An expression that must contain at least one reference to a bound control. It cannot refer to controls across multiple containers. The control must be in a repeater or in the detail group of a report.

The return value is of the Double data type.

You use the MIN function to return the minimum value in a set of values.

    MIN ( value_expr )

value_expr | An expression that must contain at least one reference to a bound control. It cannot refer to controls across multiple containers. The control must be in a repeater or in the detail group of a report.

The return value is of the Double data type.

You use the STDEV function to return the estimated standard deviation of a set of values.

    STDEV ( value_expr )

value_expr | An expression that must contain at least one reference to a bound control. It cannot refer to controls across multiple containers. The control must be in a repeater or in the detail group of a report.

The return value is of the Double data type.

You use the SUM function to return the arithmetic sum of a set of values.

    SUM ( value_expr )

value_expr | An expression that must contain at least one reference to a bound control. It cannot refer to controls across multiple containers. The control must be in a repeater or in the detail group of a report.

The return value is of the Double data type.

#### Array and Subscript Functions

You use the LBOUND function to return the lowest subscript defined for the specified dimension of an array.

    LBOUND ( array [, dimension] )

array | The name of the array to examine.
dimension | The dimension of the array to examine (default is 1).

The return value is of the Integer data type.

You use the UBOUND function to return the highest subscript defined for the specified dimension of an array.

    UBOUND ( array [, dimension] )

array | The name of the array to examine.
dimension | The dimension of the array to examine (default is 1).

The return value is of the Integer data type.

#### Data Type Conversion Functions

You use the CDBL function to convert a string or expression into a value of data type Double.

    CDBL ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Double data type.

You use the CINT function to convert a string or expression into a value of data type Integer.

    CINT ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Integer data type.

You use the CLNG function to convert a string or expression into a value of data type Long.

    CLNG ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Long data type.

You use the CSNG function to convert a string or expression into a value of data type Single.

    CSNG ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Single data type.

You use the CSTR function to convert a string or expression into a value of data type String.

    CSTR ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the String data type.

You use the CVDATE function to convert a string or expression into a value of data type Date.

    CVDATE ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Date data type.

You use the FIX function to convert a numeric expression into an integer by truncating its fractional part.

    FIX ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Integer data type.

You use the INT function to convert a numeric expression into an integer by rounding to the nearest value. The rounding operation uses the absolute value of the expression to perform the round operation and then reapplies the sign of the original expression to the rounded value.

    INT ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Integer data type.

You use the STR function to convert a number or numeric expression into a value of data type String. STR provides a leading space for positive numbers.

    STR ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the String data type.

#### Date Functions

You use the DATE function to return the current system date of your operating system as a String data type. No arguments are required.

    DATE ()

The return value is of the String data type.

You use the DATEADD function to return a time or date value as the result of adding any number of time intervals to a date variable or expression.

    DATEADD ( time_interval, num_interval, date )

time_interval | A string expression that indicates the type of time interval to add to the date.
| s or S—Second
| n or N—Minute
| h or H—Hour
| d or D—Day
| y or Y—Day of year
| ww or WW—Week
| w or W—Weekday
| m or M—Month
| q or Q—Quarter
| yyyy or YYYY—Year
num_interval | The number of time intervals to add to the specified date.
date | The date to add the time intervals to.

The return value is of the Date data type.

You use the DATEDIFF function to return the number of time intervals between two specified dates.

    DATEDIFF ( time_interval, first_date, second_date )

time_interval | A string expression that indicates the type of time interval to add to the date.
first_date | A valid Oracle Basic date variable or expression.
second_date | A valid Oracle Basic date variable or expression.

The return value is of the Date data type.

You use the DATEPART function to return the portion of a date value that corresponds to a specified time interval.

    DATEPART ( time_interval, date )

time_interval | A string expression that indicates the type of time interval to add to the date.
date | A valid Oracle Basic date variable or expression.

The return value is of the Date data type.

You use the DATESERIAL function to return the complete date with integer arguments representing year, month, and day.

    DATESERIAL ( year, month, day )

year | An integer representing the year.
month | An integer representing the month.
day | An integer representing the day.

The return value is of the Date data type.

You use the DAY function to return a long value between 1 and 31 representing the day portion of the specified date value.

    DAY ( date )

date | A valid Oracle Basic date variable or expression.

The return value is of the Long data type.

You use the HOUR function to return an integer value between 0 (12:00 a.m.) and 23 (11:00 p.m.) representing the hour portion of the specified date value.

    HOUR ( date )

date | A valid Oracle Basic date variable or expression.

The return value is of the String data type.

You use the MINUTE function to return an integer value between 0 and 59 representing the minute portion of the specified date value.

    MINUTE ( date )

date | A valid Oracle Basic date variable or expression.

The return value is of the Integer data type.

You use the MONTH function to return an integer value between 1 and 12 representing the month portion of the specified date value.

    MONTH ( date )

date | A valid Oracle Basic date variable or expression.

The return value is of the Integer data type.

You use the NOW function to return the current system date of your operating system as a Date data type. No arguments are required.

    NOW ()

The return value is of the Date data type.

You use the SECOND function to return an integer value between 0 and 59 representing the second portion of the specified date value.

    SECOND ( date )

date | A valid Oracle Basic date variable or expression.

The return value is of the Integer data type.

You use the SYSDATE function to return the current system date of your operating system as a Date data type. It is identical to the NOW function. No arguments are required.

    SYSDATE ()

The return value is of the Date data type.

You use the TIME function to return the current system time of your operating system as a String data type. No arguments are required.

    TIME ()

The return value is of the String data type.

You use the TIMESERIAL function to return the complete time with integer arguments representing hour, minute, and second.

    TIMESERIAL ( hour, minute, second )

hour | An integer representing the hour.
minute | An integer representing the minute.
second | An integer representing the second.

The return value is of the Date data type.

You use the TIMEVALUE function to convert a string into a date return value.

    TIMEVALUE ( time_value )

time_value | A valid Oracle Basic date variable or expression representing a time value.

The return value is of the Date data type.

You use the WEEKDAY function to return an integer value between 1 (Sunday) and 7 (Saturday) representing the day of the week portion of the specified date value.

    WEEKDAY ( date )

date | A valid Oracle Basic date variable or expression.

The return value is of the Integer data type.

You use the YEAR function to return an integer between 99 and 10,000, inclusive, that represents the year portion of the specified date value.

    YEAR ( date )

date | A valid Oracle Basic date variable or expression.

The return value is of the Integer data type.

#### File Input and Output Functions

You use the CURDIR function to return the current path for the default drive (volume).

    CURRDIR [[(] vol_id [)]]

vol_id | A string representing the drive or volume to search.

The return value is of the String data type.

You use the EOF function to test if the file pointer is at the end-of-file marker for the indicated file handle.

    EOF ( filenum )

filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.

The return value is of the Integer data type.

You use the FREEFILE function to return an integer value between 1 and 256, inclusive, representing the next unused file number. No arguments are required.

    FREEFILE ()

The return value is of the Integer data type.

You use the INPUT function to return a string of characters read from a file opened in the input or binary mode.

    INPUT ( n, filenum )

n | The number of characters to read from the file.
filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.

The return value is of the String data type.

You use the LOC function to return the current location of the file pointer for the indicated file handle.

    LOC ( filenum )

filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.

The return value is of the Positive Integer data type.

You use the LOF function to return the length (in bytes) of the specified file handle.

    LOF ( filenum )

filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.

The return value is of the Positive Integer data type.

You use the SEEK function to return the position of the next input or output operation for the specified file handle.

    SEEK ( filenum )

filenum | An integer number between 1 and 256 representing a file handle as a result of opening a file using the OPEN command.

The return value is of the Long data type between 1 and 2,147,483,647.

#### Financial Functions

You use the DDB function to return the amount of an asset's depreciation for a single, specified period using the double-declining balance method.

    DDB ( asset_cost, salvage, life, period )

asset_cost | The initial value paid for the asset.
salvage | The projected value of the asset at the end of its life expectancy.
life | The length of time the asset is expected to be useful.
period | The length of time to calculate the asset's depreciation over.

The return value is of the Double data type.

You use the FV function to return the future value of an asset, such as an annuity, using a constant interest rate and constant periodic payments.

    FV ( rate, periods, payment, pv, due_end )

rate | The fixed interest rate you estimate that you will be paying over the life of the annuity.
periods | The total number of payment periods over the life of the annuity.
payment | The fixed amount of the periodic payment.
pv | The amount of the annuity.
due_end | 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period.

The return value is of the Double data type.

You use the IPMT function to return the amount applied to interest from a specified periodic payment on an annuity at a fixed interest rate and fixed periodic payments.

    IPMT ( rate, which_period, periods, pv, fv, due_end )

rate | The fixed interest rate you estimate that you will be paying over the life of the annuity.
which_period | The period to calculate the amount applied to principal.
periods | The total number of payment periods over the life of the annuity.
pv | The amount of the annuity.
fv | The amount of the annuity to be unpaid at the end of the life of the annuity.
due_end | 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period.

The return value is of the Double data type.

You use the IRR function to return the internal rate of return for an investment, which is its rate of return based on a series of periodic cash flows, both payments and receipts.

    IRR ( cash_flow_array, estimate )

cash_flow_array | An array that contains the values of a series of payments and receipts over the life of an investment. There must be at least one payment and one receipt.
estimate | The fixed interest rate you estimate that you will be using over the life of the cash flow.

The return value is of the Double data type.

You use the MIRR function to return the modified internal rate of return on an investment, which is the rate of return represented by a series of periodic cash flows (payments and receipts) when the interest rates for payments and receipts are different.

    MIRR ( cash_flow_array, borrow_rate, reinvest_rate )

cash_flow_array | An array that contains the values of a series of payments and receipts over the life of an investment. There must be at least one payment and one receipt.
borrow_rate | The interest rate you pay to borrow money.
reinvest_rate | The interest rate you expect to earn when reinvesting cash received.

The return value is of the Double data type.

You use the NPER function to return the number of periods for an annuity with fixed interest rates and fixed periodic payments.

    NPER ( rate, periods, pv, fv, due_end )

rate | The fixed interest rate you estimate that you will be paying over the life of the annuity.
periods | The total number of payment periods over the life of the annuity.
pv | The amount of the annuity.
fv | The amount of the annuity to be unpaid at the end of the life of the annuity.
due_end | 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period.

The return value is of the Double data type.

You use the NPV function to return the net present value of an investment using a constant discount rate and a series of cash flow values (payments and receipts).

    NPV ( discount_rate, cash_flow_array )

discount_rate | The discount rate that applies over the whole period being considered.
cash_flow_array | An array that contains the values of a series of payments and receipts over the life of an investment. There must be at least one payment and one receipt.

The return value is of the Double data type.

You use the PMT function to return the payment for an annuity with a fixed interest rate and fixed periodic payment.

    PMT ( rate, periods, pv, fv, due_end )

rate | The fixed interest rate you estimate that you will be paying over the life of the annuity.
periods | The total number of payment periods over the life of the annuity.
pv | The amount of the annuity.
fv | The amount of the annuity to be unpaid at the end of the life of the annuity.
due_end | 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period.

The return value is of the Double data type.

You use the PPMT function to return the amount applied to the principal during one payment period for an annuity.

    PPMT ( rate, which_period, periods, pv, fv, due_end )

rate | The fixed interest rate you estimate that you will be paying over the life of the annuity.
which_period | The period to calculate the amount applied to principal.
periods | The total number of payment periods over the life of the annuity.
pv | The amount of the annuity.
fv | The amount of the annuity to be unpaid at the end of the life of the annuity.
due_end | 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period.

The return value is of the Double data type.

You use the PV function to return the present value of an annuity with a fixed interest rate and fixed periodic payments.

    PV ( rate, periods, payment, fv, due_end )

rate | The fixed interest rate you estimate that you will be paying over the life of the annuity.
periods | The total number of payment periods over the life of the annuity.
payment | The fixed amount of the periodic payment.
fv | The amount of the annuity to be unpaid at the end of the life of the annuity.
due_end | 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period.

The return value is of the Double data type.

You use the RATE function to return the interest rate per period for an annuity.

    RATE ( periods, payment, pv, fv, due_end, estimate )

periods | The total number of payment periods over the life of the annuity.
payment | The fixed amount of the periodic payment.
pv | The amount of the annuity.
fv | The amount of the annuity to be unpaid at the end of the life of the annuity.
due_end | 0 indicates that payments are due at the end of each period. 1 indicates that payments are due at the beginning of each period.
estimate | The fixed interest rate you estimate that you will be paying over the life of the annuity.

The return value is of the Double data type.

You use the SLN function to return the depreciation of an asset's value for a single period using the straight-line method.

    SLN ( asset_cost, salvage, life )

asset_cost | The initial value paid for the asset.
salvage | The projected value of the asset at the end of its life expectancy.
life | The length of time the asset is expected to be useful.

The return value is of the Double data type.

You use the SYD function to return the depreciation of an asset for a specified period using the sum-of-years-digits method.

    SYD ( asset_cost, salvage, life, period )

asset_cost | The initial value paid for the asset.
salvage | The projected value of the asset at the end of its life expectancy.
life | The length of time the asset is expected to be useful.
period | The length of time to calculate the asset's depreciation over.

The return value is of the Double data type.

#### General Functions

You use the ENVIRON function to return the string that corresponds to a specified Windows operating system environment variable. This function returns NULL on the Macintosh.

    ENVIRON ( environment )

environment | A string variable or expression that is an environment variable, such as PATH.

The return value is of the String data type.

You use the HEX function to return a string value representing the hexadecimal value (base 16) for a decimal or numeric expression (base 10).

    HEX ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the String data type.

You use the OCT function to return a string value representing the octal value (base 8) for a decimal or numeric expression (base 10).

    OCT ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the String data type.

You use the PAGENUM function to return the number of the page being printed from within a report. No arguments are required.

    PAGENUM ()

The return value is of the Integer data type.

You use the RND function to return a single precision number in the range 0 <= x < 1.

    RND ( [ctrl_num] )

ctrl_num | A number or numeric expression that controls the value returned by the random number generator.
| ctrl_num < 0 always returns the same number.
| ctrl_num = 0 returns the last number.
| ctrl_num > 0 returns the next number.
| ctrl_num omitted returns the next number.

The return value is of the Single data type.

You use the SPC function to return the specified number of space characters. You can only use this function in the PRINT # command.

    SPC ( count )

count | The number of spaces to generate.

The return value is of the String data type.

You use the SYSTEMNAME function to return the name of the operating system on which Power Objects is executed. Possible values are Windows and Macintosh as string values. No arguments are required.

    SYSTEMNAME ()

The return value is of the String data type.

You use the TIMER function to return a long integer representing the number of seconds that have elapsed since midnight on the current day. No arguments are required.

    TIMER ()

The return value is of the Long data type.

#### Mathematical Functions

You use the ABS function to return the absolute value of a number or numeric expression.

    ABS ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Positive Integer data type.

You use the ATN function to return the arctangent (in radians) of a number or numeric expression.

    ATN ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Double data type.

You use the COS function to return the cosine of an angle represented in radians.

    COS ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Double data type.

You use the EXP function to return the value of e (the natural logarithm, 2.71828) raised to the specified power.

    EXP ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Double data type.

You use the LOG function to return the natural logarithm of a positive number or positive numeric expression.

    LOG ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Double data type.

You use the SIN function to return the sine of an angle represented in radians.

    SIN ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Double data type.

You use the SQR function to return the square root of a positive number or positive numeric expression.

    SQR ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Double data type.

You use the TAN function to return the tangent of an angle represented in radians.

    TAN ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Double data type.

#### Selection Functions

You use the CHOOSE function to return an expression as the result of the value of an indexed expression.

    CHOOSE ( index, expression [, expression ...] )

index | A numeric expression that evaluates to a number between 1 and the number of expression in the choose list.
expression | Can be any valid Oracle Basic variable or expression.

This function returns the data type of the resulting expression.

You use the IIF function to return a given value when a test condition evaluates to true and another value when it evaluates to false.

    IIF ( expression, val_true, val_false )

expression | Can be any valid Oracle Basic variable or expression.
val_true | Value to return if expression is true.
val_false | Value to return if expression is false.

This function returns the data type of the resulting true or false value.

You use the SWITCH function to return the value of the expression associated with the first test expression that evaluates to true.

    SWITCH ( expression, result [, expression, result ...] )

expression | A valid Oracle Basic expression to evaluate. You can nest up to seven expressions. Each is evaluated in left-to-right order.
result | The value to be returned if its corresponding expression evaluates to true.

The return value is of the same data type as the data type of the result.

#### SQL Functions

You use the SqlErrClass function to return the error classification for the most recent EXEC SQL operation. No arguments are required.

    SqlErrClass ()

The return value is of the Integer data type.

You use the SqlErrCode function to return the error code for the most recent EXEC SQL operation. No arguments are required.

    SqlErrCode ()

The return value is of the Long data type.

You use the SqlErrText function to return the error text for the most recent EXEC SQL operation. No arguments are required.

    SqlErrText ()

The return value is of the String data type.

You use the SqlLookup function to provide a single value as the result of a custom EXEC SQL method. This function is most often used as a derived value for data-bound fields.

    SqlLookup ( [session_name,] sql_statement )

session_name | The name of a database session as an object reference. This cannot be a String data type.
sql_statement | Any valid SQL statement.

This function returns the value specified in the SQL statement using its comparable Oracle Basic data type.

You use the SqlRowCount function to return the row count for the most recent EXEC SQL operation. No arguments are required.

    SqlRowCount ()

The return value is of the Integer data type.

#### String Functions

You use the ASC function to return the ANSI code for the first character of a string or string expression.

    ASC ( string_expression )

string_expression | A string expression, constant, or variable that can be interpreted as a string.

The return value is of the Integer data type.

You use the CHR function to return the character as a string that corresponds to the ANSI code specified.

    CHR ( charcode )

charcode | ANSI code for a character to be repeated.

The return value is of the String data type.

You use the FORMAT function to convert a numeric, string, or date expression into a string value and format it according to the specified format string.

    FORMAT ( NumDateString, format_string )

NumDateString | A number, date, or string variable or expression that will be formatted according to the format_string.
format_string | A string of Oracle Basic formatting characters. For more information, refer to the Oracle Power Objects online help under the topic format and the subtopic Format Mask Characters.

The return value is of the String data type.

You use the INSTR function to return the integer offset of a search string within a test string.

    INSTR ( [start,] search_str, search_test )

start | The character offset within the search string to begin searching.
search_str | The string to be searched.
search_test | The substring that is being searched for.

The return value is of the Integer data type.

You use the LCASE function to return a string with all the uppercase letters converted to lowercase.

    LCASE ( string_expression )

string_expression | A string expression, constant, or variable that can be interpreted as a string.

The return value is of the String data type.

You use the LEFT function to return the specified number of characters starting at the left of the string.

    LEFT ( string_expression, len )

string_expression | A string expression, constant, or variable that can be interpreted as a string.
len | Number of characters to copy.

The return value is of the String data type.

You use the LEN function to return the number of characters in the specified string.

    LEN ( string_expression )

string_expression | A string expression, constant, or variable that can be interpreted as a string.

The return value is of the Integer data type.

You use the LTRIM function to return a string with all the leading (leftmost) whitespace characters removed.

    LTRIM ( string_expression )

string_expression | A string expression, constant, or variable that can be interpreted as a string.

The return value is of the String data type.

You use the MID function to return a substring from another string.

    MID ( string_expression, start [, len] )

string_expression | A string expression, constant, or variable that can be interpreted as a string.
start | Indicates the offset within the string_expression to begin copying from. Starts at 1.
len | Number of characters to copy.

The return value is of the String data type.

You use the RIGHT function to return the specified number of characters starting at the right of the string.

    RIGHT ( string_expression, len )

string_expression | A string expression, constant, or variable that can be interpreted as a string.
len | Number of characters to copy.

The return value is of the String data type.

You use the RTRIM function to return a string with all the trailing (rightmost) whitespace characters removed.

    RTRIM ( string_expression )

string_expression | A string expression, constant, or variable that can be interpreted as a string.

The return value is of the String data type.

You use the SPACE function to return a string with the specified number of spaces.

    SPACE ( len )

len | Number of times to repeat the space character.

The return value is of the String data type.

You use the STRING function to return a string with the specified character copied the specified number of times. This operates exactly like the SPACE command except that the developer can specify the repeating character.

    STRING ( len, { string_expression | charcode } )

len | Number of times to repeat the character.
string_expression | A string expression, constant, or variable that can be interpreted as a string. Only the first character is used.
charcode | ANSI code for a character to be repeated.

The return value is of the String data type.

You use the TRIM function to return a string with all the leading (leftmost) and trailing (rightmost) whitespace characters removed.

    TRIM ( string_expression )

string_expression | A string expression, constant, or variable that can be interpreted as a string.

The return value is of the String data type.

You use the UCASE function to return a string with all the lowercase letters converted to uppercase.

    UCASE ( string_expression )

string_expression | A string expression, constant, or variable that can be interpreted as a string.

The return value is of the String data type.

You use the VAL function to return the numeric portion of a string as a value of data type Double. It skips the leading whitespace characters and ignores the trailing non-numeric characters.

    VAL ( string_expression )

string_expression | A string expression, constant, or variable that can be interpreted as a number.

The return value is of the Double data type.

#### Test Functions

You use the ERR function to return an integer representing the most recent Oracle Basic run-time error code. No arguments are required.

    ERR()

The return value is of the Integer data type.

You use the ERL function to return an integer representing the line number where the most recent Oracle Basic run-time error occurred or the closest preceding line number. No arguments are required.

    ERL()

The return value is of the Integer data type.

You use the ISDATE function to determine if its argument can be converted into a value of data type Date.

    ISDATE ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Integer data type(-1 = true, 0 = false).

You use the ISNULL function to determine if its argument has a value of NULL.

    ISNULL ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Integer data type (-1 = true, 0 = false).

You use the ISNUMERIC function to determine if its argument can be converted into a value of a numeric data type.

    ISNUMERIC ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Integer data type (-1 = true, 0 = false).

You use the NVL function to return either the first expression if it is not NULL or the second expression if the first expression evaluates to NULL.

    NVL ( expression1, expression2 )

expression1 | Can be any valid Oracle Basic variable or expression.
expression2 | Can be any valid Oracle Basic variable or expression.

The return value is the same data type as the expression that is returned.

You use the SGN function to determine the sign of a number or numeric expression.

    SGN ( numeric_expression )

numeric_expression | Any valid Oracle Basic number or numeric expression.

The return value is of the Integer data type (1 = numeric_expression is greater than zero, 0 = numeric_expression equals zero, and -1 = numeric_expression is less than zero).

You use the VARTYPE function to determine the data type of the specified expression.

    VARTYPE ( expression )

expression | Can be any valid Oracle Basic variable or expression.

The return value is of the Integer data type (1 = NULL, 3 = Integer or Long, 5 = Single or Double, 7 = Date, 8 = String, and 9 = Object).

#### User Dialog Functions

You use the INPUTBOX function to present the user with a custom dialog box with a message requesting input, which waits for the user to respond and returns the user's response.

    INPUTBOX ( input_message [, [title] [, [default] [, left, top ] ] ] )

input_message | A string to display as a prompt to the user. The maximum length is 255 characters.
title | A string to display as the title of the INPUTBOX. If it is not supplied, nothing is displayed in the title bar.
default | The default value to display as the user input.
left | The horizontal offset from the left edge of the screen.
top | The vertical offset from the top edge of the screen.

The return value is of the String data type. Cancel or Escape keys return a NULL string.

You use the MSGBOX function to display a dialog box containing the specified message, using the optional dlg_options parameters to specify characteristics of the dialog box and displaying the optional dlg_title. The MSGBOX function returns the user's selection of the available buttons.

    MSGBOX [(] message [, dlg_options] [, dlg_title] [)]

message | An expression that evaluates to a string to be displayed in the dialog box.
dlg_options | A numeric expression that indicates the number and type of buttons displayed, the type of icon to display, which button is the default button, and whether the dialog box is application or system modal. The button choices include the following:
| 0—OK button only (default)
| 1—OK and Cancel
| 2—Abort, Retry, and Ignore
| 3—Yes, No, and Cancel
| 4—Yes and No
| 5—Retry and Cancel
| The icon choices include the following:
| 0—No icon (default)
| 16—Stop sign (Hand on Mac)
| 32—Question mark
| 48—Exclamation point
| 64—Information "I"
| The default button definitions include the following:
| 0—First button (default)
| 256—Second button
| 512—Third button
| The modal window choices include the following:
| 0—Application (default)
| 4096—System
dlg_title | A string to place as the title of the dialog box window. If this is omitted, the title bar displays "Oracle Power Objects."
( and ) | Parentheses are not optional if you specify either the dlg_options or dlg_title.

The return value is of the Integer data type (1 = OK, 2 = Cancel (Esc), 3 = Abort, 4 = Retry, 6 = Yes, 7 = No).

### Summary

Oracle Power Objects provides a wide range of constants, commands, and functions for the application developer to use when writing custom code to solve his database application needs. The constants, commands, and functions maintain consistency with ANSI BASIC and provide the necessary object-oriented and SQL-related extensions to ensure that Power Objects is a powerful and flexible database application development environment.