DB Triggers vs Stored Procedures

2003-01-12 Thread Sathyanaryanan_K/VGIL
Hi  All
I would like to know the difference between using the Stored procedures in
DB Triggers and writing the code directly in the DB Trigger. Which would be
better to use and what r the advantages.
Rgds
Sathya


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




RE: Database up longer that host?

2002-12-02 Thread Sathyanaryanan_K/VGIL
Try this out. thisqry is working well for hrs and mins. hop u modify this
qry get for hrs also
wishes
select
(((sysdate-startup_time)*60*24-mod((sysdate-startup_time)*60*24,60))/60)HRS
,
round(mod((sysdate-startup_time)*60*24,60))MIN from v$instance

Wishes

Sathyanarayanan




|+---
||  Stephen Andert |
||  stephenandert@firsth|
||  ealth.com   |
||   |
||  03/12/2002 09:28 |
||  Please respond to|
||  ORACLE-L |
||   |
|+---
  --|
  |  |
  |   To: Multiple recipients of list ORACLE-L   |
  |   [EMAIL PROTECTED] |
  |   cc: (bcc: Sathyanaryanan K/VGIL)   |
  |   Subject: RE: Database up longer that host? |
  --|





Govind,

Actually, what I want it the same format as I have, I just want the
numbers to match (or fall within) the numbers reported by the unix
uptime command for example up 4 days, 21:08 hours.  In my case, the
unix uptime is saying the host was last restarted after the database
startup_time reported in v$instance.

Stephen

 [EMAIL PROTECTED] 12/02/02 07:43PM 
You may use following query will give you the uptime in hours and in
minutes.

select sysdate, startup_time,
round( (sysdate - startup_time) *24*60 ,0 ) uptime_in_minutes,
round( (sysdate - startup_time) *24 ,0)  uptime_in_hours
from v$instance

SYSDATESTARTUP_TIME   UPTIME_IN_MINUTES UPTIME_IN_HOURS
-- -- - ---
20021202203918 20021202044608   953  16

Hope this is what you wanted.

-Original Message-
Sent: Monday, December 02, 2002 8:34 PM
To: Multiple recipients of list ORACLE-L



On Mon, 2 Dec 2002, Stephane Faroult wrote:

 Stephen Andert wrote:
 
  I use a script named db_uptime.sql (I think I got it from the list
here)
  to calculate how long the database has been up.  The output
compares
  nicely to the unix uptime command.

I hope that the query doesn't come from the list, because it is
 wrong. The error is to apply floor() before multiplying by 24 or 60
-
 you have tremendous rounding errors.
 My own database has not been up long enough to be 100% sure about it
but
 I believe the following to be correct :

Hi:

Neither of the scripts works for me.  Try this?

select
'Host Name : '||host_name|| chr(10)||
'Instance Name : '||instance_name|| chr(10)||
'Uptime : ' ||floor(xx)||'days '
|| floor( 24 * (xx - floor(xx)) ) || 'hours '
|| round( 60 * (24 * xx - floor(24 * xx))) ||
'minutes '
from (
 select host_name,instance_name ,(sysdate-STARTUP_TIME) xx
 from v$instance
 )
/


Meg Crocker
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Meg Crocker
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: [EMAIL PROTECTED]
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephen Andert
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP 

Re: Effect of Upgrading O/S to the 817 database !!!

2002-11-29 Thread Sathyanaryanan_K/VGIL
If ur upgrading from nt 5 to 2000 then he first opt should work. Just
install oracle on w2000 and start up the db coz the services are n nt. but
if ur goin in for a fresh install of w2000, it is better to create a fresh
db and import.
Alternatively u can create a db with the same config of existig db and copy
the old db folder with that of ur new one.
hope this should work. wishes.
Regards,

Sathyanarayanan




|+---
||  Jackson |
||  Dumas   |
||  tjaros@webma|
||  il.co.za|
||   |
||  29/11/2002   |
||  18:33|
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+---
  --|
  |  |
  |   To: Multiple recipients of list ORACLE-L   |
  |   [EMAIL PROTECTED] |
  |   cc: (bcc: Sathyanaryanan K/VGIL)   |
  |   Subject: Effect of Upgrading O/S to the 817|
  |   database !!!   |
  --|





Hi

We are currently running Oracle 817 database on a Windows NT, version
5, service pack 6. We need to upgrade O/S to Windows 2000. What should
we do on the database side, do we need to do a new Oracle 817 software
installation after upgrading O/S and try to startup the database or do
we need to do everything from scratch, i.e. install software, create
database and import ?  I tought this should not have an effect on the
database, if that the case, do we then need to just try to startup the
database after O/S upgrade ? Please help ...your response will be
highly appreciated. Desperado

___
 http://www.webmail.co.za the South-African free email service

  NetWiseGurus.Com Portal - Your Own Internet Business Today!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jackson Dumas
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




Re: SQL tuning help

2002-11-26 Thread Sathyanaryanan_K/VGIL
check out the status in v$sess
Regards,

Sathyanarayanan




|+---
||  Sergei |
||  sergei@netfl|
||  ip.com  |
||   |
||  27/11/2002   |
||  00:24|
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+---
  --|
  |  |
  |   To: Multiple recipients of list ORACLE-L   |
  |   [EMAIL PROTECTED] |
  |   cc: (bcc: Sathyanaryanan K/VGIL)   |
  |   Subject: SQL tuning help   |
  --|





Hello everybody,

I have the following query that runs every week.

UPDATE tmp_brian_metareward1 tmp
  SET offers_seen  = (SELECT count(f.fastcash_id) FROM
metareward.fastcash f
 WHERE f.subsite_id = tmp.subsite_id
   and attempt = trunc(sysdate-1)
   and attempt  trunc(sysdate)
 group by tmp.subsite_id);

This week it began to hang and I can't figure out why.  No changes were
made to a database.  Please advise me on how I can tune it, which hints
to add, or anything else I can do.

Thank you
Sergei


--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sergei
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




Re: help with connecting to sqlplus

2002-11-24 Thread Sathyanaryanan_K/VGIL
Just check for the Oracle services in Control Panel- Services.
ur Instance,TNS should be started. if not start these services and try
connecting.
Regards,

Sathyanarayanan




|+---
||  john |
||  john_g123_9@|
||  yahoo.com   |
||   |
||  23/11/2002   |
||  22:43|
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+---
  --|
  |  |
  |   To: Multiple recipients of list ORACLE-L   |
  |   [EMAIL PROTECTED] |
  |   cc: (bcc: Sathyanaryanan K/VGIL)   |
  |   Subject: help with connecting to sqlplus   |
  --|





ver 8i on windows NT4
chose the Typical install in 8i during installation
hence i guess a starter db is created by the installation

via svrmgrl did startup

trying to connect to sqlplus both gui as well as command
line (on the same server box itself)

tried with just sqlplus and it challenges for
Enter user-name: i entered sys / as sysdba
asks for password:   i entered change_on_install

error ORA-01033: Oracle initialization in progress.

waited for 20 minutes, same error. did a shutdown and startup
again, same error
---
other clues/messages

shutdown, shutdown normal show this
Error ORA-01507: database not mounted

connected to sqlplus with nolog and creating a sample table
with CREATE TABLE or do a DESCRIBE TABLE for this shows
not connected

__
Do you Yahoo!?
Yahoo! Mail Plus ? Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: john
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




Storing of number datatype in table

2002-11-22 Thread Sathyanaryanan_K/VGIL
Dear List

Have a look at the sql !!!

SQL create table trnid
  2  ( trn_id number(10));
Table created.

SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/
1 row created.

SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/
1 row created.

SQL commit;
Commit complete.

SQL select trn_id from trnid;

   TRN_ID
-
1.111E+09
1

can anyone tell why the number(10) is stored in exp format

Regards,

Sathyanarayanan


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




Re: OEM Config Assistant

2002-11-22 Thread Sathyanaryanan_K/VGIL
some rights has to granted to the user n for setting up the repository u
may need to modify ur register entry for the mgmt svr. check out the
installation notes to the set the values of key in the registry.
Regards,

Sathyanarayanan




|+
||  Mike Sardina|
||  cemail2@sprin|
||  tmail.com|
|||
||  23/11/2002|
||  01:29 |
||  Please respond|
||  to ORACLE-L   |
|||
|+
  --|
  |  |
  |   To: Multiple recipients of list ORACLE-L   |
  |   [EMAIL PROTECTED] |
  |   cc: (bcc: Sathyanaryanan K/VGIL)   |
  |   Subject: OEM Config Assistant  |
  --|





Using OEM Configuration Assistant 2.2, I tried to set up a Management
Server/repository, etc. and through the gui I created a user with a
password,
etc.  Now when I try to log into the Management Server with that
username/password, I get invalid credentials.  Through sqlplus I can log
into that username/password though.  Any ideas on what the problem could
be?
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mike Sardin
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




Re: Storing of number datatype in table

2002-11-22 Thread Sathyanaryanan_K/VGIL
Thank u list for ur imm resp. It was the prob with numwidth.
Now I have set the num width to 25 and s working fine.
But whenever i stat the sql the default is set to 9. how do i change the
def numwidth??
Regards,

Sathyanarayanan




|+---
||  Arup Nanda |
||  arupnanda@ho|
||  tmail.com   |
||   |
||  22/11/2002   |
||  19:38|
||  Please   |
||  respond to   |
||  ORACLE-L |
||   |
|+---
  --|
  |  |
  |   To: Multiple recipients of list ORACLE-L   |
  |   [EMAIL PROTECTED] |
  |   cc: (bcc: Sathyanaryanan K/VGIL)   |
  |   Subject: Re: Storing of number datatype in |
  |   table  |
  --|





The number is more than the numwidth specified. Try this

SQL set numwidth 13
SQL select trn_id from trnid;

Your numwidth is perhpas defined as 9; so anything of more precision is
displayed as exponetial notation; internally all numbers are stored the
same.

HTH

Arup Nanda
www.proligence.com

- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, November 22, 2002 6:23 AM


 Dear List

 Have a look at the sql !!!

 SQL create table trnid
   2  ( trn_id number(10));
 Table created.

 SQL insert into trnid(trn_id) values('11');/* 10 1's inserted*/
 1 row created.

 SQL insert into trnid(trn_id) values('1');/* 9 1's inserted*/
 1 row created.

 SQL commit;
 Commit complete.

 SQL select trn_id from trnid;

TRN_ID
 -
 1.111E+09
 1

 can anyone tell why the number(10) is stored in exp format

 Regards,

 Sathyanarayanan


 --
 Please see the official ORACLE-L FAQ: http://www.orafaq.com
 --
 Author:
   INET: [EMAIL PROTECTED]

 Fat City Network Services-- 858-538-5051 http://www.fatcity.com
 San Diego, California-- Mailing list and web hosting services
 -
 To REMOVE yourself from this mailing list, send an E-Mail message
 to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
 the message BODY, include a line containing: UNSUB ORACLE-L
 (or the name of mailing list you want to be removed from).  You may
 also send the HELP command for other information (like subscribing).

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Arup Nanda
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: [EMAIL PROTECTED]

Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).