Access as Frontend to Oracle questions - Memo-to-CLOB? View vs. Query?

1. Does anyone have recommendations for books or web resources for
general rules/guidelines/help on doing this? I haven't found a good
"narrower" term for searching usenet or the web that gets me to a
general guide.

2. Speed is an issue. I've been thinking that running stored Access
queries on our distributed network will take longer than simply
calling defined Views in Oracle running on the dedicated Oracle
servers. Any advice here?

3. I have lots of tables where I liberally used "Memo" fields. Some
of these, I'm realizing were just lazy, and I should have made text
columns of a certain size. However, a few, I need to be open ended
for people to type up reports/feedback/etc. Any guidance out there on
the proper equivalent in Oracle? CLOB's? Text with 4000 characters?
(Usage: The total number of these types of fields will be fairly
limited. I anticipate, at most, maybe 10,000 in 5 years at the most.
Many times, these will be blank or just have one or two sentences.
It's only wordy guys like me who'll use lots of space...)

Thus, while not a book on migration to Oracle from Access/Jet, the book
that I've had great success with is _Oracle SQL and PL/SQL Handbook_ by
John Adolph Palinski. Should be available via Amazon. There are
doubtless other great books out there, but this one is what is on my
desk (staring up at me, battered and with pages falling out of the well
used bindings).

The big disadvantage of pass through queries is that they are read only.
Thus if you have a continuous form in which you add records on the
fly, you can't do it the way you would if you had a Jet or linked table
Access query as a recordsource for the form. This leads to more
development consideration (routines for adding records, routines for
updating records, routines for deleting records) that those of us who
also develop in Jet find longer than what we're used to.

Not related to questions, but if your organization is running Oracle,
you will have Oracle admins who will be of help in terms of setting up
table spaces, your schema and such.

I do find the
varchar2 data type (which can be up to 4000 characters long) to be very
useful for most things.

Yeah, I think for my skill level at the moment, I'll have to stay away
from pass through queries and just stick with linked tables. I'd like
to do more, but projects have deadlines, and my mind is only so big...

jonceramic wrote:
>
>>
In my opinion (and Rick Brandt may disagree with me on this), even if
you exclusively use ODBC linked tables (against which you current
Access querydefs will work with no or very little modification
needed), you should still have an understanding of how Oracle SQL
works.
Thus, while not a book on migration to Oracle from Access/Jet, the
book that I've had great success with is _Oracle SQL and PL/SQL
Handbook_ by John Adolph Palinski. Should be available via Amazon. There
are doubtless other great books out there, but this one is what
is on my desk (staring up at me, battered and with pages falling out
of the well used bindings).
>>
My personal preference is pass through queries using Oracle syntax
versus using linked tables and Jet SQL syntax. In a well designed
Oracle database, simple to moderately complex queries in Access
against Oracle tables work just as well and even fractionally faster
than straight Oracle SQL via pass through queries. The key, here,
though is "well designed" Oracle tables, ie, properly indexed and
PK/FK and other constraints defined. Really, to properly design
Oracle tables, you use the same care and consideration you would to
establish good Jet table design.
>
However, sometimes, even in a well designed Oracle database, if you
are putting together a lot of tables in a Select statement, the ODBC
connection can start dropping whole columns of information and
groaning considerably.
>
In my experience, Select clause sub queries (indeed sub queries of any
kind) suck golf balls through a narrow urethra in a linked tables set
up. Not sure what Rck Brandt's comments on this might be.
>
In the situation described in the previous two paragraphs, I found
pass through queries were the cat's meow. Plus, they allowed me to
better talk to my Oracle admins when I was having problems or wanted
them to look at see why a table might be behaving unexpectedly.
>
The big disadvantage of pass through queries is that they are read
only. Thus if you have a continuous form in which you add records on
the fly, you can't do it the way you would if you had a Jet or linked
table Access query as a recordsource for the form. This leads to more
development consideration (routines for adding records, routines for
updating records, routines for deleting records) that those of us who
also develop in Jet find longer than what we're used to.
>
Not related to questions, but if your organization is running Oracle,
you will have Oracle admins who will be of help in terms of setting up
table spaces, your schema and such.
>>
I actually don't have experience with CLOBs, BLOBs or BFILE data
types. My major Oracle app is an older one and uses Longs (of which
you may only have one in any table or view - a "view" BTW, if you're
not sure, is actually the equivalent of a stored Access/Jet query. I
do find the varchar2 data type (which can be up to 4000 characters
long) to be very useful for most things.
>
I guess the thing to do is run a Jet totals query on numbers of
current record for the length of your larger field(s) and go from
there.

jonceramic wrote:>
In my opinion (and Rick Brandt may disagree with me on this), even if
you exclusively use ODBC linked tables (against which you current Access
querydefs will work with no or very little modification needed), you
should still have an understanding of how Oracle SQL works.
>
Thus, while not a book on migration to Oracle from Access/Jet, the book
that I've had great success with is _Oracle SQL and PL/SQL Handbook_ by
John Adolph Palinski. Should be available via Amazon. There are
doubtless other great books out there, but this one is what is on my
desk (staring up at me, battered and with pages falling out of the well
used bindings).
>>
My personal preference is pass through queries using Oracle syntax
versus using linked tables and Jet SQL syntax. In a well designed
Oracle database, simple to moderately complex queries in Access against
Oracle tables work just as well and even fractionally faster than
straight Oracle SQL via pass through queries. The key, here, though is
"well designed" Oracle tables, ie, properly indexed and PK/FK and other
constraints defined. Really, to properly design Oracle tables, you use
the same care and consideration you would to establish good Jet table
design.
>
However, sometimes, even in a well designed Oracle database, if you are
putting together a lot of tables in a Select statement, the ODBC
connection can start dropping whole columns of information and groaning
considerably.
>
In my experience, Select clause sub queries (indeed sub queries of any
kind) suck golf balls through a narrow urethra in a linked tables set
up. Not sure what Rck Brandt's comments on this might be.
>
In the situation described in the previous two paragraphs, I found pass
through queries were the cat's meow. Plus, they allowed me to better
talk to my Oracle admins when I was having problems or wanted them to
look at see why a table might be behaving unexpectedly.
>
The big disadvantage of pass through queries is that they are read only.
Thus if you have a continuous form in which you add records on the
fly, you can't do it the way you would if you had a Jet or linked table
Access query as a recordsource for the form. This leads to more
development consideration (routines for adding records, routines for
updating records, routines for deleting records) that those of us who
also develop in Jet find longer than what we're used to.
>
Not related to questions, but if your organization is running Oracle,
you will have Oracle admins who will be of help in terms of setting up
table spaces, your schema and such.
>>
I actually don't have experience with CLOBs, BLOBs or BFILE data types.
My major Oracle app is an older one and uses Longs (of which you may
only have one in any table or view - a "view" BTW, if you're not sure,
is actually the equivalent of a stored Access/Jet query. I do find the
varchar2 data type (which can be up to 4000 characters long) to be very
useful for most things.
>
I guess the thing to do is run a Jet totals query on numbers of current
record for the length of your larger field(s) and go from there.
>
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me