• 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

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.

Table E.2. Constants for control data types.

Value

Constant

Description

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

Table E.4. Constants for special values.

Value

Constant

Description

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

Value

Constant

Description

Table E.6. Constants for text justification.

Value

Constant

Description

Table E.7. Constants for windows.

Value

Constant

Description

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

Value

Constant

Description

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}

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 ...]

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 ... ]

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

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]...]

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]...]

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 ... ]

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] ...]

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

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]...]

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]

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 }

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

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 [)]]

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

CHDRIVE drive

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

FILL filename

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

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

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

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

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

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

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

RMDIR dir_path

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 ...] ]

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

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 ...]

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

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]

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 ...]

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

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

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

WIDTH # filenum, line_width

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 ...]

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

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

RANDOMIZE [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] [)]

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 ) ]

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]

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 }

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

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: }

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: }

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

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

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

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

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 }

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

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

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 )

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 )

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 )

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 )

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 )

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 )

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] )

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] )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 [)]]

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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] )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 ...] )

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 )

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 ...] )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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] )

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 )

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 )

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 )

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 } )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 )

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 ] ] ] )

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] [)]

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.