HDTenEightyP/NetNews huggingface dataset:
From: philn@optigfx.optigfx.com (Phil Nguyen)
Newsgroups: comp.databases.oracle
Subject: Re: SQL: Translate
Date: 15 Apr 92 23:35:41 GMT
Followup-To: ayers@shark.micro.umn.edu
Organization: Optigraphics Corporation, San Diego

In article <1992Apr15.165953.1542@news2.cis.umn.edu> ayers@shark.micro.umn.edu (Timothy R. Ayers) writes:
>
>I am trying to update an address line in the database where someone
>entered an "*" or "**" at the end of each mailbox number.  So the records
>look like this:    2076**
>                   1367*
>
>I would like to remove the "*" and replace it with spaces or null.
>Someone suggested using the TRANSLATE function, but I can't find
>much documentation on it.  Does anyone have any suggestions how to make
>this function work.
>
>Thanx in advance for your help.
>
>Judi

TRANSLATE and REPLACE functions are described in the Oracle's SQL Language
Reference Manual Version 6.0.  REPLACE does not exist in Oracle V.5.

To fix your problem try:

update table_name set field_name = translate(field_name,'*',' ')
where field_name like '%*%';

or 

update table_name set field_name = replace(field_name,'*','')
where field_name like '%*%';


Hope this helps.


Phil
From: mmwang@adobe.com (Michael Wang)
Newsgroups: comp.databases.oracle
Subject: Re: SQL: Translate
Date: 15 Apr 92 18:41:11 GMT
Organization: Adobe Systems Incorporated

In article <1992Apr15.165953.1542@news2.cis.umn.edu>
ayers@shark.micro.umn.edu (Timothy R. Ayers) writes:
>
>I am trying to update an address line in the database where someone
>entered an "*" or "**" at the end of each mailbox number.  So the records
>look like this:    2076**
>                   1367*
>
>I would like to remove the "*" and replace it with spaces or null.
>Someone suggested using the TRANSLATE function, but I can't find
>much documentation on it.  Does anyone have any suggestions how to make
>this function work.
>
>Judi

It has been a while and I don't have any docs with me, but if you want
spaces try:

     TRANSLATE(mailbox,'*',' ')

If the mailbox number can vary in length and you want NULLs try:

     SUBSTR(mailbox,1,INSTR(mailbox,'*') - 1)

or

     RTRIM(TRANSLATE(mailbox,'*',' '))

If your mailbox number is always four digits long and you want
NULLs try:

     SUBSTR(mailbox,1,4)


All of these commands are documented in the SQL Lanuage Reference
Manual.

-- 
Michael Wang
mmwang@adobe.com
Newsgroups: comp.databases.oracle
From: irving@casbah.acns.nwu.edu (Thomas Myers)
Subject: Re: SQL Forms 3.0 and displaying linked list info?
Organization: Northwestern University, Evanston, Illinois
Date: Fri, 17 Apr 1992 14:12:55 GMT

In <1992Apr16.175704.23378@cas.org> lwv26@cas.org (Larry W. Virden) writes:

>I am hoping to find a neat way to do the following.

>I have an employee type database which has the following as a portion of
>its information:

>Employee ID		Employee Name		Employee Manager ID

>100			John Doe		90
>90			Charlie Brown		80
>91			Alvin Chipmunk		70
>80			Calvin Hobbes		70
>70			Dick Tracy		10
>10			Gary Larson		0

[description of needing to build a form which lists an employee and all
managers...]

Try using a simple select with the CONNECT BY clause.  Displaying this sort of
hierarchical data is what it is designed to do. 

Look in you SQL language reference manual.
-- 
Tom Myers, Programmer/Analyst				Email: t-myers@nwu.edu
Reingold ECG Center - Northwestern University
Xref: sparky comp.databases.oracle:446 comp.databases.ingres:291
From: cooper@beno.CSS.GOV (Dale Cooper)
Newsgroups: comp.databases.oracle,comp.databases.ingres
Subject: Re: Moving data from Ingres -> Oracle ??
Date: 6 Apr 92 14:38:19 GMT
Followup-To: comp.databases.oracle
Organization: Center for Seismic Studies, Arlington, VA

In article <1992Apr3.104852.9555@covax.commerce.uq.oz.au> bellamy@covax.commerce.uq.oz.au writes:
>
>We have the need to convert data from a colleague's Ingres database to a
>database using Oracle.  The database is fairly large (about 500Mb) so
>an efficient method would be appreciated (although any will do :-)).  This is
>likely to be an annual effort.  Do either Oracle or Ingres provide tools for
>this (the Oracle manuals which we have make no mention of Ingres).

We had to do something quite similar here at the Center.  In fact we had to
download data from an Ingres installation on a VAX then upload the data into 
an Oracle instance on a SUN 4.  Since the two databases can't communicate,
this entailed downloading the data into ASCII format from within Ingres SQL
and uploading the data into Oracle using the SQL*Loader utility.  The download 
from Ingres was relatively easy in that we used the copy...to...path/filename 
command from within Ingres SQL.  Is was neat and easy as Ingres provided the 
ability to dump data into ASCII with user defined delimited fields.  
(Why doesn't Oracle provide this type of capability?...I guess that's another 
can of worms entirely, right guys?)

Anyhow, once the data was downloaded to ASCII, we tar'd off the files to tape
for backup and FTP'd the data to the new box for importing into Oracle.  We
created individual control files for each table and slammed the data into the
Oracle database.  The SQL*Loader facility is quite reliable and can handle 
large blocks of data in a short period of time.

>_Idealy_ we would like to connect through TCP/IP, but tape unload/reload would
>be fine.

The latter is probably your best option...

>David E. Bellamy              Email: bellamy@commerce.uq.oz.au
>Dept. Commerce, University of Queensland, St. Lucia, AUSTRALIA

Dale Cooper, DBA			Why say Why.
Center for Seismic Studies		   Drink Homebrew...
Arlington, VA
From: bmb@pandora.cs.wayne.edu (Amir M. Baig)
Newsgroups: comp.databases.oracle
Subject: Any Book(s) on Oracle V.6?
Date: 7 Apr 92 19:18:40 GMT
Article-I.D.: cs.1992Apr7.191840.15991
Organization: Wayne State University, Detroit

Hello Netter,

Can any one of you tell me some good books on oracle v.6? The manuals
sometime do not seem to help much.

Thanks, (direct e-mail would be appreciated)

Amir Baig 
bmb@pandora.cs.wayne.edu
:
From: mar7363@tm0006.lerc.nasa.gov (Michael A Rife)
Newsgroups: comp.databases.oracle
Subject: Existance of a block in SQL*FORMS 3.0
Date: 8 Apr 92 18:37:00 GMT
Article-I.D.: tm0006.8APR199213374051
Organization: NASA Lewis Research Center / Cleveland, Ohio
News-Software: VAX/VMS VNEWS 1.41

Summary of determining the existance of a block in SQL*Forms 3.0:

Situation:

We are writing screens that pop-up blocks on the screen based on user inout.  We
did not want to "Hard-code" the block names, so we decided to store the block
name in a file called :CONTROL.FORM_BLOCK_NAME.  The user enters data into field
:CONTROL.FORM_BLOCK_NAME.  If a block with a name matching the user data exists
then, then go to that block.  If the block does not exist, then go to a block
called GENERAL.


Solution:

In our key navigation trigger we have the following:

      GO_BLOCK(NAME_IN(':CONTROL.FORM_BLOCK_NAME'));
      IF FORM_FAILURE THEN
          GO_BLOCK(NAME_IN(':CONTROL.FORM_BLOCK_NAME'));
      END IF;

where :CONTROL.FORM_BLOCK_NAME has data entered by the user.  (That is could be
a name of a block that does not exist in the form.)


On a form level ON-ERROR trigger:

      IF ERROR_CODE = 40104
          THEN :CONTROL.FORM_BLOCK_NAME := 'GENERAL';
      ELSE
          MESSAGE(ERROR_TYPE||'-'||TO_CHAR(ERROR_CODE)||': '||ERROR_TEXT);
          BELL;
      END IF;


Caveat:

If Oracle changes it's error codes, then we will need to change the ON-ERROR
trigger.



Sure would be easier to have a packaged function called BLOCK_EXISTS which
returns TRUE if the passed block name exists in the form, or FALSE if it does 
not exist.  Then we could do:

  If BLOCK_EXISTS(NAME_IN(':CONTROL.FORM_BLOCK_NAME')) Then
    GO_BLOCK(NAME_IN(':CONTROL.FORM_BLOCK_NAME'));
  Else
    GO_BLOCK('GENERAL');
  End if;


We are working on a Procedure to emulate a BLOCK_EXISTS package function.

Thanks to everyone who responded with suggestions!!


+------------------------------------------------------------------------------+
|  Michael A. Rife                    | Email:   MAR7363@TM0006.LERC.NASA.GOV  |
|  Boeing Computer Support Services   |                                        |
|  Space Station Freedom Project      |                                        |
|  NASA Lewis Research Center         | Phone:   216-734-9679                  |
|  Cleveland, Ohio                    |          216-433-5938 FTS 297 (NASA)   |
+------------------------------------------------------------------------------+
From: kcameron@erc.cat.syr.edu (Ken Cameron)
Newsgroups: comp.databases.oracle
Subject: Re: What is PL/SQL?
Date: 9 Apr 92 02:55:32 GMT
Article-I.D.: newstand.1992Apr8.225532.5979
Organization: Electronic Resource Center; Syracuse, NY

In article <1992Apr3.115047.9048@jyu.fi> hkau@jyu.fi (Harri J Kaukovuo) writes:
>In article <1992Mar27.174137.5277@fmsrl7.srl.ford.com> hugh@slee06.srl.ford.com (Hugh Fader) writes:
>>I have some literature from Oracle which describes a product called
>>PL/SQL. It looks like it is an extension to SQL which allows procedural
>...
>PL/SQL is part of SQL*Forms 3.0. You can use PL/SQL in procedures and
>...
Also PL/SQL will work from SQL*PLUS and the compiled language products
like PRO*C. However I find that very long >500 line scripts (under
SQL*PLUS) tend to abort at times.

-- 
-ken cameron. SkyDiver: Zoo-602, A-8596, D-11839. Skier.
Employer: Computer Task Group.  /cny UNIX Users Group Director.
Disclamer: "I said it, when I said it, so what! Now is different!"
Internet: kcameron@erc.cat.syr.edu
From: jean@beno.CSS.GOV (Jean Anderson)
Newsgroups: comp.databases.oracle
Subject: Is Conference for Oracle Developers and Integrators (CODA) worth it?
Date: 6 Apr 92 22:00:57 GMT
Organization: SAIC Open Systems Division, San Diego, CA

Somebody sent a flyer to my boss about the Conference for Oracle
Developers and Integrators (CODA) that meets in San Francisco at the
end of April.

Does anybody know how high the technical content of this conference is? 
Or is it mostly marketing oriented?

Thanks in advance,

  -jean

+-----------------------------------------------------------------------+
| Jean Anderson, DBA                       email:  jean@seismo.css.gov  |
| SAIC Open Systems Division, MS A2-F         or:  jean@esosun.css.gov  |
| 10210 Campus Point Drive                 phone:  (619)458-2727        |
| San Diego, CA  92121                       fax:  (619)458-4993        |
+-----------------------------------------------------------------------+
|                execute sp_disclaimer @opinion="my dog''s"             |
+-----------------------------------------------------------------------+
From: cooper@beno.CSS.GOV (Dale Cooper)
Newsgroups: comp.databases.oracle
Subject: Re: Fragmentation Problems
Date: 6 Apr 92 14:22:43 GMT
Organization: Center for Seismic Studies, Arlington, VA

In article <3615@unixhub.SLAC.Stanford.EDU> ian@unixhub.SLAC.Stanford.EDU (Ian A. MacGregor) writes:
>In article <1992Apr2.072539.14769@scammell.ecos.tne.oz.au>,
>brt@scammell.ecos.tne.oz.au (Brett Dunstan) writes:
>|>
>|>	I have a small fragmentation problem with the ORACLE database
>|>that is currently in use on a sparc 2.  The result of the following

[free space stuff deleted]

>|>	It seems odd that although many of the blocks are physically
>|>next to each other Oracle cannot join them together.  This may seem
>|>a very easy question, but, is there a way of fooling Oracle to join
>|>them together, either by creating and dropping tables, or any other 
>|>method.

>The results of selecting from user_free_space are somewhat misleading.  Oracle
>can join the blocks together, but does not until it needs to do so.  If you  
>create an object with large enough extents or if the next extent for a current
>object is of sufficient size, the joining of contiguous free space extents will
>occur.  I believe Oracle adopted this strategy for performance reasons. 

Kind of.  I doesn't really "join the blocks together." in the true sense of
the word.  Yes, they can be combined to create a single object but when you
drop the object, the extents that were used by that object are released
back to free block list in the same or smaller chunks than before.  

Net gain:  Nada.

Your best defrag option is still to export the data, drop the tablespace and 
reimport.  Currently, there is no other option.  I guess you could just keep
adding files, but that can get expensive, plus your performance will eventually
begin to take a hit due to continual increase in the number of the individual 
extents that the database must keep track of.  Ain't it fun?

>|>	Currently we drop the tablespace ( including its contents ),
>|>re-create it and then re-insert the indexes or reimport the data in order
>|>to remove any fragmentation ( ie: create one large extent ).

Indeed, the best solution.


Dale Cooper, DBA
Center for Seismic Studies
Arlington, VA
Newsgroups: comp.databases.oracle
From: robin@prc.unisys.com (Robin McEntire)
Subject: Distributed Databases
Keywords: distributed databases
Organization: Paramax Systems
Date: Thu, 9 Apr 1992 18:37:24 GMT

We currently have Oracle V6, but we're thinking of using Sybase.  Among the
issues that we're comparing between the two (and perhaps the most important issue
for us) is how each of them handles distributed databases.

Does anyone have any opinions, warnings or pointers to some useful papers on the
subject.

thanks,
  robin


-- 
--------------------------------------------------------------------------------
Robin McEntire				   | Internet: robin@prc.unisys.com
Paramax					   |
Valley Forge Labs Research and Development | 
--------------------------------------------------------------------------------
From: ayers@shark.micro.umn.edu (Timothy R. Ayers)
Newsgroups: comp.databases.oracle
Subject: SQL: Translate
Date: 15 Apr 92 16:59:53 GMT
Article-I.D.: news2.1992Apr15.165953.1542
Organization: University of Minnesota CIS


I am trying to update an address line in the database where someone
entered an "*" or "**" at the end of each mailbox number.  So the records
look like this:    2076**
                   1367*

I would like to remove the "*" and replace it with spaces or null.
Someone suggested using the TRANSLATE function, but I can't find
much documentation on it.  Does anyone have any suggestions how to make
this function work.

Thanx in advance for your help.

Judi
Newsgroups: comp.databases.oracle
From: lwv26@cas.org (Larry W. Virden)
Subject: SQL Forms 3.0 and displaying linked list info?
Reply-To: lwv26@cas.org (Larry W. Virden)
Organization: Nedriv Software and Shoe Shiners, Uninc.
Date: Thu, 16 Apr 1992 17:50:52 GMT

I am hoping to find a neat way to do the following.

I have an employee type database which has the following as a portion of
its information:

Employee ID		Employee Name		Employee Manager ID

100			John Doe		90
90			Charlie Brown		80
91			Alvin Chipmunk		70
80			Calvin Hobbes		70
70			Dick Tracy		10
10			Gary Larson		0

Now, what I need to be able to do is build a form where the user can
enter a portion of the employee name, and that employee appears , along
with all of the managers from that point on down - for instance, if I
entered

Name:	Brown

and hit Execute query, I could get:

Name:   Charlie Brown
	Calvin Hobbes
	Dick Tracy
	Gary Larson

Actually, I would rather the order be the other way, but I could live with
the above.

Now, there surely is some way using PL/SQL to code a loop to do this, but
I am stymied on how I could define things.  

For instance, there could be multiple hits for Brown.  Thus, what I really
want to do is retrive all of the names, and then, some how, using triggers,
fill in (or blank out) the rest of the fields as appropriate.

Anyone have any good pointers as to where to look for help in something like
this?

Thanks!
-- 
Larry W. Virden			UUCP: osu-cis!chemabs!lvirden
Same Mbox: BITNET: lwv26@cas    INET: lvirden@cas.org
Personal: 674 Falls Place,   Reynoldsburg,OH 43068-1614
America Online: lvirden
From: scott@prism.gatech.EDU (Scott Holt)
Newsgroups: comp.databases.oracle
Subject: System Planning
Date: 9 Apr 92 18:14:23 GMT
Organization: Georgia Institute of Technology


   Over the next couple of years our site will be bringing Oracle Financials
   applications online. In addition, we will be phasing out the hardware on
   which our older systems run and making new purchases. We are also developing
   a number of custom, non-financial applications. 

   At this point in time, we are trying to plan purchases of new hardware.
   We are running into some trouble with this effort because we are not
   terribly familiar about what type of impact Financials will have on a
   system. We do, however, have a decent idea of how many users we will 
   have to support. Does anyone know of a general method for planning what
   type of hardware will be required to support a given number of Oracle
   Financials users in a UNIX environment?


   Thanks in advance,
   Scott Holt
-- 
This is my signature. There are many like it, but this one is mine.
Scott Holt                 		Internet: scott@prism.gatech.edu
Georgia Tech 				UUCP: ..!gatech!prism!scott
Office of Information Technology, Technical Services
Newsgroups: comp.databases.oracle
From: richards@sequent.com (Richard Shields)
Subject: Re: SQL Forms 3.0 and the Copy/Name_in procedure/functions
Organization: Sequent Computer Systems Inc.
Date: Wed, 15 Apr 92 16:11:56 GMT

I believe you want the procedure to look like this:

procedure tst_cp (blk in char) is
begin
    copy(name_in(blk || '.par_id'), 'GLOBAL.par_id');
end;

-Rich

In article <1992Apr14.152113.3243@cas.org> lwv26@cas.org (Larry W. Virden) writes:
>I have a series of blocks in a form which have the same field names within
>each.
>
>I would like to write a procedure which, when given the name of a block
>as an argument, would perform some action on the fields in that block.
>
>What I am trying to do is something like:
>
>procedure tst_cp (blk in char) is
>begin
>   copy(':' || name_in(blk) || '.par_id', :global.par_id);
>end ;
>
>
>and then I want to call this procedure as:
>
>tst_cp('junk');
>
>so that :junk.par_id is copyed into the global variable par_id.
>
>The problem is that when I get to the copy statement, I get an Undefined
>field or variable reference error from Sql Forms 3.0 .
>
>Anyone have any ideas on what I might be doing wrong here?
>
>I tried changing the above to :
>
>procedure tst_cp is
>begin
>	copy(':" || name_in(:global.blk) || '.par_id', :global.par_id);
>end;
>
>and then
>
>:global.blk := 'junk';
>tst_cp;
>
>and got the same results.
>
>
>-- 
>Larry W. Virden			UUCP: osu-cis!chemabs!lvirden
>Same Mbox: BITNET: lwv26@cas    INET: lvirden@cas.org
>Personal: 674 Falls Place,   Reynoldsburg,OH 43068-1614
>America Online: lvirden
From: gevel@kub.nl (A.J.W. van de Gevel)
Newsgroups: comp.databases.oracle
Subject: Oracle and EMM386.EXE
Keywords: Oracle, EMM386
Date: 6 Apr 92 09:43:55 GMT
Organization: Tilburg University, Tilburg, The Netherlands


	Dear Networkers,

	Again problems with Oracle!
        Oracle doesn't get along with any Expanded Memory emulator (EMM386.EXE,
QEMM386.SYS), but we need some Expanded Memory Emulator for other
software. I think that 2 autoexecs and 2 configs would do, but this
would make things very complicated for our students (who are using
Oracle and other software). Any suggestions?

P.S.    We are using Oracle 6 (server) with Oracle 5 (Dos) on a Novell
386 network.

					Regards,
					Rutger van de Gevel

Student-assistent
at Tilburg University,
The Netherlands
E-mail: rutger@kub.nl
	gevel@kub.nl
Newsgroups: comp.databases.oracle
From: bergstro@src.honeywell.com (Pete Bergstrom)
Subject: Re: Help with compiling XWindows-to-C-to-Oracle
Organization: Honeywell Systems & Research Center
Date: Wed, 15 Apr 1992 21:44:50 GMT


Can you give more information on what Oracle doesn't like?  What do
you mean by its "access section"?

I've been writing a lot of embedded SQL in the last six months and
there shouldn't be anything different between what you're doing and
what my stuff does.

Pete
Newsgroups: comp.databases.oracle
From: mccurdy@bestsd.sdsu.edu (McCurdy M.)
Subject: Instructional Use of SQL*Menu and application security
News-Software: VAX/VMS VNEWS 1.4-b1  
Organization: San Diego State University - Dept. of Redundancy Dept.
Date: Mon, 13 Apr 1992 19:06:00 GMT

	In SQL*Menu, I am told, there is no way to restrict access to 
applications from other users. That is, in a multiple user 
environment, each user has access to and may modify any other users 
application. I am running VMS V. 5.4-2 and SQL*Menu. 

	If any instructional users have comments or alternatives, it 
would be good hearing about them.

	
Mike McCurdy				
University Computing Services
San Diego State University
mccurdy@bestsd.sdsu.edu			What?  :()
From: uqecd08@ucl.ac.uk (John Thomas)
Newsgroups: comp.databases.oracle
Subject: Re: NASA/Need Opinions On Oracle
Date: 7 Apr 92 21:13:16 GMT
Organization: Bloomsbury Computing Consortium

barrydm@dryden.dfrf.nasa.gov (Mark Barry) writes:

>different vendors: Ingres, Informix, Sybase and Oracle.  Considering either 
>of these two hardware platforms, can anyone express opinions and/or exper-
>iences with Oracle?  Thanks...

Quick answer:

You want the latest bees knees that does everything quickest on only the 
very latest platform? - Don't use Oracle.

You want a solid (though not always without bugs) data-processing RDBMS
with _relatively_ easy to use data-entry and report generation tools. You
also want to be able to communicate with other SQL databases running on quite
different platforms & port your applications to them, either now or in the
future. And a system that's well known, (sometimes with poor Oracle support)
by lots of programmers, DBAs and SyS Admins... choose Oracle.

(Two year's experience of Oracle PC, 12 months on AIX - application medical
database with up to 240,000 records in each of 50+ tables.)
-- 
|   v___v    | John Thomas, Programmer         e-mail: J.Thomas@ucl.ac.uk     |
|  / o-o \   | LEPRA Evaluation Project                                       |
|  \__Y__/   | London School of Hygiene and Tropical Medicine                 |
| ( ~ ~ ~ )  | Keppel St, London WC1E 7HT, England                            |