non-DBA FAQ (first cut)

From: David Bath <dtb_at_otto>
Date: 26 Feb 93 13:51:03 GMT
Message-ID: <dtb.730734663_at_otto>

0. Introduction

---

This is the initial (very empty) draft of an FAQ for the oracle newsgroup but DOES NOT INCLUDE DBA issues. These are covered in a separate newsgroup.

As yet, the formatting needs to be tidied up (all questions brought to the top, for example), but I think it is worthwhile getting this thing started. Also, the questions I have included are only useful hacks that are not necessarily easily found by RTFM.

Some of you MAY have sent me stuff that bounced back because otto (my gateway) was dead recently due to a firebug. (Guess I should flame him!)

While I am prepared to collate and edit this FAQ (which will eventually split into a number of FAQ's, one for each product), experience and suggestions for questions and answers are more than welcome. If I have not put enough information for some of these ideas, let me know.

Any errors so far are mine. Please forgive and inform me.

Contact me via email on dtb_at_otto.bf.rmit.oz.au

Regards,
David T. Bath

1. General Questions

   ---

2. What is Oracle ? Oracle is a trademark of Oracle Corporation and is commonly used to reference the database engine and interface products they sell. 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

2.  What are the advantages of using Oracle over competitors?
	First of all, portability.  Oracle runs on more platforms than
	the others.  Everything from (high end) PC's to MVS.

	Second, their market presence means you will not be left in the
	lurch and that there will always be heaps of third party
	products to help interface to other products.

	Third, despite the (possibly historic) tendency of their reps to
	be snake-oil salesmen, they do offer excellent support with
	upgrades.  I have never been blasted by an ugly upgrade, lots of
	warnings for things that will become obsolete, for example.
	This is in contrast to some bad experiences with upgrades from
	competing vendors.

	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, are a draft ANSI standard for
	procedural DBMS languages.

3.  What are potential disadvantages of Oracle?
	a.  Cost.  Oracle ain't cheap.

	b.  Not as object-oriented as some competitors, but that is
		changing.

4.  What products are available from Oracle ?
	Apart from the core engine and consulting/education services...
	This list is NOT exhaustive.

	1.  SQL*Plus
		In essence, a basic shell for queries, basic reports,
		manipulating the database.  A must have.

	2.  SQL*DBA
		A user-unfriendly version of SQL*Plus with extra power
		for DBA's.  Unfriendly so you do not spend all your time
		in a product that can destroy your database if you type
		the wrong thing.

	3.  SQL*Forms
		A development tool for screen based applications.  Event
		driven.

	4.  SQL*ReportWriter
		Helps write reports.  Version 2, soon to be released is
		reasonably useful.  It seems that v1 is perhaps the most
		discarded bit of Oracle product at many sites.

	5.  SQL*Net
		Needs to run on PC's and Mac's before you can access
		Oracle on other machines.  Most mid-range and above
		boxes have it.

	6.  Oracle*CASE
		A pretty nifty data dictionary and design tools.  Good,
		like anything that comes from Oracle UK.

	7.  SQL*Menu
		A menu system that integrates well with other Oracle
		products and the host system.

	8.  Oracle*Mail
		E-mail using Oracle to glue systems together across
		architectures.  Betrays a Vax VMS heritage.

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

	10. Oracle*Card
		Helps develop hypercard like applications in Windows and
		Mac environments.

	11. Oracle Glue
		Helps develop applications under MS-Windows that use
		things like Excel and Visual Basic, i.e. an API for DDE
		applications.

	12. SQL*Graph
		Prepares graphs from Oracle data.

	13.  SQL*Loader
		Loads data from flat files into Oracle

	14.  RPT/RPF
		Ancient tool with ugly syntax still used by some for
		batch updates where they want portable code and do not
		want to embed SQL in a language like C, especially
		before PL/SQL extensions to SQL*Plus became available.

	15.  Pro*C,  Pro*COBOL,  Pro*Fortran .......
		Precompilers that let you embed SQL statements into a
		standard language without *too* much trouble.

 

B. SQL Questions

---

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

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 REAL statement according to Oracle soon.

3.  Can I update using a view ?
	Only if the view is a simple horizontal slice through a single
	table.
 

C. SQL*PLUS QUESTIONS

---

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.
2. How can I get a value from a table into a plus substitution variable for later use ? 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).
 

D. SQL*FORMS 3 QUESTIONS

---

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

2.  Can I edit SQL*Forms code ?
	Yes, but it is unsupported.

	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.
 

E. UNIX QUESTIONS

---

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
	compress < myexport.dmp > myexport.dmp.Z &
	export scott/tiger filename=myexport.dmp

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.

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

	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.

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.

 

F. MISC QUESTIONS

---

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).
2. Any good PD tools available? For general programming, try oraperl, patches to perl. Perl can be obtained from GNU archives and comp.sources.unix and is a brilliant general purpose language that is a cross between C and the unix shells. (Thank Larry Wall for Perl). Oraperl is a series of patches to perl that let it deal with Oracle at the cursor level. (Thank Kevin Stock for Oraperl). Oraperl can be obtained from comp.sources.misc.

        Apparently a generic DB perl extension is under development.

	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.

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

4.  What makes the best Oracle server for a network ?
	OK, I am biased and will say Sun.  Get a SPARC-10 if you are a
	smaller site, or go up to a SPARCenter-2000 (dragon) if you are
	a larger site with more money.

	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.

Regards,
David

---
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
-- 
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 Fri Feb 26 1993 - 14:51:03 CET