Re: Date / Time
On Tue, Jul 10, 2001 at 05:26:09AM -0800, Sajid Iqbal wrote:
I want to display the time elapsed between two dates - in days, hours,
minutes and seconds.
If I do select date1 - date2, the result is : 12.0194907
Is there a function that will turn the number of days into something more
legible? Ideally i'd like to do ;
to_char(12.0194907,'DD:HH:MI:SS') but obviously that won't work. Is
there a solution other than writing a complex function myself which will
have to * by 24, / by 60 and substr etc to get the different bits of the
number?
using a as your value (cos it's shorter to write)
trunc(a)||':'||to_char(trunc(sysdate)+a-trunc(a), 'HH24:MI:SS')
You could get away with using
to_char(a+to_date('31-Dec-00'), 'DD:HH24:MI:SS')
if you can ensure that 1 = a 32
--
T.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tommy Wareing
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: Invalid packages not being recompiled by Oracle
On Thu, Jun 28, 2001 at 10:56:23AM -0800, Troiano, Paul (CAP, GEFA) wrote: We have one package A that refers to package B. If package B's body and specification are both recompiled by user 1, package A is correctly marked as invalid. Another user, user 2, then attempts to execute package A and gets the following error stack: -- ORA-04068: existing state of packages has been discarded -- ORA-04061: existing state of package B has been invalidated -- ORA-04065: not executed, altered or dropped package B -- ORA-06508: PL/SQL: could not find program unit being called -- ORA-06512: at B, line n ORA-06512: at line 2 User 2 then issues the statement 'alter package A compile;' It compiles successfully and user 2 can now execute the package. Why does Oracle not automatically recompile package A as it should? Since user 2 was able to recompile the package in its existing state, Oracle should not have failed at doing so. User 2's session instantiation of package A (as well as all of their other instantiated packages) should have been lost when package A was invalidated. I understand that two possible options are: (1) Flush the shared pool after recompiling; and (2) Manually recompile all dependent packages (such as A). The first seems like overkill and will cause performance issues. The second, with many dependencies involved, is not an option. We need to consistently be able to recompile a package that other packages are dependent upon without a user recieving the above error stack in addition to not impacting performance or without having to recompile all the dependent packages. Having read the other responses, and thinking odd, I've been doing this for years, I've spotted the source of your confusion: Oracle does automatically recompile package A as it should. ;-) The problem is that package B declares some package variables, and user 2 has already invoked it at some point in their session. So when B is recompiled by user 1, the package variables of user 2 will need to be flushed, and reinitialised. This doesn't happen until they reinvoked A, which then produces the exception that you're discovering. This is really just a warning, to tell you that the variables are being flushed (and at this point the reset happens). If the operation is tried again, it'll succeed. So you could code... PACKAGE A IS BEGIN ... BEGIN B.proc_call; EXCEPTION WHEN STATE_DISCARDED THEN B.proc_call; END; ... END; Tedious though... -- T. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tommy Wareing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: Different behavior of Too_many_rows exception in pl/sql betwe
On Fri, Jun 22, 2001 at 12:45:34AM -0800, Amar Kumar Padhi wrote: if you are looking for one row only, then A and C will do. Since C involves a little bit of more coding and A does the same thing, A would be a better choice. But if you are coding to trap too many rows, then you should check for exceptions, all depends on your requirement. Unless, of course, the select statement in question has an order by clause. Since rownum is now generated before the ordering is applied, you'll get the first row of the unordered set, and that row will then be sorted. Not good. rgds amar -Original Message- Sent: Friday, June 22, 2001 12:36 AM To: Multiple recipients of list ORACLE-L betwe I have seen code that depends on the 8.1 behavior. It does bring up a question I have always wondered about. The question for the internals ubergeeks is: which of the following is best to use? (All should have the same result, so which one has the best performance and why?) A) declare y number; begin select x into y from z where rownum = 1; foo(y); end; B) declare y number; begin begin select x into y from z; exception when too_many_rows then null; end; foo(y); end; C) declare y number; cursor c is select x into y from z; begin open c; fetch c into y; close c; foo(y); end; -- Tommy Wareing Oxford English Dictionary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tommy Wareing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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: explain plan
On Fri, Jun 15, 2001 at 02:05:44AM -0800, Raymond Lee Meng Hong wrote:
Hei Guru.
I have a query which involve to some across table , here is my explain plan
. As a developer is we alway choice a best way to select table , I do join
those field related together ,but why it still perform table access(FULL)
???FULL SCANING ?
SELECT STATEMENT Optimizer=CHOOSE
SORT (ORDER BY)
CONCATENATION
MERGE JOIN
Properly joined
SORT (JOIN)
MERGE JOIN
SORT (JOIN)
NESTED LOOPS
MERGE JOIN
TABLE ACCESS (BY ROWID) OF CF
INDEX (UNIQUE SCAN) OF CF_PK (UNIQUE)
FILTER
TABLE ACCESS (FULL) OF LN01
TABLE ACCESS (BY ROWID) OF CF99
INDEX (UNIQUE SCAN) OF CF99_PK (UNIQUE)
SORT (JOIN)
TABLE ACCESS (FULL) OF LN01OTH
SORT (JOIN)
TABLE ACCESS (FULL) OF LN21PNB
Improperly Joined
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
NESTED LOOPS
TABLE ACCESS (FULL) OF LN21PNB
TABLE ACCESS (BY ROWID) OF CF01
INDEX (UNIQUE SCAN) OF OLDIDNO_CON (UNIQUE)
TABLE ACCESS (FULL) OF LN01OTH
TABLE ACCESS (FULL) OF CF99
TABLE ACCESS (FULL) OF LN01
here is my SQl.
select A.cifkey ,b.BRNCD,
b.ACNO,b.CHKDGT,acsts,REPAY_AMT,trunc(fldchar),icno,finance_cd,agent_br_cd
from ln21pnb , cf01 A,LN01 B , cf99 c , ln01oth d
where oldidno = icno
or idno = icno
You're missing missing a bracket here: (oldidno=icno or idno=icno)
What you're actually getting is any entries from the 4 tables where
oldidno=icno, and no other joins are taking place, in conjunction with
those entries where idno=icon and the four tables are properly joined.
The first alternative gives you the Improperly Joined section of the
plan above.
AND A.CIFKEY = B.CIFKEY
and b.brncd = c.brncd
and b.acno = d.acno
and b.chkdgt = d.chkdgt
and a.cifkey = '40'
and cd ='PNBMEMBERNO'
AND acsts in ('A','R','L','2')
and fldchar is not null
order by a.cifkey
can it be optimizer ???
--
T.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tommy Wareing
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
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: OT: Perl module question
On Tue, Jun 12, 2001 at 11:15:42PM -0800, Nguyen, Long (ITS, Limestone Ave) wrote: Hi, I am installing a request tracking software (WREQ) which requires Perl 5 with GDBM support. I could not find GDBM_File.pm under /usr/local on my Solaris 2.6 machine. The command perl -e 'use GDBM_File;' gave the message: Can't locate GDBM_File in @INC (@INC contains ) Questions: 1) Do you know if a standard perl installation should contain GDBM_File module? If yes then I will try to re-install the Perl software. It doesn't. 2) I also downloaded Gnu gdbm and installed it on the machine. It seems to install a number of .so and .a files in /usr/local/lib (but I could not find GDBM_File.pm anywhere). Do I need to link the software into Perl somehow so GDBM_File.pm module is created in the Perl library? You also need the GDBM_File module, which you can get from CPAN. -- Tommy Wareing Oxford English Dictionary -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Tommy Wareing INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
