non-DBA FAQ ver 0.3
Date: 23 Mar 93 00:26:12 GMT
Message-ID: <dtb.732846372_at_otto>
ORACLE NON-DBA FAQ (Frequently Asked Questions) - Version 0.3
0. Introduction
0.0. Contents
0. Introduction
0.0. Contents
0.1. What is this document ?
0.2. Who has contributed to this document ?
0.3. How is this document formatted ?
0.4. What biases does the primary author have ?
1. General Questions
1.1. What is Oracle ?
1.2. What are the advantages of Oracle ?
1.3. What are potential disadvantages of Oracle ?
1.4. What is SQL ?
1.5. What is PL/SQL ?
1.6. What products are available from Oracle ?
1.7. What Public Domain interfaces are there ?
1.8. What third party interfaces are available ?
1.9. How portable are Oracle applications to other RDBMS ?
1.10. What Query Optimisers are there ?
1.11. Is there an anonymous FTP site for Oracle stuff ?
2. SQL Questions
2.1. How can I avoid a divide by zero error ?
2.2. Can I update tables from other tables?
2.3. Can I remove duplicate rows?
2.4. Can I update using a view ?
2.5. Are views automatically updated when I update base tables ?
2.6. Should we use complex views that cruel performance ?
2.7. Can I implement tree structured queries ?
2.8. How can get information on the row based on group information.
2.9. How can I get a name for a temporary table that will not clash ?
2.10. How can I find out about what tables, columns and indices
there are ?
2.11. Is there a formatter for SQL statements ?
2.12. What is the DUAL table ?
2.13. What is the difference between CHAR and VARCHAR ?
3. SQL*PLUS QUESTIONS
3.1. How can I control the startup configuration of SQL*Plus ?
3.2. Can I get a column value into a substitution variable ?
3.3. How can I change the (hated default) editor to my favorite ?
3.4. What is the difference between & and && ?
3.5. What is the difference between "host" and "!" ?
3.6. What can I not use a table name in a substitution variable ?
4. SQL*FORMS 3 QUESTIONS
4.1. How can I get a list of values from a hard coded list ?
4.2. Can I edit SQL*Forms code with my text editor ?
4.3. Can I edit SQL*Forms code by updating the database ?
5. PRO*C QUESTIONS
5.1. Why are my C variables overwritten ?
5.2. Can I use C preprocessor definitions for VARCHAR size ?
5.3. What can I do about "line too long" errors with version control?
6. UNIX QUESTIONS
6.1. Can I create a compressed export on the fly without needing to
have the space for both the export file and the compressed file?
6.2. How can I prevent trailing spaces in a spooled report ?
6.3. How can I get an environmental variable into SQL*Plus variables?
6.4. Can I pipe stuff through SQL*Plus ?
6.5. Why do Pro*C compiles or programs crash on my Sun ?
6.6. How can I find a lost Oracle export file ?
7. MISC QUESTIONS
7.1. How can I change the table storage parameters from an export
file?
7.2. What makes the best Oracle server for a network ?
7.3. How can I implement version control ?
0.1. What is this document ?
This is an early cut of a FAQ for the Oracle newsgroup
(comp.databases.oracle) but does not include DBA issues.
Database Administration issues are covered in a separate FAQ
which has been posted to the net. It also does not include
details on oraperl, a separate document by Kevin Stock.
Daniel Druker has also posted a feature summary of V7 and
also a V6 to V7 comparison. These are not included, and I
hope he posts these every now and again.
As yet, this FAQ covers a range of Oracle products and issues.
It is expected that as it grows, there will be a need for
separate FAQ's for the various products to be separated from
this FAQ.
While there are only a few basic questions and tips included in
this document so far, it is worthwhile getting the FAQ started.
Currently this document addresses Oracle 6 and 7.
Any errors are mine. Mea culpa. Mea maxima culpa.
Comments, suggestions for questions (with or without answers)
and corrections should be mailed to me at
dtb_at_otto.bf.rmit.oz.au currently [131.170.40.10]
If your mail bounces, try Andy Finkenstadt, listed in the
contributors section below.
One thing you could do if you have posted a question to the net
and got a reasonable response is mail your question and a
summary to me.
Legal Stuff ...
---------------
Opinions expressed in this document do not reflect those of
Oracle Corporation or the organizations to which any of the
contributors belong. Also, responsibility for use of the
information is yours. Information contained in this document
should be considered as if distributed under terms of the GNU
General Licence Agreement or Artistic Licence, i.e. you cannot
sell it or copyright it yourself.
0.2. Who has contributed to this document ?
David T. Bath
Organization:
Global Consulting Services Ltd.
Activities:
DBA and UNIX SysAdm for Global, Hired out for
general consulting and education.
Address:
dtb_at_otto.bf.rmit.oz.au
Assisted With:
Initial cuts, general editing
Andy Finkenstadt
Organization:
Vista-Chrome Inc.,
Homes & Land Publishing Corporation
GEnie Unix RoundTable Manager
Activities:
Is currently organizing an Oracle archive site
Address:
andy_at_vistachrome.com
andy_at_genie.geis.com.
Assisted With:
General comments throughout text
Kevin Stock
Organization:
Encore (I think)
Activities:
Author of oraperl patches to perl
Heavily involved in DBperl
Address:
kstock_at_encore.fr
Assisted With:
Oraperl and DBperl information
Joel Garry
Organization:
unknown
Address:
INTERNET: tumidity_at_netlink.cts.com
UUCP: ...!ryptyde!netlink!tumidity
Assisted With:
Opinions on RPT/RPF
Daniel Druker
Organization:
University of California Los Angeles (?)
Arthur Anderson School of Management (?)
Student (?)
Address:
ddruker_at_agsm.ucla.edu
Assisted With:
Comments on advantages of Oracle
Jack Love
Organization:
University of Michigan (?)
Address:
jlove_at_ivrit.ra.itd.umich.edu
Assisted With:
Comments on Clear Access
Apologies to those I have omitted.
0.3. How is this document formatted ?
Tabs are set to 8 spaces.
Form feeds are placed between major sections
0.4. What biases does the primary author have ?
I am not and have never been an employee for any particular
major hardware or software vendor, preferring to stay in small
software houses and consultancies.
While I have used a number of multi-user operating systems
since 1974 (DG, TOPS-20, GCOS, MS-DOS, CPM, PRIMOS, VMS and
UNIX) I am primarily a UNIX fan ... OK, a zealot.
Having used Ingres, Informix, Oracle and Unify RDBMS systems
(also a number of micro-based SQL and non-SQL systems), I
believe that Oracle gives the best SQL overall.
I am in favor of source code being in ASCII format so it can
be easily edited and placed under version control. I am also
suspicious of languages that think they know what I want, so I
really like C and C++.
I am also not a theoretical computer programmer, indeed my
majors were in pathology and pharmacology. (Well, in one SQL
consultancy, half of us were biologists of one sort or another).
If you understand my biases, you will know where to take my
comment with a decent amount of salt.
1. General Questions
1.1. What is Oracle ?
Oracle is a trademark of Oracle Corporation and in common usage
refers to the database engine (which actually looks after the
data) and a range of front-end products.
Oracle is the largest selling SQL based RDBMS and on the whole,
is in my opinion, the most commercially useful.
Major competitors to Oracle include:
DB2 (IBM platforms only)
Informix
Ingres
Sybase
It is *not* to be confused with the Oracle of Delphi, which led
the market in executive information systems in the ancient world
until about 7 BC. The chief data analyst (the Pythia) would get off
her skull on steam and laurel leaves and mutter gibberish (Said
Quietly on Laurel, SQL for short) which was then translated by
priests in an ambiguous manner in order to please the end-user
and thus extract a hefty donation. Probably the most famous bit
of management information was "if you cross river X, a great victory
will be won". Taking this advice, management crossed the river,
promptly got involved in a great battle, getting decimated. The
great victory was won .... by the other side.
1.2. What are the advantages of Oracle ?
a. Portability
Oracle is ported to more platforms than any of its competition,
running on more than 100 hardware platforms and 20 networking
protocols. This makes writing an Oracle application fairly safe
from changes of direction in hardware and operating system, and
therefore a safe bet. One caveat, however, is that applications
using some constructs (such as field level triggers) may have to
be reworked when porting them to a block mode environment. You
can also develop a fairly fully featured application with little
knowledge of the underlying OS. Personally, I have developed
applications on OS systems barely knowing how to copy and edit
text files.
b. Market Presence
Oracle is by far the largest RDBMS Vendor, and spends more on
R&D than most of its competitors earn in total revenue. Oracle
has the largest independent RDBMS market share in VMS, UNIX and
OS/2 Server fields. This market clout means that you are
unlikely to be left in the lurch by Oracle and there are always
lots of third party interfaces supported and also, proficient
staff are relatively easy to get.
c. Version Changes
Having been working with Oracle since beta versions of Oracle 4,
I have found that Oracle have given almost a full major revision
warning when a documented feature was intended to be withdrawn,
and have never had the behaviour of a feature change dangerously
like I have with some other products (requiring me to check
every source file in a forms interface for example). However,
it should be noted that there were some changes in names of DBA
scripts between v6.0.0 and v6.0.30.
d. Backup and Recovery
Oracle provides industrial strength support for on-line backup
and recovery and good software fault tolerance to disk failure.
You can also do point-in-time recovery. Of course, you need the
archive mechanisms and storage space to do this, but Oracle
supports continuous archiving to tape devices spanning multiple
volumes.
e. Performance
Speed of a *tuned* Oracle database and application is quite good,
even with large databases. Oracle refer to >100 GB databases and
I have personal experience administering 10 GB databases.
f. Cursor Support
Oracle, like Ingres, but unlike Sybase, supports cursors which
ease programming when performance is needed. Oracle supports
multiple cursors per Oracle connection and form part of the ANSI
standards.
g. SQL Dialect
The dialect of SQL offered by Oracle is in my opinion superior
to the others in the extensions it offers over base-level
ANSI-2.
h. Multiple Database Support
Oracle has a superior ability to manage multiple databases
within the same transaction using a two-phase commit protocol.
This is best implemented in V7. You can fairly easily move
where data is actually stored from node to node in a network and
have data mirroring, making it easy to optimise the location of
the data from time to time. This is not so easily done with
offerings from other vendors or earlier versions of Oracle,
where you were not able to update more than one database in the
one transaction with any reliability. This meant that you could
not move data around without recoding your programs. With V7,
your DBA can optimise the location without pre-planning by
programmers or re-examination of the code prior to the move.
i. PL/SQL
PL/SQL, the procedural extensions, is a draft ANSI standard for
procedural DBMS languages.
j. Declarative Integrity
Oracle V7 supports declarative database integrity (the current
ANSI standard) and V6 permits you to enter the declarations.
With V6, you can get the toolset (such as SQL*Forms 3) to read
the declarations and automatically generate the required code.
With V7, not even this is required, as the database engine
automatically enforces the integrity. This means that you can
open up your database to end-users through simple third party
interfaces as they simply cannot break your business rules even
even if they try.
1.3. What are potential disadvantages of Oracle ?
Cost. Oracle ain't cheap. At least before you consider costs
of porting, programmer availability, etc, etc. Remember, you
will almost certainly need a full-time DBA. Good DBA's are not
cheap, but are worth their weight in gold. You will also need
training for programmers and DBA's. Again, not cheap, but shop
around - both for the cost of the courses and for the content.
Oracle are not the only people who give Oracle training, and
often a smaller consultancy can tailor a course for your needs
and still be cheaper per training hour per person.
Oracle is not (currently) as object-oriented as some of the
competition.
Implementation on some systems betrays the heritage of the
system it was developed on (e.g. Mail REEKS of VMS) and can be
counter-intuitive to programmers used to their (non VMS or non
UNIX) OS. On some systems, performance is not what you would
(or were led to) expect, so you may need to upgrade your
system. (The moral of this story is use systems that are
inexpensively scalable). Joel notes particularly that IBM 9370
implementation is slow. (Hey, so is that news to us UNIX types?)
While quite a few people have commented on buggy code, poor
implementation, different keystrokes for different hardware, etc
(all of which have some justification), this is in my opinion
when comparing Oracle to products designed for a more single
minded purpose (examples would be compilers and the sort of
tools written by and for programmers). When compared to other
RDBMS offerings, however, I do not think Oracle comes off too
bad.
Version control of "source" for your applications in some
products is painful where you do not have editable ASCII files.
See the section on version control.
Historically, sales staff were more prone to hyperbolae than
your garden variety marketroid and technical support was poor.
This has improved. Still, installation and upgrade scripts have
copped a *lot* of flack in the newsgroup and I must admit to
requiring a second pass at this sort of stuff, with a decent
browse (and hack) through the install scripts in the meantime.
Just as well I had disk and time to spare ...
1.4. What is SQL ?
SQL (often pronounced SEQUEL) formally stands for Structured (or
Standard) Query Language. In common usage, however, it also
encompasses the DML (Data Manipulation Language - for inserts,
updates and deletes) and DDL (Data Definition Language - for
structuring tables).
The basic idea of SQL is to accept and process requests for the
information you want without you knowing how to extract it (like
you do for network databases).
SQL is NOT the only language available for querying relational
databases - the first versions of Ingres used "Quel", which had
some advantages over SQL. Still, SQL came from IBM, implemented
nicely by Oracle and became the de facto standard.
SQL is the subject of international standards. SQL-2 is
currently the lowest common denominator for most DB engines, but
like any language, each vendor provides extensions to (and
occasionally changes and omissions from) the standard ...
1.5. What is PL/SQL ?
PL/SQL is a group of procedural extensions to the SQL language
and is available to SQL*Forms (3.0 and above), SQL*ReportWriter
(2.0 and above) and SQL*Plus (3.0 and above). Procedures
written in this language are available to be stored in the
database from version 7 of the core product.
Currently, an ANSI commitee is specifying procedural extensions
to SQL, and Oracle PL/SQL was accepted as the first draft. If
anyone has details on this, please mail me.
1.6. What products are available from Oracle ?
Apart from the core engine and consulting/education services...
This list is NOT exhaustive. If I have missed anything, let me
know, preferably with a quick review.
1. SQL*Plus
Known in days of yore as UFI (User Friendly Interface), this is
the basic "shell" for queries, basic reports and database
manipulation. It can be used interactively or driven from scripts.
It is a must-have as installation requires scripts to be run
through this interface and most general administration by
programmers will be carried out through it.
Apart from the basic ability to issue SQL and PL/SQL commands,
it has a number of extensions to permit programming (parameter
passing, variables, prompting for user input, etc) and report
formatting.
It operates identically across all platforms.
2. SQL*DBA
A user-unfriendly version of SQL*Plus with extra power for DBA's.
The history editing facilities of SQL*Plus have been removed so
that you do not spend all your time in a product that has extra
(needed but dangerous) capabilities to do things like start up
and shut down your database.
It also has general performance monitoring facilities. Oracle 6
SQL*DBA is mainly line oriented but has a screen oriented
performance monitor. Oracle 7 SQL*DBA is entirely screen based
by default (although you can invoke it as follows to get line
mode)
sqldba lmode=y
3. SQL*Net
Needs to run on PC's and Mac's before you can access Oracle on
other machines. Most mid-range and larger machines have it as
part of the core product.
Needs to be used on ALL machines to communicate across platforms
or via a network. It is a separately licensed product for Unix,
MS-DOS and Macintosh versions and you need both compliant versions
to communicate between a client (which can be an RDBMS) and a server
(which must be an RDBMS).
4. SQL*Async
Gives functionality of SQL*Net but over a dialup-type line.
5. SQL*Forms
A development tool for screen based applications that allows
code to be stored in database tables. Version 3.0 and above use
PL/SQL for procedural parts of the code.
6. SQL*ReportWriter
Helps write reports and like Forms, permits source code to be
stored in the database. Version 2, soon to be released,
includes triggered events, the ability to update the database
and uses PL/SQL. Version 1, on the other hand, is perhaps the
one Oracle product tried and discarded by most sites.
7. Oracle*CASE
A nifty set of data dictionary (once known as SDD) and design
tools that let you model your database and processes and then
generate reasonable first cut forms, etc from the definitions.
The design sides need a GUI for model display. This product is
good, like anything out of the Oracle UK group that used to be
an independent company that Oracle purchased.
Various reviewers have commented that while there are "me too"
modelling and design tools, (and of course, no others take
advantage of Oracle-specific enhancements), Oracle*CASE has the
best capability to reverse engineer existing Oracle applications
back into the modelling dictionary.
Rumor has it that Oracle UK are currently working on extending
this product to include CASE for languages like C et al.
8. SQL*Menu
A suite of tools that allows the developer to enter menu details
into a database and generate a menu from it. It integrates well
with the other Oracle tools and has the capacity to produce
quite useful end-user documentation.
One nice feature is that a number of different menu appearances
(full screen, pull down, lotus ring-like) can be generated from
the same input specifications.
9. Oracle*Card
Helps develop hypercard like applications in Windows and
Mac environments.
10. Oracle Glue
Helps develop applications under MS-Windows that use things like
Excel and Visual Basic, i.e. an API for DDE applications. It
thus competes against the forthcoming Microsoft OBDC API. Sun
and Mac versions of Glue are forthcoming.
11. SQL*Graph
Prepares graphs from Oracle data.
12. SQL*Loader
Loads data from flat files into Oracle. Data can be loaded into
more than one table at a time (e.g. if your incoming data is in
the format of a master record followed by a number of detail
records).
13. RPT/RPF
An ancient reporting tool with a syntax like runoff that because
of its ability to perform updates, is still used by some batch
processes where its procedural capabilities are useful,
especially where PL/SQL is not necessarily available and the
developer does not want to put SQL in a compiled language like
C. You need this for some aspects of Oracle*CASE. The RPT part
is the program which goes through the tables and feeds data to
the RPF (formatting part). If you are using this for reports
you need more than a fair share of disk for temporary files,
especially if the RPT part must finish before RPF can start the
formatting and there is a lot of data to process. It has advantages
over SQL*ReportWriter in that the code is ASCII and HUMAN READABLE,
and can thus be put under version control tools. Also, it is a
lot faster than SQL*ReportWriter and can be altered much quicker
than wading through a m[ae]ss of menus.
Actually, my earliest comments about RPT/RPF were perceived as
somewhat dismissive, and generated quite a lot of "fan-mail" for
this product.
14. Pro*C, Pro*COBOL, Pro*Fortran .......
Precompilers that let you embed SQL statements into a standard
language without *too* much trouble. While there are other
means of accessing Oracle from languages such as C (the OCI for
example), these interfaces are fairly low level and should
probably only be used when you are being fairly sophisticated.
While I cannot comment about languages other than C, the C code
that comes out of the Pro*C precompiler is NOT really editable.
Anyway, some of the function calls generated are specific to
libraries that are part of the Pro*C product. Thus taking the
generated C code to a site without a Pro*C licence is probably
not a good thing to do.
15. Data*Query
A fairly new product that permits you to generate reports fairly
easily. The nice thing about this (again from Oracle UK) is that
if you have put constraints into the dictionary, then this will
pick up those relationships, so you do not have to constantly
specify the linking field between the EMP and DEPT tables, for
example.
16. Data*Browser
Running under window this gives graphical and analysis capabilities
according to one reviewer and is not too bad.
17. Oracle*Mail
E-mail using Oracle to glue systems together across architectures.
Betrays a Vax VMS heritage. Probably not a bad idea, using
Oracle to glue together a mess of different architectures for
mail, but why would you want to do that when there are other
common standards for mail messages that will still work without
needing Oracle up and running? For many common networks, such
as those made of DOS, OS/2, Mac, UNIX and VMS boxes, if all you
want is a common mail facility, there are probably better
alternatives.
18. Oracle*Financials
A BIG product with modules for accounting, human resources, etc.
If you are a large company, worth considering if you have lots of
disk and cash to spare. Even Oracle admit that if you are not a
large company, it is not worth putting on your short list. It
is reported to cost about $20K per user.
19. Oracle*TextRetrieval
Lets you access text documents using queries. Next release (2)
will support storage of common PC and Mac formats (e.g. MesS-Word
and WordimPerfect). Can do keyword searches.
20. Export and Import
These product files that can be migrated across architectures
and can produce archives of a selection of tables, users and or
indices.
....
It should probably be noted that Oracle*Forms (4.0),
Oracle*ReportWriter (2.0), Oracle*Graphics (2.0), and
Oracle*Menu (6.0) will be all integrated together into one
large Oracle*Tools package.
1.7. What Public Domain interfaces are there ?
1. Oraperl
The oraperl patches to perl are available from comp.sources.misc
archives and were written by Kevin Stock. These patch perl from
Larry Wall (see GNU or comp.sources.unix archives and
comp.lang.perl newsgroup) to give it access to Oracle at a
fairly basic level, permitting you to even have simultaneous
connections to one or more databases (e.g. under different
Oracle logins). The perl language is available (as far as I
know) for UNIX, VMS, DOS, OS/2 and Macintoshes and is a cross
between the UNIX shell and C, and gaining rapidly in popularity.
One of the useful things that comes with oraperl is a script
that takes an SQL statement from a command line and executes it.
I have posted a separate FAQ for oraperl that I obtained from
Kevin.
2. DBperl
Soon to be available is DBperl, which unifies the syntax of the
Sybase and Oracle patches to perl. It will also permit access
to Ingres and Interbase.
There is a mailing list, <perldb-interest_at_vix.com>. You can mail
<perldb-interest-request_at_vix.com> to receive it. This contains
stuff similar to the files that flow through this newsgroup, so
a fair bit of it is technical discussion on the innards of
DBperl which may not be to your interest. As soon as I find
about releases, I will announce it in this FAQ.
3. Unload
Another utility is unload (which a lot of people rename to
sqlunload). It takes a SQL statement and creates two files, a
data loader control file and the data file. I think this was in
comp.sources.misc. I'll post it up to comp.databases.oracle if
need be (I forget where it came from originally). If anyone
knows where it comes from and where it is archived, let me know.
Please let me know of any others.
1.8. What third party interfaces are available ?
Heaps. Oracle interfaces are usually the first RDBMS interface
a third party tool vendor will support. If you are on a PC,
look at Q&E that comes with Excel 4 for Windows, providing you
have SQL*Net. (Mind you, this is an old, cut down copy of Q&E).
Some interfaces written mainly for the UNIX world support a
number of databases. Two examples I have used and found OK were
JAM (from JYACC) - good for C programmers who wanted to write
forms quickly, and UNIFY - which also supports its own backend
if you do not have Oracle (or Ingres or ...)
Some more detailed product notes follow.
Q&E Range of Products
---------------------
Platforms: MS-Windows
Requires: SQL*Net
Features:
Broad range of products from end-user tools to Visual
Basic libraries for programmers. Many other database
servers are supported, including flat text files with
arbitrary delimiters. What is worth noting, is that the
SQL statement in the cut down version even lets you edit
the SQL statement, taking advantage of the particular
vendor brand of SQL.
Clear Access
------------
Platforms: Macintosh and MS-Windows
Requires: SQL*Net
Features:
1. Point and click SQL data retrieval to applications
such as MS-Excel or flat files.
2. Script manager
3. Can link appications directly to SQL servers, e.g.
An Excel macro button can populate a worksheet.
4. Supports DAL.
Pricing:
$450 (US?) list per station, but there are volume discounts.
Contact:
Clear Access Corp, 200 West Lowe Fairfield, Iowa 52556
(515) 472-7077 FAX (515) 472-7198
If anybody reading this is a user or vendor of a particular
third party interface that you think should be mentioned, mail me
direct rather than via the newsgroup together with a quick summary.
This section needs expanding and correction.
1.9. How portable are Oracle applications to other RDBMS ?
While the core SQL is portable, each vendor has their own
extensions and ways in which you need to structure the data
model to take advantage of the way each RDBMS does things.
To get any real performance, you need to take advantage of the
extensions, or be stuck with a lowest common denominator.
Examples of extensions include functions available to SQL (such
as decode), query constructs (such as the hierarchical CONNECT
BY PRIOR and outer join constructs) and physical storage
parameters.
Not only this, but the languages used to implement non-SQL
operations and the various tools are not compatible across
different RDBMS systems (again, unless you use a third party
tool such as Unify).
1.10. What Query Optimisers are there ?
Oracle <= 6 has rule based optimization that depends on two
things, the presence of indices and the way you construct your
where clause. Oracle 7 has the option of using a statistical
optimizer. This requires you to run jobs that get a statistical
picture of the spread of data values in columns across your
database. This is VERY INTENSIVE AND CAN TAKE AGES. It also
needs to be regularly run as the population of your data changes
otherwise the assumptions it makes will be wrong.
While I have not used the Oracle stat.opt., while using Ingres I
found we could get good results by running it on near empty
tables and then editing the tables storing the statistical
information to force it to the look like the way I expected the
data to be some time down the track. Not supported though!!
1.11. Is there an anonymous FTP site for Oracle stuff ?
As far as I am aware, while one is planned, none currently
exist. If anyone knows of a site, could they let me know ?
2. SQL Questions
2.1. How can I avoid a divide by zero error ?
Use the DECODE function. This function is absolutely brilliant
and functions like a CASE statememt, and can be used to return
different columns based on the values of others.
For example, assume we sell products at variable cost, depending
on the ability of the purchaser to pay (e.g. academic discount).
Our table includes the following columns,
PRODUCT, (the key)
NUM_SOLD,
TOTAL_REVENUE.
Average sell price should be TOTAL_REVENUE / NUM_SOLD, but if we
have not sold any, we are in trouble. We can avoid this by
returning NULL as the result if NUM_SOLD is zero as follows:
select PRODUCT,
decode (NUM_SOLD,
0, NULL,
TOTAL_REVENUE/NUM_SOLD) AVG_SELL_PRICE
from my_table;
A fuller example of decode is as follows
select decode (SEX_CODE,
'M', 'man',
'm', 'boy',
'F', 'woman',
'f', 'girl',
'unknown')
.......
In the above example, SEX_CODE would be a single character code,
and the statement would return a description based on that code
or "unknown" if there was no match.
2.2. Can I update tables from other tables?
Yes.
For example, if we had a table DEPT_SUMMARY, we could update
the number of employees field as follows
update dept_summary s
set num_emps = (select count(*) from emp e
where e.deptno = s.deptno);
2.3. Can I remove duplicate rows?
Yes, using the ROWID field, which *will* be unique. There are
many variations on this statement, but the logic will work.
This assumes an EMP table keyed on EMP_ID, which is NOT NULL.
delete from EMP e
where e.ROWID not in (
select min(f.ROWID) from EMP f
where f.EMP_ID = e.EMP_ID );
I'll get the OFFICIAL statement according to Oracle soon.
2.4. Can I update using a view ?
Only if the view is a simple horizontal slice through a single
table. (Except of course if non-essential fields are omitted).
2.5. Are views automatically updated when I update base tables ?
Yes, that is the whole idea of views. The only thing Oracle
stores for a view is the text of the definition. When you
select from a view, Oracle looks up the text used to define the
view and then executes that query.
2.6. Should we use complex views that cruel performance ?
Because view queries that involve sorting, grouping, etc can
lead to a high performance overhead, it might be better to write
some reports with a procedural component that fills up a
temporary table and then does a number of queries from it.
While this is non-relational, it can be justified for some
cases. Nevertheless, it is useful to have the view definition
in the database. You can then test the output from the view
against the output from your procedural manipulations. The view
definition can also be used as the unambiguous gospel.
2.7. Can I implement tree structured queries ?
Yes! This is commonly asked by those migrating from non-RDBMS
apps. This is definitely non-relational (enough to kill Codd
and then make him roll in his grave) and is a feature I have not
seen in the competition.
The definitive example is in the example SCOTT/TIGER database,
when looking at the EMP table (EMPNO and MGR columns). The
MGR column contains the employee number of the "current"
employee's boss.
You have available an extra pseudo-column, LEVEL, that says how
deep in the tree you are. Oracle can handle queries with a
depth up to 255.
select LEVEL, EMPNO, ENAME, MGR
from EMP
connect by prior EMPNO = MGR
start with MGR is NULL;
You can get an "indented" report by using the level number to
substring or lpad a series of spaces and concatenate that to the
string.
select lpad(' ', LEVEL * 2) || ENAME ........
You use the start with clause to specify the start of the
tree(s). More than one record can match the starting condition.
One disadvantage of a CONNECT BY PRIOR is that you cannot
perform a join to other tables. Still, I have not managed to see
anything else like the CONNECT BY PRIOR in the other vendor
offerings and I like trees. Even trying to doing this
programmatically in embedded SQL/C is difficult as you have to
do the top level query, for each of them open a cursor to look
for child nodes, for each of these open a cusor .... Pretty
soon you blow the cursor limit for your installation.
2.8. How can get information on the row based on group information.
Imagine we have the EMP table and want details on the employee
who has the highest salary. You need to use a subquery.
select e.ename, e.empno, e.sal
from emp e
where e.sal in ( select max (e2.sal) from emp e2 );
You could get similar info on employees with the highest
salary in their departments as follows
select e.ename, e.deptno, e.sal
from emp e
where e.sal in (
select max (e2.sal)
from emp e2
where e2.deptno = e.deptno
);
Notice that I used "in" rather than "=" for the subquery, as
there MAY be more than one row that was returned by the
subquery.
2.9. How can I get a name for a temporary table that will not clash ?
Use a sequence, and use the number to help you build the
temporary table name.
2.10. How can I find out about what tables, columns and indices there are ?
Oracle maintains a live set of views that you can query to tell
you what you have available. In V6, the first two to look at
are DICT and DICT_COLUMNS which act as a directory of the other
dictionary views. It is a good idea to be familiar with these.
Not all of these views are accessible by all users.
2.11. Is there a formatter for SQL statements ?
There are a number of "beautifiers" for various program
languages. The cb and indent programs for the C language spring
to mind (although they have slightly different conventions). As
far as I know there is no formatter for SQL available.
Note that you CANNOT use cb and indent with Pro*C as both these
programs will screw up the embedded SQL code.
2.12. What is the DUAL table ?
The DUAL table is a table with a single row and a single column
used where a table is syntactically required, for example in
SQL*Forms 2.3 where you want to use a function available to
SQL.
e.g. If we have two fields, x and y and we want y to be set to
a substring of x
select substr(x, 2, 4) into y from dual;
If DUAL has NO rows or more than one, many forms under 2.3
syntax (or 2.3 triggers in forms 3) will not work properly.
At worst, the logic of some of the triggers in your form will be
reversed, without any message to the user and data could be
destroyed.
When I was writing Forms 2.3 code, the *first* thing I did
was to check that there was one row (and only one row) in
the DUAL table, otherwise I displayed a message to contact the
DBA and then quit the form. This was done in KEY-STARTUP.
2.13. What is the difference between CHAR and VARCHAR ?
Oracle have stated that CHAR will become fixed length character
type at storage level, and that VARCHAR will be variable length.
Currently they are synonymous. This will probably mean that CHAR
types will not be able to be NOT NULL, and will take up all the
storage required when the record is first inserted. Currently,
the only physical storage required is the actual data and a
length specifier. It may also mean that restrictions such as
where RESPONSE in ('YES', 'NO')
may have to be rewritten for CHAR types as
where RESPONSE in ('YES', 'NO ')
At the level of Pro*C, of course, they are markedly different.
3. SQL*PLUS QUESTIONS
3.1. How can I control the startup configuration of SQL*Plus ? SQL*Plus first looks at a glogin.sql (global login) in a directory underneath the main Oracle installation directory
($ORACLE_HOME/dbs/glogin.sql for UNIX) and then in login.sql
in your CURRENT directory. I think the VMS glogin file is in SYS$ORACLE, but I am not sure about that. It is a pity that Oracle does not use a file in your $HOME directory, but I have hacked glogin.sql to view a file in the $HOME if it exists. 3.2. Can I get a column value into a substitution variable ? Use the OLD_VALUE spec with the COLUMN command. Imagine we want the surname of an employee to go into a variable, getting it via the employee id. COLUMN x OLD_VALUE y SELECT surname x FROM employee WHERE emp_id = 1234; PROMPT I found employee with surname &&y Of course, this was more often used as a kludge before PL/SQL became available in SQL*Plus (or if you had v6 without TPO), but it is still useful for a number of things. 3.3. How can I change the (hated default) editor to my favorite ? When you type "edit" to SQL*Plus, it invokes a default editor for your system. On DOS this is often EDLIN, on UNIX ed and on VMS, it is usually EDT. In your login.sql file (or preferably the glogin.sql file set up by your DBA), you should set the variable that changes this to your favorite editor. define _editor="/usr/ucb/vi" 3.4. What is the difference between & and && ? You can create a "permanent" definition of a substitution variable using DEFINE, the OLD_VALUE or NEW_VALUE clauses of a COLUMN statement (when the SQL statement uses them) or by referencing the variable with &&. Use of a & does not create a permanent version and will prompt you for a value if the variable does not exist (as will &&). However, next time you reference the variable with & or &&, it will ask you for the value again. Try the following to figure it out (do not enter the "SQL>" and remember to type something in when asked, make it different each time). SQL> undefine try_1 SQL> prompt &try_1 SQL> prompt &try_1 SQL> prompt &&try_1 SQL> prompt &&try_1 SQL> prompt &try_1 SQL> prompt &&try_1 Note that use of positional parameters (&1, &2, &3 ....) are slightly different, they are given a "permanent" definition. P.S: When I say "permanent" I mean permanent for the current process. 3.5. What is the difference between "host" and "!" ? The host and ! (sometimes another character) both create operating system commands as child processes of SQL*Plus. The difference is that the "host" will perform variable substitution of & or && symbols, whereas "!" will not. 3.6. What can I not use a table name in a substitution variable ? Often, people try and put a table name in a substitution variable and then use it as follows: define WORK_TABLE="EMP" select &&WORK_TABLE.DEPTNO from &&WORK_TABLE; Mind you, the substitution in the "from" clause is OK. What is happening is that the "." between &&WORK_TABLE and DEPTNO is a terminator for substitution. Thus define XYZ="ABC" prompt &&XYZ.abc will give "ABCabc" Thus, you should have used select &&WORK_TABLE..DEPTNO from &&WORK_TABLE;
4. SQL*FORMS 3 QUESTIONS
4.1. How can I get a list of values from a hard coded list ? Assume the field is SEX_CODE, use the following in the list-of-values SQL statement SELECT 'M', 'Adult Male' INTO SEX_CODE FROM DUAL UNION SELECT 'F', 'Adult Female' FROM DUAL UNION SELECT 'm', 'Juvenile Male' FROM DUAL UNION SELECT 'f', 'Juvenile Female' FROM DUAL UNION SELECT 'H', 'Adult Hermaphrodite' FROM DUAL UNION SELECT 'h', 'Juvenile Hermaphrodite' FROM DUAL UNION SELECT 'U', 'Unknown' FROM DUAL It's for a zoo catalog of animals...... :-) 4.2. Can I edit SQL*Forms code with my text editor ? Yes, but it is unsupported (since IAG became SQL*Forms). Personally, I use the SQL*Forms Designer interface to cut the basic code and screen layout and then do the rest inside my text editor. I wonder how many others do it this way ? Forms 2.3 is difficult because the syntax is so obtuse and takes a fair bit of mucking around if you want to learn how to change the logic. Just changing an SQL statement, however is easy enough. (I was able to do it because I grew up with SQL*Forms before it got called SQL*Forms). Forms 3 is a LOT easier, but be warned, DO NOT INCLUDE TABS. Use leading spaces. TURN OFF AUTOINDENT FEATURES OF YOUR EDITOR (or post-process with the expand utility of UNIX which converts tabs to spaces). Oracle proposed not having a human readable .inp file for SQL*Forms 4, but enough protests were received to prevent removal of this feature. If you are using a text editor, you must remember to reload the form back input the database using the iac program. You will have to remove it using "iac -d" first. One thing worth noting is the fact that the text file (and the database definition) permits association of prompt text to be associated with the field, and position above or to the left of the field, moving automatically when you edit the position of the field. With the screen-based interface, the text is hard-coded into display of the form and is NOT associated with the field. Thus you must move TWO things, the field and the associated text. 4.3. Can I edit SQL*Forms code by updating the database ? Yes, but it is unsupported. In SQL*Forms 2.3, the tables being with "IAP", in 3.0, with "FORM". Quite often, when I change the name of a column or table, I use a form created with forms tables as the base tables of the blocks to update the database and then generate the new forms. Using these tables for reports can also be handy to compare the definition and consistency of columns and fields that use it as well as for technical documentation (such as "where is such-and-such a table used?). One very useful thing to do is run a query that brings up fields based on the same column and use the "duplicate field" key to copy the help message quickly. This can be a *lot* faster than opening up all the forms under SQL*Forms designer, navigating a million menus and then doing heaps of typing. Where I have had the luxury of specifying an entire application where each column had a unique name, it was very easy to write scripts that updated datatypes, lengths and help from the table definitions and comments stored in the main data dictionary
(after I had run a report to see what my update was going to
do). This gave me a high degree of confidence in the integrity of my application. Similar scripts made help consistent for fields across the entire application (I had a supplementary table that kept my help keyed on field name, this was used to update the forms database).
5. PRO*C QUESTIONS
5.1. Why are my C variables overwritten ? This question only gets phrased this way once you've got to the stage of debugging your programs by putting printf() calls everywhere because you cannot figure out what is going on. It usually stems from the fact that you have been writing C for some time and are new to the Pro*C precompiler. The problem is that Pro*C does *not* understand the scoping of C variables in a file. Basically, if you have declared a variable to Pro*C in an EXEC SQL BEGIN DECLARE .... END DECLARE secion, it is global to the file rather than limited to the C scoping you have declared it in. You also cannot use Pro*C to refer to the same variable in different files (i.e. the same part of memory, by declaring the variable to be extern in one file). Basically, except where necessary, it might be a good idea to limit a Pro*C source file to a single function, thus avoiding this sort of problem. This can pose a problem if you want to deal with varchar data from other files, as Pro*C does the declaration for you. You can pass the varchar across to other functions by pointers. 5.2. Can I use C preprocessor definitions for VARCHAR size ? Using Pro*C, you cannot - Pro*C has finished before the C preprocessor gets a look in and it won't accept a symbol. One way around is to write a set of C functions that deal with varchars and use these in functions that do not directly access the database. I have a suite of functions that create a varchar library that permits such declarations, mallocs for varchars, as well as implementations of varchar versions of string.h type functions and conversions. You can mail me, or if I become overwhelmed, I will post it again to comp.databases.oracle. 5.3. What can I do about "line too long" errors with version control? Version control systems usually substitute a symbol in your original code with a longer expansion. For example, in RCS, you can have a symbol $Id$ that expands to include the filename, author, date, etc. As Pro*C is at least slightly brain-damaged in not being able to handle long lines (partially fixed by a command-line option that ups the limit a bit), you should be careful of this and use separate version control key words on different lines, e.g. the filename, author and version number keywords should be kept separate. You can also make it safer by using a variable in your make files (or equivalent), PROC, defined to include the appropriate command line switches you commonly use, including the one to set input and output line lengths to the maximum.
6. UNIX QUESTIONS
6.1. Can I create a compressed export on the fly without needing to have the space for both the export file and the compressed file? Yes, use named pipes. mknod p myexport.dmp # Make the pipe compress < myexport.dmp > myexport.dmp.Z & # Background compress export scott/tiger filename=myexport.dmp # Do export 6.2. How can I prevent trailing spaces in a spooled report ? You can't. Post-process with sed. sed -e 's/ *$//' < infile > outfile. This problem affects other OS versions as well. 6.3. How can I get an environmental variable into SQL*Plus variables? a. Create a sequence accessed in glogin.sql (e.g. my_seq) This permits you to have a "unique" number to build a file that will not conflict with another process. column x old_value tmp_num noprint; set heading off set pause off select my_seq.newvalue x from dual; b. Create a getenv.sql script like the following host echo "define &1=\"$&1\"" > $HOME/s&&tmp_num..sql start $HOME/s&&tmp_num..sql host rm $HOME/s&&tmp_num..sql c. When you want to create a variable "LOGNAME" that reflects the LOGNAME environment variable, do the following start getenv.sql LOGNAME OK, so it is a bit resource heavy, and may need to be altered for your implementation, but you should get the basic idea. Of course, SQL variables are NOT case sensitive.... Other operating systems can do a similar thing, I would expect, but not necessarily so easily. 6.4. Can I pipe stuff through SQL*Plus ? Yes. For example in vi, I often need to reference a table structure. I simply type "desc EMP" into a new line in my buffer, :.,.!sqlplus -s / and wait for the employee table description to appear, which then gets cut and pasted into the SQL statement I want. 6.5. Why do Pro*C compiles or programs crash on my Sun ? SunOS 4.1 (a.k.a. Solaris 1) is a BSD based OS. It comes with two C compilers. One is in /usr/bin/cc (which should be linked to /bin/cc) and it the BSD compiler, pointing to BSD header files and libraries. The other is in /usr/5bin/cc (which should be linked to /5bin/cc) and points to AT&T System V headers and libraries. Oracle libraries assume you are using the System V compiler. (It is worth noting that Solaris 2 is System V.4). When working in the Oracle environment (or simply to help you change to a System V frame of mind), you should put the System V directories ahead of the BSD directories in your PATH. Also, the CC variable (especially in your Makefiles) should point to the System V compiler. Alternatively, you can try using the -I /5include and -L /5lib flags ahead of other directories.
(These are probably linked from /usr/5include and /usr/5lib).
Gnu C (GCC) is a BSD-derived compiler, so I assume that similar arguments apply. 6.6. How can I find a lost Oracle export file ? You need to add a description of the layout of an export file to the /etc/magic file. See magic(4) and file(1) for details. Use the fact that an Oracle export starts with a control-A and then the word EXPORT and the version number of the export that produced it. (Use the strings(1) or od(1) command to check this out). Your system administrator can then edit /etc/magic to not only recognise an export file but tell you the version number when you use file(1). Say the key description was "Oracle Export", you can look for Oracle export files from the current directory and below as follows: find . -type f -exec file {} \; | grep "Oracle Export" | \ sed -e 's/:.*//'
7. MISC QUESTIONS
7.1. How can I change the table storage parameters from an export file? Use the undocumented INDEXFILE=xxxxx parameter. This creates an ASCII file "xxxxx" with the table creation statements REMmed out and the index creation statements not. Edit this to change the table storage parameters (such as initial size, tablespace, etc). Example: imp scott/tiger filename=myexport.dmp indexfile=myexport.sql 7.2. What makes the best Oracle server for a network ? OK, I am biased and will say get a Sun. Even the low-end SPARClassic might service around 32 concurrent users, but you are limiting your upgradeability. A SPARC-10 is probably the best option for mid-range stuff for the long term. If you are pushing departmental servers (users pushing the 100 mark and above) consider the SPARCserver 600 series or even the 2000 if you want mainframe grunt. There is some justification for this, Oracle do most of their development on Suns, more Oracle licences go onto Suns than anything else and so releases will be timely and well supported. Also, as the most important source of revenue for Oracle is UNIX based, going UNIX for the server is pretty sensible. Actually, Oracle and Sun get to look at each other's source code at early development levels and are HEAVILY allied against the common enemy, a.k.a Bill Gates. Talk is that Oracle and Sun are about to release benchmarks using Oracle V7 and Sun's Solaris 2.1 that will show very good bang for buck compared to anything else. The other thing is that Sun's Solaris 2.2 is rumored to have RAID and Journalled File Systems, making it virtually OK to kick out the power supply without too much drama. This combined with Oracle DBA tools makes a pretty good fault tolerant machine. 7.3. How can I implement version control ? The issue of version control for Oracle applications code is a sticky one and is complicated by issues of different operating systems, but most especially by Oracle's predilection for non-ascii storage formats. Note the ruckus caused when Oracle proposed no ascii .inp format file for Forms4. Version control is an important issue for MIS departments however and MUST be successfully addressed, otherwise things start falling apart. 1. Choice of Version Control Tool ---------------------------------- There are a number of version control tools available for different platforms. These include CMS for VAX/VMS and SCCS for AT&T UNIX. In the MS-DOS world, most formats are proprietary. Our choice of tool was RCS however, because of the following reasons a. Reports and management are more user friendly than SCCS, the default tool for UNIX, our main development platform (although our network includes DOS and VMS nodes). b. Source code is available in the public domain from most sites archiving GNU code. This makes it possible to port it fairly easily, especially if you are running VAX/VMS and have either the POSIX C libraries or using GNU C. DOS source code and binaries are also available. We can also modify the way some of the programs (particularly the report programs) work to perform extra things we like. c. The binary files used to store initial code and deltas (from which any version can be retrieved) are compatible across architectures. For example, I can use the DOS RCS programs to extract source files from the binary RCS files transferred from the UNIX machine. d. An add-on, CVS, also available in the public domain, permits fairly easy management where TWO sites are carrying out concurrent development. This is common where a vendor is providing the source, but some local changes are being made. 2. Outline of RCS operation ---------------------------- Essentially, you check in a file to RCS (with optional security information, "aliases" for the version and state information). Subsequent check ins are stored as deltas within the RCS storage file, (unless you are checking in binary files, in which case the entire new copy is stored). For ascii files, RCS looks for certain keywords, and on check out, substitutes the version specific information. These keywords include $Author$ The user name of the file owner $Source$ Full path/filename of source $Filename$ filename without path $Date$ Date and time of check in $State$ A keyword you can assign e.g. "Cut", "Tested", etc $Id$ A combination of all information (Dangerous for many Oracle products) You can also cause RCS to include in your source all comments made specific to that version, for example, the reason why changes had to be made. Note that with ASCII files, only storage for the changes between successive versions are required. For binary files, the entire file is stored - chewing up disk fairly quickly. 3. Basic Philosophy of Version Control with Oracle App Source -------------------------------------------------------------- Wherever possible, use version control on ASCII representations of the code. Thus for forms, use the .inp file, for menus, the .sql file produced by "exporting" the menu. For SQL*Reports, .rex files rather than .rep files are put under version control. The keywords are placed in the source, however you like, the ASCII file produced, checked in to RCS. When moving to a testing or production environment, the source is checked out of RCS (resulting in keyword substitution) and "compiled" in the target environment to produce the runtime application. 4. General Caveat ------------------ Where source code for an Oracle application can be stored in the database (such as forms or menu), keyword expansion of the $Id$ keyword can be dangerous, as it may expand to MORE than the database allows to be stored. Thus, rather than use $Id$, I use the more atomic keywords on different lines, these being unlikely to expand above legal limits. 5. Specifics for some products ------------------------------- a SQL*Plus Scripts RCS keywords are generally placed in REMark lines b SQL*Forms RCS keywords are placed in the comments at forms level. Selected keywords are placed as default values for non-enterable fields, so that the user can see version information. c SQL*Menu RCS keywords are placed either in the titles section of a menu or in the default value for a parameter, depending on how we want to use the keyword. If you do not want to do this, put a wraparound function to create the REMark line(s) at the top of the .sql file prior to checking it in. This will NOT affect compilation of the resulting .dmm. d SQL*ReportWriter Major BUMMER, .rex files are not "sensible" ASCII that will permit expansion of RCS keywords. The only way I know how to solve this is to version an ascii file produced by selecting the data out of the report writer internal tables and versioning that, then using SQL*Loader to put them back into the SQL*ReportWriter tables in the production environment. While this would work, I have not tried it myself (as we do not use RW), but apparently some group in the US has a tool that performs this task. If anyone knows how I can contact that group, let me know please. Notes: 1. RCS and CVS, together with the GNU compiler you may need are available from most GNU archive sites. If you are in Australia, try archie.au in ~ftp/gnu. Less up-to-date versions are also available in comp.sources.unix. 2. DOS RCS executables are available from SIMTEL. Again, in Australia, try archie.au (somewhere under ~ftp/micros/pc/oak, I forget which subdirectory). 3. I think I have seen OS/2 versions of RCS floating around. You could probably get the DOS patches and compile it for the OS/2 environment with a few mods anyway. -- David T. Bath | Email:dtb_at_otto.bf.rmit.oz.au (131.170.40.10)Senior Tech Consultant | Phone: +61 3 347-7511 TZ=AEST-10AEDST-11 Global Technology Group | 179 Grattan St, Carlton, Vic, 3153, AUSTRALIA "The robber of your free will does not exist" - Epictetus Received on Tue Mar 23 1993 - 01:26:12 CET