Point and click queries.

            Any data source.

                        Any computer.

                                    Data server.

                                                Free.


        Oracle Database Server
                ( Please scroll down. )

Disclaimer

Brand names are addressed only as a service to CoreReader users. See the source specifics page for details.

To add to these notes or to add your favorite data source, see instructions on the source specifics page.

Source

The CoreReader development environment includes an Oracle Enterprise Server version 8.1.7 running on a Ms. Win2k Server, and using the native Oracle data sockets.

Running With Oracle

This is recommended reading if you are new to Oracle !

Oracle is a great product for any large company that has a dedicated DBA staff, but it has some peculiarities.

Oracle has a unique and interesting implementation of the relational database concept. It is not incorrect and is not problematic, but if one does not know it and assumes that it is like other implementations, it will not make sense.

CoreReader presents Oracle data in a form similar to other servers by handling each Oracle schema as a separate database.

Formatting SQL

CoreReader allows SQL statements to be formatted with carriage returns and tabs so humans can read them. Some versions of Oracle cannot handle control characters so they cause problems when they receive formatted SQL. Remove all formatting from the statements before sending them to Oracle unless you know that your version can handle them.

DSN Driver Name

When using a DSN connection, do not enter the Oracle driver name. When the Oracle driver name is entered, the Oracle data socket ignores the DSN and requires a complete connection string even if a DSN is requested.

Limiting The Data Return

Caution!

Oracle provides no standard safeguard to limit the size of a data set. If you inadvertently ask for a very large dataset, Oracle will stomp on your little workstation.

When that happens, the problem is sometimes compounded by the Oracle sub-systems which can continue the crash into the rest of an infrastructure. If that frightens you, that is the intent. However, reasonable precautions will prevail.

Load Speed

Loading an Oracle database can be very slow. Be prepared for a five or ten minute load of a company's database.

This is caused by the Oracle implementation of the database concept, which is not incorrect, but is different from most others. Its relational construct implementation conforms as well as any other to the EF Codd model, but the basic database concept is mapped into the computing environment slightly differently. It is not a bad implementation, but it will cause a slow load of a twenty-thousand-table database.

This problem can be handled through the CoreReader configuration. See the Database Abstraction section of the documentation for help.

Data Socket Hangs

If CoreReader happens to be connecting to an Oracle server during a server crash, the data socket locks onto CoreReader and sometimes takes CoreReader down with it.

I've not yet found a way to protect CoreReader from Oracle because of the socket lock. Perhaps newer versions of the socket will behave better.

Persistent Connection Hangs

When an Oracle server locks up because it has used up the RAM on its server, the data socket sometimes locks into CoreReader. When this happens, problems with Oracle connections will persist until the server is cleared.

DBA Login Warning

Be careful of which login is used. Connecting and loading with a DBA login can inundate a PC with twenty or thirty thousand tables although you ask for only one database. For assistance with this problem, refer to the Database Abstraction section.

Data Type Peculiarities

Be aware of the peculiarities of Oracle's data types. Because Oracle has problems with some data types, Oracle data sockets should be used if they are available.

Also, Oracle's query ability is limited by some of its data types. If you are not familiar with Oracle, you will learn that you need to be aware of the types of data in each table. Certain Oracle data types combined with certain queries will cause the queries to fail.

A Major Join Shortcoming

Oracle cannot do industry standard joins. It is limited to equal joins only.

Also, Oracle uses non-standard plus operators in its joins. If you are new to Oracle, you must be aware of this deviant behavior. However, CoreReader will support it. Enter the non-standard operators into the fields which contain the names of the join columns. For example: columnname (+) equals columnname

This is so dumb that perhaps it will be rectified in future versions of Oracle.

The "AS" keyword

Do not enable the SQL 'as' keyword because Oracle is not capable of understanding it.

More Data Type Problems

! Do not use an ODBC connection to query the Oracle LONG, CLOB, and BLOB datatypes. Those Oracle datatypes cause catastrophic failure of the Oracle ODBC data socket. When that occurs, the data socket locks into CoreReader and cannot be cleared. It sometimes requires a machine reset to clear it! The Oracle OLEDB data socket does not seem to have this problem. ( I am still looking for a way to insulate CoreReader from ill behaved data sockets. )

Inaccessible Tables

I personally do not consider this a shortcoming, although it can be problematic. The preturn of a table name in an Oracle load does not necessarily mean that it is available to the connection. You may build a query with a table that is listed on the screen, only to find that Oracle cannot see it!

The problem seems to be that the Oracle schema construct canonical boundary is intended to exhibit transparency without permeability.

For some purposes, this could be a fantastically useful feature. This may merit study as a new tool within the larger theoretical construct. Very interesting behavior, indeed.

In any case, schema boundaries may be rendered permeable through the use of the CoreReader prepended segmented qualifiers when not obviated by the security matrix. See the Database Abstraction section of the documentation for details.

This web site is maintained with Notepad.

Copyright 2001 - 2010 John Ragan.