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

[jonceramic]

Hi All,

I started developing in Access, and people took notice and so we're
starting to migrate into our corporate's bigger Oracle system.

I'll still be using my developed Access front ends, but will be
migrating my back ends to Oracle ODBC.

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...)

Thanks,

Jon

[Tim Marshall]

jonceramic wrote:

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.

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).

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?

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.

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...)

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

[jonceramic]

On Feb 1, 1:19 pm, Tim Marshall
<[e-mail]:

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).

Our Developer had that on her lap this morning as I talked to her! :)

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.

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...

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.

Most definitely.

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

In talking to my admin, varchar2 is definitely the way to go because
of the "var" part. No more worrying about space because of the "var"
part.

Many thanks, Tim.

[Tim Marshall]

jonceramic wrote:

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...

That's how I started and it did very well for me, though I did have some
issues.

You asked about views and I didn't really comment on them. here's my
experience:

Views can be linked to as if they were Oracle tables. So if you find
something a bit sticky or doesn't quite give you the performance you
want in a bunch of linked tables set up in an Access query, you can talk
to your Oracle admin about setting up a view for you in Oracle that
gives you the results you want and then linking directly to the view.

In terms of speed - as I said, I use exclusively PTQs. I have found on
small to moderate size databases (less than a million records) that I
don't have a noticeable difference in speed when I constuct my oracle
SQL via VBA without using views from doing the same sort of queries
using views.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me

[Rick Brandt]

Tim Marshall wrote:

jonceramic wrote:

>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.

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).

>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?

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.

>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...)

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.

I agree with everything you say here. Passthroughs will be better any time
you are joining two or more tables on the server. The use of links is
mostly when you need to edit the data. Querying a single table is when I
have found that an Access query on a link is usually just as fast as a
passthrough. I qualify that by adding that my experience has been with SQL
Server and UDB400. Oracle might be a completely different experience in
these areas.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[dreadnought8]

There's also good free online documentation at:
http://www.oracle.com/pls/db102/homepage?remark=tahiti
You might also be interested in SQL*PLUS, the Oracle command line
utility that lets you execute SQL in a command line window for small
ad hoc queries.
I've been doing Access/Oracle for the last 6 months (after years of
Acces/Jet/SQL Server);
Oracle SQL is very powerful. PL/SQL is a great procedural language to
work in and to my mind much easier to use than T-SQL.
Good luck
Terry Bell

On Feb 2, 6:19 am, Tim Marshall
<[e-mail]:

jonceramic wrote:

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.

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).

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?

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.

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...)

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