Re: General Oracle FAQ

In <dtb.744524773_at_otto> dtb_at_otto.bf.rmit.oz.au (David Bath) writes:

>Have been off-line for a while.
>Here is the most recent copy of the general FAQ I have.
>Uploading to RMIT was a pain. Hope this is not garbled.
>Let me know if it is and I'll get the file there via the
>Adidas Network Protocol :-)

>ORACLE NON-DBA FAQ (Frequently Asked Questions)
>-----------------------------------------------
>$RCSfile: oracle.faq $
>$Revision: 1.2 $
>$Date: 1993/06/04 00:29:25 $
>$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 ?
>1. GENERAL QUESTIONS
> 1.1. What is Oracle ?
> 1.2. What are the advantages of Oracle ?
> 1.3. What are potential disadvantages of Oracle ?
> 1.4. What is SQL ?
> 1.5. What is PL/SQL ?
> 1.6. What products are available from Oracle ?
> 1.7. What Public Domain interfaces are there ?
> 1.8. What third party interfaces are available ?
> 1.9. How portable are Oracle applications to other RDBMS ?
> 1.10. What Query Optimisers are there ?
> 1.11. Is there an anonymous FTP site for Oracle stuff ?
> 1.12. What mailling lists are there ?
> 1.13. What bulletin boards are there ?
> 1.14. What news groups are there ?
>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 ?
>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 ?
>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 ?
>5. SQL*FORMS 4 QUESTIONS
> 5.1. What new features can be expected in forms 4 generator ?
>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 ?
>7. UNIX QUESTIONS
> 7.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?
> 7.2. How can I prevent trailing spaces in a spooled report ?
> 7.3. How can I get an environmental variable into SQL*Plus variables?
> 7.4. Can I pipe stuff through SQL*Plus ?
> 7.5. Why do Pro*C compiles or programs crash on my Sun ?
> 7.6. How can I find a lost Oracle export file ?
> 7.7. How can I tell make about SQL*Forms ?
>8. MISC QUESTIONS
> 8.1. How can I alter table storage parameters from an export file?
> 8.2. What makes the best Oracle server for a network ?
> 8.3. How can I implement version control ?
 

>
> COMP.DATABASES.ORACLE NON-DBA FREQUENTLY ASKED QUESTIONS
> ========================================================
>0. INTRODUCTION
>0.1. What is this document ?
> This is the FAQ (Frequently Asked Questions) file for non-DBA
> issues of 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.
 

> 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 0.4 had a misplaced paragraph, one from ROWID
> got put into the discussion on ROWNUM, and later I thought
> that ROWID was a typo in that para and "fixed" it - Sorry]
>
> 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.
 

> BTW ...........................................................
> If you have a comment as to whether this should be distributed
> as a uuencoded zip or whatever as well, let me know.
 

>0.2. Who has contributed to this document ?
> David T. Bath
> Organization:
> Global Consulting Services Ltd.
> Activities:
> DBA and UNIX SysAdm for Global, Hired out for
> general consulting and education.
> Address:
> dtb_at_otto.bf.rmit.oz.au
> Assisted With:
> Initial cuts, general editing
> Andy Finkenstadt
> Organization:
> Vista-Chrome Inc.,
> Homes & Land Publishing Corporation
> GEnie Unix RoundTable Manager
> Activities:
> Is currently organizing an Oracle archive site
> Address:
> andy_at_vistachrome.com
> andy_at_genie.geis.com.
> Assisted With:
> General comments throughout text
> Kevin Stock
> Organization:
> Encore (?)
> Activities:
> Author of oraperl patches to perl
> Heavily involved in DBperl
> Address:
> kstock_at_encore.fr
> Assisted With:
> Oraperl and DBperl information
> Joel Garry
> Organization:
> unknown
> Address:
> INTERNET: tumidity_at_netlink.cts.com
> UUCP: ...!ryptyde!netlink!tumidity
> Assisted With:
> Opinions on RPT/RPF
> Daniel Druker
> Organization:
> 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
> 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
>
> Apologies to those I have omitted.
 

>0.3. How is this document formatted ?
> Tabs are set to 8 spaces.
> Form feeds are placed between major sections.
> 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.
 

> 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
> system rather than production systems, although I have worked on
> some largish systems (>5G).
 

> 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, CASE,
> Financials and Menu, among others. Send in contributions!!!!!
>
>1. GENERAL QUESTIONS
>1.1. What is Oracle ?
> Oracle is a trademark of Oracle Corporation and in common usage
> refers to the database engine (which actually looks after the
> data) and a range of front-end products.
 

> Oracle is the largest selling SQL based RDBMS and on the whole,
> is in my opinion, the most commercially useful.
 

> Major competitors to Oracle include:
> DB2 (IBM platforms only)
> Informix
> Ingres
> Sybase
 

> It is *not* to be confused with the Oracle of Delphi, which led
> the market in executive information systems in the ancient world
> until about 7 BC. The chief data analyst (the Pythia) would get off
> her skull on steam and laurel leaves and mutter gibberish (Said
> Quietly on Laurel, SQL for short) which was then translated by
> priests in an ambiguous manner in order to please the end-user
> and thus extract a hefty donation. Probably the most famous bit
> of management information was "if you cross river X, a great victory
> will be won". Taking this advice, management crossed the river,
> promptly got involved in a great battle, getting decimated. The
> great victory was won .... by the other side.
 

>1.2. What are the advantages of Oracle ?
> a. Portability
> Oracle is ported to more platforms than any of its competition,
> running on more than 100 hardware platforms and 20 networking
> protocols. This makes writing an Oracle application fairly safe
> from changes of direction in hardware and operating system, and
> therefore a safe bet. One caveat, however, is that applications
> using some constructs (such as field level triggers) may have to
> be reworked when porting them to a block mode environment. You
> can also develop a fairly fully featured application with little
> knowledge of the underlying OS. Personally, I have developed
> applications on OS systems barely knowing how to copy and edit
> text files.
 

> b. Market Presence
> Oracle is by far the largest RDBMS Vendor, and spends more on
> R&D than most of its competitors earn in total revenue. Oracle
> has the largest independent RDBMS market share in VMS, UNIX and
> OS/2 Server fields. This market clout means that you are
> unlikely to be left in the lurch by Oracle and there are always
> lots of third party interfaces supported and also, proficient
> staff are relatively easy to get.
 

> c. Version Changes
> Having been working with Oracle since beta versions of Oracle 4,
> I have found that Oracle have given almost a full major revision
> warning when a documented feature was intended to be withdrawn,
> and have never had the behaviour of a feature change dangerously
> like I have with some other products (requiring me to check
> every source file in a forms interface for example). However,
> it should be noted that there were some changes in names of DBA
> scripts between v6.0.0 and v6.0.30.
 

> d. Backup and Recovery
> Oracle provides industrial strength support for on-line backup
> and recovery and good software fault tolerance to disk failure.
> You can also do point-in-time recovery. Of course, you need the
> archive mechanisms and storage space to do this, but Oracle
> supports continuous archiving to tape devices spanning multiple
> volumes.
 

> e. Performance
> Speed of a *tuned* Oracle database and application is quite good,
> even with large databases. Oracle refer to >100 GB databases and
> I have personal experience administering 10 GB databases.
 

> f. Cursor Support
> Oracle, like Ingres, but unlike Sybase (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 base-level
> ANSI-2.
 
> h. Multiple Database Support
> Oracle has a superior ability to manage multiple databases
> within the same transaction using a two-phase commit protocol.
> This is best implemented in V7. You can fairly easily move
> where data is actually stored from node to node in a network and
> have data mirroring, making it easy to optimise the location of
> the data from time to time. This is not so easily done with
> offerings from other vendors or earlier versions of Oracle,
> where you were not able to update more than one database in the
> one transaction with any reliability. This meant that you could
> not move data around without recoding your programs. With V7,
> your DBA can optimise the location without pre-planning by
> programmers or re-examination of the code prior to the move.
 

> i. PL/SQL
> PL/SQL, the procedural extensions, is a draft ANSI standard for
> procedural DBMS languages. 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.
 

>1.3. What are potential disadvantages of Oracle ?
> Cost. Oracle ain't cheap. At least before you consider costs
> of porting, programmer availability, etc, etc. Remember, you
> will almost certainly need a full-time DBA. Good DBA's are not
> cheap, but are worth their weight in gold. You will also need
> training for programmers and DBA's. Again, not cheap, but shop
> around - both for the cost of the courses and for the content.
> Oracle are not the only people who give Oracle training, and
> often a smaller consultancy can tailor a course for your needs
> and still be cheaper per training hour per person.
 

> Oracle is not (currently) as object-oriented as some of the
> competition.
 

> Implementation on some systems betrays the heritage of the
> system it was developed on (e.g. Mail REEKS of VMS) and can be
> counter-intuitive to programmers used to their (non VMS or non
> UNIX) OS. On some systems, performance is not what you would
> (or were led to) expect, so you may need to upgrade your
> system. (The moral of this story is use systems that are
> inexpensively scalable). Joel notes particularly that IBM 9370
> implementation is slow. (Hey, so is that news to us UNIX types?)
 

> While quite a few people have commented on buggy code, poor
> implementation, different keystrokes for different hardware, etc
> (all of which have some justification), this is in my opinion
> 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.
 

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

>1.6. What products are available from Oracle ?
> Apart from the core engine and consulting/education services...
> This list is NOT exhaustive. If I have missed anything, let me
> know, preferably with a quick review.
 

> 1. SQL*Plus
> Known in days of yore as UFI (User Friendly Interface), this is
> the basic "shell" for queries, basic reports and database
> manipulation. It can be used interactively or driven from scripts.
> It is a must-have as installation requires scripts to be run
> through this interface and most general administration by
> programmers will be carried out through it.
 

> Apart from the basic ability to issue SQL and PL/SQL commands,
> it has a number of extensions to permit programming (parameter
> passing, variables, prompting for user input, etc) and report
> formatting.
 

> It operates identically across all platforms.
 

> 2. SQL*DBA
> A user-unfriendly version of SQL*Plus with extra power for DBA's.
> The history editing facilities of SQL*Plus have been removed so
> that you do not spend all your time in a product that has extra
> (needed but dangerous) capabilities to do things like start up
> and shut down your database.
 

> It also has general performance monitoring facilities. Oracle 6
> SQL*DBA is mainly line oriented but has a screen oriented
> performance monitor. Oracle 7 SQL*DBA is entirely screen based
> by default (although you can invoke it as follows to get line
> mode)
 

> sqldba lmode=y
 

> 3. SQL*Net
> Needs to run on PC's and Mac's before you can access Oracle on
> other machines. Most mid-range and larger machines have it as
> part of the core product.
 

> Needs to be used on ALL machines to communicate across platforms
> or via a network. It is a separately licensed product for Unix,
> MS-DOS and Macintosh versions and you need both compliant versions
> to communicate between a client (which can be an RDBMS) and a server
> (which must be an RDBMS).
 

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

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

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

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

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

> 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 product files that can be migrated across architectures
> and can produce archives of a selection of tables, users and or
> indices.
 

> ....
> It should probably be noted that Oracle*Forms (4.0),
> Oracle*ReportWriter (2.0), Oracle*Graphics (2.0), and
> Oracle*Menu (6.0) will be all integrated together into one
> large Oracle*Tools package.
 

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

> 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 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.003 (version 2, patchlevel 3).
> 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 three 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.
 

> 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 perl that could use oraperl instead
> and thus create an X-windows/Oracle development platform. This
> is called WAFE.
 

> I have posted a separate FAQ for oraperl that I obtained from
> Kevin.
 

> 2. DBperl
> Soon to be available is DBperl, which unifies the syntax of the
> Sybase and Oracle patches to perl. It will also permit access
> to Ingres and Interbase.
 

> There is a mailing list, <perldb-interest_at_vix.com>. You can mail
> <perldb-interest-request_at_vix.com> to receive it. This contains
> stuff similar to the files that flow through this newsgroup, so
> a fair bit of it is technical discussion on the innards of
> DBperl which may not be to your interest. As soon as I find
> about releases, I will announce it in this FAQ.
 

> 3. Unload
> Another utility is unload (which a lot of people rename to
> sqlunload). It takes a SQL statement and creates two files, a
> data loader control file and the data file. I think this was in
> comp.sources.misc. I'll post it up to comp.databases.oracle if
> need be (I forget where it came from originally). If anyone
> knows where it comes from and where it is archived, let me know.
> I have tried to contact the author, but mail bounces.
 

> Please let me know of any others. I'm currently looking through
> a few -- more details next issue.
 

>1.8. What third party interfaces are available ?
> Heaps. Oracle interfaces are usually the first RDBMS interface
> a third party tool vendor will support. If you are on a PC,
> look at Q&E that comes with Excel 4 for Windows, providing you
> have SQL*Net. (Mind you, this is an old, cut down copy of Q&E).
 

> Some interfaces written mainly for the UNIX world support a
> number of databases. Two examples I have used and found OK were
> JAM (from JYACC) - good for C programmers who wanted to write
> forms quickly, and UNIFY - which also supports its own backend
> if you do not have Oracle (or Ingres or ...)
 

> Other front end tools include Gupta SQLWindows (which can also
> be used for its own database) which is not bad and PowerBuilder,
> which has been reported to me as promising, but not as mature as
> Gupta.
 

> 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
> Requires: SQL*Net
> Features:
> Broad range of products from end-user tools to Visual
> Basic libraries for programmers. Many other database
> servers are supported, including flat text files with
> arbitrary delimiters. What is worth noting, is that the
> SQL statement in the cut down version even lets you edit
> the SQL statement, taking advantage of the particular
> vendor brand of SQL. Note that a cut-down an old version
> of Q&E comes with MS-Excel for Windows. At the moment,
> there is a special upgrade offer for this.
> Contact:
> Pioneer software (no more details, I am afraid)
> Other Notes:
> While I have not used other than the basic version that
> comes with Excel, all the comments I have heard about the
> full blown version and the Visual Basic libraries are very
> positive, apart from one friend who is as pernickety as
> myself and found a bug with an upgrade.
 

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

> If anybody reading this is a user or vendor of a particular
> third party interface that you think should be mentioned, mail me
> direct rather than via the newsgroup together with a quick summary.
> This section needs expanding and correction.
 

>1.9. How portable are Oracle applications to other RDBMS ?
> While the core SQL is portable, each vendor has their own
> extensions and ways in which you need to structure the data
> model to take advantage of the way each RDBMS does things.
> To get any real performance, you need to take advantage of the
> extensions, or be stuck with a lowest common denominator.
> Examples of extensions include functions available to SQL (such
> as decode), query constructs (such as the hierarchical CONNECT
> BY PRIOR and outer join constructs) and physical storage
> parameters.
 

> Not only this, but the languages used to implement non-SQL
> operations and the various tools are not compatible across
> different RDBMS systems (again, unless you use a third party
> tool such as Unify).
 

>1.10. What Query Optimisers are there ?
> Oracle <= 6 has rule based optimization that depends on two
> things, the presence of indices and the way you construct your
> where clause. Oracle 7 has the option of using a statistical
> optimizer.
>
> This requires you to run jobs that get a statistical picture
> of the spread of data values in columns across your database.
> This 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.11. Is there an anonymous FTP site for Oracle stuff ?
> As far as I am aware, while one is planned, none currently
> exist. If anyone knows of a site, could they let me know ?
> Does anyone feel like starting one up ?
 

>1.12. 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.13. 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.14. 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.
 

> 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.
>
>2. SQL QUESTIONS
>-----------------
>2.1. How can I avoid a divide by zero error ?
> Use the DECODE function. This function is absolutely brilliant
> and functions like a CASE statememt, and can be used to return
> different columns based on the values of others.
 

> For example, assume we sell products at variable cost, depending
> on the ability of the purchaser to pay (e.g. academic discount).
 

> Our table includes the following columns,
> PRODUCT, (the key)
> NUM_SOLD,
> TOTAL_REVENUE.

> Average sell price should be TOTAL_REVENUE / NUM_SOLD, but if we
> have not sold any, we are in trouble. We can avoid this by
> returning NULL as the result if NUM_SOLD is zero as follows:

> select PRODUCT,
> decode (NUM_SOLD,
> 0, NULL,
> TOTAL_REVENUE/NUM_SOLD) AVG_SELL_PRICE
> from my_table;

> A fuller example of decode is as follows
> select decode (SEX_CODE,
> 'M', 'man',
> 'm', 'boy',
> 'F', 'woman',
> 'f', 'girl',
> 'unknown')
> .......
> In the above example, SEX_CODE would be a single character code,
> and the statement would return a description based on that code
> or "unknown" if there was no match.

>2.2. Can I update tables from other tables?
> Yes.

> For example, if we had a table DEPT_SUMMARY, we could update
> the number of employees field as follows

> update dept_summary s
> set num_emps = (select count(*) from emp e
> where e.deptno = s.deptno);

>2.3. Can I remove duplicate rows?
> Yes, using the ROWID field, which *will* be unique. There are
> many variations on this statement, but the logic will work.

> This assumes an EMP table keyed on EMP_ID, which is NOT NULL.

> delete from EMP e
> where 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.

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

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

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

> My approach would be to use a 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:

>ransaction.

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

>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%'

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

>3.2. Can I get a column value into a substitution variable ?
> Use the OLD_VALUE spec with the COLUMN command.

> Imagine we want the surname of an employee to go into a
> variable, getting it via the employee id.

> COLUMN x OLD_VALUE y
> SELECT surname x
> FROM employee
> WHERE emp_id = 1234;
> PROMPT I found employee with surname &&y

> Of course, this was more often used as a kludge before PL/SQL
> became available in SQL*Plus (or if you had v6 without TPO), but
> it is still useful for a number of things.

>3.3. How can I change the (hated default) editor to my favorite ?
> When you type "edit" to SQL*Plus, it invokes a default editor
> for your system. On DOS this is often EDLIN, on UNIX ed and on
> VMS, it is usually EDT. In your login.sql file (or preferably
> the glogin.sql file set up by your DBA), you should set the
> variable that changes this to your favorite editor.

> define _editor="/usr/ucb/vi"

>3.4. What is the difference between & and && ?
> You can create a "permanent" definition of a substitution
> variable using DEFINE, the OLD_VALUE or NEW_VALUE clauses of a
> COLUMN statement (when the SQL statement uses them) or by
> referencing the variable with &&.

> Use of a & does not create a permanent version and will prompt
> you for a value if the variable does not exist (as will &&).
> However, next time you reference the variable with & or &&, it
> will ask you for the value again.

> Try the following to figure it out (do not enter the "SQL>" and
> remember to type something in when asked, make it different each
> time).
> SQL> undefine try_1
> SQL> prompt &try_1
> SQL> prompt &try_1
> SQL> prompt &&try_1
> SQL> prompt &&try_1
> SQL> prompt &try_1
> SQL> prompt &&try_1

> Note that use of positional parameters (&1, &2, &3 ....) are
> slightly different, they are given a "permanent" definition.

> P.S: When I say "permanent" I mean permanent for the current
> process.

>3.5. What is the difference between "host" and "!" ?
> The host and ! (sometimes another character) both create operating
> system commands as child processes of SQL*Plus. The difference
> is that the "host" will perform variable substitution of & or &&
> symbols, whereas "!" will not.

>3.6. 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 using SET,
> 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
> SET LONG 5000
> SET WRAP
> and maybe use COLUMN xxxx FORMAT A60 WORD_WRAP for it.

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

>
>4. SQL*FORMS 3 QUESTIONS
>-------------------------
>4.1. How can I get a list of values from a hard coded list ?
> Assume the field is SEX_CODE, use the following in the
> list-of-values SQL statement

> SELECT 'M', 'Adult Male' INTO SEX_CODE FROM DUAL
> UNION
> SELECT 'F', 'Adult Female' FROM DUAL
> UNION
> SELECT 'm', 'Juvenile Male' FROM DUAL
> UNION
> SELECT 'f', 'Juvenile Female' FROM DUAL
> UNION
> SELECT 'H', 'Adult Hermaphrodite' FROM DUAL
> UNION
> SELECT 'h', 'Juvenile Hermaphrodite' FROM DUAL
> UNION
> SELECT 'U', 'Unknown' FROM DUAL

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

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

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

>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.
>
>5. SQL*FORMS 4 QUESTIONS
>-------------------------
>5.1. What new features can be expected in forms 4 generator ?
> 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.

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

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

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

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

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

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

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

>7.6. How can I find a lost Oracle export file ?
> You need to add a description of the layout of an export file to
> the /etc/magic file. See magic(4) and file(1) for details. Use
> the fact that an Oracle export starts with a control-A and then
> the word EXPORT and the version number of the export that
> produced it. (Use the strings(1) or od(1) command to check this
> out). Your system administrator can then edit /etc/magic to not
> only recognise an export file but tell you the version number
> when you use file(1). Say the key description was "Oracle
> Export", you can look for Oracle export files from the current
> directory and below as follows:

> find . -type f -exec file {} \; | grep "Oracle Export" | \
> sed -e 's/:.*//'

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

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

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

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

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

>--
>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 Sat Aug 07 1993 - 08:24:49 CEST