Skip to main content.

Web Based Programming Tutorials

Homepage | Forum - Join the forum to discuss anything related to programming! | Programming Resources

Oracle Unleashed

Oracle Unleashed ounxefi.htm

Previous Page TOC Next Page Home




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.

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.

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.

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.

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.

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.

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.

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.

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.

Previous Page TOC Next Page Home