Re: = NULL vs. IS NULL
At 9:51 -0700 23/10/01, Michael Peppler wrote: And just to be complete, Sybase (and possibly MS) has an ansinull option that can be set. If it is not set then the x = null *will* work correctly. I'm not sure about the use of 'correctly' here. NULL is the absence of value and therefore cannot be equal to anything. 'x = NULL' is the same as 'FALSE'. Regards Paul Miller -- - Carib Data Limited mailto:[EMAIL PROTECTED] http://www.caribdata.co.uk
Re: Delete
At 11:41 -0400 18/10/01, Yibin Dong wrote:
It depends on which database you are using. In Oracle,
when TO_DATE() is employed within where clause, all indexes
will be useless. Oracle will do a range scan instead of
index row scan.
Not in the case used.
At 9:48 -0400 18/10/01, Rozengurtel, Daniel wrote:
$sql = DELETE FROM ft_t_ispc WHERE LAST_CHG_TMS
TO_DATE('$final_date','MM/DD/');
In this case, the to_date() function is on a literal. This has no
effect on optimiser choice. If the to_date() function had been
applied to a column value, then it would have suppressed the index.
Regards
Paul Miller
--
-
Carib Data Limited
mailto:[EMAIL PROTECTED]
http://www.caribdata.co.uk
Re: DBI ORACLE TNS (better explained)
At 9:34 -0300 17/10/01, [EMAIL PROTECTED] wrote:
After some executions of this CGI, the listener on Oracle server machine
goes down.
For a while, other clients that was connected to the Oracle server can still
access it, althought it is not possible to stabilish new connections to
Oracle.
This is because the listener is only responsible for spawning new
server processes. The existing server processes are unaffected
because they are already established.
After some more time, the dabase stops definitivelly and needs to be
restarted.
This is what you need to check on. My guess is that you have exceeded
either 'processes' or 'sessions' init.ora parameter. You say CGI, but
if you are actually using mod_perl, it is astonishingly easy eaven
for an experienced programmer to leave hundreds of defunct servers -
which take a suprising amount of time to clear from Oracle's internal
session table. Oracle rarely crashes. If you think that it is,
connect with svrmgrl _before_ running your tests, and stay connected
after the 'crash'. Query v$session and v$session_wait and look at the
number of sessions established and active then compare that with the
value column of v$parameter where name = 'processes'.
Also look at the logs in $ORACLE_BASE/admin/SID/[ubc]dump and at
the listener log in $TNS_ADMIN/log/listener.log. They will provide
clear indications of why/whether the listener/Oracle crashed.
$v_sql1 = 'select count(numero) ' . ... ;
$v_count = $v_dbh-selectrow_array($v_sql1, {RaiseError = 1});
$v_dbh-{LongReadLen} = 128 * 1024;
$v_dbh-{LongTruncOk} = 1;
$v_sth = $v_dbh-prepare($v_sql2);
$v_sth-execute();
while (... $v_sth-fetchrow_array)
...
$v_sth-finish;
$v_dbh-disconnect;
There is nothing obviously wrong here, excepting that the LongReadLen
is a bit ambitious. Oracle's implementation and performance on Long
Raw data is lamentable, but not normally unstable. This is almost
definitely _not_ the problem though, since it would not make the
listener crash.
I'd like to know if somebody knows about pitfalls and problematic situations
related to DBI, Oracle server 7.3.4, or in the stablishment of various quick
and simultaneos connections to Oracle (as occurs with the typical use of
this CGI), which can be result in a listener crash.
The only error detected is just AFTER the listener crash, obviously a TNS
error.
Any clues?
If you want to make a better and more responsive CGI, look at
mod_perl in which one connection can handle multiple sequential
requests. Carefully written you might see an increase in performance
of 40x (what we found in testing) and a reduction in machine
resources used by Oracle.
Regards
Paul Miller
--
-
Carib Data Limited
mailto:[EMAIL PROTECTED]
http://www.caribdata.co.uk
Re: (Fwd) DBI Oracle TNS error May you help me?
At 17:01 +0100 16/10/01, Tim Bunce wrote: I use Perl to connect to Oracle version 7.3.4 through DBI Perl (Unix Sun-Solaris). After some days of access, the driver has been knocking down the DB. So, the DB is started again. The presented error is TNS ora12154 (DBD: login failed). Straight from oerr: /u01/millerp/hps oerr ORA 12154 12154, 0, TNS:could not resolve service name // *Cause: The service name specified is not defined correctly in the // TNSNAMES.ORA file. // *Action: Make the following checks and correct the error: // - Verify that a TNSNAMES.ORA file exists and is in the proper // place and accessible. See the operating system specific manual // for details on the required name and location. // - Check to see that the service name exists in one of the // TNSNAMES.ORA files and add it if necessary. // - Make sure there are no syntax errors anywhere in the file. // Particularly look for unmatched parentheses or stray characters. // Any error in a TNSNAMES.ORA file makes it unusable. See // Chapter 4 in the SQL*Net V2 Administrator's Guide. If // possible, regenerate the configuration files using the Oracle // Network Manager. The error is actually in the tnsnames.ora file which is preventing a tns resolution of the nickname used in the connect string. Try: $ sqlplus scott/tiger@ORCL substituting a real user for 'scott', the real passeord for 'tiger' and finally the TNS connect string you are trying to use for 'ORCL'. You can also use the tnsping utility to troubleshoot this problem. If the problem is suspected to be the first one named (tnsnames.ora file not in the right place) try setting the environment variable 'TNS_ADMIN' to the path to the directory containing a known-to-be-good tnsnames.ora file. TNS is very frustrating to debug, but using tnsping and testing with sqlplus will eventually get you there... Regards Paul Miller -- - Carib Data Limited mailto:[EMAIL PROTECTED] http://www.caribdata.co.uk
Re: Unix GUI tool for Oracle (and others like mysql in future)
At 13:23 +0100 15/10/01, Tim Bunce wrote: If you're using Oracle from unix (or even windows) then this looks very interesting: www.globecom.net/tora Rather expensive though since it relies on the Qt system from Trolltech who charges $1500 for a license. How does that fit in with the GPL license that comes with TOra? PD Miller -- - Carib Data Limited mailto:[EMAIL PROTECTED] http://www.caribdata.co.uk
Re: Unix GUI tool for Oracle (and others like mysql in future)
At 13:23 +0100 15/10/01, Tim Bunce wrote: If you're using Oracle from unix (or even windows) then this looks very interesting: www.globecom.net/tora Rather expensive though, since to use it (on UNIX) you have to pay $1500 to license Qt from Trolltech. Regards Paul Miller -- - Carib Data Limited mailto:[EMAIL PROTECTED] http://www.caribdata.co.uk
Re: Unix GUI tool for Oracle (and others like mysql in future)
At 7:58 -0700 15/10/01, Andy Duncan wrote: Those using the Qt libraries, which Tora relies upon, may also wish to check out the following pages to make sure they're not straying into any kind of local financial difficulties with Trolltech's QPL licence, which does seem to have become a little less restrictive, as of late: http://www.trolltech.com/products/download/freelicense/ http://www.trolltech.com/products/download/freelicense/license.html http://www.trolltech.com/developer/faq/simple.html It seems to have become more restrictive again. All of these links have disappeared in the last couple of hours. The free license is now not mentioned at all and only the $1500 developer license is available for UNIX it seems. Shame too, the bumpf on TOra looks very good. Regards Paul Miller -- - Carib Data Limited mailto:[EMAIL PROTECTED] http://www.caribdata.co.uk
Re: date format in Oracle
At 16:50 -0700 1/10/01, jie zhang wrote:
Somehow, TZ is not recognized in my system. I am using oracle 8.1.7 on
solaris 5.8.
Did you actually tried out in your system ? Do I need to
set up any NLS variable in order to use the 'TZ' keyword ?
I'm afraid that Oracle's handling of timezones is primitive at best.
There is no date format modl string for time zone and the function
new_time() only recognises a very Americano-centric set of zones.
You can though use the environment variable TZ (on SVR4 systems and
BSD if memory serves) to establish the zone of the session:
TZ=GMT+8 sqlplus scott/tiger
or
$ENV{TZ} = 'PST8PDT';
$dbh = DBI-connect(DBI:Oracle:,scott,tiger);
You can find the available zones in /usr/share/lib/zoneinfo on SVR4 or BSD.
From there, you can 'mask' the timezone in your date string like so:
insert into datetest values ( to_date('Thu Sep 20 15:03:10 PDT 2001'
, 'Dy Mon DD HH24:MI:SS --- ') )
As an aside, most other RDBMSs support timezones in the data, but
Oracle is just a bit slow on this.
Regards
Paul Miller
--
-
Carib Data Limited
mailto:[EMAIL PROTECTED]
http://www.caribdata.co.uk
RE: Easiest way to tell if a table already exists?
At 21:56 -0400 25/4/01, Sterin, Ilya wrote: Why would (select * from foo) take up lost of resources? Won't it just place the cursor at the first row and only return when rows are fetched? Speaking for Oracle, as soon as the statement is executed (and before any fetch) Oracle will execute the statement - and that means performing the full table scan(s) or index scan(s) necessary to fulfill the statement. In fact, any transactional (and therefore relational) database will have to do the same. The point in time for data consistency is the instant of execution, and for non-Oracle databases, locks will be applied at the time of the execution. It is bad practice to execute queries of the form often mentioned in this thread with no predicate. The waste of resource could be very significant (on some of our databases possibly several hours). If you need to know if a table exists, query the SQL standard data dictionary: select table_name from user_tables where table_name = 'TABLE_NAME'; Regards Paul Miller -- - Carib Data Limited mailto:[EMAIL PROTECTED] http://www.caribdata.co.uk
Re: connecting / as sysdba when using DBI/DBD-Oracle
At 10:23 -0500 27/4/01, Jay Strauss wrote: $ sqlplus / as sysdba The benefit to this method, as opposed to: $ sqlplus system/manager being, I don't have to include my database password in any script. Does anyone know how to do this in DBI/DBD-Oracle? I see in the doc you can do it like: If you don't need to be sysdba (and really you don't since you won't be able to do the only two distinguishing actions: startup and shutdown), I suggest that you look at external authentication. Very short course: # useradd -g dba scripter SQL alter user ops$scripter identified externally; % sqlplus / $user = $passwd = ''; $dbh = DBI-connect( $dsn, $user, $passwd ) Note that the external identification prefix (here shown as the default 'OPS$' may have to be defined in your init.ora file (os_authent_prefix = ops$. Regards Paul Miller -- - Carib Data Limited mailto:[EMAIL PROTECTED] http://www.caribdata.co.uk
RE: Oracle sqlload alternatives?
At 10:07 -0400 26/4/01, Simon Fan wrote: Does the DIRECT load option activate triggers during insert? No. And it only checks integrity constraints after the load. Regards Paul Miller -- - Carib Data Limited mailto:[EMAIL PROTECTED] http://www.caribdata.co.uk
