OpenROAD

Frequently Asked Questions (FAQ)

Access to Non-Ingres Databases

Page Last updated: 24th May 1998

Archived at ftp.naiua.org

This document is COPYRIGHTED to control its distribution and to prevent it from being used for profit. PLEASE DO NOT DISTRIBUTE IT WITHOUT THIS DISCLAIMER OR THE COPYRIGHT NOTICE. Replication of this document by any means is explicitly encouraged, provided that the content is not altered in any way.

Disclaimer

This document is not an official CA product information release. They know nothing of its contents. Well, I hope they know something about the subject, but you know what I mean. It is a voluntary effort by many contributors, co-ordinated by me.

Their respective employers are in no way responsible for the content of this FAQ. Nothing in this FAQ should be regarded as true. Any item of information may become obsolete or out of date at any time. Information about undocumented features should always be regarded as potentially harmful.

The co-ordinator, contributors, and distributors of this document make no representations as to the correctness of the information contained herein, and make no promises to correct any errors or omissions. You use this information at your own peril.

Pat McGibbon pat@megadata.demon.co.uk

MegaData Ltd.,

Dairy Cottage,

High Street, MeonStoke Tel: 0(044)1489 877887

Southampton SO32 3NH

UK

Access to Oracle: how to & experiences

There follows a series of hints and tips for converting between Ingres and Oracle as the target database for OpenROAD applications. It was originally posted on comp.databases.ingres by

Graham Bolton, Principal Consultant

Elegant Relational Development (ERD) bv

Hoogstraat 125a, NL-3111 HD SCHIEDAM, The Netherlands

Tel: +31.10.2732233, Fax: +31.10.2731114

INTRODUCTION

One of ERD's clients had seen our system generator, Elegance, in action, generating OpenROAD applications using an INGRES database. They had one problem: they are an Oracle shop. (Asked which version, they replied "an average of six": they have Oracle 5, 6 and 7 in production!)

They understood the concepts upon which Elegance is founded and wanted to use it to generate GUIs to work with Oracle. The OpenROAD Driver to Oracle was not yet on the market, but the client realised that OpenROAD was worth waiting for.

They chose to rent Ingres to serve as their development database until we were able to provide them with our Oracle offering. They were able to build up the specifications of their system in the ERD Data Dictionary resident in a CA-INGRES 6.4 database, testing the resulting application as they worked, with the knowledge that we would move them to ORACLE within three months of the Driver to Oracle coming onto the market.

This document contains a translation of the notes made by the development team in the two months which they spent changing the code generator to speak native Oracle SQL instead of native Ingres SQL. We do not use Open SQL, the subset of Ingres SQL, since it prevents the use of the better features of both databases. The fact that rules and procedures in Ingres do not translate one-to-one to triggers and functions in Oracle is not a good reason to avoid their use!

TRANSLATION OF DEVELOPMENT TEAM NOTES

Copy function
There is no equivalent for the Ingres copy() statement in Oracle. We had to hand build file handling statements for each table in the ERD Data Dictionary in order to implement our meta-data import and export functions. The Oracle Data Loader (ODL) is a separate utility and its functionality is not available via the OpenROAD SQL connection.
Datatype conversion
While we were able to find equivalents for the various we still had some problems with datatype conversion. For example, conversion in INGRES of INT2(1,000,000) does not give an error but the result is incorrect - Oracle does inform you that the value is too large.

If you want to write portable applications be careful. If you have used a smallint in INGRES, you have a range of -32768 to 32767. In Oracle you have to use the datatype number(6), so your users can then use a value of 40000! We explicitly check the lower- and upperbound so that the data in Oracle could be exported back to Ingres.
Date 'today' and 'now', DBMSINFO()
Use for "date('now')" the Oracle function "trunc(sysdate)"

If you want the client time you still can use "date('now')"
SELECT sysdate FROM dual
select TRUNC(sysdate) FROM dual
Dbmsinfo('dba'):
via user_role_privs with granted_role='DBA'
Dbmsinfo('database'):
No idea! Any suggestions?
Dbmsinfo('user')
SELECT user FROM dual /* or */

SELECT username FROM user_users
Naming of variables
In database procedures we gave variables the same name as the corresponding columns of tables. With Oracle this is a problem. So we have added p$ before all parameter names.
NULL value with strings
An empty string ('') is not allowed in Oracle, it will be saved as a NULL. If you want to look for an empty string in Oracle, use NULL or the NVL function. We also use the statement IFNULL(<expression>,'') in Ingres: the Oracle equivalent NVL(<column>,'') is has no effect because if the column is NULL the empty string will be used and an empty string is NULL.
Patterns
Selects with character matching patterns are not possible (e.g. like '0\[1-9\]' escape '\'; we have found no equivalent for this.
Raise_application_error
The call of raise_application_error stops a function or procedure. We use a return value of -1 to communicate to the client that we had an error. This is not possible in Oracle. So we initialize an in/out parameter to a value of -1 and set it to 1 at the end of the procedure if everything went well.

Return value

It is not possible to return a value with a database procedure, use instead a function.

INGRES:

create procedure employee$del(
....
return 1;
end;

Oracle:

create or replace function employee$del(
...
return 1;
end employee$del;

Reserved words
The set of reserved words differs from INGRES. This makes life interesting!
String and date's with NULL values
INGRES sees a difference between a string (or date) with the value '' and NULL. Oracle sees all '' values as NULL.
Special character "@"
The character "@" is reserved for dblink in Oracle. So if you want to use these characters for a table name you must delimit the name with quotes (").
Statements in Oracle
After each statement you must add a semicolumn (;), also after an endif or endwhile in a database procedure. In Ingres, the semicolon acts as a statement seperator, in Oracle as a statement terminator.

INGRES:
```
if iirowcount != 0 then
raise error 1 'There are still Employees, Department not deleted'
endif;
```
Oracle:
```
if sql%rowcount!= 1 then
rollback work;
raise_application_error(
-2000, 'There are still Employees, Department not deleted');
endif;
```
String concatenation
Instead of using '+' for string concatenation use '||'.
Session
In Oracle you can change the session variables with 'alter profile' and 'alter session'. The session variables are the CPU time per call and session, the connect time per session and trace facility. A user has always a profile, starting with a default.

Setting of the total CPU time per session

ALTER PROFILE <profile>
LIMIT cpu_per_session { <integer> | UNLIMITED | DEFAULT }

Setting of the total CPU time per call (fetch, execute)

ALTER PROFILE <profile>
LIMIT cpu_per_call { <integer> | UNLIMITED | DEFAULT }

Setting of total connection time

ALTER PROFILE <profile>
LIMIT connect_time { <integer> | UNLIMITED | DEFAULT }

Setting of maximum idle connection-time

ALTER PROFILE <profile>
LIMIT idle_time { <integer> | UNLIMITED | DEFAULT }

Setting of maximum read of data blocks per call

ALTER PROFILE <profile>
LIMIT logical_reads_per_call { <integer> | UNLIMITED | DEFAULT }

Setting of maximum cost per session:

ALTER PROFILE <profile> LIMIT composite_limit { <integer> | UNLIMITED | DEFAULT }

On/off of the trace facility

ALTER SESSION SET sql_trace = { TRUE | FALSE }
/* trace info does NOT appear in the trace window */

System catalogs
The Oracle system catalogs of course are quite different to those of Ingres:
- Your own tables: USER_TABLES
- Their columns: USER_TAB_COLUMNS

Accessible functions/procedures:

ALL_OBJECTS where object_type='{ PROCEDURE | FUNCTION }'

Information of users in the database:
```
ALL_USER
```
Use of newline in scripts
When you create database procedures or functions dynamically using a text-string be careful. Between "begin" and "end" it is not possible to use special characters like HC_NEWLINE. In all other objects you are free to use them (e.g. in the value parameters of the INSERT statement).

Oracle files interpreted with SQL+ must end with a string with the character '/'.
Compile errors with database objects
After the creation of database objects they must be checked. Not all errors will result in compilation errors, so you do not know if your objects will work! Check them with a select on the view "user_objects" with the status != 'VALID'.

Problems can occur with functions/procedures which call each other. If you have a main procedure which calls a child procedure and you redefine the child procedure, the master procedure will become invalid.
Create or replace
In Oracle you do not have to drop a function/procedure explicitly in order to change it. You can use the statement CREATE OR REPLACE FUCTION <function>

Parameter passing

Parameters are not passed by name, so you must be careful to put the parameters in the right order (just like in C!).

INGRES:

create rule employee#r1
after insert or update
on employee
execute procedure department#reg
( number = new.number,
message = 'Department' );

Oracle

create trigger employee#a
before insert or update
on medeweker
for each row
begin
...
department#reg(:new.number, 'Department');
end;

Parameter passing
With a function/procedure you can pass parameters. We are used to specify all necessary parameters with 'not null not default'. With Oracle you have to declare them with 'IN'.
Synonyms
The use of synonyms can be useful, you do not have to specify the user. Do not forget to remove the synonym with a drop table.
Table owner
Like in INGRES each table has a table owner. The principle is the same. Oracle has an exception. If you want to retrieve information from an other non-dba user you have to add the owner of the table in the from clause (FROM 'owner.table', see the "User's Guide" of the Oracle7 driver).
iirowcount and iierrornumber in database procedures
After an database action we use the following check. First check rowcount, if it is < 0 we have a problem. After this we check errornumber, if errornumber != 0Ingres has already sent a message to the client, so we do not.

This is completely different with Oracle because SQLCODE is only used in the exception part but with a user defined exception we always find SQLCODE of 1.
Raise_application_error
Raise_application_error(<errornumber>, <errortext>) is what we have used instead of raise error. The errornumber must lie between -20999 and -20000 and <errortext> has a maximum length of 512 bytes. The procedure will only perform a rollback for the actions in the function/procedure where it is used.
Rollback with an error
You can use the procedure raise_application_error() from Oracle to send an errormessage (and an errornumber) to the client. The action of the procedure is not a complete ROLLBACK, only the statements within the function/procedure are rolled back! So if you want to be sure that your complete transaction will be rolled back you have to put an explicit ROLLBACK before a call to the procedure raise_application_error;
Rollback with drop table
A drop table can not be rolled back in Oracle, so be careful!
Triggers
With Oracle you can use before and after triggers. With each type of action (insert, update or delete) you can only define one before or after trigger. In tests we found that even if you define a before trigger on an INSERT, the INSERT will be performed first. So you cannot write a before insert trigger to check for duplicate key on INSERT because Oracle will give its own error message before your trigger can.

It is possible to call several procedures within one trigger. Within a procedure it is possible to call an other procedure.
Execute immediate
Be careful not to end strings passed to execute immediate with ';' (See the "User's Guide" of the Oracle7)
Inquire_ingres, Inquire_sql: do not use them
We were used (of course) to using inquire_sql. In OpenROAD it is better to use the attribute DBMSError. This object also has other methods and attributes like Errornumber, database, rowcount, commitwork(), rollbackwork(), connect(), opennewconnection() en disconnect().

If you use these you will be more database independent without incurring significant overhead.
Return value
In a strange way we always get a return value 1 of a database function. We use a value != 1 to indicate that we have encountered an error. Now we use the DMBSError attribute of the databasesession object rather than the return code.
SQL statements in code
Try to avoid the use of functions in SQL statements within your 4GL code. Use the functions within OpenROAD.
Trace of queries
If you want to trace queries into a file, use the parameter IIQL_QUERY_LOG = <filename> in config.ing of OpenROAD (See the "User's Guide" of the Oracle7 driver).
Values from the database
The values retrieved from the database seems to be all right. You must be careful with char length (255) and date intervals (See the "User's Guide" of the Oracle7 driver).

Books

Learn Oracle 8i

Oracle8i Tips & Techniques

Oracle PL/SQL Programming: Guide to Oracle8i Features

HTMLised by Don Simonetta