HDTenEightyP/NetNews huggingface dataset:
From: mike@swsrv1.cirr.com (Mike Haddon)
Newsgroups: comp.databases.oracle
Subject: List of Values (restrict) in query mode.
Date: 4 Sep 92 16:28:14 GMT
Distribution: usa
Organization: Software Services; Garland, Tx

I would like to know if there is a way to qualify a LOV while in QUERY mode
and also implicitly execute the query if the user actually selects a value
from the LOV. We seem to be having problems in this area because the 
KEY_LISTVAL trigger is not being executed while in query mode. Any help would 
be greatly appreciated.



-- 
******************************************************************************
*                      |                        |                            *
* Mike Haddon          | 6717 Woodsmoke Way     | Fort Worth, Tx.  76137     *
* MTL Custom Services  | mike@swsrv1.cirr.com   | ...!void!swsrv1!mtlsrv!mh  *
Newsgroups: comp.databases.oracle
From: p228@uni05.larc.nasa.gov (Bailey Bob)
Subject: Re: SQL*Net using multiple protocols
Keywords: Novell SQL*Net ORacle
Organization: Unisys-Hampton VA
Distribution: comp
Date: Thu, 10 Sep 1992 20:58:29 GMT

In article <1569@rwja.umdnj.edu> holowczk@rwja.umdnj.edu (Richard Holowczak) writes:
>
>We are thinking of setting up a Novell Netware LAN and running 
>Oracle Server for Netware.  For PCs on the LAN, we would use the IPX/SPX
>version of SQL*Net. So far this is nothing earth shattering. We
>also intend on connecting this LAN up to the university network which
>is based on TCP/IP. The network people tell me there should be no
>problems if we would like to access the Oracle database from a remote
>site by going accross the Internet. For PCs this is not a problem.
>
>We have several users with Sun workstations who would also like to
>have access to the Oracle database on the Novell server. We also
>have some people who would like to dial in from home and have
>access to the database. 
>
>What are our options in terms of using SQL*Net
>to do these tasks? 
>
>What would performace be like? 
>
>Users would like
>to be able to run SQL*Forms, SQL*Plus and SQL*Menus. Is anyone out
>there using SQL*Net Asynchronus? 
>

We developed an application that runs in an environment that is somewhat
similar to yours.  The application is distributed across 8 servers.  A   
rough description of the environment follows:

     -	8 DECstation 5000's connected via TCP/IP form the application servers
     -	User access is provided via TCP/IP
		User workstations include PCs, Macs, & Sun Sparcs
     -	The above is located on the NASA Langley Research Center and this
	incorporates many different buildings across the Langley campus.
     -	Another DECstation 5000 is located 2 miles away in our contractor
	office.  This is a development machine.  In our office, we access
	the development machine via PCs that are connected to a Novell 
	network.  As I understand it, the Novell net is embedded in TCP/IP
	packets.  The office is also connected to the Langley campus via
	a T-1 line running TCP/IP.  From each PC, we can connect to either
	the development 5000 or to any of the 8 production servers.  The
	production servers and the development machine have SQL*Net installed
	for Oracle database access.  None of the PCs have SQL*Net.  They are
	all running FTP's PC/TCP software which permits TCP/IP access using
	telnet with VT220 emulation.

With this environment we have a satisfactory and reliable solution to user
access.  With this solution, the PCs, Macs, & Sun are used as terminals, so
SQL*Net was not needed for these platforms.  Dial-up access is available and
provided by a Cisco box.  All of the dial-up lines enter the Cisco box and
allow the dial-up user to telnet into any of the machines on the net.  I have
accessed several of the Oracle databases by using my PC from home to dial into
the net.  I have not had any problems with any of the Oracle products when
accessed via dial-up; this includes SQL*Forms 3.0, SQL*Plus, & SQL*ReportWriter.

Bob Bailey, p228@uni05.larc.nasa.gov
Paramax/Unisys
Newsgroups: comp.databases.oracle
From: dtrum@jets.srg.af.mil (David E. Trum x4772)
Subject: Re: Import into a new table space is BROKEN!
Organization: ARINC Research, Corp.
Date: Tue, 15 Sep 92 18:42:21 GMT


David Taylor writes:

>I created a special tablespace for the data (~200M) on a new disk.
>I created a user and granted resource access to the table space.
>I made the new table space the default for this user.
>
>When I run import it appears to work, but it creates
>the tables in the SYSTEM table space, and falls over eventually
>because there isn't enough space there. The manual clearly states
>that it should load into the default table space for this user.
>It even gives an example of how to use this feature to transfer
>stuff between table spaces.
....[deleted]

------------------
Yup, Unix (SCO and SunOS) have this problem too.
My way around it is to write a shell script to dump the
names of each table to a file and then create an export
dump script to export the INDIVIDUAL tables.  This creates
a single dump file with each table in it (and data) without
requireing that the target tablespace be of the same name
as the original tablespace.

As an illustration of what I mean, perform an ASCII dump
of a regular user dump file 
 (UNIX: cat expdmp.dat |strings >expdmp.text)
and notice the "TABLESPACE" clause in each "CREATE TABLE"
command.  Arggg!

piece of shell script:

...[deleted]
  echo "Exporting Tables.......\c"
  echo "userid=$LOGIN"  >./params.exp
  echo "file=tables.dmp">>./params.exp
  echo "grants=Y"       >>./params.exp
  echo "indexes=N"      >>./params.exp
  echo "constraints=Y"  >>./params.exp
  echo "tables=(\c"     >>./params.exp
  sqlplus $LOGIN @$PGM_DIR/get_tb_lst.sql >/dev/null
  OLD_IFS=$IFS
  IFS=$LINE_IFS
  for LINE in `cat tables.all |grep -v "^$"`
  do
        TABLE=`echo $LINE|cut -f1 -d" "`
        echo "$C$TABLE\c" >>./params.exp
        C=", "
  done
  echo ")"  >>./params.exp
  IFS=$OLD_IFS
  $EXP_DIR/exp parfile=./params.exp
...[deleted]

Where 
LINE_IFS='
'
and get_tb_lst.sql is:
REM     This script creates a list of tables to be exported.

set heading off
set feedback off
set linesize 35
set pagesize 1000
spool tables.all
select TABLE_NAME from user_tables
        order by 1 ;

spool off
exit


-- 

#########################################################
David E. Trum
Database Administrator		Email:  uunet!srg!dtrum
ARINC Research Corporation	Phone:  (410)266-4772
2551 Riva Road  MS 5-230	Fax:    (410)573-3024
Annapolis, MD  21401-7461
#########################################################
--

#########################################################
David E. Trum
Database Administrator		Email:  uunet!srg!dtrum
From: pihlab@hhcs.gov.au
Newsgroups: comp.databases.oracle
Subject: Re: How to print something in PL/SQL
Date: 7 Sep 92 10:04:26 +1000
Organization: Aust. Dept. Health, Housing and Community Services

> Oracle, in their infinite wisdom, has deemed it appropriate that PL/SQL
> have no native print utilities.  A rather hideous means of "printing"
> is to write results to a table and then print them out with a select
> statement following the PL/SQL script.  But this is horrible (and costly
> in terms of extra database access) alternative to a print statement.

I think that they actually are worried about supporting the I/O now that
they've introduced stored procedures because the interface would have to be in
the Kernel itself.  

I've been toying with the idea that SQL*Connect could be used to write output
from within PL/SQL but you would have to define a "table" to SQL*Connect that
is really an OS output stream.  Unfortunately, we don't have SQL*Connect/RMS
here so I can't play with it.

Another alternative would be to invoke SQL*Forms3 from inside SQL*Plus to do
the PL/SQL stuff (with messages) but that's taking a sledge hammer to the
problem and would probably not work too well.

At the moment, if you want output then you go thru tables OR use a 3GL.

-- 

Bruce...        pihlab@hhcs.gov.au
                 ^^
*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************
From: johe@pbutv.pb.se (Jonas Heyman)
Newsgroups: comp.databases.oracle
Subject: Books on Oracle programming
Date: 9 Sep 92 19:57:49 GMT
Organization: Pressbyr}n , Svenska AB


Hi,

I've looked in our local bookstore after Oracle books, with no luck.
I'm looking for PL/SQL, Forms 3 and PRO/C books.
Tips and techniques for application development.

Any suggestions on anything I should get ?
Thanks, Jonas,
 
-- 

   /johe@pbutv.pb.se/
Newsgroups: comp.databases.oracle
From: smuench@oracle.com (Steven P. Muench)
Subject: Re: Forms3 LOV
Organization: Oracle Corp., Belmont CA
Date: Sun, 6 Sep 1992 21:47:55 GMT
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.

IAN -- While Forms4 will make this much easier by allowing the
       designer to specify a static group of records at design time on
       which an LOV will be based at runtime, the simplest way to
       accomplish this in 3.0 involves only the LOV-Select-Statement.

       If you'd like the values 'YES','NO', and 'MAYBE' to be shown in
       the LOV for field CONTROL.RESPONSE, for example, then the
       LOV-SQL-Statement for CONTROL.RESPONSE could read:

           SELECT 'YES' INTO :CONTROL.RESPONSE  FROM DUAL
            UNION
           SELECT 'NO' FROM DUAL
            UNION
           SELECT 'MAYBE' FROM DUAL

       Hope this helps. :-)
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
  Steve Muench              Email: smuench@oracle.com
  SQL*Forms Development
  Product Manager
From: dlm@hermes.dlogics.com
Newsgroups: comp.databases.oracle
Subject: Re: How to print something in PL/SQL
Date: 9 Sep 92 10:17:53 CST
Organization: Datalogics, Inc., Chicago, IL

In article <1992Sep7.100426.353@hhcs.gov.au>, pihlab@hhcs.gov.au writes:
>> Oracle, in their infinite wisdom, has deemed it appropriate that PL/SQL
>> have no native print utilities.  A rather hideous means of "printing"
>> is to write results to a table and then print them out with a select
>> statement following the PL/SQL script.  But this is horrible (and costly
>> in terms of extra database access) alternative to a print statement.
> 
> I've been toying with the idea that SQL*Connect could be used to write output
> from within PL/SQL but you would have to define a "table" to SQL*Connect that
> is really an OS output stream.  Unfortunately, we don't have SQL*Connect/RMS
> here so I can't play with it.
> 
In version 7, oracle offers packaged procedures called debug_io which resemble
"c" print statements. This is a cosmetic improvement, for internally, the
procedures write to tables. Furthermore, in v7 sqlplus you can declare
pl/sql variables which are bound to your dml statements. you can examine the
scalar values in much the same way as defined &symbols.

No matter what you do, the kernel has no choice but to write to structures
available to it. One item nobody has considered is the session trace file.
Could the kernel be seduced to put debug messages there?
--
Dave Mausner, Senior Consultant / Datalogics Inc / Chicago IL / 312-266-4450
dlm@dlogics.com                                 Motto: Just show me the code
Newsgroups: comp.databases.oracle
From: francois@welchgate.welch.jhu.edu (Francois Schiettecatte)
Subject: SQL*Textretreival
Organization: Johns Hopkins Univ. Welch Medical Library
Date: Mon, 14 Sep 1992 17:45:28 GMT

Hi

I remember seing some documentation and brochures for an oracle
full(?) text retrieval system about 3 years ago. I think it may
be called sql*textretrieval. I also think that the front-end
to it was built using sql*forms btu I am not sure. Does anyone have
any information about it, or even better does anyone use it. If there
is interest, I will post a summary back to the group.


francois


Francois Schiettecatte
Software Engineer
Welch Medical Library
Johns Hopkins University
Internet: francois@library.welch.jhu.edu
Phone	: (410) 955-7581
From: dlm@hermes.dlogics.com
Newsgroups: comp.databases.oracle
Subject: Re: How to print something in PL/SQL
Date: 10 Sep 92 12:47:21 CST
Organization: Datalogics, Inc., Chicago, IL

mmcgreg@uswnvg.com (Mark McGregor) writes:
> : Oracle, in their infinite wisdom, has deemed it appropriate that PL/SQL 
> : have no native print utilities.   
> : 
> Sybase has already implemented stored procedures 
> as part of there kernel and also includes a message and error facility 
> with the following specifications: 
>  
>     if exist (select * from table) where something = something 
>     print "something" 
> [and] 
>     Raiserror #### 'message' 
>

It seems to me that Oracle could create a v$ table with columns sessionid,
message. Make the "message" function one of the pl/sql kernel built-ins. The
function inserts to the invisible v$ table. When the pl/sql function finishes,
SQL*Plus can dump out the contents of the V$ table; SQL*forms can write the
contents to the message line; SQL*reportwriter can write it to the report file.
and so on. Oracle guys?
-- 
Dave Mausner, Senior Consultant / Datalogics Inc / Chicago IL / 312-266-4450 
dlm@dlogics.com                                 Motto: Just show me the code
Newsgroups: comp.databases.oracle
From: sstephen@us.oracle.com
Subject: Re: Problem with EXIT_FORM (Domino-effect)
Organization: Oracle Corporation, USA
Date: Sun, 13 Sep 1992 01:00:10 GMT
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.

In article <1992Sep9.085923.44@bmwf1f.bmwf.gv.at>, hatzinger_m@bmwf1f.bmwf.gv.at writes:
> 
> Hi,
> 
> My english is not very good, but I hope you will understand me. I have a form 
> which calls another form. And this form calls another form and another form 
> and so on. Now if I press the <PAGE UP> key, I want to exit the current 
> form. But if I press the <EXIT> key, I want to exit all forms, so I get 
> back to the operating system or SQL*Menu.
> 
[Diagrams and triggers deleted]
> 
> This is my solution, but is there any way to do this more easily ?
> 
> Any ideas ?
> 
> ================================================================================
>                                                                           ^
>  Federal Ministry of Science and Research                               B | M
> <-------------------------------------------------------------------------+---->
>  Computer Center                                                        W | F
>                                                                           |
>  Klaus-Michael Hatzinger            mail: hatzinger_m@bmwf1f.bmwf.gv.at   |
>  Bankgasse 1/209                   phone: 0043-222-53120/5188             |
>  1014 Vienna, Austria                fax: 0043-222-53120/5155             V
> ================================================================================

If you don't mind losing data on the calling form, (you can keep your global
variables), you COULD do something like :

KEY-PRVBLK
==========
NEW_FORM(FORM_ATTRIBUTE('CALLING_FORM'));

KEY-EXIT
========
EXIT_FORM;

This has the special advantage that you can have an unlimited number of forms. 
There really isn't any reason for using CALL_FORM, (and thus stacking all the
forms you just called), unless you still want to allow your user to rollback
even after leaving the first form, (assuming you POSTed it).

Probably even more elegant would be to have a table of "what form to call
next", and query a value rather than using :SYSTEM.CALLING_FORM, but I guess
that depends on how complex your application is.


Scott Stephens (Just my own observations, not the offical Oracle answer).
From: pihlab@hhcs.gov.au
Newsgroups: comp.databases.oracle
Subject: Re: Summary: Controlling "ad hoc" queries
Date: 14 Sep 92 08:55:03 +1000
Organization: Aust. Dept. Health, Housing and Community Services

In article <1992Sep11.014224.19879@usage.csd.unsw.OZ.AU>, troy@cbme.unsw.EDU.AU (Troy Rollo) writes:
> A number of solutions are being used. The simplest, for dealing with
> overgrown queries, is both fascist and liberal limits being imposed
> on the tool (We do this by means of a client/server system, and impose
> the limits at the server, thus preventing wasted network traffic).
> The liberal limit informs the user periodically that the current
> query is really not at all simple and asks them if they really
> want you to do it. The fascist limit states in not so many words that
> the administrator thinks you are being unreasonable and you should
> go back and try it again.

I assume that you take the user's query and run it through EXPLAIN, then you
examine the results of EXPLAIN to determine how the script will perform, and
then you notify the users of the performance of the query before actually doing
the query.

This would be nice but adds a slower startup and you still don't know how many
rows are going to be returned OR processed.
 
> These are both configurable limits based on number of bytes and/or rows.
> Should the limit be enforced, the server informs the database via a
> cancel message, and if the user is really desperate they can still use
> whatever they did get back.

The problem with adhoc querries is more the fact that the user processes
thousands of records usually with many repeated full table scans to pull off a
report of 10 or less rows.

SQL*ReportWriter already allows you to put a restriction on the size of the
returned report but doesn't factor in the amount of work done by the kernel to
get weed out the required rows and get them into the right order before
actually sending them to the user.

> The second issue I think you were asking about is the issue of
> users not knowing the structure of the database and doing something
> which returned meaningless results (by, for example, misspecifying
> the joins). Without trying to get into an advertising blurb, our
> approach is, said briefly, to have the tool do the joins itself,
> thus eliminating the need for the user to do them. There are a
> lot of subtleties in doing that, which I won't go into here.

Having the tool actually formulate the script from the requirements as
specified by the user is a safe way of ensuring the indexes are used
correctly.

Oracle are doing this (I hope) with their new end-user tools.

-- 

Bruce...        pihlab@hhcs.gov.au
                 ^^
*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Health, Housing & Community Services *
* Canberra, Australia                             (W) 06-289-7056 *
*******************************************************************
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************
From: sstephen@us.oracle.com
Newsgroups: comp.databases.oracle
Subject: Re: Changing Column Name
Date: 12 Sep 92 06:57:24 GMT
Distribution: usa
Organization: Oracle Corporation, USA
X-Disclaimer: This message was written by an unauthenticated user
              at Oracle Corporation.  The opinions expressed are those
              of the user and not necessarily those of Oracle.

In article <BuBryy.Ep8@well.sf.ca.us>, mharper@well.sf.ca.us (Michael J. Harper) writes:
> This should be an easy one :-)
> 
> How do I change the name of a column in a table?
> 
> Michael J. Harper
> mharper@well.sf.ca.us

Not so easy.  You could try,

create table my_temp as
select a,b,c d from a_table;   -- table my_temp will have columns a,b,d
drop table a_table;
rename my_temp to a_table;

However, you would end up losing all synonyms, views, indexes, grants, and
constraints in the process.  You could get around that by exporting the table
with those objects, using the above statements, then import everything except
the table.  But, this does not "rename" references to the column in views,
synonyms, indexes and some grants.  I seem to remember the SQL1 standard saying
that since columns rely on names, not position, that this operation was
disallowed as an integrity violation, however, I have absolutely no proof that
this is true, (and I know some databases allow this).

Most designers I know would do something like this :

alter table a_table add column d ...;  -- same as format for "c"
update a_table set d = c;
update a_table set c = NULL;

Then, wait until the next generation of your application to get rid of column
C.

Scott Stephens  (from my own experience, NOT official word of my company)
From: dlm@hermes.dlogics.com
Newsgroups: comp.databases.oracle
Subject: Re: Problems in olsqlforms30 and OCI (in C)
Date: 7 Sep 92 11:51:56 CST
Organization: Datalogics, Inc., Chicago, IL

In article <1992Sep3.163521.26698@cbfsb.cb.att.com>, 
colten@cbnewsb.cb.att.com (marc.colten) writes:
> [...]
> 
> 3)  Can you do a query on a detail block and have the relevant
>     information come up in the master block?  It works fine
>     the other way.  I do a query in a master block and it brings
>     up two records in the detail (which it should).  I've tried
>     doing the reverse and it won't work.
> 
>     Is this possible?
> 
It is possible, but in very limited conditions. The limitation is that you must
have only one master corresponding to the set of retrieved detail rows. If you
query the detail on some non-key attribute, you will retrieve rows which may
correspond to several different masters. In this case, which master shall you
display? You might say, "all of them", but there is no key-enforcement
mechanism in sqlforms that can do that; to query the master as if it were a
detail, you will need just ONE key to enforce.

If this limitation is okay with you, then just write triggers that treat
KEY-ENTQRY and KEY-EXEQRY in the detail exactly as if they are in the master,
but with the block associations reversed. Sqlforms will not generate this
relationship in the default-block display.
> 
> 4)  Can you do a LOV on a block, or failing that on a non-enterable
>     field?  I just want a list relevant to the data I've queried
>     as an alternative to a second page, which is not really necessary
>     and really slows things down.
> 
You can write KEY-LISTVAL triggers at form, block, or field level. Write a
pop-up block that displays the info you want. Make the key-trigger go to that
block and execute your query. The "<list>" tag will not display on the message
line since you will not be using the built-in list-of-values hook. But you will
have much more flexibility.
--
Dave Mausner, Senior Consultant / Datalogics Inc / Chicago IL / 312-266-4450
dlm@dlogics.com                                 Motto: Just show me the code
Newsgroups: comp.databases.oracle