Number: TB0006

Availability: As of BuildPro 8.00

Introduction

Some changes are required when converting an existing ISAM application to run with Oracle. This document attempts to identify some of these requirements.
 

Problems

Access to Oracle via Background Processes. For every process which is run in background, insert a database logon routine as the first statement You will need to scan the logic (functions/processes) list for the BACKGRND statement. This list may contain actual process names and/or soft names. For soft names, further scanning will be needed to establish the actual process names.

FILE *NEXT locking: Scan the logic list for FILE *NEXT. Using this list, mark the places where *NEXT is repeatedly tried, if a record is locked. For every such place, set *LOCKTRY to a non zero value when interfaced to Oracle, just before the *NEXT, and reset it to zero after the *NEXT command. NOTE: On Oracle, a non zero *LOCKTRY value means that a locked record will be tried until successful.

INTERNAL file builds. Scan the logic list for INTERNAL data_manager and INTERNAL file_build statements. Insert a database login routine just after the internal data_manager statement. Also add the ‘-l *ENV("USER_ORACLE") to *PASS assignment. This should only be performed when using Oracle. If the application caters for more than one database, this can be validated by using the statement "IF *DEFDBASE = M-Oracle_db THEN".

All Tables (Files) used across a suite of applications must be defined consistently.

FILE *PRVIOUS, *LAST & *UNLOCK. These file operations must be removed as these operations are not supported under a relational database.

FILE I/Os involving alternate record layouts. All File I/Os using alternate record layouts must be changed to use the default record layout.

External Names. External Names of files, indexes and field names should not conflict with the databases reserved work list. A list of reserved words is supplied in this document (see Oracle documentation up-to-date list).

Reserved Word List

The reserved words in this list originate from Oracle, and SQL Server.
 

ABS	ABSOLUTE	ACCESS	ACOS
ACTION	ADA	ADD	ADDDATE
ADDTIME	ALL	ALLOCATE	ALPHA
ALPHANUMERIC	ALTER	AND	ANY
ARE	ARRAYLEN	AS	ASC
ASCII	ASSERTION	ASIN	AT
ATAN	ATAN2	AUDIT	AUTHORIZATION
AUTOINCREMENT	AVG	BEGIN	BETWEEN
BINARY	BIT	BOOLEAN	BOTH
BREAK	BROWSE	BULK	BY
BYTE	CALL	CASCADE	CASCADED
CASE	CAST	CATALOG	CEIL
CEILING	CHAR	CHARACTER	CHARACTER_LENGTH
CHAR_CONVERT	CHAR_LENGTH	CHECK	CHECKPOINT
CHR	CLOSE	CLUSTER	CLUSTERED
COALESCE	COBOL	COLLATE	COLLATION
COLUMN	COMMENT	COMMIT	COMMITTED
COMPRESS	COMPUTE	CONCAT	CONFIRM
CONNECT	CONNECTED	CONNECTION	CONSTRAINT
CONSTRAINTS	CONTINUE	CONTROLROW	CONVERT
CORRESPONDING	COS	COSH	COT
COUNT	COUNTER	CREATE	CROSS
CURDATE	CURRENCY	CURRENT	CURRENT_DATE
CURRENT_TIME	CURRENT_TIMESTAMP	CURRENT_USER	CURSOR
CURTIME	DATABASE	DATE	DATEDIFF
DATETIME	DAY	DAYNAME	DAYOFMONTH
DAYOFWEEK	DAYOFYEAR	DBA	DBCC
DBSPACE	DBYTE	DEALLOCATE	DEC
DECIMAL	DECLARE	DECODE	DEFAULT
DEFERRABLE	DEFERRED	DEGREES	DELETE
DESC	DESCRIBE	DESCRIPTOR	DIAGNOSTICS
DIGITS	DIRECT	DISALLOW	DISCONNECT
DISK	DISTINCT	DISTINCTROW	DO
DOMAIN	DOUBLE	DROP	DUMMY
DUMP	EBCDIC	ELSE	ELSEIF
ENCRYPTED	END	ENDIF	END_EXEC
EQV	ERRLVL	ERROREXIT	ESCAPE
EXCEPT	EXCEPTION	EXCLUSIVE	EXEC
EXECUTE	EXISTS	EXIT	EXP
EXPAND	EXPIREDATE	EXTERNAL	EXTRACT
FALSE	FETCH	FILE	FILLFACTOR
FIRST	FIXED	FLOAT	FLOAT4
FLOAT8	FLOOR	FLOPPY	FOR
FOREIGN	FORTRAN	FOUND	FROM
FULL	GENERAL	GET	GLOBAL
GO	GOTO	GRANT	GRAPHIC
GREATEST	GROUP	GUID	HAVING
HEX	HEXTORAW	HOLDLOCK	HOUR
IDENTIFIED	IDENTITY	IDENTITYCOL	IDENTITY_INSERT
IEEEDOUBLE	IEEESINGLE	IF	IFNULL
IGNORE	IMMEDIATE	IMP	IN
INCREMENT	INDEX	INDICATOR	INITCAP
INITIAL	INITIALLY	INNER	INOUT
INPUT	INSENSITIVE	INSERT	INSTEAD
INT	INTEGER	INTEGER1	INTEGER2
INTEGER4	INTERNAL	INTERSECT	INTERVAL
INTO	IS	ISOLATION	JOIN
KEY	KILL	LANGUAGE	LAST
LCASE	LEADING	LEAST	LEFT
LENGTH	LEVEL	LFILL	LIKE
LINENO	LINK	LIST	LN
LOAD	LOCAL	LOCALSYSDBA	LOCATE
LOCK	LOG	LOG10	LOGICAL
LOGICAL1	LONG	LONGBINARY	LONGFILE
LONGTEXT	LOWER	LPAD	LTRIM
MAKEDATE	MAKETIME	MAPCHAR	MATCH
MAX	MAXEXTENTS	MBCS	MEMBERSHIP
MEMO	MESSAGE	MICROSEC	MIN
MINUS	MINUTE	MIRROREXIT	MOD
MODE	MODIFY	MODULE	MONEY
MONTH	MONTHNAME	NAMED	NAMES
NATIONAL	NATURAL	NCHAR	NEXT
NO	NOAUDIT	NOCHECK	NOCOMPRESS
NOHOLDLOCK	NONCLUSTERED	NOROUND	NOT
NOTFOUND	NOW	NOWAIT	NULL
NULLIF	NUM	NUMBER	NUMERIC
OBID	OBJECT	OCTET_LENGTH	OF
OFF	OFFLINE	OFFSETS	OLEOBJECT
ON	ONCE	ONLINE	ONLY
OPEN	OPTION	OPTIONS	OR
ORDER	OTHERS	OUT	OUTER
OUTPUT	OVER	OVERLAPS	OWNERACCESS
PACKED	PAD	PARAMETERS	PARTIAL
PASCAL	PASSTHROUGH	PCTFREE	PERCENT
PERM	PERMANENT	PI	PIPE
PIVOT	PLAN	PLI	POSITION
POWER	PRECISION	PREPARE	PRESERVE
PREV	PRIMARY	PRINT	PRIOR
PRIVILEGES	PROC	PROCEDURE	PROCESSEXIT
PUBLIC	RADIANS	RAISERROR	RAW
READ	READTEXT	REAL	RECONFIGURE
REFERENCE	REFERENCED	REFERENCES	REJECT
RELATIVE	RELEASE	REMOTE	RENAME
REPEATABLE	REPLACE	REPLICATION	RFILL
RESOURCE	RESTRICT	RETAINDAYS	RETURN
REVOKE	RIGHT	ROLLBACK	ROUND
ROW	ROWCOUNT	ROWID	ROWLABEL
ROWNO	ROWNUM	ROWS	RPAD
RTRIM	RULE	SAVE	SAVEPOINT
SCHEDULE	SCHEMA	SCROLL	SECOND
SECTION	SELECT	SELUPD	SERIAL
SERIALIZABLE	SESSION	SESSION_USER	SET
SETUSER	SHARE	SHORT	SHOW
SHUTDOWN	SIGN	SIN	SINGLE
SINH	SIZE	SMALLINT	SOME
SOUNDEX	SPACE	SQL	SQLBUF
SQLCODE	SQLERROR	SQRT	SQLSTATE
STAMP	START	STATISTICS	STDEV
STDEVP	STOP	STRING	SUBDATE
SUBSTR	SUBSTRING	SUBTIME	SUBTRANS
SUBTRANSACTION	SUCCESSFUL	SUM	SYNCHRONIZE
SYNONYM	SYNTAX_ERROR	SYSDATE	SYSDBA
SYSTEM_USER	TABLE	TABLEID	TAN
TANH	TAPE	TEMP	TEMPORARY
TEXT	TEXTSIZE	THEN	TIME
TIMEDIFF	TIMESTAMP	TIMEZONE	TIMEZONE_HOUR
TIMEZONE_MINUTE	TINYINT	TO	TOIDENTIFIER
TOP	TRAILING	TRAN	TRANSACTION
TRANSFORM	TRANSLATE	TRANSLATION	TRIGGER
TRIM	TRUE	TRUNC	TRUNCATE
TSEQUAL	UCASE	UID	UNCOMMITTED
UNICODE	UNION	UNIQUE	UNKNOWN
UPDATE	UPDATETEXT	UPPER	USAGE
USE	USER	USERGROUP	USING
UTCDATE	UTCDIFF	VALIDATE	VALUE
VALUES	VAR	VARBINARY	VARCHAR
VARCHAR2	VARGRAPHIC	VARIABLE	VARIANCE
VARP	VARYING	VIEW	VOLUME
WAITFOR	WEEK	WEEKOFYEAR	WHEN
WHENEVER	WHERE	WHILE	WITH
WORK	WRITE	WRITETEXT	XOR
YEAR	YESNO	ZONE	ZONED