non-DBA FAQ ver 0.2
From: David Bath <dtb_at_otto>
Date: 6 Mar 93 12:55:15 GMT
Message-ID: <dtb.731422515_at_otto>
Date: 6 Mar 93 12:55:15 GMT
Message-ID: <dtb.731422515_at_otto>
ORACLE NON-DBA FAQ (Frequently Asked Questions) - Version 0.2
0. Introduction 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. 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. Comments, suggestions for questions (with or without answers) and corrections should be mailed to me at dtb_at_otto.bf.rmit.oz.au [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. Opinions expressed in this document do not reflect those of Oracle Corporation or the organizations to which any of the contributors belong. 0.2 Who has contributed to this document ? David T. Bath Organization: Global Consulting Services Ltd. Address: dtb_at_otto.bf.rmit.oz.au Andy Finkenstadt Organization: Vista-Chrome Inc., Homes & Land Publishing Corporation GEnie Unix RoundTable Manager Address: andy_at_vistachrome.com andy_at_genie.geis.com. Kevin Stock Organization: Encore (I think) author of oraperl patches to perl Address: kstock_at_encore.fr Joel Garry Organization: unknown Address: INTERNET: tumidity_at_netlink.cts.com UUCP: ...!ryptyde!netlink!tumidity 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. 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. 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). 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 1.2. What are the advantages of Oracle ? As Oracle is ported to more platforms than I can mention, your applications are very portable across architectures, making the decision to use Oracle fairly safe. Indeed, I have written Oracle applications for platforms where I barely knew the commands to copy and edit plain text files. One caveat, however, is that block mode terminal type systems are difficult to port to as many constructs do not work (e.g. in SQL*Forms).
(Moral: Rightsize NOW!)
The market presence of Oracle means that you will be left in the lurch and there will always be lots of third party products and programmers to help you. Despite the (historic?) tendency of Oracle Sales Reps to be snake-oil salesmen, they do offer excellent support. The quality of warnings for changes in the language has been excellent since I first started using Oracle 4 Beta versions. Generally, Oracle warn you a major release in advance when a feature will be withdrawn, and I have never had the behaviour of a language component change (as I have with other products that shall not be named) that required me to go over every source file I had written. Speed, potentially is an advantage. Depends whether you believe benchmarks or not. The dialect of SQL Oracle implement is in my opinion, the best. PL/SQL, the procedural extensions, is a draft ANSI standard for procedural DBMS languages. 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. 1.4. What is SQL ? Standard (or Structured) Query Language. Now it encompasses DML
(Data Manipulation Language) for doing inserts, deletions and
updates to tables, as well as 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 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. 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. 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 fairly ugly syntax 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 mess of menus. Actually, my earliest comments about RPT/RPF were perceived as somewhat dismissive, and generated quite a lot of "fan-mail" 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. 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). If anybody reading this is a user or vendor of a particular third party interface, mail me direct rather than via the newsgroup together with a quick summary. third 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!!
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.
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.
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. 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.
5. UNIX QUESTIONS
5.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 5.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. 5.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. 5.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. 5.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. Gnu C (GCC) is a BSD-derived compiler, so I assume that similar arguments apply. 5.6. 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. MISC QUESTIONS
6.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). 6.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. 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. -- 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 Mar 06 1993 - 13:55:15 CET