List of problems with Oracle

Hi,

Here's a list of problems we encountered using Pro*ada with Oracle 7 on VAX VMS, using DEC Ada.

Some time ago an Oracle employee wrote to me that Oracle hardly ever receives feedback from the Ada community, and therefore he suspected the Ada community to be a 'self-sufficient lot'.

To change this, in the hope that it will improve the Oracle products, we compiled a list of problems and peculiarities using Oracle with DEC Ada.

We wasted some considerable time finding causes and solutions to the following problems. I hope that by providing this list Pro*Ada users will save time in the future.

If you are a Pro*Ada user, please post your experiences. We and Oracle Corp. will learn from it.

Pagenumbers refer to the "Programmer's Guide to the Pro*ADA Precompiler" version 1.5.

The list:

• p. 3-38 Tasking: The manual suggests here that using tasks with timeslicing and Oracle is possible. In reality, this is NOT possible. At least not on our machine, where it yields Access Violation errors.
• p. 2-10: The book often uses Oracle functions before they are explained. Some are not explained at all. Example: What is INITCAP? What is upper?
• The new Oracle manuals smell badly. Though the format is quite handy, the smell really keeps me from looking up things in the manuals. The subjective obnoxiousness seems to vary from person to person.
• p. 7-3 and 7-5: Is it: ORACLE_TYPES.ORACLE.TYPE or ORACLE_TYPES.ORACLE_TYPE?
• p 9-10 and 9-11: Is it: EXEC SQL OPEN cursor USING DESCRIPTOR BIND_DESC; or EXEC SQL OPEN cursor USING BIND DESCRIPTOR BIND_DESC; ? These statements are taken from example programs. This means that example programs have not been tested. Bad examples.

About the precompiler:

• While there is automatic conversion from nearly all standard Oracle types to Ada types, there is none for the date and enumeration types. The implementation should be trivial. Oracle's DATE should be automatically converted to CALENDAR.TIME and vice versa. Ada's enumeration types should automatically be converted to Oracle's NUMBER and vice versa.
• When 'semantic checking' is activated, DELETE FROM <non-existent table> is recognized as wrong, but INSERT foo INTO <non-existent table> is not. This inconsistency makes one doubt that a precompilation without error messages really is successful.
• Error messages are put in a .LIS file or sent to the terminal. It is difficult to correct your programs this way; you have to look up the line number and finding the correct line in your .PAD file. Since many people use LSE, the precompiler should be able to generate the error messages into a diagnostics (.DIA) file when the /DIAGNOSTICS flag is given. This would also be very easy to implement. The precompiler should also call the Ada compiler after successful precompilation. If errors in the Ada compilation occur, the linenumbers should be translated to line number in the original .PAD file. As a temporary solution, we wrote a utility that translates the error messages in a .LIS file to a .DIA file. This utility is available on request: send mail to gijs_at_mbase97.hacktic.nl.
• Our beginner's mistake: not being logged in, was very primitively fed back to us by presenting the message 'Statement not parsed'. When we added some error-raising code in the generated code, we found out what was wrong. Then Oracle DOES seem to be able to issue a message like 'Not logged in'. For the same reason, erroneous SQL statements, that were precompiled correctly, would yield 'statement not parsed' instead of a more useful message. Inserting exception-raising code would reveal the real cause of the statement not being parsed.

	Note that this manual insertion takes much time: precompilation and 
	compilation are quite slow on a VAX.

	The placement of error-raising code by the precompiler seems to 
	wrong, or at least not optimal.

	After a while, we changed the Oracle library unit 'ORACLE__SQL', so
	that proper error messages are generated automatically. It outputs
	the message with TEXT_IO, so it is only suitable at development time.



-	When oreclen < ireclen, we encountered the following problem when using


	long comment lines:
 
	-------------------------------
	>proada iname=prog.pad sqlcheck=semantics userid=/ ireclen=255 lreclen=255 lname=lis.lis
	Pro*Ada: Release 1.5.6.2.0 - Production on Fri Aug 13 15:35:40 1993

	Copyright (c) Oracle Corporation 1979, 1992.  All rights reserved.


	Precompiling prog.pad
	Allocated memory block has been overfilled.

	PRO* has detected an internal error (2).
	Please contact Oracle Corporation Customer Support.
	-------------------------------

	First we didn't know about the oreclen>=ireclen rule, so it took us a 
	long time to figure out what caused this message. 

	The manual states that oreclen must be set to at least the ireclen,
	but I would have preferred it if this rule were somehow built-in to the
	precompiler, so that:
	a: it could generate an error message, or
	b: it could set oreclen itself.



-	Functions used in place of host variables only work when they have zero


	or one parameter. Something goes wrong at the comma separating the
	parameters. This also means you cannot use more-dimensional arrays
	as host-variables.
	It seems that there is no correct Ada parser in the precompiler.



-	In the following where clause:


	'WHERE STRING_COLUMN = :SOME_ADA_INTEGER'
	the STRING_COLUMN is apparently converted to an integer and subsequently 
	compared to the SOME_ADA_INTEGER. This yields a NUMBER_ERROR if the
	string does not contain a number, even though it is clear that the
	fields are not equal.
	It would be better to convert the INTEGER to a STRING, and then compare
	the fields.



-	When you forget a closing parenthesis in an embedded SQL statement,


	the preprocessor often hangs, eating much memory and CPU time.
	Another case of a strange parser.



-	From a user's point of view, I find it very strange that a bind 


	descriptor must not be prefixed by a ':', even though they are 
	declared as an Ada variable.

About PL/SQL:

• A procedure declaration only worked with <CR>, <LF>, spaces and tabs in the right place. In these days, these space characters should be interchangeable, especially because PL/SQL is modelled after Ada.

Conclusions:

• A few (easily implementable) additional features and bugfixes would make life a lot easier.
• The parsers for both Ada and SQL constructs contain bugs.
• The manual contains untested example programs, which is unacceptable.

A reaction from Oracle Corp. would be appreciated.

-- 
Gijs Bok (gijs_at_mbase97.hacktic.nl)
Listen to M-BASE music!