RE: comparing null values
While I agree that it could return the rows, I also understand the system
side.
Null is nothing so it can not be compared to anything else, even another
null. It is something like:
I don't know whether it is like '%STU%' or not because it is nothing - I
don't know what nothing is
Witold
-Original Message-
Jackson
Sent: 30 January 2003 3:27 PM
To: Multiple recipients of list ORACLE-L
Why is this?? If I know that value X has no value, I certainly know that
value X is not like '%STU%' ?
From: Whittle Jerome Contr NCI [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
CC: [EMAIL PROTECTED]
Subject: RE: comparing null values
Date: Thu, 30 Jan 2003 13:15:33 -0600
Gary,
Null has no value. It can't be like or not like anything. If you want to
see the null you'll need to add OR IS NULL to your Where clause.
Jerry Whittle
ASIFICS DBA
NCI Information Systems Inc.
[EMAIL PROTECTED]
618-622-4145
-Original Message-
From: Gary Jackson [SMTP:[EMAIL PROTECTED]]
Can anyone explain why it is that I seem unable to use 'like' and 'not
like'
on columns containing null values. (I am unable to find information
regarding this on MetaLink.)
For example:
SQL select * from tester2;
COL1 COL2 WHATEVER
11STUFF
22STUFF
33
44
SQL select * from tester2 where whatever not like '%STU%';
no rows selected
My question is why does this not return the 3 4 columns?
_
Protect your PC - get McAfee.com VirusScan Online
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Gary Jackson
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.net
--
Author: Witold Iwaniec
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).
Using connection of another user
Hi all I wonder if an application can use an already opened connection but pass credentials of the application user? I would like to use a connection pool to increase performance of my application by reducing opening and closing DB connections. So when my application server starts up it would open let say 10 connections connecting as APP_USER user. Then when user JDOE runs my application, the application would use one of the already opened connections, but Oracle would know that it should use JDOE's rights and any changes, also saved in an audit tables, would be made as JDOE. Of course JDOE would also be a database user but the application would not have to open new connection for JDOE. I have done it differently - open connections as APP_USER and when JDOE starts the application, I use one of the APP_USER's connections to verify that JDOE is a valid user, but then use the APP_USER's connection to run queries, make changes etc. Can, what I am looking for, be done? If yes, could you pass references to some info? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Witold Iwaniec 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: Using connection of another user
Dennis Thanks for the reply. Currently I have available versions 8.1.7 on Win 2000 and Sun Solaris, but soon I may get 9i (9.2.0.1) installed. Witold -Original Message- WILLIAMS Sent: 21 January 2003 12:00 PM To: Multiple recipients of list ORACLE-L Witold - You didn't say which Oracle version you are on, but you may want to look at application role, secure application role, global application context. Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Tuesday, January 21, 2003 7:04 AM To: Multiple recipients of list ORACLE-L Hi all I wonder if an application can use an already opened connection but pass credentials of the application user? I would like to use a connection pool to increase performance of my application by reducing opening and closing DB connections. So when my application server starts up it would open let say 10 connections connecting as APP_USER user. Then when user JDOE runs my application, the application would use one of the already opened connections, but Oracle would know that it should use JDOE's rights and any changes, also saved in an audit tables, would be made as JDOE. Of course JDOE would also be a database user but the application would not have to open new connection for JDOE. I have done it differently - open connections as APP_USER and when JDOE starts the application, I use one of the APP_USER's connections to verify that JDOE is a valid user, but then use the APP_USER's connection to run queries, make changes etc. Can, what I am looking for, be done? If yes, could you pass references to some info? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Witold Iwaniec 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.net -- Author: DENNIS WILLIAMS 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.net -- Author: Witold Iwaniec 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: Automatic backup on Oracle 9i -- For Jared
ORACLE-L FAQ: http://www.orafaq.net -- Author: OraCop 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). __ 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.net -- Author: mkb 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.net -- Author: Witold Iwaniec 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).
Oracle traffic
Hi DBAs Are there any tools that would show me the traffic coming in and out of the database when I run my application? We have used sniffer software for the network traffic but sometimes we get questions related to the numbers from the database side. I believe the sniffers show what gets send over the network including the packets info, address, etc. I wonder if I could get the numbers just for the data? Thanks in advance Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Oracle licensing
Hi There have been some postings related to Oracle licensing. An interesting article: http://www.sacbee.com/content/politics/story/2219532p-2613285c.html Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Good Article on differences between ORACLE and SQLServer
If I find a file in Mac format and don't have a tool to open it, I guess I should blame Mac... Witold On 19 Apr 2002 at 8:13, Jesse, Rich wrote: Which, of course was written by MS in MS Wurd format, which promptly locked up when I tried to close it. Figures. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -Original Message- From: Jason Rowski [mailto:[EMAIL PROTECTED]] Sent: Friday, April 19, 2002 10:49 AM To: Multiple recipients of list ORACLE-L Subject: Good Article on differences between ORACLE and SQLServer Hi Check the document below which discusses the differences in Oracle and SQL Server databases. http://www.dbresources.com/modules.php?name=Newsfile=articlesid=86 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Do programmers tune SQL?
I may open a can of worms - but don't intend to... Sometimes DBAs and Sys Admins make tuning impossible or at least very hard for developers. I am a developer and do a lot of tuning of statements used by our applications. I have, in our office, the luxury of DBA access to databases and access to UNIX servers. So life is not bad. But I have been in places were security was very strict and: - I had, in database, only user account with limited rights - I didn't have access to PLAN_TABLE so after writing my SQL I had to ask DBA to run it. And the same if I changed it, and then I may have changed it number of times... - it was worse when I had to tune stored procedures. Again, I had only a user account but not the procedure owner account - so I wrote procedure, asked the DBA to compile it, run my statements, and maybe all over again... - I didn't have account on the UNIX server so when the trace file was generated I had to ask the DBA to run TKPROF and send me the output. In an extreme case I run into junior DBA who had never run TKPROF before so it took even more time explaining why I wanted to use explain=... and sys=no etc. Occasionally he had to contact senior DBA and wait for his response. On top of that DBAs had other tasks so sometimes it took quite some time to get a procedure recompiled, trace file generated, or run TKPROF. In one place there was a rule that DBAs had 48 hrs to respond to any request and sometimes it took that long. Sometimes a very simple task, that could be completed in few minutes, took days because of all procedural things. At the end it cost companies lot of money. In our company many developers have DBA access to databases and in fact nobody does anything stupid. I have found that if people don't know what they are doing, they ask. Nobody tries to drop or truncate tables because they have found the script that seesm cool and want to see what it does... Over my 8 years here only once we had a problem - a few tables were dropped by mistake but the tables were dropped by our DBA who run the wrong script. It took almost no time at all to rebuild it. And it happened in a test database. I understand restrictions in production databases but more access to development and test would make life easier and am sure more happy faces around. Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Make first character Versal
Actually both return the same (just tried in 8.1.7 on NT): select substr (test_col, 0, 1) from my_table select substr (test_col, 1, 1) from my_table The problem may be with getting the VERSAL but don't know what it means Witold On 27 Mar 2002 at 4:38, Nicoll, Iain (Calanais) wrote: Roland, Substr starts with 1 eg select (substr (namn,1,1)) from test will give you the 1 character starting from the first character. Unfortunately I don't know what you mean by versal. Iain Nicoll -Original Message- Sent: Wednesday, March 27, 2002 11:59 AM To: Multiple recipients of list ORACLE-L Hallo, I know this question sound a bit simple, but can anyone give me a select statement which make the first character of the word in a field Would appreciate very much. I have checked the manual but cant get it right. I am starting with this sql statement: select (substr (namn,0,1)) from test to pick outthe first character and I want that first character to be a VERSAL. Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nicoll, Iain (Calanais) 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). == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
PO 9i and Windows Me
Hi everyone Has anyone installed Personal Oracle 9i on Windows Me? You could run PO 8.1.7 for Windows 98 on Windows Me. Friend of mine just installed PO 9i for Windows 98 on his Windows Me computer and run into problems. I wonder whether it doesn't run on Windows Me or it is his computer issue Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: outer join
You can have multiple outer joins in the where clause but one table can be outer-joined to one other table only. For example you can write: select tb1.col_1 from table1 tb1, table2 tb2, table3 tb3 where tb1.some_col = some value and tb1.col_a(+) = tb2.col_a and tb2.col_b(+) = tb3.col_b but you can not write: select tb1.col_1 from table1 tb1, table2 tb2, table3 tb3 where tb1.some_col = some value and tb2.col_a(+) = tb1.col_a and tb2.col_b(+) = tb3.col_b It depends how you need to outer-join the tables But last time I used outer joins it was in I believe Oracle 8.0.5 Maybe something has changed in 8i, or 9i Witold On 12 Mar 2002 at 5:23, [EMAIL PROTECTED] wrote: Hi, im trying to create a view comprising of about 10 tables, trying to join them together.i need a few outer jojns, but i have been told this is not possible, i.e. having numerous outer joins in the where clause.. anyone got any info on these rules for outer joins? cheers -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
SQL Loader and decimals
Hello There are few numeric formats when you load data from a file. I am looking for a way, if possible, to load a number from a field that doesn't have the decimal point. For example I may know that in my five-character field the first three characters always represent the most-significant digits and the last two are always the decimal values. Something like: field 12345 would be loaded as 123.45 If, in data file, I have the value 123.45 I can use decimal external but wonder if I can avoid the decimal point in datafile. How can I define the field in control file? Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: selecting on time
I guess you need to include the minutes as well: ... to_number(to_char(date_field, HH24:MISS')) 14 otherwise the time 14:25:00 will not be greater than 1400 Witold One way would be the following SELECT ... FROM table WHERE TO_NUMBER(TO_CHAR(date_field,'HHSS')) 1400; Rick I need to do a select based upon the time element in a date column. i.e, eg where the time is greater than 14:00 Can anyone provide an example of how to do this? John -- == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Rollback Segments
Hello DBAs Is there a command that tells me which rollback segment is being used by current transaction? Also, can you force a session to use particular rollback segment? I have used in the past the command: set transaction use rollback segment rbs_01 but the named rollback segment seemed to be used until the end of tranasction. After a commit or rollback a different rollback segment could have been used by Oracle. I wonder if there is a way to make sure that once a user logs in, a particular rollback segment is used. Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Unbreakable Oracle - Metaslink
Must be entire Oracle - technet.oracle.com or www.oracle.com seem to be down too. www.microsoft.com is alive ;-) (I don't want to start another OT thread) Witold On 30 Jan 2002 at 7:50, Murray, Margaret wrote: Can't get in either. Been trying for a couple of hours (it's not 10:50 am) with no luck. So much for checking on my tars... -Original Message- From: Thomas, Kevin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 9:11 AM To: Multiple recipients of list ORACLE-L Subject: RE: Unbreakable Oracle - Metaslink It was up and running earlier on (8am-1pm GMT) -Original Message- Sent: 30 January 2002 13:15 To: Multiple recipients of list ORACLE-L Seems to be down this morning must be the flu ... Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Murray, Margaret 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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 : howto RTFM
And you shouldn't... This is just a web trick - I mean it's not a Microsoft's site. You can see the same page, loaded even faster, if you remove: www.microsoft.comitem=q209354@ from the url. Just try: http://hardware.no/nyheter/feb01/Q209354%20-%20HOWTO.htm You can replace in the url www.microsoft.com with whatever company you want and get redirected to the same page, try www.oracle.com ... It will take you to the same page... http://hardware.no is website of a company in Norway and they have this page. Getting the graphics, style, etc and make a page look like Microsoft's is a piece of cake. While it may be funny I guess it's a poor joke to make pages and present as someone else's... Witold On 29 Jan 2002 at 5:30, Rajesh Dayal wrote: Cant' believe that !!! -Original Message- Sent: Tuesday, January 29, 2002 4:35 PM To: Multiple recipients of list ORACLE-L http://www.microsoft.com[EMAIL PROTECTED]/nyheter/feb01/Q209354%20-% 20HOWTO.htm have fun, Marin ...what you brought from your past, is of no use in your present. When you must choose a new path, do not bring old experiences with you. Those who strike out afresh, but who attempt to retain a little of the old life, end up torn apart by their own memories. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Marin Dimitrov 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajesh Dayal 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). == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Raw I/O
Thanks Martin I guess that's what we need... Witold On 16 Jan 2002 at 12:15, Martin Kendall wrote: Hey now, be nice :-). The best thing is to point people towards the archives. Martin http://oracle-rescue.com -Original Message- Mladen Sent: 16 January 2002 19:57 To: Multiple recipients of list ORACLE-L Real life opinion from me: Yes, Oxford is using it. Yes, it is faster. Yes it makes it practically impossible to backup without a 3rd party backup tool. Yes, it is boring to chew raw vs. cooked debate over and over again. Why doesn't someone come up with something new and not yet discussed topic like RAID implementations and oracle? -Original Message- Sent: Wednesday, January 16, 2002 2:37 PM To: Multiple recipients of list ORACLE-L Hi all I am looking for some real life opinions about Oracle raw I/O - are people using it, is there really a performance gain? Any other pros, cons? I have seen some info on Metalink but real life often is quite different... Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gogala, Mladen 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Martin Kendall 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Raw I/O
Hi all I am looking for some real life opinions about Oracle raw I/O - are people using it, is there really a performance gain? Any other pros, cons? I have seen some info on Metalink but real life often is quite different... Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Becoming a DBA questions
for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Dan Whatley 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Randy Kirkpatrick 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: using hints on views
I had to use hints inside views number of times and it worked well but you have to be careful. Hinting just the SQL statement that builds the view may be worse than no hinting at all. When you add hints you have to keep in mind how you will use the view. In result I ended up with few views, selecting the same columns from the same tables but hinted and ordered differently. It may look messy if you just look at the data dictionary but within application you know which button is clicked, what you query on etc... so you know which view you should use. HTH Witold On 3 Jan 2002 at 18:55, Maria Aurora VT de la Vega wrote: i find the ordered hint extremely helpful... so i usually find myself using this hint... what is your opinion on using hints inside views? thanks. -- Maria Aurora VT de la Vega (OCP) Database Specialist Philippine Stock Exchange, Inc. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Maria Aurora VT de la Vega 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). == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: using hints on views
I would say it's a different way ;-) It depends on the data, tables joined, and other things. It may look and work great in typical emp-dept example: few departments, few dozens employees. When you use GLOBAL hints you can overwrite hints used inside the view but I don't think you can change order of tables. Maybe you can? I don't know. If you had tens of thousands departments and millions employees, it would make a big difference when you use ORDERED hint. Also, if I use the view in different applications, I have to write hints in each applictaion. If the hints are inside a view, there is only one place. Witold On 4 Jan 2002 at 7:10, Paul Baumgartel wrote: There's a better way: use global hints in the queries that select from the views. From Designing and Tuning for Performance: Global Hints Table hints (i.e., hints that specify a table) normally refer to tables in the DELETE, SELECT, or UPDATE statement in which the hint occurs, not to tables inside any views or subqueries referenced by the statement. When you want to specify hints for tables that appear inside views or subqueries, you should use global hints instead of embedding the hint in the view or subquery. You can transform any table hint in this chapter into a global hint by using an extended syntax for the table name, as described below. Consider the following view definitions and SELECT statement: CREATE VIEW v1 AS SELECT * FROM emp WHERE empno 100; CREATE VIEW v2 AS SELECT v1.empno empno, dept.deptno deptno FROM v1, dept WHERE v1.deptno = dept.deptno; SELECT /*+ INDEX(v2.v1.emp emp_empno) FULL(v2.dept) */ * FROM v2 WHERE deptno = 20; The view V1 retrieves all employees whose employee number is less than 100. The view V2 performs a join between the view V1 and the department table. The SELECT statement retrieves rows from the view V2 restricting it to the department whose number is 20. There are two global hints in the SELECT statement. The first hint specifies an index scan for the employee table referenced in the view V1, which is referenced in the view V2. The second hint specifies a full table scan for the department table referenced in the view V2. Note the dotted syntax for the view tables. --- Witold Iwaniec [EMAIL PROTECTED] wrote: I had to use hints inside views number of times and it worked well but you have to be careful. Hinting just the SQL statement that builds the view may be worse than no hinting at all. When you add hints you have to keep in mind how you will use the view. In result I ended up with few views, selecting the same columns from the same tables but hinted and ordered differently. It may look messy if you just look at the data dictionary but within application you know which button is clicked, what you query on etc... so you know which view you should use. HTH Witold __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel 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). == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Little OT: CLOB, Oracle Text, and ADO
Hello It may be little OT but I am sure there are developers here. Also I hope some DBAs may know the answer... I am experimenting with Oracle Text - need to use the field from a VB application. I have a table with a CLOB column and a ctxcat index built on that column. If I insert records using SQL Plus, TOAD, or similar tool, the index is automatically updated so my query: select note_oid from lrmi_note where catsearch(note_data, 'SomeValue', null) 0; returns records matching the condition. In the application - if I use ADODB.Connection with parameters and execute my insert, the data is written, index automatically updated, and my query returns matching records. But if I use ADODB.Recordset to write the record, the data is written but the index doesn't get updated. If I open SQL Plus and run: select * from lrmi_note where note_oid = my_new_oid; it shows me the record with the CLOB column filled. But if I query the CLOB column using catsearch(), it doesn't return the record. If I rebuild the index, the query with catsearch() will return the records. But one of the reasons to use ctxcat indes is that it is updated with each transaction... While it may be specific to ADO I wonder if some Oracle gurus know, or can direct to some info, why Oracle doesn't update the ctxcat index even though the record is written and apears correctly. Witold== Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Little OT: CLOB, Oracle Text, and ADO
Thanks Jared I understood that ctxsrv affects the index of type ctxsys.context only while the ctxsys.ctxcat gets updated with transactions but certainly will do some more reading... Witold My experience with intermedia or context server, or whatever they call it this week, is in dealing with iFS, so I know just enough to be dangerous. :) It sounds like you need to have the context server running to periodically update the indexes. On unix it's 'ctxsrv'. I suggest you read the docs on this and do a search on metalink, there is some helpful information there. HTH Jared Witold Iwaniec wiwaniec@novali To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] stech.com cc: Sent by: Subject: Little OT: CLOB, Oracle Text, and ADO [EMAIL PROTECTED] 12/21/01 07:15 AM Please respond to ORACLE-L Hello It may be little OT but I am sure there are developers here. Also I hope some DBAs may know the answer... I am experimenting with Oracle Text - need to use the field from a VB application. I have a table with a CLOB column and a ctxcat index built on that column. If I insert records using SQL Plus, TOAD, or similar tool, the index is automatically updated so my query: select note_oid from lrmi_note where catsearch(note_data, 'SomeValue', null) 0; returns records matching the condition. In the application - if I use ADODB.Connection with parameters and execute my insert, the data is written, index automatically updated, and my query returns matching records. But if I use ADODB.Recordset to write the record, the data is written but the index doesn't get updated. If I open SQL Plus and run: select * from lrmi_note where note_oid = my_new_oid; it shows me the record with the CLOB column filled. But if I query the CLOB column using catsearch(), it doesn't return the record. If I rebuild the index, the query with catsearch() will return the records. But one of the reasons to use ctxcat indes is that it is updated with each transaction... While it may be specific to ADO I wonder if some Oracle gurus know, or can direct to some info, why Oracle doesn't update the ctxcat index even though the record is written and apears correctly. Witold== Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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
RE: Little OT: CLOB, Oracle Text, and ADO
Thanks for the reply I have tried ctx_ddl.sync_index() but it can be used to synchronize ctxsys.context indexes only. In my case it is ctxsys.ctxcat index. Since it gets updated properly when I use few different ways to insert records I believe it is something specific to the way ADODB.Recordset modifies database. The index gets updated properly when I run, in any way, an INSERT statement. I guess the ADODB.Recordset works on a different level. It may be something similar to differences in loading data with SQL Loader using conventional load and direct load - issuing INSERT statements vs. writing directly to the table. It is just a guess... Witold On 21 Dec 2001 at 10:00, MacGregor, Ian A. wrote: Ctxserv has been deprecated. Check the directory $ORACLE_HOME/ctx/sample/script for the approved ways of keeping the indexes in synch. Ian MacGregor Stanford Linear Accelerator Center [EMAIL PROTECTED] -Original Message- Sent: Friday, December 21, 2001 9:41 AM To: Multiple recipients of list ORACLE-L My experience with intermedia or context server, or whatever they call it this week, is in dealing with iFS, so I know just enough to be dangerous. :) It sounds like you need to have the context server running to periodically update the indexes. On unix it's 'ctxsrv'. I suggest you read the docs on this and do a search on metalink, there is some helpful information there. HTH Jared Witold Iwaniec wiwaniec@novali To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] stech.com cc: Sent by: Subject: Little OT: CLOB, Oracle Text, and ADO [EMAIL PROTECTED] 12/21/01 07:15 AM Please respond to ORACLE-L Hello It may be little OT but I am sure there are developers here. Also I hope some DBAs may know the answer... I am experimenting with Oracle Text - need to use the field from a VB application. I have a table with a CLOB column and a ctxcat index built on that column. If I insert records using SQL Plus, TOAD, or similar tool, the index is automatically updated so my query: select note_oid from lrmi_note where catsearch(note_data, 'SomeValue', null) 0; returns records matching the condition. In the application - if I use ADODB.Connection with parameters and execute my insert, the data is written, index automatically updated, and my query returns matching records. But if I use ADODB.Recordset to write the record, the data is written but the index doesn't get updated. If I open SQL Plus and run: select * from lrmi_note where note_oid = my_new_oid; it shows me the record with the CLOB column filled. But if I query the CLOB column using catsearch(), it doesn't return the record. If I rebuild the index, the query with catsearch() will return the records. But one of the reasons to use ctxcat indes is that it is updated with each transaction... While it may be specific to ADO I wonder if some Oracle gurus know, or can direct to some info, why Oracle doesn't update the ctxcat index even though the record is written and apears correctly. Witold== Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: Create a database without using DbAssist
') 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). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gavin D'mello 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). __ Do You Yahoo!? Check out Yahoo! Shopping and Yahoo! Auctions for all of your unique holiday gifts! Buy at http://shopping.yahoo.com or bid at http://auctions.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Oracle text and CLOBs
Hi all I am experimenting with Oracle Text and CLOBs in Oracle 8.1.7 and have run into something strange. I have a table, LRMI_NOTE, with column NOTE_DATA that is defined as a CLOB. SELECT NOTE_DATA FROM LRMI_DATA; NOTE_DATA == this is starting test I have built both ctxsys.context and ctxsys.ctxcat indexes. When I run: select * from lrmi_note where contains(note_data, 'test') 0 or select * from lrmi_note where contains(note_data, 'starting') 0 I get back the record. But when I run: select * from lrmi_note where contains(note_data, 'is') 0 or select * from lrmi_note where contains(note_data, 'this') 0 I don't get anything back. The same happens when I used the catsearch() function. I updated this column, set to various strings so that this and is were in different positions in the string, but couldn't get anything back using is or this in my queries. Can anyone explain this? Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Database link
db_domain in init.ora is commented out in both cases. I also set it to: db_domain = but it didn't make any difference. Setting GLOBAL_NAMES to false helped but someone mentioned that it is not a good practice. Can anybody explain little bit why? Thanks Witold On 10 Dec 2001 at 21:50, Nikunj Gupta wrote: Check for DB_DOMAIN.. in INIT.ORA else Set GLOBAL_NAMES to FALSE ALTER SYSTEM SET GLOBAL_NAMES=FALSE; HTH Make a FREE long distance call from your PC! http://www.eboom.com/free/ - Original Message - From: Witold Iwaniec To: Multiple recipients of list ORACLE-L Sent: Monday, December 10, 2001 11:00 AM Subject: Database link Hi DBAs I have a problem with database links in one of the databases - the name of the link gets us.oracle.com appended to it. create database link test connect to test_user identified by test_pwd using 'test_db' When I execute: select * from my_table@test I get the ORA-02085 error message the the link test.us.oracle.com connects to test. When I execute: select * from global_name; in the target database the result is: TEST but in the database where I created the link the database name has the suffix: NOVALIS.US.ORACLE.COM I have no problem with database links in the TEST database but would like to know which parameter causes the suffix to be appended in the NOVALIS database. In both init.ora files the global_names is set to true and db_domain is commented out. In both databases the command show parameters; returns the db_domain as blank. Actually both databases are on the same server - Oracle 8.1.6. What should I change to remove the US.ORACLE.COM suffix. I don't care too much about the database, it's my test only, but don't want it appended in the links and maybe other objects... Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Database link
Hi DBAs I have a problem with database links in one of the databases - the name of the link gets us.oracle.com appended to it. create database link test connect to test_user identified by test_pwd using 'test_db' When I execute: select * from my_table@test I get the ORA-02085 error message the the link test.us.oracle.com connects to test. When I execute: select * from global_name; in the target database the result is: TEST but in the database where I created the link the database name has the suffix: NOVALIS.US.ORACLE.COM I have no problem with database links in the TEST database but would like to know which parameter causes the suffix to be appended in the NOVALIS database. In both init.ora files the global_names is set to true and db_domain is commented out. In both databases the command show parameters; returns the db_domain as blank. Actually both databases are on the same server - Oracle 8.1.6. What should I change to remove the US.ORACLE.COM suffix. I don't care too much about the database, it's my test only, but don't want it appended in the links and maybe other objects... Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Adding intermedia Text to 8.1.6 database
Hello Can someone point me to documentation how to add intermedia text to a database in Oracle 8.1.6 (on NT)? I installed interMedia and can use in the database created by the setup but would like to add it to another database on the same server. The documentation that I have found at OTN refers to running few scripts that are in %ORACLE_HOME%\ctx\admin. That's all clear but I don't have the ctx\lib directory and obviously the libctxx8.so file required to build data dictionary. The interMedia works in the default database - I can build index of type ctxsys.context, do text search so all the libraries must be installed. But search for file libct*.so on all my hard drives returns nothing. Something else must be used... Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Adding intermedia Text to 8.1.6 database
Hi all I have found my answer... I have seen other people asking on other forums so the answer may help someone some day... On NT the library is: %ORACLE_HOME%\bin\oractxx8.dll Witold Hello Can someone point me to documentation how to add intermedia text to a database in Oracle 8.1.6 (on NT)? I installed interMedia and can use in the database created by the setup but would like to add it to another database on the same server. The documentation that I have found at OTN refers to running few scripts that are in %ORACLE_HOME%\ctx\admin. That's all clear but I don't have the ctx\lib directory and obviously the libctxx8.so file required to build data dictionary. The interMedia works in the default database - I can build index of type ctxsys.context, do text search so all the libraries must be installed. But search for file libct*.so on all my hard drives returns nothing. Something else must be used... Thanks Witold == Witold Iwaniec Sr Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Commit trigger
Hello My life would be easier ;-) if there was a commit (and rollback) trigger - Oracle 8i. I did some research and found various database- level triggers but couldn't find COMMIT and ROLLBACK event triggers. Can one actually write a code that would be fired only at COMMIT and/or ROLLBACK event? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Commit trigger
Thanks for the reply. I guess more details may be helpful. I need to get data from one table into a text file. The file needs to be generated only once after any changes to the table. It's easy enough to write a program that queries the table and generates a file but I thought about automating the process so that the user does not need to run any program. I thought about writing a PL/SQL using the UTL_FILE package to generate the file (it's a small table). But I would want the trigger to fire only when changes are commited, not on every insert, update, or delete. And I need only the commited data. I guess I was wondering if there was a way to write something like COMMIT_CHANGES_TO_A_TABLE trigger. I guess I will end up with small program but if there was a way to do it it would be a good learning. Thanks Witold On 25 Oct 2001, at 8:30, Amar Kumar Padhi wrote: I am sorry, I actually couldn't understand your query? Do you want a gobal trigger for commit and rollback that would fire irrespective of the change involved at table/view level? Afaik, there is no such trigger. Normally, your table/view level triggers would fire as per the event and apply the change only when commit is done. Same way the changes done via a trigger would be removed on rollback. Please let the list know what you would actually intend to do if such an option is provided. rgds amar -Original Message- Sent: Thursday, October 25, 2001 7:45 PM To: Multiple recipients of list ORACLE-L Hello My life would be easier ;-) if there was a commit (and rollback) trigger - Oracle 8i. I did some research and found various database- level triggers but couldn't find COMMIT and ROLLBACK event triggers. Can one actually write a code that would be fired only at COMMIT and/or ROLLBACK event? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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 -- the volume of job postings is way down
Another sign of the times is that ITI is closing its US schools. For those who don't know - it is a computer training school. They started years ago as a school in Atlantic Canada offering a 10 month course. Later they expanded to few Canadian and US cities. Last year they advertised on TV and radio about 400,000 unfilled positions in e-commerce etc. and offered-commerce training. Yesterday I have heard that they are scaling back... Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Version of personal oracle that runs on Window ME
I have installed Personal Oracle 8i for Windows 98, on Windows Me, and it runs. There was a change required in win.ini to get the installer to work properly but other than that everything was OK. Witold Cale, Rick T (Richard) [EMAIL PROTECTED] on 07/31/2001 12:30:52 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Hi All, Does anyone know where I can get/download copy of Oracle that runs on Window ME I downloaded 8i for 98 but BSOD. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Version of personal oracle that runs on Window ME
On this site you can find the steps - it solved my problem with installer http://www.orafaq.com/msgboard/windows/messages/209.htm Witold Cale, Rick T (Richard) [EMAIL PROTECTED] on 07/31/2001 13:58:07 To: Witold Iwaniec/ATL_BLUECROSS_CA@ATL_BLUECROSS_CA cc: Do you know what change you had to make in win.ini to work properly? The installer is not working properly on ME. Thanks Rick -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 31, 2001 1:12 PM To: Multiple recipients of list ORACLE-L I have installed Personal Oracle 8i for Windows 98, on Windows Me, and it runs. There was a change required in win.ini to get the installer to work properly but other than that everything was OK. Witold Cale, Rick T (Richard) [EMAIL PROTECTED] on 07/31/2001 12:30:52 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Hi All, Does anyone know where I can get/download copy of Oracle that runs on Window ME I downloaded 8i for 98 but BSOD. Thanks Rick -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Cale, Rick T (Richard) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
OT: Encryption software
Hi We are looking for a product that can be used to encrypt an Oracle report. In short a process will run a report, the file will be encrypted, and sent by email. And we would like to be able to use the product from command line so that the entire process can run automatically. I have already looked at products from PGP but the only product that can be used with the command line option is PGP E-business server for about $7000. The price is way too high for the volume that we will have. I started to look at other products and found a few Has anyone used an encryption product and can share experience, good and bad? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Privilege problem ?????
Andrea I assume it is a shell (or batch) script, run from OS - you have connect internal in your script so even if joe_doe runs the script, he connects as internal, not as joe_doe. This can be your problem. Try it yourself - connect to DB as internal (not yourself) and try to execute the commands that you have in the script. Or is it error related to OS permissions- if you are on UNIX, is the script's mode set as executable by everyone or at least a group the users belong to? I have done similar things many times and have my script run be users who were not even database users. But I have used internal for DB installation/configuration related tasks and different user for DDL or DML - the table/procedure owners or user with proper privileges. For example I have created setup programs for some products that required Oracle database with set of tables, viewes, procedures, etc. Instead of sending the scripts to build tables, views, synonyms, etc, which users often failed to do, I included the database (datafiles, controlfiles, etc) in my install program. During the setup I connected as internal and set up the database eg. modified paths to datafiles, redologs etc and then connected as different user to modify records in tables. While internal is used to install, startup/shutdown, and other similar tasks I am not sure if it has the same privileges as SYSTEM when it comes to tables, views etc. DBA's can tell it better. HTH Witold Andrea Oracle [EMAIL PROTECTED] on 07/10/2001 20:00:29 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Hi all, I wrote a script for developers to run. I have svrmgrl, connect internal in the script. The developer got error since he doesn't have privileges. So how to make the script runnable by non dbas??? Thanks. Andrea __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Andrea Oracle 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: need to recreate database
Lyuda That's not terribly difficult and you mentioned registry so I guess it is NT. Make sure the files were copied after the original database was shut down. The easiest way would be to reinstall Oracle and create the default database with the same SID as your original database (maybe you already have it done). It will create the required services, generate the directory tree (if it is 8i) etc. Then shut down your new database. copy your datafiles, control files, log files, init file to right locations (just in case keep copies...) and restart your database. If the directory structure is the same as on the old machine, you should be all set. If the directory structure is different, you have to: 1. start server manager 2. connect as internal 3. execute: startup mount 4. let the database know where datafiles and log files are. I don't remember the command but I believe it is: alter database rename 'the_old_path\data01.dbf' to 'the_new_path\data01.dbf'; and you have to do it for each datafile and log file. 5. execute: alter database open or shutdown and startup I have done similar things number of times on NT and never had problems... HTH Witold I need to recreate a database, basically make a complete copy of one of the existing databases on a new server. My networking people already copied all datafiles, control file, log files, init file, etc to the target server. They also reproduced operating system directory structure. What I need to do is to make the second part of the task work, create the database, services, etc and make it run. I have not done it before and if I was doing it I would probably do export/import type of thing but the higher ups would like to have it done this way. One of the people has an idea suggesting just recreating the registry setting by exporting it from the existing machine to the new one. The other option is to reinstall Oracle but I am not sure how to make it to accept existing physical components(datafiles, control file, etc). If someone has a suggestion I would greatly appreciate it. Thank you in advance. Lyuda Hoska -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Date / Time
Hi
The script below may help you - it will display different components in separate
columns, modify it with your two dates and proper table name and remove what you
don't need
select sysdate,
sysdate - to_date('0307200100', 'DDMMHH24MISS') diffr,
trunc(sysdate - to_date('0307200100', 'DDMMHH24MISS')) days,
trunc(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'), 1) *
24) hrs,
trunc(mod(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'),
1) * 24, 1) * 60) min,
round(mod(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60, 1) * 60) sec,
to_char(trunc(sysdate - to_date('0307200100', 'DDMMHH24MISS')))
|| ' ' ||
lpad(trunc(mod(sysdate - to_date('0307200100', 'DDMMHH24MISS'),
1) * 24), 2, '0') || ':' ||
lpad(trunc(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60), 2, '0') || ':' ||
lpad(round(mod(mod(mod(sysdate - to_date('0307200100',
'DDMMHH24MISS'), 1) * 24, 1) * 60, 1) * 60), 2, '0') Difference
from dual
HTH
Witold
Sajid Iqbal wrote:
Hello All
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?
Please CC any replies directly to me at [EMAIL PROTECTED]
Thanks in advance,
Saj.
--
Sajid Iqbal
Database Team Leader
--
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
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).
Select only one of three tables
Hello list I have a scenario in which I have to check three tables. If there is record in table A, take it otherwise check table B, if there is record in table B, take it otherwise check table C. Let say I am looking for DEPT column and the tables are DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPT column. While I can check each of the tables in order I would like to do it in one statement. I have tried DECODE but it did not like combination of count and column names - error ORA-00937. To make it simpler here is my query from two tables only: select decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept from dept_two d2, dept_three d3 where d3.emp_id = TESTER_1' and d2.emp_id(+) = d3.emp_id Can someone recommend a solution? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Select only one of three tables
Tom Thanks for the reply. The UNION would be good if I wanted all dept values from the tables. But the rule is more complex - if there is eg. one record in table DEPT_ONE, I have to get back only this one record even though there may other/more records for the same employee in the other tables. If there is no record in table DEPT_ONE and there is rcord in DEPT_TWO - I want back only what is in DEPT_TWO, regardless of what is in DEPT_THREE. Witold Mercadante, Thomas F [EMAIL PROTECTED] on 07/03/2001 13:01:51 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Witold, have you tried using the UNION operator? like: select dept from dept_one union select dept from dept_two union select dept from dept_three order by 1; this will give you only one occurrence of the value of dept from all three tables. hope this helps Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 03, 2001 10:11 AM To: Multiple recipients of list ORACLE-L Hello list I have a scenario in which I have to check three tables. If there is record in table A, take it otherwise check table B, if there is record in table B, take it otherwise check table C. Let say I am looking for DEPT column and the tables are DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPT column. While I can check each of the tables in order I would like to do it in one statement. I have tried DECODE but it did not like combination of count and column names - error ORA-00937. To make it simpler here is my query from two tables only: select decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept from dept_two d2, dept_three d3 where d3.emp_id = TESTER_1' and d2.emp_id(+) = d3.emp_id Can someone recommend a solution? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Select only one of three tables
Hi Remco Thanks for the reply. There can be more than one record that I should get. I guess I could have been clearer - the rule is: if there is one or more records in dept_one, return all matching records from dept_one and don't look at other tables. If there is no record in dept_one, check dept two and if there is one or more records in dept_two, return the matching records and don't check dept three. If there are no records in dept_two (so also no records in dept_one) check dept_three Originally I thought about decode but it will not work. Ater reading the responses I think about using union with something like: select dept from dept_one where emp_id = TESTER_1' UNION select dept from dept_two where emp_id = TESTER_1' and not exists (select 1 from dept_one where emp_id = 'TESTER_1') UNION select dept from dept_three where emp_id = TESTER_1' and not exists (select 1 from dept_one where emp_id = 'TESTER_1') and not exists (select 1 from dept_two where emp_id = 'TESTER_1') It should work but I have to look at performance. All I want is save some network traffic - calls from application to the server Thanks Witold Daemen, Remco [EMAIL PROTECTED] on 07/03/2001 14:17:41 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Hi Witold, Try this: select * from ( select dept from dept_one union all select dept from dept_two union all select dept from dept_three ) where rownum =1 ; HTH, Remco -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Verzonden: dinsdag 3 juli 2001 18:37 Aan: Multiple recipients of list ORACLE-L Onderwerp: RE: Select only one of three tables Tom Thanks for the reply. The UNION would be good if I wanted all dept values from the tables. But the rule is more complex - if there is eg. one record in table DEPT_ONE, I have to get back only this one record even though there may other/more records for the same employee in the other tables. If there is no record in table DEPT_ONE and there is rcord in DEPT_TWO - I want back only what is in DEPT_TWO, regardless of what is in DEPT_THREE. Witold Mercadante, Thomas F [EMAIL PROTECTED] on 07/03/2001 13:01:51 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Witold, have you tried using the UNION operator? like: select dept from dept_one union select dept from dept_two union select dept from dept_three order by 1; this will give you only one occurrence of the value of dept from all three tables. hope this helps Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 03, 2001 10:11 AM To: Multiple recipients of list ORACLE-L Hello list I have a scenario in which I have to check three tables. If there is record in table A, take it otherwise check table B, if there is record in table B, take it otherwise check table C. Let say I am looking for DEPT column and the tables are DEPT_ONE, DEPT_TWO, and DEPT_THREE. At the end I need only one DEPT column. While I can check each of the tables in order I would like to do it in one statement. I have tried DECODE but it did not like combination of count and column names - error ORA-00937. To make it simpler here is my query from two tables only: select decode (count(d2.emp_id), 0, d3.dept, d2.dept) dept from dept_two d2, dept_three d3 where d3.emp_id = TESTER_1' and d2.emp_id(+) = d3.emp_id Can someone recommend a solution? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F 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). -- Please see the official ORACLE-L FAQ
Java, Vector, and PLSQL
Hi In a web application taht I write in java I need to pass a number of parameters to a PL/SQL stored procedure. Since the number of parameters can be different a Vector, ArrayList, or HashTable on the java side would be a good choice. Can you pass a Vector to a PL/SQL stored procedure? If I have to, I could write the stored procedures in java but PL/SQL is almost done. A new requirement has come up and it requires a variable number of parameters. Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: TRUNCATE IN PL/SQL
Not 100% sure but I think only the table owner can use TRUNCATE Witold Raymond Lee Meng Hong [EMAIL PROTECTED] on 06/21/2001 01:10:52 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Got these error ? may be my DBA restrict these ? ORA-00903: invalid table name ORA-06512: at SYS.DBMS_SYS_SQL, line 239 ORA-06512: at SYS.DBMS_SQL, line 32 ORA-06512: at line 8 -Original Message- Sent: Thursday, June 21, 2001 11:20 AM To: Multiple recipients of list ORACLE-L You can try the following , not sure if it will work though..let me know if it does... :-) DECLARE myCur number; mySQL varchar2(2000); BEGIN mySQL := 'TRUNCATE TABLE (table_name)'; myCur := DBMS_SQL.open_cursor; DBMS_SQL.PARSE(myCur, mySQL, DBMS_SQL.NATIVE); DBMS_SQL.CLOSE_CURSOR(myCur); END; Regards, Karthik -Original Message- Sent: Thursday, June 21, 2001 10:55 AM To: Multiple recipients of list ORACLE-L Hello guru , how can I execute a truncate table in PL/SQL ?? It only work for delete DML only ? why ? Raymond Lee Infopro Sdn Bhd Block B3 Level 8, Leisure Commerce Square No. 9, Jalan PJS 8/9 46150 Petaling Jaya Selangor , Malaysia Tel : 603-7876 ext : 266 Fax : 603-78761233 Email : [EMAIL PROTECTED] Friendship with oneself is all important, because without it one cannot be friend with anyone else in the world - Eleanor Roosevelt -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohan, Karthik (GEP) 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Raymond Lee Meng Hong 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: SyncSort - Unix - Datawarehousing.
Hi I used Syncsort on UNIX few years ago working on a large data conversion project - converting millions of records from mainframe to Oracle database. Syncsort was a great tool and it has number of extra options. We used it to sort files, eliminate duplicate records, merge files, extract certain columns only, etc. And many of the files were over 500 MB, maybe even close to 1GB in size. In my case I mixed Syncsort with C++ programs. Should I ever work on a similar project, syncsort would definitely be part of it - well, I would like it to be part of it... HTH Witold Kevin Lange [EMAIL PROTECTED] on 06/15/2001 09:51:52 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) -Original Message- Hi, I want some informarmation about SyncSort for Unix. If anyone is using sincsort under unix - I need the following details: 1. What is the basic purpose of syncsort? ( I have gone thru www.syncsort.com - So, I dont want this answer). 2. How is sync sort used in data loads or data warehousing? Is there any documentation online? Any whitepapers? TIA, Rajaram -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: SyncSort - Unix - Datawarehousing.
Rajaram Look at inline comments Rajaram [EMAIL PROTECTED] on 06/15/2001 12:16:08 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Hi, Thanks Witold for the info. I have some more Q's. 1. For the Conversion part - I believe you must have used some other tools like - FilePort ( from the SyncSort company). Is it right? No and yes... No - I did not use any other third party tools Yes - as I said I wrote some C++ programs according to our needs. The conversion process was a mixture of our programs and syncsort. It was a project in which we merged assessment, registry of deeds, and land information centre systems and it included very serious remodelling. We used C++ programs to generate unique IDs, find matching data in different files, create records in different formats, extract/generate data that we needed, etc. And we used Syncsort to sort files, eliminate duplicate reocrds, merge files in the same format. Syncsort ability to work with tab-delimited files saved us a lot of space (and processing time). If you have a few records it's not a big deal but if you have hundreds of millions of records, a few spaces in each record make big difference... 2. Also, the conversion is from Mainframe flat file to Unix Flat file - but not directly in Oracle DB . Right? That's correct. We received the data on tapes in EBCDIC format (some also as dfb files or flat files - dump from Oracle databases) and ended up with tab-delimited files that were loaded with SQL*Loader. 3. Last Q, Would I be right in saying that SyncSort replaces/enhances the capability provided by awk in Unix? My exposure to awk was very limited - few excersises at school so can not answer this question. HTH Witold TIA, Rajaram -Original Message- Sent: Friday, June 15, 2001 9:51 AM To: Multiple recipients of list ORACLE-L Hi I used Syncsort on UNIX few years ago working on a large data conversion project - converting millions of records from mainframe to Oracle database. Syncsort was a great tool and it has number of extra options. We used it to sort files, eliminate duplicate records, merge files, extract certain columns only, etc. And many of the files were over 500 MB, maybe even close to 1GB in size. In my case I mixed Syncsort with C++ programs. Should I ever work on a similar project, syncsort would definitely be part of it - well, I would like it to be part of it... HTH Witold Kevin Lange [EMAIL PROTECTED] on 06/15/2001 09:51:52 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) -Original Message- Hi, I want some informarmation about SyncSort for Unix. If anyone is using sincsort under unix - I need the following details: 1. What is the basic purpose of syncsort? ( I have gone thru www.syncsort.com - So, I dont want this answer). 2. How is sync sort used in data loads or data warehousing? Is there any documentation online? Any whitepapers? TIA, Rajaram -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). NetZero Platinum No Banner Ads and Unlimited Access Sign Up Today - Only $9.95 per month! http://www.netzero.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rajaram 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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
Full user export
Hi DBA's What is the proper syntax of the exp command to get export of all objects owned by a user - tables, views, stored procedures, synonyms, grants. And will the import require special parameters too? I have a small development database on Windows NT and would like to create a copy on Windows Me running Personal Oracle for Win 98. When I duplicated the database to anotehr Windows NT machine it was simple - copy all datafiles, mount the database, update control files, etc. But here I have different operating systems so can not simply copy files. Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: What columns are in the Primary Key
Helmut ALL_CONS_COLUMNS contains columns and position for each constraint. Similarly ALL_IND_COLUMNS contains columns for each index. If you need to find the columns through SQL, use the views or join to them. If you don't need the SQL, get a tool like TOAD (free version, http://www.toadsoft.com) and you can find a lot through database - Schema Browser HTH Witold Helmut Daiminger [EMAIL PROTECTED] on 05/10/2001 14:22:34 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Hi! Is there a data dictionary view that gives me all the columns in a primary key of a table? I can query user_constraints to find out about all constraints on a table: select * from user_constraints where table_name='TBACCOUNTS' OWNER CONSTRAINT_NAME C TABLE_NAME SEARCH_CONDITION -- --- - - VIVOUSER SYS_C003011 C TBACCOUNTS COMPANYID IS NOT NULL VIVOUSER SYS_C003012 C TBACCOUNTS STORELOCID IS NOT NULL VIVOUSER SYS_C003013 C TBACCOUNTS ACCOUNTID IS NOT NULL VIVOUSER PK_ACCOUNTS P TBACCOUNTS How can I get what columns are in the primary key PK_ACCOUNTS ? I have more NOT NULL columns in the table than in the PK. Any idea? This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Helmut Daiminger 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: date+time in milliseconds
If you get milliseconds in the data and want to keep it, you have to store it in a field different than date, If you don't need to keep the milliseconds, you can use different functions to convert the data. If you need to get the time in milliseconds, you can write a Java stored procedure. You should in fact find the code in the list archives. In either case more details would help someone to help you Witold -Original Message- can anyone help me with date + time in milliseconds. i want to inpurt the data from a user in this format. rgds raj -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: Performance of views
Helmut When you tune views you have to keep in mind how you select from the view - you may tune perfectly the select statement that builds the view but when you select from the view the query may be very slow. When you run select * from myview the performance may be much different than select * from myview where mycolumn = somevalue Whenever I had to tune a view I used the view select statement but added the condition that was going to be used later and tuned all together. If the view was going to be used with two different conditions, we ended up with two different views hinted in different ways. IN the final result the view select statement may be slow but the select statement from the view has to be fast. If you need more details, let me know HTH Witold === Witold Iwaniec IT Consultant [EMAIL PROTECTED] Hi! Some of our developers are having concerns about using views in the application. So they approached me and wanted to clarify some of their issues. When I issues a select * from viewname, Oracle executes the underlying select statement of the view. This underlying statement should be optimized (using availabale indexes on tables etc.) If I issue a select * from viewname where condition 3 or the like, will the indexes still be used. Or how is this statement executed? Does Oracle first run the underlying select statement and then apply the where condition 3 to the returned result set? Or is the statement being rewritten internally? The Oracle documentation is not very clear on this. Any ideas would be appreciated. This is 8.1.6 on Win2k. Thanks, Helmut -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: call stored procedure from a stored procedure
Let say you have my_procedure1 (param1 IN VARCHAR, param2 OUT VARCHAR); In procedure2 code you could write: PROCEDURE my_procedure2 AS par_1 VARCHAR2(10) := My Test; par_2 VARCHAR2(5); BEGIN my_procedure1(par_1, par_2); DBMS_OUTPUT.PUT_LINE(par_2); -- etc END; HTH Witold Woody Mckay [EMAIL PROTECTED] on 05/03/2001 12:25:49 Please respond to [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Witold Iwaniec/ATL_BLUECROSS_CA) Please help, Sorry if this sounds like a dumb question but I'm having a hard time finding examples of how to call a stored procedure with IN and OUT parameters from another stored procedure. Any resources or examples. TIA, Woody -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Woody Mckay 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
Oracle Reports
Is there a version of Reports that runs on Windows Me? You can download only reports for NT, Sun, and Linux. Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Oracle Reports - SQL
Hi I started to work with Oracle Reports (2.5.5). My first task to maintenance of existing reports. On one of them I need to change the SQL - whenever I change it, the data model changes - some columns get moved between groups. Is it the reports' behavior or do I miss a setting? Thanks Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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: sql*loader default value
Sorry I misunderstood your request. Try something like this: THE_VAL POSITION(111:120) DECIMAL EXTERNAL "NVL(:THE_VAL, 33)" It works for me - inserts 33 into numeric column when the datafile contains spaces or nothing (if it is the last field) HTH Witold On 3 Apr 2001, at 7:20, [EMAIL PROTECTED] wrote: Thanx to Witold and Prakash for suggesting NULLIF and DEFAULTIF. Unfortunately, it looks like both of those do basically the opposite of what I'm trying to accomplish. Essentially, I want to prevent sql*loader from ever inserting NULL in a column (which may not be numeric), substituting whatever value is appropriate for the given application instead. Something like using "nvl(:field,'unknown')" in the field specification of the control file for conventional path loads. As far as I can see, there's no way to do this within sql*loader itself. So the choices would be to run a cleanup just after the load (which may or may not negate the speed benefits of using direct path), or to somehow pre-process the data file to fill in the missing values (perhaps an awk or perl script). Of course, if I'm missing something obvious please let me know... the sql*loader documentation seems designed to confuse. (-: Cheers! ========== Witold Iwaniec Senior Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: olap?
Go to Oracle web site and search for Olap... Witold On 26 Mar 2001, at 23:05, [EMAIL PROTECTED] wrote: Hallo, Can anyone tell me: What is OLAP and how does it work, a short description, thanks. Roland Skldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
OLAP again
Hi Our product uses quite complex data model and we are looking for ways to speed up data access, especially large reports in the range of 25,000+ records. One of the ideas was to look into OLAP, or now Business Intelligence, tools. After some reading it seems to me that OLAP is completely separate product - it is the Express Server that runs its own database, and tools like Express Objects, Express Analyzer that use the Oracle 9iAS and Express Server technology. You can still connect to your Oracle database or import/export the data for analysis, reporting, etc. but you have to set up completely new environment. We hoped that we could use OLAP tools to point to Oracle 8i database and use it to provide faster access to our complex data model but it does not seems so straightforward. Or maybe I missed a tool. Could someone, please, correct me? Thanks Witold == Witold Iwaniec Senior Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
OLAP info
Hi all I would like to learn about OLAP in Oracle. I have looked at OTN and found Express Objects and other Express ... tools. Is it a place to start or someone can recommend something better Thanks Witold == Witold Iwaniec Senior Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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:
"Oracle 8i Utilities" Witold On 22 Mar 2001, at 12:10, Chesebro, Eric wrote: Any one know where to find good documentation on sqlloader? Thanks, Eric -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Chesebro, Eric 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). ====== Witold Iwaniec Senior Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Columns in triggers
Hi all I would like to create an audit trial for particular tables within Oracle database and thought about using triggers. I would like to log changes to each column separately. Is it possible to find dynamically name of the columns that were updated? I guess on INSERT I can check value of each column and if it is not NULL write the log, and on UPDATE I can compare the NEW and OLD values and write the log if different but wonder if there is better/nicer solution. Thanks Witold == Witold Iwaniec Senior Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
Invoking svrmgrl
Hi In one of my applications, that connects to Oracle database, I generate a package and have to compile it. The platform of the application - Windows while database can be on a remote machine. I know that from my application I can call sqlplus to compile the package. For example: sqlplus user/password@my_db @c:\temp\my_script.sql but I think in some minimal installations you may not have sqlplus installed. I thought about calling svrmgrl but I don't think it accepts command line parameters. I tried to write small batch file: svrmgrl connect user/password@my_db @c:\temp\my_script.sql But it is not going to work - the problem is that the commands are executed as three separate commands while I need to write the batch file so that the connect... and @c:\tmp\... commands are executed by server manager. Can anyone show me the correct way to write the batch file? Or maybe there is another way - in my application I am already connected to the database using ODBC API. Thanks Witold == Witold Iwaniec Senior Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Invoking svrmgrl
Thanks for the reply. It does the job. I didn't try to invoke svrmgrl with script on the command line. I tried: svrmgrl connect system/manager@my_db and the command failed with Server Manager Command Line error... Thanks Witold On 1 Mar 2001, at 19:45, wrote: s1.bat svrmgrl @s2.sql eof s2.sql = connect system/manager @my_sql.sql eof my_sql.sql select * from scott.emp / exit / eof -Original Message- From: Witold Iwaniec [mailto:[EMAIL PROTECTED]] Sent: Thursday, March 01, 2001 7:16 PM To: Multiple recipients of list ORACLE-L Subject:Invoking svrmgrl Hi In one of my applications, that connects to Oracle database, I generate a package and have to compile it. The platform of the application - Windows while database can be on a remote machine. I know that from my application I can call sqlplus to compile the package. For example: sqlplus user/password@my_db @c:\temp\my_script.sql but I think in some minimal installations you may not have sqlplus installed. I thought about calling svrmgrl but I don't think it accepts command line parameters. I tried to write small batch file: svrmgrl connect user/password@my_db @c:\temp\my_script.sql But it is not going to work - the problem is that the commands are executed as three separate commands while I need to write the batch file so that the connect... and @c:\tmp\... commands are executed by server manager. Can anyone show me the correct way to write the batch file? Or maybe there is another way - in my application I am already connected to the database using ODBC API. Thanks Witold == Witold Iwaniec Senior Software Developer NovaLIS Technologies [EMAIL PROTECTED] http://www.novalistech.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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). . AVP for MS Exchange. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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: Anyone using CA products?
A simple little thing - I asked for the Oracle 8i Data Dictionary poster. The QA Specialist said that she would be happy to send it to me and of course it hasnt arrived yet - about a year... I can really live without the poster but it tells me something about the company. If they said they didn't supply it or run out of it or whatever else excuse that they can not send it, I would have had more respect for them. What's the point of making yourself looking very bad with such a little thing. It looks like the support is not much different... Witold -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Witold Iwaniec 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).
