FAQ 1.4 - oracle.faq

From: David Bath <dtb_at_otto.bf.rmit.oz.au>
Date: 31 Aug 93 06:04:44 GMT
Message-ID: <dtb.746777084_at_otto>


ORACLE NON-DBA FAQ (Frequently Asked Questions)


$RCSfile: oracle.faq,v $
$Revision: 1.4 $
$Date: 1993/08/31 05:35:18 $
$Author: dtb $

TABLE OF CONTENTS



0. INTRODUCTION
	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 ?
	0.5.	What does this FAQ need ?
	0.6.	Response form
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 is Procedural Option ?
	1.7.	What products are available from Oracle ?
	1.8.	What Public Domain interfaces are there ?
	1.9.	What third party interfaces are available ?
	1.10.	How portable are Oracle applications to other RDBMS ?
	1.11.	What Query Optimisers are there ?
	1.12.	Is there an anonymous FTP site for Oracle stuff ?
	1.13.	What mail lists are there ?
	1.14.	What bulletin boards are there ?
	1.15.	What news groups are there ?
	1.16.	How does Oracle compare to ... ?
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 I 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 ?
	2.14.	What is ROWNUM good for ?
	2.15.	How do I get a top ten ?
	2.16.	How can I control the rollback segment I use ?
	2.17.	How can I order a union ?
	2.18.	How can I rename a column ?
	2.19.	Who are SCOTT/TIGER, SYSTEM and SYS ?
	2.20.	Who do various access methods compare ?
	2.21.	What are clusters ?
	2.22.	How can I update a big table without blowing rollback segments ?
	2.23.	Why don't I get records for the date I want ?
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.	Why can't I use a table name in a substitution variable ?
	3.7.	How can I see all of a LONG ?
	3.8.	How can I force a column to begin on the left of the page ?
	3.9.	Can I alias SQL commands ?
	3.10.	Can I escape significant punctuation marks ?
4.  SQL*FORMS 3 QUESTIONS
	4.1.	How can I get a list of values from a hard coded list ?
	4.2.	How can I get find to look at description with list of values ?
	4.3.	Can I edit SQL*Forms code with my text editor ?
	4.4.	Can I edit SQL*Forms code by updating the database ?
	4.5.	Why can't I see data in a control field ?
	4.6.	Why is my terminal scrambled in a user exit ?
	4.7.	What happens to LONGs ?
	4.8.	What are user-written form level functions ?
	4.9.	How can I use regular expressions for field validation ?
	4.10.	What is a user-exit ?
	4.11.	How can I call a popup window for field validation ?
5.  SQL*FORMS 4 QUESTIONS
	5.1.	What new features can be expected in forms 4 
		generator from CASE ?
	5.2.	What new features can be expected in forms 4 ?
6.  PRO*C QUESTIONS
	6.1.	Why are my C variables overwritten ?
	6.2.	Can I use C preprocessor definitions for VARCHAR size ?
	6.3.	What can I do about "line too long" errors with version control?
	6.4.	Why do my compiles crash or weird things happen ?
	6.6.	How do I use OPS$login ?
7.  CASE QUESTIONS
	7.1.	Can CASE generate forms with owner prepended to table names ?
	7.2.	Can CASE generate V7 databases ?
8.  UNIX QUESTIONS
	8.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?
	8.2.	How can I prevent trailing spaces in a spooled report ?
	8.3.	How can I get an environmental variable into SQL*Plus variables?
	8.4.	Can I pipe stuff through SQL*Plus ?
	8.5.	Why do Pro*C compiles or programs crash on my Sun ?
	8.6.	How can I find a lost Oracle export file ?
	8.7.	How can I tell make about SQL*Forms ?
9.  MISC QUESTIONS
	9.1.	How can I alter table storage parameters from an export file?
	9.2.	What makes the best Oracle server for a network ?
	9.3.	How can I implement version control ?
	9.4.	What books are available about Oracle ?
 
	COMP.DATABASES.ORACLE NON-DBA FREQUENTLY ASKED QUESTIONS
	========================================================
0.  INTRODUCTION
0.1.	What is this document ?
	This is the general oracle FAQ (Frequently Asked Questions) file
	for the comp.databases.oracle newsgroup.  I also post it
	occasionally to the ORACLE-L listserver.

	Database Administration issues are covered in a separate FAQ
	series 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.

	Tina London also has made available an excellent document on sql
	layout standards and performance issues for Oracle V6.

	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.  I expect this to happen by early to mid 1994.

	Currently this document addresses Oracle 6 and 7.

	Any errors are mine.  Mea culpa.  Mea maxima culpa.  Actually,
	there is one little untruth I put in on purpose.  Its harmless
	enough though.
	
	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]
	Please mail me direct.

	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 if you think it would be useful.

	BTW: I'll probably post an updated version every month or two.

	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 and you cannot pass it on in
	altered form without making the original available unless you
	are posting a free version to the net in another format (such as
	postscript) and have merely changed the presentation, not the
	text.  As with GNU though, donations of $$, software or hardware
	to my address below are gratefully accepted.

	BTW 1..........................................................
	If you have a comment as to whether this should be distributed
	as a uuencoded zip or whatever as well, let me know.  If your
	newsreader or mail handler chokes on the size let me know.  As
	I got a couple of messages to this effect with 0.4, I also make
	a uuencoded compressed version available rather than split this
	file yet.
	BTW 2..........................................................
	Thanks to the Business Faculty of the Royal Melbourne Institute
	of Technology who have permitted me free network access although
	I am neither staff nor student.  They offer education and
	business consulting services outside Australia too.  I've really
	gotta give them a plug (grovel, grovel).

0.2.	Who has contributed to this document ?
	David T. Bath
		Organization:
			Global Consulting Services Ltd.
			179 Grattan St, Carlton, Vic, 3153, Australia
			Ph: +61 3 347 7511,
			Fax: +61 3 347 0182
			TZ=+10 at the moment, +11 in MY Summer
		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:
			?
		Activities:
			Author of oraperl patches to perl
			Heavily involved in DBperl
		Address:
			# Unknown
		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:
			Independent DBMS consultant
			MBA Studebt at Anderson School of Management UCLA
			Former worker at Oracle
		Address:
			ddruker_at_agsm.ucla.edu
		Assisted With:
			General comments throughout text
	Jack Love
		Organization:
			University of Michigan (?)
		Address:
			jlove_at_ivrit.ra.itd.umich.edu
		Assisted With:
			Comments on Clear Access
	Jeff Stander
		Organization
			CSIRO Marine Laboratories
			GPO Box 1538, Hobart, Tas, 7001, Australia
		Address:
			jstander_at_ml.csiro.au
			Jeff.Stander_at_ml.csiro.au
		Assisted With:
			Info on XSQL
	Dave Campbell
		Organization:
			CSIRO DIT Centre for Spatial Information Systems
		Address:
			dave_at_csis.dit.csiro.au
			PO Box 664, Canberra, ACT, 2601, Australia
		Assisted With:
			Note that DEC Pro*C requires Sys V compatability
	R. Scott Unrein
		Organization:
			Texaco (?)
		Address:
			unreirs_at_Texaco.COM
		Assisted With:
			Mailling lists
	Simon Stow
		Organization:
			Oracle UK (?)
		Address:
			sstow_at_oracle.com
		Assisted With:
			CASE Generator and Forms 4
	Tina London
		Organization:
			Independent consultant
		Address:
			tinalondon_at_artemis.demon.co.uk
		Assisted With:
			Query rankings
	Kevin Mallory
		Organization:
			Oracle SQL*Forms development
		Address:
			kmallory_at_oracle.com
		Assisted With:
			SQL*Forms 4 notes
	Andreas Bartelt   /   Nickname: Tares
		Organization:
			Fachbereich 10 Informatik Softwarelab.,
			Universitaet Oldenburg
		Address:
			Andreas.Bartelt_at_Informatik.Uni-Oldenburg.DE
			bartelt_at_hpux01.HRZ.Uni-Oldenburg.DE
		Assisted With:
			FTP site setup
	Ian Dixon
		Organization:
			?
		Address:
			idixon_at_infocom.co.uk
		Assisted With:
			SQLPATH
	Mark Gurry
		Organization:
			Independent consultant
		Address:
			mag_at_scammell.ecos.tne.oz.au
		Assisted With:
			Info on status of his book.
	Dan Bikle
		Organization:
			Independent consultant
		Address:
			dbikle_at_alumni.caltech.edu
		Assisted With:
			Info on his book
	Tony Jambu
		Organization:
			DBA, Colonial Mutual, Melbourne, Australia
		Address:
			tjambu_at_cmutual.com.au
		Assisted With:
			General comments
		
	Herodotus
		Organization:
			Dead Prose Writers Society
		Address:
			herodotus_at_depthistory.helicarnassus.edu
		Assisted With:
			Oracle and Croesus


	Apologies to those I have omitted or offended.

0.3.	How is this document formatted ?
	Tabs are set to 8 spaces.
	If using vi, then :set ts=8
	Form feeds are placed between major sections.
	Would some people prefer troff source ?

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.  I like open
	systems and feel that threatened by MegalosauruSoft.

	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.
(In fact, the only thing about Ingres SQL I think is better
is the way you can use UNIX glob wildcards *? and [A-Z] style rather than %_ ANSI standard.) 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 do not have a GUI fetish: I can type more accurately than I can move a mouse. 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). I also have spent much more time supporting software development systems rather than production systems, although I have worked on some largish systems (>5G). Also, I am not a full time DBA, but also cut general code for oracle and UNIX/C as well as perform Unix and Novell SysAdmin. I am currently wrestling with the MS-Windows programming interface as well. If you understand my biases, you will know where to take my comments with a decent amount of salt. 0.5. What does this FAQ need ? As yet, there is no section for RPT, ReportWriter, Financials and Menu, among others. Send in contributions!!!!! 0.6. Reader Response Form Simply complete, cut and mail the following section to me at dtb_at_otto.bf.rmit.oz.au to help me improve this document. ---- BEGIN READER RESPONSE FORM ---- 1. Address: 2. Platforms: 3. Are you [Y/N] : Reading this fully : Browsing here and there : Only scanning briefly : Printing : Archiving : 4. Describe your function: End user Programmer DBA IS management 5. Describe your Oracle experience: no RDBMS RDBMS but no Oracle < 1 year between 1 and 3 years 3 year to 5 years > 5 years 4. How useful is this FAQ ? Not at all A little bit OK Very good I'll offer you half a million p.a. to work for me 5. What was most useful ? 6. Has your mailer / newsreader choked ? (Give details) 7. How did you get this ? 8. What do you dislike about this FAQ ? ---- END READER RESPONSE FORM ---- 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 Others worth noting include Interbase from Borland (Unix/MS-DOS) shql (free UNIX tool works like ingres isql) varous xbase products Postgres (Stonebraker's other work - free but unsupported except for the Net - which is about the same as commercial support anyway). 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 Quixotically 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 to Croesus, (King of Lydia and known in the phrase "as rich as Croesus"), when he received the advice "If you attack the Persians, you will destroy a great empire". Well, he did attack, and lost his own empire. There's a moral to this story I think ... 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 Oracle seem very good at informing you in detail as to what is not going to be supported in the next major release and usually have some knob you can twiddle for good backward compatability, or simply leave it working, but with "don't use this, use xxxx instead" warnings in the manual. Backward compatability is very good meaning you will not be in for an application re-write when you upgrade the DBMS. [Compare this with the Ingres v5->6 OSQL upgrade from hell.] I've worked with Oracle since V4 Beta and have never been in for nasty surprises as far as syntax goes. However, see "Version Changes" under disadvantages. 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. The performance is not only "raw", but includes consideration of performance with locking and transaction control. f. Cursor Support Oracle, like Ingres, but unlike Sybase (until Release 10 I think), supports cursors which ease programming when performance is needed. A cursor basically lets you do row-by-row processing. Oracle supports multiple cursors per Oracle connection in line with 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 ANSI-2, which is very much a lowest common denominator. Constructs such as the absolute function and decode keyword are very powerful Oracle additions to the standard SQL. 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. See main discussion on PL/SQL. 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. It makes it easy to administer changes in business and data rules as there is only one spot where the change needs to be made. This lowers the cost of required modification to the system because you do not have to edit all applications code that works with the table. It is reasonable to expect Oracle to release the first SQL-93 implementation at near-full compliance. As one DBA noted on the net recently "With Oracle V8 I'll be out of a job because everything will be in the pfile". 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. If you are getting an application from a VAR, the Oracle components can often be obtained at a good price. 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
generally a bit harsh. Sure, when comparing Oracle to more single minded systems such as a compiler or programmer tool, Oracle comes of poorly in quality. But, remember you are talking about a large system and compare Oracle to other RDBMS systems and you'll find it actually isn't too bad. Just make sure the DBA runs each new release in a spare area for a while ... 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. Version changes can give you nasty surprises. OK, so this affects everyone, but there have been a few "patches from hell".
(Ask any VMS DBA who applied V6_36 or thereabouts). Personally
I generally start feeling comfortable around Vx.0.15 but much prefer Vx.1). 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 that weekend... Also, there has been quite a bit of criticism, at least here in Oz, of the way Oracle do not inform you about important patches to the RDBMS until you ring support with a problem...meanwhile your data just got corrupted. Apply pressure to your support representative to improve the situation. 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-87 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 ... SQL-93, still draft, handles procedural extensions. 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 including these in SQL-93. Oracle PL/SQL was accepted as the first draft. SQL-93 is still at working draft stage, but the quick read I have had of it looks like fairly standard PL/SQL with a few extra statements thrown in. BTW, the spec is currently over 1K pages and *not* bedtime reading unless you like browsing a cross between legalese and BNF ! Come to think of it, excellent bedtime reading if you are an insomniac ! Early version of PL/SQL could have no direct I/O with the process sending it to the server. Forthcoming versions do. 1.6. What is Procedural Option ? The procedural option to Oracle V7 permits you to define database triggers (e.g. a procedure to carry out when a record is updated, for example) and use DBMS PIPES, where an external procedure can effectively be called from one of these triggers. These triggers are written in PL/SQL. An example is as follows: create or replace trigger MY_TRIGGER after update on MY_TABLE for each row begin if updating ('MY_SCALAR') then insert into MY_AUDIT_TBL ( AUDIT_KEY, VAL_NOW, VAL_PREV, DIFF, DT, WHO) values ( MY_KEY, :new.MY_SCALAR, :old.MY_SCALAR, :new.MY_SCALAR - :OLD.MY_SCALAR, sysdate, user); end if; end; Note the new keywords "new" and "old". This is similar to V6 and "Transaction Processing Option". Basically it contained most of the "goodies" of the new version and was licensed $erately. The TPO included sequences and the ability to run PL/SQL at the back end. 1.7. 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).
Note that if you are running MeSs-Windows, you can get into real diffuculty as the SQL*Net driver cannot handle two cursors returning data the same instant - mainly because windows is not true multi-tasking. (Broken As Designed?). Mind you, SQL*Net v2 is out soon and even gets over the problem of OPS$logins when you do not actually log in to the Oracle server. 4. SQL*Async Gives functionality of SQL*Net but over a dialup-type line. 5. SQL*Connect SQL Based DBMS Gateways 6. Open Gateway SQL based and Procedural based DBMS Gateways 7. Net Interchange Software Protocol Converter 8. 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. Version 4 is coming out and has SQL*Menu facilities built-in. 9. 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. Despite improvements, Version 2 looks to be getting the same reputation. 11. 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. 11. 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. Obsolete with SQL*Forms 4. Nearly every DBA who has had to install SQL*Menu (on UNIX platforms at least) that I have spoken to had a hard time. Once that is done, however, it is easy to look after. 12. Oracle*Card Helps develop hypercard like applications in Windows and Mac environments. 13. 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. 14. SQL*Graph Prepares graphs from Oracle data. 15. 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). 16. 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. 17. 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. 18. 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. It will also do break processing and allow you to define calculated fields easily so you do not have to retype them all the time. A suite of tables are kept internally, permitting users to have easy access to their own queries and with the ability to share queries among users. One nice feature for administrators is the ability to set a timed break on user queries to prevent "run-away" queries being generated. There are still some limitations upon the number of tables that can be joined together, but DBA management of some appropriate views would solve this problem. 19. Data*Browser Running under a window this gives graphical and analysis capabilities according to one reviewer and is pretty good. 20. Book Viewer Hypermedia application creating and viewing 21. Co-Author A grammar checking Utility 22. Oracle*Mail and Calendar/Scheduler 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. A similar application provides office calendar information and is packages with Oracle*Mail as Oracle Office. 23. 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. 24. 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. 25. Export and Import These produce files that can be migrated across architectures and can produce archives of a selection of tables, users and or indices. 26. Oracle*Terminal Terminal specification utility, that replaced the CRT suite used by Forms 2.3. Lets you map logical key names, key help and display attributes to custom escape sequences. Offers much more power than CRT, but most of us DBA's could drive CRT. If ReportWriter is the least used because of its limitations, Terminal is the second least used, because the documentation is limited and it is hard to see how it all hangs together. Please someone, send in some hints. 27. Oracle*Alert Lets you define certain conditions that when met trigger an event, usually an Oracle*Mail message, to someone. For example, if monthly sales go to low, fire off a message to the sales manager. 28. SQL Modules Currently in beta is the V1.0 support for Oracle in host languages not including Embedded SQL but according to ANSI Standards. V1.0 requires Oracle V7.1 and is scheduled for production with 7.1 production version. Languages supported will be Ada, C, COBOL and FORTRAN and follows the 1989 ANSI standards with some of the low-level 1992 standard. Also lets you call stored procedures. Limitations are one SQL statement per procedure, no DDL, no dynamic SQL, no multiple concurrent connections. Full(ish) SQL92 expected with V2.0. .... It should probably be noted that Oracle*Forms (4.0), Oracle*ReportWriter (2.0), Oracle*Graphics (2.0), and Oracle*Menu will be all integrated together into one large Oracle*Tools package, the CDE (I think it stands for Common Development Environment). Oracle V7 needs a "procedural option" for database triggers, etc. Oracle 7.1 has been announced and will include Parallel Query and Multi-Media extensions. Alpha Now, Beta mid year, Production end of year. Oracle 8 is slowly being revealed and will include Object Oriented extensions, and specialized servers for images, video, sound, text, and SQL RDBMS. Looking at the SQL-93 spec will give you a glimpse at the way this will move -- abstract datatypes are defined, etc. 1.8. What Public Domain interfaces are there ? Not Public Domain but free include various tools that Oracle will distribute *when* you ask for them. These are utility scripts written by the Oracle Performance Group UK. I was given them and told that they were copyright by Oracle but freely distributable. They do things like take snapshots of internal SYS.V$* tables (one set for V6, one for V7), analyze forms 2, libraries of mathematical functions for forms 3. It includes a version 6 database defragmenter in Pro*C; (V7 automagically defrags). It is usually distributed as a UNIX tar file on a DOS floppy names dostools.tar. Third Party Freebie Tools available include:... 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 major advantage is that it makes many C libraries available for use in an interpretive script language, and is thus more powerful than the shell. One of the useful things that comes with oraperl is a script that takes an SQL statement from a command line and executes it. Another useful script takes a table and turns it into a series of INSERT statements. The current version of Oraperl is 2.004 (version 2, patchlevel 4). You can get it by FTP or a mailserver from any comp.sources.misc archive site, for example: USA: wuarchive.wustl.edu [128.252.135.4] Australia: archie.au [139.130.4.6] The directory should be something like: usenet/comp.sources.misc/volume3[0-9]/oraperl-v2/* There are five parts and four patches. Note that you need to get perl (and check it compiles as is), from any GNU archive site (e.g. archie.au in ~ftp/gnu). I am using perl v4 patch 36 at the moment with oraperl v2 patch 3. I will put in patch 4 soon. Version 5 of perl is currently in alpha release on ftp_at_netlabs.com. The availability of other patches to perl such as curses lets you use screen-handling functions. There is also a GUI development environment that sits on top of Tcl/perl that could use oraperl instead and thus create an X-windows/Oracle development platform. This is called WAFE. [Also available are perl patches to Sybase, Ingres and RDB. You could possibly link them all together, although the SQL functions have different names and syntax depending on which interface you are using.] 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, with consideration now being given to xBase requirements. 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. Nevertheless, now is your chance to put in your wish-list and comment on the design of the API. You can get perl related stuff (including individual perl interfaces to sybase, ingres, informix, etc) from ftp.demon.co.uk 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. I have tried to contact the author, but mail bounces. 4. TCL extensions TCL is a GUI oriented language. Tom Poindexter has extended it
(pretty much like perl is extended) to permit access to Sybase
and Oracle. Tom can be reached by tpoindex_at_nyx.cs.du.edu and the extensions should be available from the TCl archives at harbor.ecn.purdue.edu. Further information would be available via the comp.lang.tcl newsgroup. TCl/Tk is also available from sprite.berkely.edu 5. XSQL Just written by Jeff Stander, this takes an SQL statement and sends the results to an X-window. See credits for his address. 6. Other GUI interface application builders Now, I do not know too much about the following, but they could be useful for you: XView from xview.ecdavis.edu Xtpanel (used by XSQL) from lth.se 7. Documentation While they are still in draft mode, SQL93 standards are available via gatekeeper.dec.com:~ftp/pub/standards/sql A description of ODBC is available on Windows archives cica and mirrored as monu6.cc.monash.edu.au:~ftp/pub/win3/programr/odbc.lzh. These are winword documents and are written using strange styles, fonts and with missing graphics. If your desperate for doco, have a look and see if you feel like spending the time to make them readable. 1.9. 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 ...) Other front end tools include Gupta SQLWindows (which can also be used for its own database). MS-Access and ODBC is fairly new. One criticism is that you are prevented from using the native SQL (which removes the ability to use Oracle extensions) because the front-end product is too "smart" and builds the SQL for you. However, if you ask Microsoft *nicely* they will give you access to a routine that you can send a straight SQL statement to that is passed to the backed RDBMS without modification, thus bypassing this problem.
(Don't you hate mushroom treatment?)
Some more detailed product notes follow. Q+E Range of Products --------------------- Platforms: MS-Windows, OS/2 Requires: SQL*Net if connecting to Oracle Product Range: Broad range of products: ODBC development kit if you want to write your own ODBC driver ODBC 12-pack ODBC drivers for common databases Database library "Large" model for Microsoft C. Many databases supported. include xbase and ASCII files SQL used is native to the host DBMS. xbase includes many functions ASCII files are R/O either delimited or fixed Most functions also supported in MS VisBasic. (Those fiddling with pointers are not) Royalties on run-time .DLLs required. Database Editor Version 2 comes bundled with Excel4 for Win. Version 5 can be bought as an upgrade, and is well worth looking at. Permits easy generation of queries from various sources and generation of summary information. End users can build a parameterized query and then save it. Has forms and report painter. Can edit SQL text directly. Has script ability for talking via OLE and DDE and permitting multiple commands. (No IF, WHILE type constructs tho). Comes with connect library for driving Q+E from C, Visual basic, etc. Comes with demo templates for WinWord, add-ins for Excel, Ami-Pro, Actor, C and VisBasic. Less common ODBC drivers are also available. Contact: Pioneer Software (US) developed it. In Australia, contact Azonic in Sydney. (02) 878-6600 Other Notes: Most comments about these products are very favorable and generally include the terms "rugged" and "well-designed". Rumor has it there are quite a few ex-Oracle guys at Pioneer. IDAPI will be supported, but ODBC is preferred. Apparently someone in Finland markets an OO C++ extension to the database library. Do not know if you can open two different types of DB at once - that would be really useful. 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 Other Notes: Got this info from the vendor. Have heard no reviews. JAM from JYACC -------------- Platforms: MS-Windows, Motif, Open Look, Character Based DOS, UNIX Requires: SQL*Net Features: 1. Can use various other formats 2. More flexible validation than forms triggers 3. Can be real-time 4. Easy C interface Pricing: ? Contact: US: 800-458-3313 International: 212.267.7722 Fax: 212-608-6753 Other Notes: Once worked in an R&D shop we others in the team were using this. They were very happy with it. Will appeal to C and UNIX programmers particularly because it integrates so well with C. It seemed to be designed with uncompromising code-cutters in mind. Not for cowboys and novices. One of the cute things was the ability to use UNIX regular expression matching on fields as part of standard validation. SQR --- Platforms: Various OS and databases Features: 1. Report generation language 2. Can update the database 3. Normal OS file interface capability Contact: US: ? Australia: Sequel P/L, Melbourne Other Notes: Good if you have different database and OS platforms and want to use similar code. I used it for a short time a few years ago, but others I trust who used it more loved it. Quite popular. PowerBuilder ------------ Developer tool for management of databases and generation of front-end forms and reports. Platform: MS-Windows Features: Some CASE and OO constructs for front-end A few internal CASE tables for back-ends Can generate ASCII files of DDL (No triggers or procedures) Looks useful as a working DBA tool as well. Interfaces to most common RDBMSs. Contact: Powersoft, the developer. Other Notes: Smells of original design for Sybase. SuperNOVA --------- Platforms: MS-Windows, Motif, OpenLook UNIX, MS-DOS, VMS Oracle, Sybase, Ingres, Informix, C-ISAM, Teradata Contact: Four Seasons Software, Bilthoven, Holland. Fax: +44 81 446 9143 ? Phone: +44 81 446 6481 ? SQL*ETON -------- Meant to be like "skeleton" for Oracle Forms development. Features: Application development skeletons and utilities Uses standard Oracle tools e.g. Straight forms; no user-exits Can retrofit existing forms. Contact: Logical Technologies Pty Ltd 2nd Floor, 535 Flinders Lane, Melbourne, Vic, 3000, Australia Ph: +61 3 629 5200, Fax: 629 8383 Notes: Logical also make a Forms 2.3 to 3.0 converter - more on this soon. They also have "Definition", an alternative to SQL*Case. 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.10. 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.11. 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 information is used to help decide how to perform the query. The data collection can be very intensive and take ages (unless you use the ESTIMATE option which helps a lot) and run regularly as the population of your database changes lest your queries suffer from being optimized using misleading data. Note that the ESTIMATE function only scans the first part of the table, which may not be representative of the whole table. In The Future: I'll be bringing in edited highlights of Tina C. London's discussion on how to optimize queries using the V6 rule-based optimizer and how to use EXPLAIN PLAN. 1.12. Is there an anonymous FTP site for Oracle stuff ? In Germany, one has been started up by Andreas Bartelt. ftp.Uni-Oldenburg.DE:/pub/unix/oracle It is mostly empty now, but he says to feel free to upload. In the USA, Andy Finkenstadt, is starting one up soonish ftp.vistachrome.com A more general site, best known for storing dbperl stuff is ftp.demon.co.uk:/pub/perl/dbperl/oraperl which is growing a suite of oraperl programs. 1.13. What mail lists are there ? Apart from those that Oracle run if you have Oracle Support.... I heard rumors about an "orawiz" mailling list that had possibly shut down, but here are notes on what I do know. Name: ORACLE-L (ORACLE database mailing list.) Mail Address: ORACLE-L_at_SBCCVM.BITNET List Server Address: LISTSERV_at_SBCCVM.BITNET LISTSERV_at_CCVM.SUNYSB.EDU To Subscribe: Send "Subscribe ORACLE-L Fred J. Nerk" command to list server address To Unsubscribe: Send "SIGNOFF ORACLE-L" command to list server address *NOT* to mail address Archives: Send "INDEX ORACLE-L" to list server address for info Extra Notes Send "INFO REFCARD" to list server address to get reference Send "INFO DATABASE" to list server address to get search notes Has just started activity again after a quiet period. 1.14. What bulletin boards are there ? I do not have the details, but I heard of a BBS called SQLware or something that only charges an annual fee. Does not specialize in Oracle. Have no further details. Would love them and a list of the sort of stuff they carry (hint hint). Unfortunately, it is not a local call to the States from here! Oracle themselves set up a bulletin board for customers with support. Some local user groups are setting up there own bulletin boards. One is the Victorian Oracle Users Group here in Australia. Are there any others? 1.15. What news groups are there ? You probably already know of comp.databases.oracle (where you picked this up). comp.databases is not bad for general questions not related specifically to Oracle problems or techniques. There are other comp.database sub-groups such as theory, ingres, sybase, informix and xbase. BTW: On all these information access media, *PLEASE* keep traffic down by editing severely when you are quoting another mail. Also, if you post an question, it is useful and polite to post a summary of replies and solutions back. For these, prefix the subject with "SUMMARY:". This enables quick selection of important information from the group or your mail and allows sites to keep only summaries. I am not trying to dictate, only speak from the point of view of someone snowed under who wants to be able to vgrep mail and news easily. 1.16. How does Oracle compare to ... ? OK, I'll admit this section is purely personal. My asbestos suit is on. Mail comments if you feel strongly. Sybase: I would be most likely to choose Sybase if Mainly OS/2 site Ingres: Oracle has better SQL Oracle has more robust backend Oracle is more portable Oracle has more flexible security. Ingres front end tools are niftier I would be most likely to choose Ingres if Most using X-terminals on UNIX host Had DBA with good UNIX SysAdmin skills Informix: SQL interfaces can "pipe" output of SQL SELECTS through a host UNIX command. Nifty ! Has SQL dialect like cross of Ingres and Oracle. I would be most likely to choose Informix if Hosted under UNIX on small systems Dollars were scarce DB2: I would be most likely to choose DB2 if What? Me? In an IBM shoppe?

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;

	OK, so that is one problem solved, but DECODE is much more
	useful and it is worth giving a couple of more examples:
		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.

	Decode is especially useful if you even want to choose different
	information from two tables:
		select decode (EXPR_1,
			EXPR_2,	TBL_1.COL_1,
			EXPR_3,	TBL_2.COL_1,
				TBL_1.COL_2)
		from ...

	The DECODE function is always worth keeping in mind.  Many
	times, it will be your only way to do what you want in a tricky
	bit of SQL.

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 not e.ROWID = (
		select min(f.ROWID) from EMP f
		where f.EMP_ID = e.EMP_ID );

	I'll get the OFFICIAL statement according to Oracle sometime.

	The other good thing about ROWID is that it is the quickest way to
	reference a particular row in a table, providing that no table
	reorganization happens in the meantime.  A possible use is to
	select information from a table including the row number into a
	block in SQL*Forms, and pass the ROWID to a user-exit that goes
	and picks up the LONG RAW in that table and displays it as a
	picture.

2.4.	Can I update using a view ?
	Only if the view is a simple horizontal slice through a single
	table.  Columns from the base table can be omitted from the view
	if they are NOT NULL.  Note that views can be created with a
	check option that prevents you using the view to insert records
	contravening selection criteria for that view.

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.

	The way around this is to use PL/SQL, open the driving cursor with
	the CONNECT BY PRIOR statement, and "join" programmatically for
	each row returned.

2.8.	How can I 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 = (
			select max (e2.sal)
			from emp e2
			where e2.deptno = e.deptno
		);

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.  Note that SQL-93 is developing specific
	constructs for using temporary tables.

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.  If you are
	a DBA you should also create private DBA synonyms by running
	$ORACLE_HOME/rdbms/admin/dba_syn.sql in your account.

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.  Given that
	a public domain SQL parser is available, maybe someone could base
	a reformatter based on the grammar definitions therein.

	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.

	BTW, the behaviour of CHAR will be able to be altered by a PFILE
	setting affecting V6 compatability.  With V6 compatability ON, the
	CHAR will be the same as V6.  With compatability OFF, CHAR will be
	padded and VARCHAR will be a true varchar.

	There are some performance gains that MIGHT be possible with padded
	strings: if the initial strings or keys are padded, then the back-end
	engine can quickly skip to the field rather than scanning for field
	delimiters.

2.14.	What is ROWNUM good for ?
	ROWNUM is a pseudo-column returned as rows are selected and
	gives you a number for the row.  "Fine", say a lot of people, "I
	will use it to peel off the first few rows by using (ROWNUM < n)
	in a WHERE clause".  The problem with this is that ROWNUM is
	evaluated BEFORE the sorting (though after the WHERE).  Try the
	following to see my point.

		select ROWNUM, DEPTNO from EMP
		where ROWNUM < 10
		order by DEPTNO, ROWNUM

	When you think about it for a minute or two, it cannot be any
	other way!

	However, ROWNUM is good when you want to get a UNIQUE reference
	for each row returned, as long as you do not mind the order!

2.15.	How do I get a top ten ?
	This question gets bandied around from time to time and the answer
	is often given just to use ROWNUM (see above).  However, what you
	have to do is order them first, then get the first ten.  Some
	answers included creating a temporary table created by a select
	with an order by and then select from the temp table where
	ROWNUM<10.  This will not always work.

	I do not believe that straight SQL is the way to go for such problems
	when you have PL/SQL available.

	My approach would be to use a PL/SQL cursor to select the ordered
	data and abort the loop after 10 records.  If using Pro*C, you do
	not even need a temporary table.  If you are using a temporary table
	then you will not need it to be as big as the original which straight
	SQL will do.

2.16.	How can I control the rollback segment I use ?
	In SQL, you may need to control the rollback segment used as the
	default rollback segment may be too small for the required
	transaction, or you may want to ensure that your transaction
	runs in a special rollback segment, unaffected by others.  The
	statement is as follows:

	SET TRANSACTION USE ROLLBACK SEGMENT segment_name;

	On a related note, if all you are doing are SELECTS, it is worth
	telling the database of this using the following:
	SET TRANSACTION READ ONLY;

	Both these statements must be the first statement of the
	transaction.

2.17.	How can I order a union ?
	Use the column number.

	Say we are getting a list of names and codes and want it ordered
	by the name, using both EMP and DEPT tables:
	select DEPTNO, DNAME from DEPT
	union
	select EMPNO, ENAME from EMP
	order by 2;

2.18.	How can I rename a column ?
	There is no way a column can be renamed using normal SQL.  It
	can be done carefully by the DBA playing around with internal
	SYS dictionary tables and bouncing the database, but this is
	not supported.  (I have successfully done it in V4 thru V6.  Have
	not tried it with V7.)

2.19.	Who are SCOTT/TIGER, SYSTEM and SYS ?
	These three users are common in many databases.  SCOTT is the
	standard demo user, with TIGER as the password.  SYSTEM/MANAGER
	is a DBA account that runs the internals as far as supported use
	goes and SYS/CHANGE_ON_INSTALL actually owns the internals.  It is
	dangerous to fiddle with objects owned by SYS.

	Another common user/password is PLSQL/SUPERSECRET used for PL/SQL
	demo stuff.

	Oh, and don't complain about the passwords being told here.  These
	are default passwords.  For SYSTEM and SYS they ***MUST*** be
	altered for security reasons.  After all, everybody knows them.

2.20.	Who do various access methods compare ?
	How you organize your SQL and indices controls what access
	methods will be used.  The following ranking is valid for
	V6.  I do not know about V7.

	QUERY PATH RANKING (lowest rank is the best)
	Rank	Path
	1	ROWID = constant
	2	Unique indexes column = constant
	3	Entire unique contatenated index = constant
	4	Entire cluster key = corresponding key in another
		table in same cluster
	5	Entire cluster key = constant
	6	Entire non-unique contatenated index = constant
	7	Non-unique single column index merge
	8	Most leading concatenated index = constant
	9	Index column BETWEEN low AND hi or LIKE 'C%'
	10	Sort/merge (joins only)
	11	MAX/MIN of single indexed column
	12	ORDER BY entire index
	13	Full table scans
	14	Unindexed column = constant or column IS NULL
		or column LIKE '%C%'

2.21.	What are clusters ?
	Clustering data is a technique where the storage of records of
	related tables is intermixed, with the dependent records of one
	table in the same block as those of the master record.

	The logic behind this is that you probably most use the
	dependent records after you have accessed the master table.

	Thats all fine, but having a table in a cluster prevents
	you from dropping one and not the other.

	Also, the master and dependent records of other tables must fit
	into an Oracle block, typically 2k (though adjustable at install
	time by a brave DBA).  Now, if you are thinking of cluster
	transactions with an account master record, how long before you
	have blown 2k ?

	No Oracle DBA I ever knew, apart from Oracle staff, thought that
	using clusters was worth the hassle.

2.22.	How can I update a big table without blowing rollback segments ?
	Imagine you have a HUGE table and need to update it, possibly
	updating the key.  You cannot update it in one go because your
	rollback segments are too small.  You cannot open a cursor and
	commit every n records, because the cursor will close.  You
	cannot have a number of updates of a few records each because
	the keys may change - causing you to visit records more than
	once.

	The solution I have used was to have one process (with SET
	TRANSACTION READ ONLY) select ROWID from the appropriate rows
	and pump these (via standard I/O) to another process that
	looped around reading ROWIDs from standard input, updating the
	appropriate record and committing every 10 records or so.  This
	was very easy to program and also was quite fast in execution.
	The number of locks and size of rollback segments required was
	minimal.

2.23.	Why don't I get records for the date I want ?
	You are trying to retrieve data based on something like:
	SELECT fld1, fld2 FROM tbl WHERE date_field = '18-jun-60'

	You *know* there are records for that day - but none of them are
	coming back to you.

	What has happened is that your records are not set to midnight

(which is the default value if time of day not specified).
You can either use to_char and to_date functions, which can be a bad move regarding SQL performance, or you can say WHERE date_field >= '18-jun-60' AND date_field < '19-jun-60' An alternative could be something like WHERE date_field between '18-jun-1960' and to_date('23:59:59 18-jun-60', 'HH24:......YY')

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. Late item: someone reported glogin.sql in another directory: $ORACLE_HOME/sqlplus/admin, but I am not sure of which version and platform. I only REPRESENT the directory as a UNIX path, I do not know whether it was UNIX or not. Actually, I think $ORACLE_HOME/sqlplus/admin is a much better place for it. It is a pity that Oracle does not use a file in your $HOME directory, but I have hacked glogin.sql to use a file in the $HOME if it exists. Another alternative is to set an environment variable SQLPATH
(i.e. $SQLPATH in UNIX, %SQLPATH% in MS-DOS, and probably a
logical in VMS) that points to the directory where the login.sql file is to be read, rather than the current directory. This can include more than one directory, separated by colons (in UNIX, that is ... MS-DOS probably uses semi-colons as delimiters) with the first login.sql found being the one that is used. 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. Note that some people prefer to use the NEW_VALUE directive rather than the OLD_VALUE directive. For this example where only one row is returned, it does not make much difference. 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. A possibility for UNIX: define _editor="/usr/ucb/vi" A possibility for VMS: define _editor="EVE" 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. Note also that the default shorthand character varies with platform. Under VMS, you use the $ symbol. 3.6. Why can't I 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;
(You can change this meaning of "." if it bugs you use the SET
command with the CONCAT argument but I do not recommend changing it without lots of thought). 3.7. How can I see all of a LONG ? First of all, the following might be useful SET MAXDATA 32767 SET ARRAYSIZE 1 You should then SET LONG 5000 SET WRAP and maybe use COLUMN xxxx FORMAT A60 WORD_WRAP for it. Oh yeah, if there is a hex display function in Oracle SQL, but you can't use it on a LONG when you need it. You should also know something of the SET parameters and ensure you do not go outside system limits. MAXDATA Maximum total buffer size ARRAYSIZE Number of records buffered LONG Characters of a long "seen" RecordSize * Arraysize must be less than MAXDATA. Note that ARRAYSIZE defaults to 10 or 20, and you will probably blow the MAXDATA extracting large LONGS so check these out and put appropriate SET statements in your script. It is worth having a couple of library scripts in Oracle that look after these parameters in a co-ordinated way so that other scripts could simply start $ORACLE_HOME/local/plus/set4long TRUE or start $ORACLE_HOME/local/plus/set4long FALSE 3.8. How can I force a column to begin on the left of the page ? Use the COLUMN FORMAT command with FOLD (BEFORE|AFTER). 3.9. Can I alias SQL commands ? There is no real alias mechanism in SQL*Plus, but you can do something similar using && variables. 3.10. Can I escape significant punctuation marks ? Many UNIX-oriented users like to have a general "escape" character to remove the signifance of special characters. They want to be able to refer to a real "&" using \&, for example. Well, you cannot. [Hint to any of you at Oracle]. What you have to do is check out the SET command parameter that is specific to the character you were interested in and change it. For example: rem alter the character set define "~" select "This & that are normal" from dual; accept yourinput prompt "Give me a string" select ~yourinput from dual; set define "&" Now this is fairly painful if you want keep track of things and there are some characters you cannot escape. Do a "SHOW ALL" and you'll see all the various things you can play with.

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  'U', 'Adult Unknown' FROM DUAL
	UNION
	SELECT  'u', 'Juvenile Unknown' FROM DUAL

	It's for a zoo catalog of animals......   :-)

4.2.	How can I get find to look at description with list of values ?
	Imagine that you are have the following list of values text for
	a key (e.g. for the DEPTNO field of the EMP block):
	select DEPTNO, DNAME, LOC into :EMP.DEPTNO from DEPT

	The list-of-values find capability only works on the first field
	however, which is next to useless.  To get the searching on the
	name, you need to put the name first in the SELECT clause.

	Of course, it needs somewhere to go, so you need to have a
	field (GLOBAL variables won't work) that you use as a bitbucket.
	select DNAME, DEPTNO, LOC
	into   :CONTROL.BITBUCKET, :EMP.DEPTNO
	from   DEPT

	Of course, you had better make :CONTROL.BITBUCKET 255 characters
	wide to avoid lots of annoying truncation messages.

4.3.	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 even had a screen painter or field triggers
	or key triggers, so I learnt gradually).

	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.  You can also automatically have the prompt repeated
	in multi-row blocks.
	
	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.

	Why don't Oracle support the full .inp capabilities ?

	By the way, check out the advantages of using .inp files as
	referenced source, particularly version control mentioned near
	the end of this document.

4.4.	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). Depending on how you set up forms, most users see tables like SYSTEM.FORM_APP, SYSTEM.FORM_BLK, etc. These contain code for forms owned by that user. If you are SYSTEM, then you can see tables SYSTEM.FORM_APP_, SYSTEM.FORM_BLK_, etc that have code for all users in them. 4.5. Why can't I see data in a control field ? If you have the DISPLAYed attribute on for a field, that only means that the field is displayed, not the data. You need to turn on ECHO INPUT, even if the field is not to be entered by the user. 4.6. Why is my terminal scrambled in a user exit ? When running Oracle Forms, the line settings are all changed. Particularly whether the I/O is raw or cooked. If you are doing terminal I/O in a user exit you will need to save the settings, set them to what you want, do your stuff and then reset them. You can save settings with the UNIX shell command stty -g > stty.save.file Restore settings using stty `cat stty.save.file` 4.7. What happens to LONGs ? LONGs are only partially supported in Forms 3. Anything after the 255th character goes to the great bit bucket in the sky. How to deal with LONGs using forms is to get the ROWID into a field in your form. Your user exit picks this up and selects from the base table using the ROWID, then breaks the LONG up into a number of forms fields. Rumor has it that Forms 4 will handle LONGs a lot better. 4.8. What are user-written form level functions ? These are a specialized version of procedures not properly documented. They vary from procedures in that they keyword "function" is used, they must be declared with a return type and the keyword "return" is used within the code. A simple example is as follows: DEFINE PROCEDURE NAME = TO_FAHRENHEIT DEFINITION = <<< function MY_FUNCTION ( VNAME in number ) return number is begin return ((VNAME * 9 / 5) + 32 ); end; >>> ENDDEFINE PROCEDURE Within other code, it is used as follows: :f := TO_FAHRENHEIT (:c); Choosing to use a function over a procedure is a stylistic issue. I prefer to keep all arguments to functions read-only
(i.e. declared as "in") and use them where I want to use the
result in an expression a lot. I also use the word function for those procedures that return a simple value and do not really perform side effects. 4.9. How can I use regular expressions for field validation ? OK, you're probably a UNIX person asking this. For those of you that are not, regular expressions are a much more expressive way of specifying a "wildcard string", analogous to % and _ used with the "LIKE" keyword of SQL. For example [A-C]*T would match T, AT, CT, CAT, BAT, BBACACCACT. You need to write a C user exit that uses the sedstr() function. This permits both matching (and tells you the position of the match) or substitution (hence the name - "sed" on C strings). This is available from any comp.sources.unix archive. This also uses other PD code, the "regexp" package out of EMACS. You can get EMACS from any GNU archive. Of course, you could write the whole thing from scratch using the regexp routines provided straight from the C libraries, but sedstr() is *MUCH* easier to use. Novice C programmers can use sedstr(), they cannot use the standard regexp package. Both these archives are on archie.au in Australia, in ~ftp/gnu and ~ftp/usenet/comp.sources.unix. I think garbo in Europe and simtel in the US also have this stuff. Contact your local archie server to find the site nearest you. 4.10. What is a user-exit ? A user-exit is a routine in some other language (typically C or Pro*C) written locally and linked in to Oracle forms. When I say relinked, I mean create a new executable that includes your object (*.o) files. This *must* be managed by your DBA and thought out carefully, as your forms executable size will increase. Also, your code should be damn well behaved or you'll core dump everyone and be most unpopular. User exits are called from your PL/SQL and can be passed an argument as a bit fat string up to 255 chars. They can read and write forms fields using "get" and "put" functions. It is thus a bad move to include application-specific code as user exits except where absolutely necessary. General purpose routines are another matter. Attributes of good candidates are
(1) good at something forms cannot do easily
(2) not suitable for V7 DBMS pipes.
(Actually DBMS pipes are sort-of "kernel user-exits") Examples of good candidates include a) getting data from non-Oracle sources: text files, other RDBMS b) extra string routines such as regex type searches c) dealing properly with LONGS d) fancy mathematical functions e) standard I/O via files and pipes f) Interprocess communication: shmem, sockets, msgq, sems, etc. [ Warning: Ad On ] Actually I've written a few of these, along with the parsing of the single command argument into argc, **argv for you C programmers. Cannot release it though as it is proprietary, although good deals negotiable with my lords and masters. [ Ad Off ] proprietary). User-exits are good for things that are (1) not in sqlforms area of expertise and/or (2) to be done at the front end, not via V7 DBMS pipes. Good examples would be routines to perform regex searches, read data from non-Oracle sources, send inter-process communication messages routines to calculate Normal distribution stuff, read stuff from text files into fields. 4.11. How can I call a popup window for field validation ? OK, so you are trying to go to a popup window in your on-validate trigger and you cannot. Basically you cannot do "go(blk|fld) (blk|fld)name)" type things in most "on-", "pre-" and "post-" triggers. What you need to do is a. Write a procedure or function that does the to-and-froing. b. Call that function from all the appropriate "key-" triggers in that field context. You will need to check whether the field has changed, etc. Yep, it is ugly. Anyone got a better way ?

5. SQL*FORMS 4 QUESTIONS


5.1.	What new features can be expected in forms 4 generator from CASE ?
	Forms will be generated with support for forms 4 widgets such as
	push buttons, radio boxes, check boxes.  GUI objects will follow
	layout preferences defined in the CASE dictionary.
	Client-server applications will be generated appropriately.

5.2.	What new features can be expected in forms 4 ?
	Full backward compatability with SQL*Forms 3.0.  If you have
	forms 2.3 code, this should be updated first to SQL*Forms 3
	using iac30/iag30.  Backward compatability with SQL*Menu 5.0
	is also supported.  (SQL*Menu functionality is included in
	SQL*Forms 4).

	GUI objects (buttons, check boxes, radio buttons, images,
	dynamic boilerplate, etc) even in characters and block modes.

	Bitmap editor.

	Support for source in flat files.

	Better integration with other Oracle products (such as graphs,
	etc, embedded in a form).

	Shared modules:  Some modules can be put into shared memory
	segments if you write a nice set of library functions and
	triggers.  This cuts down the total memory requirements.
 

6. PRO*C QUESTIONS


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

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

	Another way using Pro*C is to DECLARE your Pro*C variables as
	pointers to varchar, and declaring the *real* storage in parts
	of the file not seen by Pro*C and using C preprocessor symbols.
	You can be quite evil to embedded SQL precompilers if you are
	wanting to get Oracle to return a maximum of n characters and
	not overwrite your n+1th null character and thus make C strings
	easy.  Simply declare your C variables at the appropriate length
	to C rather than in a DECLARE section.  To hold a 6 character
	string, you need seven bytes.

	Put the EXEC SQL DECLARE section inside C comments and make the
	size of the char to be 6, easy if you know that the column is
	char(6).  Note that the EXEC keyword must NOT appear on the line
	in which the comment opens, but further down.
		e.g.
		/* Begin C comment
			This stuff not seen by C but still visible
			to Pro*C
		EXEC SQL BEGIN DECLARE SECTION;
			myvar	char(6);
		EXEC SQL END DECLARE SECTION;
		End of C comment */
		char	myvar[7] = { 0,0,0,0,0,0,0 } ;

	Note that Pro*C then assumes it is binding a 6 byte char field,
	C has got 7 bytes, which you set to all null (0x) characters
	and you can get data in and out quickly as Oracle will only take
	or put 6 characters, but C functions like strcmp() will not
	runaway as the 7th byte has a null terminating character.
	Unless you hide the SQL declarations from C, you will get duplicate
	definition errors, which is why you DECLARE within C comments.
	Definitely not supported but can make life much easier.  It is
	also the nicest perversion of a language facility that I ever
	cooked up.  This trick was first developed for the Ingres esqlc
	precompiler.  Have not tried it for a while - I use my varchar
	handling library to deal with varchars and I do not risk getting
	into trouble should the next release of Pro*C get the smarts to
	know if it is commented out.  Let me know if you find it to
	work.

6.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 the keyword 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.4.	Why do my compiles crash or weird things happen ?
	Sometimes your C compiler does not obey the System V way of doing
	things.  This happens in BSD derived versions of Unix (like SunOS)
	and VMS.  Basically if there is an option available to use System V
	compatability on your gear, then those options should be used when
	working with Pro*C.  See similar question in UNIX QUESTIONS section.
	If you have a "dual universe" machine, like Pyramids and some
	others, make sure you do your C compilation in the AT&T System V
	environment.

6.5.	Can I use C++ ?
	Pro*C is not designed for C++.  You can of course use the OCI (the
	Oracle C Interface) in C++ and link in the appropriate libraries as
	you do not have to pass your code through the Pro*C precompiler.

	If you *do* want to use C++ with embedded SQL, then what you should
	do is write a "wraparound" C function in Pro*C and call this from
	your C++ code where appropriate.  Be careful, however, because not
	all the code produced from Pro*C is suitable as re-entrant code.

	I believe that a third party has an SQL*C++ product available,
	but I have no details as yet.

6.6.	How do I use OPS$login ?
	Use "/" for the username/password combination

 

7. CASE QUESTIONS


7.1.	Can CASE generate forms with owner prepended to table names ?
	Set the TABOWN option.

7.2.	Can CASE generate V7 databases ?
	CASE 5.1 is needed to take full advantage of V7 constructs from
	within CASE.

 

8. UNIX QUESTIONS


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

	The only space you need is that for the final compressed file
	and about 4 disk blocks for the pipe.  This is because if the
	pipe is full (compress might be slower than export), the writing
	process is blocked until the pipe empties a bit.  The result is
	very efficient with disk.

	You can do a similar thing the other way to import the file.

8.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.  For VMS, you
	would have to write a DCL script that fiddles with SYS$INPUT
	and SYS$OUTPUT and uses LEXICALS to trim the lines.  Don't ask
	me for details - my VMS is rusty and I do not have a manual
	handy.  Could someone mail me (directly) the DCL and I'll
	include it here and move the question to SQL*Plus.

8.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.  Under VMS, for example, I had to
	write a C program that did a getenv(3C) and then produced the
	sqlplus script file that had the define in it.  However,
	because of the process model of VMS, it was much too slow
	apart from the most important variables.  Maybe let the C
	program take multiple arguments and produce a line for each.

8.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.  Piping through grep is handy, as is
	through sed or even awk for formatting.  Seriously though, get
	oraperl.

8.5.	Why do Pro*C compiles or programs crash on my Sun ?
	Some Background
	---------------
	SunOS 4.x (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.  You may have GNU C installed on your system.  Note
	that Solaris 2 is System V-based.

	SunOS 4.1 comes standard with TWO C Compilers.
		/bin/cc		The BSD based compiler
				Gets default includes and libraries from
				/usr/include and /usr/lib
		/5bin/cc	The AT&T compiler
				Gets default includes and libraries from
				/usr/5include and /usr/5lib

	You may also have GNU C installed on your system.  This too is
	based upon BSD.

	The BSD and AT&T compilers use libraries and include files from
	different directories, reflecting the system libraries of the two
	different dialects of UNIX.  Sometimes the function is just called
	under another name, sometimes the number of parameters (and their
	ordering) is different.

	Examples include
		Get working directory		getcwd vs. getwd
		Copy Memory			memcpy vs bcopy
		Compare Memory			memcmp vs bcmp

	Oracle Pro*C libraries call System V library functions if one is
	commonly available for your architecture.  Obviously, if the Oracle
	libraries have references to the System V version, they will either
	be unresolved in the BSD libraries, or worse, resolve to something
	inappropriate.

	What To Do
	----------
	Oracle recommends that when using Pro*C (or even installing and
	linking Oracle) you have the /5bin directory ahead of /bin in your
	PATH variable.

	[  This is good practice anyway, because you will get used to the 
	System V versions of commands (df and ps are good examples of this -
	I usually create a soft link to /bin/ps and /bin/df as 
	/usr/local/bin/bps and /usr/local/bin/bdf - a similar practice to 
	HP when they supply both versions of a utility). ]

	You might like to try the following if you *really* want to use
	the GNU C (or other BSD) compiler.

(1) Using GNU C, but point at System V libraries
(2) Using GNU C and libraries but add in your own, written
in GNU C that mimic the system V calls. You'll know which ones you need, as they will be the unresolved references. Some of these may simply be stubs that call the BSD function of a different name or reorder the parameters.
(3) Extract the functions you need from the System V libraries
and add them to your list to be linked in via the make file. 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).
Look, I know it involves some hacking, but if you are that keen on Gnu C and only using your products in-house, you should be OK. I am not knocking Gnu C, mind you. Gnu C is a must for many shops developing in-house code and compiling things from the net. One R&D shop I worked in tried it out on our code, and it gave tighter executables than the native HP compiler at the time !!!! One other thing to note if you are running Solaris 2 is that the good C compiler has been unbundled. The compiler that comes standard with Suns now is reported by some to be dain bramaged. If you are serious about your compiler (and you should be if you are running Oracle) then spend the money and get the *REAL* developers kit. (At least this is not as bad as some UNIX systems distributed without a compiler!) 8.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/:.*//' 8.7. How can I tell make about SQL*Forms ? I use a make include file $ORACLE_HOME/local/include/oracle.mk. Among other things it sets up the following : OUP = / # Oracle user/password, defaults to OPS$login IAG = iag30 # Generates .frm from .inp IAC = iac30 # Converts b/w FORM_% tables and .inp # I use .dbo to sign a form out of database, and .dbi as a sign-in .SUFFIXES: .inp .frm .dbo .dbi .inp.frm: - rm $*.frm $*.erf - $(IAG) $* $(OUP) > $*.inp.err 2>&1 test $*.frm .inp.dbi: - rm $*.frm $*.erf - $(IAG) $* $(OUP) > $*.inp.err 2>&1 test $*.frm - $(IAC) -d $* $* $(OUP) $(IAC) -i $* $* $(OUP) touch $*.dbi .dbo.inp: - mv $*.inp $*.inp.bak $(IAC) $* $* $(OUP) - touch $*.dbo touch $*.inp

9. MISC QUESTIONS


9.1.	How can I alter 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

	BTW, it IS documented in V7.

9.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 1000 series.

	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.

	With Netware (Novell) and WABI (run Windows binaries) facilities
	available or bundled, it is a pretty attractive machine.

	In the mid-range, I do think I should at least mention HP and
	Sequent.  Certainly I have never had any problems with HP kit or
	service.

	For low end users, even though a Novell, or OS/2 server might
	look cheaper, I do think that UNIX makes a better architecture
	for something as complex as a multi-user database to live in.
	Unless, of course, rumors of Novell bundling Oracle sometime in
	the future are true and the price becomes impossible to resist.
	The (apparent) lack of Virtual Memory in Novell means the Oracle
	server could run out of memory and blow up.

	Windows NT could be interesting.  It promises heaps of features,
	but on looking recently at the "system calls" for Windows I
	baulked at the loss of clean elegance at UNIX structures.  Perhaps
	Windows NT will be just as threatening.  [What platform does NT
	perform best on?  A 35mm slide projector :-)]  Its chief
	designer, an ex-DEC SE, however, does deserve respect tho.

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

	e.  Perusal of Oracle source code for scripts and the like shows
	that they use RCS.  If it's good enough for them to use PD
	products .....

	f.  RCS is more efficient than the System V standard SCCS.  SCCS
	stored the original intact and then the deltas.  RCS stores the
	most recent intact and then the deltas.  Getting more recent
	files is thus easier with RCS.

	g.  RCS permits you to "remove" intermediate versions (e.g.
	between 7.2 and 7.5 when you are on 9.1) when required.  SCCS cannot.

	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 are enclosed between dollar signs.
	
	These keywords include
		RCSfile		filename without path
		Header		Combination of the keywords below
		Author		The user name of the file owner
		Source		Full path/filename of source
		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 should be able to see expanded versions of these near the
	top of this file.  I have not used the dollar signs here as they
	would get expanded and ruin the format.

	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 using the Log keyword.

	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.

	Various RCS utilities allow you to extract any version, merge
	changes from different branches and see the differences between
	any two versions.

	Versions for multi-user operating systems allow better control
	than the native operating system, as you can assign a list of
	users authorized to manipulate the files.  Authorized users can
	be different for each file.

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

9.4.	What books are available about Oracle ?
	First of all, I must mention the range of books produced by
	Oracle.  You might not get the ones you need simply by ordering
	the manuals for the products you buy.  For example, there are quite
	a few books written by the Oracle UK team that are excellent,
	especially "Advanced SQL*Forms Techniques" and various CASE
	references (well Oracle UK was originally an independent group
	that created CASE, then known as SDD).  Also look out for the
	Oracle Performance Tuning Guide.  Some of the "Oracle UK" books are
	published independently by Addison-Wesley and written by Richard
	Barker and Cliff Longman.  Note that Oracle have a whole book
	that lists technical publications available from them.  This is
	worth getting.

	Other books include:
	1.	Oracle Tuning Beyond The Manual
		Peter Corrigan and Mark Gurry
		Covers Oracle performance from design, DBA and
		application programmer perspectives.  This book has been
		recommended by a number of people, and having worked
		with Mark briefly in 1988, I can tell you that he is no
		idiot.  The thing to remember about this book is that
		there are about to be two versions of it.  The first has
		been published by the authors themselves for A$69.
		(52 Rowena Parade, Richmond, Melbourne, Vic, Australia 
		or mag_at_scammell.ecos.tne.oz.au).  The second version is
		to be made available by O'Reilly and Associates in Sep 93
		and is probably to be called "Oracle Performance
		Tuning".  According to Mark, there has been almost a
		complete re-write together with the O'Reilly editors and
		it is now at the final review stage.  While there have
		been a number of typos introduced by the rewrite which
		are currently being addressed, Mark seems to think that
		the result is a much more readable book than the
		original version.  O'Reilly are sending me a review copy
		(thanks!) so expect more details in upcoming issues.

	2.	Mastering Oracle V6
		Daniel Cronin and Joe Lee
		Provides a good overview of V6 application development
		in one handy book with an almost complete application
		used as an example.  This book came out very soon after
		the release of V6 and thus only discusses Forms 2.3 and
		has a V5 to V6 migration guide.  Not really detailed
		enough for power programming, does not go into Pro*XXX
		languages, but great for somebody just moving into RDBMS
		or Oracle.  It would be nice to see a new edition for
		V7.

	3.	??????????
		George Koch
		Have not seen this book, but has generally received
		favorable reviews.  Apparently the V7 stuff seems tacked
		on to the end.

	4.	Dan's Oracle7 Guide
		Daniel B. Bikle
		I've put this here, because I think Dan will be
		marketing this, though some drafts are available from
		him gratis.  He is building an ASCII file of tips and
		example code that can be cut and pasted into your code.
		I've seen early drafts and it looks useful.  Dan is an
		ex-Oracle employee and can be contacted via
		dbikle_at_alumni.caltech.edu and US phone 415/854-9542.


END OF DOCUMENT

-- 
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 Aug 31 1993 - 08:04:44 CEST

Original text of this message