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