RE: Documents in or outside the database.
And don't forget file permissions issues either--especially if there will ever be a firewall between your db and your users. If the docs are in the db, users won't need OS perms to read/write/delete anything on the filesystem... Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, September 10, 2003 10:55 AM To: Multiple recipients of list ORACLE-L You also need to consider replication issues. If you will have another site that needs to store the documents, you will need to include that in the decision. For example, if you store them inside, Oracle can handle the propogation of the documents to the alternate site. If you store them outside, a separate mechanism is required. Rachel Carmichael wrote: Compromise? BFILE? Unless we are actually trying to search the documents themselves, I store documents outside the database and store the pathname to the document location within the database itself. --- M.Godlewski [EMAIL PROTECTED] wrote: Hi listers, We are working on a project that will have documents as attachments. The developers want to store the documents outside the database on the application server. I want to store the documents inside the database for recovery purposes. Is there a white paper or document that has performance information for blob/clob storage and retrieval database performance information? TIA M. - Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Pardee, Roy E 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: asp page errors, odbc drivers for Oracle
Have you tried the kb? http://support.microsoft.com/default.aspx?scid=fh;EN-US;kbhowtosd=MSDNln=EN-USFR=0 this one caught my eye: How to Configure IIS to Connect to Oracle from ASP and ADO http://support.microsoft.com/default.aspx?scid=kb;en-us;193225 HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, September 11, 2003 6:39 AM To: Multiple recipients of list ORACLE-L I have been struggling with an asp-based IIS web application one server, it is tarting to look like a global asp variable is not being passed down to a local asp variable. Has anyone ever seen this type of thing before? I have had a number of problems with this system, I am starting to wonder how compatible IIS asp and ODBC are with Oracle 8i. The Microsoft tech pages on ole_DB for Oracle, ODBC driver for Oracle, are not as up to date as I would like, looks like the last time they tested anything against Oracle was at versions 7.3, 8.0, and 8.1.7.0.0.. Oracle already mentioned to me that I should be using Oracle ODBC drivers, but the 3rd party vendor is telling me to use the MS Driver for Oracle because that is the best driver for IIS. Do you know of any web pages or links that would provide more info on how to configure / troubleshoot IIS and asp when connecting to Oracle? Patrice. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Boivin, Patrice J 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: Pardee, Roy E 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: OT: Hey Jared!! -90 degree OT Joke
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: Tanel Poder 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! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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: Freeman Robert - IL 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 mail! ing 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: Tanel Poder 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: Sinardy Xing 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: Orr, Steve 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! SiteBuilder - Free, easy-to-use web site design software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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
anybody got an improved 'DESC' procedure?
Does anybody have a sproc or script they'd care to share that improves on the DESCRIBE command? Specifically, I would like to see table and column level comments. Check constraint search conditions would be a bonus... Thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: More on Uday Qusay
From _the Onion_'s what-do-you-think regular feature, when the news about Uday Qusay was more recent: It's great that we got Uday and Qusay. But what about the eapons-way of ass-may estruction-day? I'm still laughing at that one... www.theonion.com Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 26, 2003 6:59 AM To: Multiple recipients of list ORACLE-L Now that Uday and Qusay have been eliminated, a lot of the lesser-known family members are coming to the attention of American authorities. Among the brothers: Sooflay ..the restaurateur Guday... the half-Australian brother -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: possible to dynamicly connect to a different user in pl/sql?
My guess is that 'connect' is a sql*plus command (rather than a pl/sql command) so wouldn't work with execute immediate. But maybe you could break out the portions of your code that need to run as a different user into a separate package, owned by that user? HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, August 20, 2003 8:22 AM To: Multiple recipients of list ORACLE-L I doubt it... but this could save me from coding it into a unix script and keeping all my code in my package. I tried execute immediate 'connect user/password; and it failed. is this doable? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: OT -- Boston Globe job listings
- 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: Wolfe Stephen S GS-11 6 MDSS/SGSI 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: KENNETH JANUSZ 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: Pardee, Roy E 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).
you can take the user off windows, but... (was RE: Off Topic Question - Update.inf file)
This is an excellent point. Anybody remember when there was (is?) a stigma associated with being an AOL user, as opposed to using a real ISP? AOL made things easy enough that the less adept could 'get on the internet', where they were generally reviled by the old-hands, who were more of a select, 'nerd' elite. My guess is it'll be the same thing as the less technical people start migrating to linux. Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, August 13, 2003 10:35 AM To: Multiple recipients of list ORACLE-L Joe Testa scribbled on the wall in glitter crayon: Good point, i'm such a bigot as i used linux back when it was minix and have been doing unix admin work for 20 years now ;) joe well, unlike you there are now starting to be a number of linux users that are just windows users in disguise.;-) case in point, duheveloper comes to me with his laptop and tells me his windows oracle doesn't work anymore. someone had installed linux on the laptop with dual boot defaulted to linux running gnome. and he couldn't tell the difference. boot into windows and every thing works fine. -- Bill Shrek Thater ORACLE DBA BAARF Party member #25 [EMAIL PROTECTED] The whole of science is nothing more than a refinement of everyday thinking. - Albert Einstein -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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: Pardee, Roy E 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: Question on views
I think that's a fair characterization. What makes them cool (and dangerous) is that you can set Oracle up to automatically treat queries like SELECT * from MyTable as if they said SELECT * FROM MyMaterializedView instead. That's the query rewrite feature. If the MV is based on a query that takes a long time to run (and that's generally where you're using them) then you can get blindingly fast perf where you'd otherwise get bogged down. But at the cost of storage space and (depending on how you have them set up) some currency. And, there are a couple of different types of MVs--some of them are 'fast refreshable' (meaning a refresh doesn't require completely ditching the existing data recomputing all of it from scratch) and some aren't. For a fast refreshable MV, you can even specify that it should be updated transactionally along with its dependant tables. All this more are in the OLAP docs: http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90 237/mv.htm#33614 Cheers, -Roy P.S. I think mssql's analogue to this is the 'indexed view', but am not sure. Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Thank you very much. Come to find our the MV the ICDB folks were using did not have 'stale' data in it, but it flat out had the wrong data in it because they used the wrong selection parameters. So essentially a MV is a SQL select stored to a table, kinda of like: SELECT * FROM MyTable INTO MyOtherTable v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Pardee, Roy E [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 6:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: Question on views From the concepts manual: quote Materialized Views A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment. /quote Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 8:25 AM To: Multiple recipients of list ORACLE-L It is confirmed it is a materialized view. How can they go stale? What is the difference in them and a traditional view? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Question on views Stephen, Traditional views don't go stale though they can be made invalid if the underlying objects change. This shouldn't really be happening on a regular basis unless the schema is changing rapidly. He may be using a materialized view. These can go stale. Before going any further ask him which of these is the cause and get the name of the view. I'd have thought that if a materialized view was created by his application then it's his responsibility. Cheers, Mike Hately, Consultant DBA -Original Message- [mailto:[EMAIL PROTECTED] Sent: 12 August 2003 13:40 To: Multiple recipients of list ORACLE-L I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that met diabetic conditions stored in the database over the past year, how can a view go 'stale
RE: Implementing different document types with different attributes
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day 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: Vernaillen Tim 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: Thomas Day 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: Vernaillen Tim 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: Pardee, Roy E 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: last column in a table is added with a DEFAULT
It is impossible to add a NOT NULL column that *doesn't* have a DEFAULT to a table (unless the table is empty). The error you get is ORA-01758: table must be empty to add mandatory (NOT NULL) column. But that's a logical problem, not a bug. Is there any chance the dev is confusing this problem with something else? Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, August 07, 2003 12:30 PM To: Multiple recipients of list ORACLE-L See the test below: [EMAIL PROTECTED] create table t1 (c1 number); Table created. [EMAIL PROTECTED] insert into t1 (c1) values (1); 1 row created. [EMAIL PROTECTED] insert into t1 (c1) values (2); 1 row created. [EMAIL PROTECTED] commit; Commit complete. [EMAIL PROTECTED] select * from t1; C1 -- 1 2 [EMAIL PROTECTED] alter table t1 add (c2 number default 999); Table altered. [EMAIL PROTECTED] select * from t1; C1 C2 -- -- 1999 2999 -Original Message- Sent: Thursday, August 07, 2003 3:09 PM To: Multiple recipients of list ORACLE-L I understand that part. What the programmer is saying that you can not add the last column to a table with a default value. Does that sound reasonable? thanks, Raj. David. -Original Message- Sent: Thursday, August 07, 2003 1:25 PM To: Multiple recipients of list ORACLE-L as soon as you add a column all depending code goes invalid, the dependency checking process doesn't discriminate about the default value. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, August 07, 2003 1:09 PM To: Multiple recipients of list ORACLE-L Has anybody ever heard of this? I have a developer saying this is an oracle bug. It caused some packages to go invalid. The error seems to be related to a bug in oracle caused when the last column in a table is added with a default. David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David 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: Pardee, Roy E 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: Question on views
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: Wolfe Stephen S GS-11 6 MDSS/SGSI 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: Pardee, Roy E 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: ** is there PL/SQL for case
9i pl/sql has a case statement--see the docs. Here is sample code
copied from said docs:
quote
DECLARE
n number;
BEGIN
CASE n
WHEN 1 THEN dbms_output.put_line('n = 1');
WHEN 2 THEN
dbms_output.put_line('n = 2');
dbms_output.put_line('That implies n 1');
ELSE dbms_output.put_line('n is some other value.');
END CASE;
END;
/quote
HTH,
-Roy
Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Thursday, August 07, 2003 1:34 PM
To: Multiple recipients of list ORACLE-L
I am not sure in 9i. But in 8i I think you can use case in sql but not
in pl/sql. You have to use if elsif in pl/sql.
Guang
-Original Message-
Joshi
Sent: Thursday, August 07, 2003 4:24 PM
To: Multiple recipients of list ORACLE-L
Hi,
Is there a statement in pl/SQL like case or is if elsif the only way.
Meaning if I need to transalate state depending on input number as
follows :
1 - CA
2 - OR
3 - WA
4 - AR
Can I have one statement like case 'state# :
1: state := 'CA'
2: state := 'OR'
etc.
or do i have to do :
IF state# = 1 THEN
state := 'CA';
ELSIF state# = 2 THEN
state := 'OR';
etc
Thank You.
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Pardee, Roy E
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: 9i-OCP Question
I thought you need to preserve the groups, not (necessarily) the members. If you have each group on its own disk then if one disk goes, you've got a complete set of logs on the other. Or am I not thinking about this correctly? Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, July 30, 2003 11:10 AM To: Multiple recipients of list ORACLE-L Answe is 4 disks . to keep each member on different disks . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 10:19 AM Hi all, What is the correct answer for this? Q If you have 2 redo log groups with 4 members each, how many disks does Oracle recommend to keep the redo log files? 1. 8 2. 2 3. 1 4. 4 Which is the correct answer. TIA Senthil -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar 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: AK 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: Pardee, Roy E 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: 9i-OCP Question
Yep, that's right--I had it wrong. You need to have at least one member from each group in order to do a full recovery. I see now from my trusty dba fundamentals I class text that each member of a group is identical (or is supposed to be). So I guess I'll go back to being confused about why the answer to the question below is 2. I guess 2 is the minimum required to survive a single disk failure? Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, July 30, 2003 12:29 PM To: Multiple recipients of list ORACLE-L dont know from experience, since I dont handle backups and redo, but according to the books, you are multiplexing your redo log groups. so if one of the files gets corrupted, etc... you have exact duplicates on different storage devices. So if you put all the members on the same storage device and you move onto the the next redo log group and then that storage device goes bad, you lose all the redo. now lets say you are in archive log mode and your archive log hasnt kept up... and was still archiving data. you cant recover from that point in time. please correct me if Im wrong. Im half responding to make sure I understand it correctly. From: Pardee, Roy E [EMAIL PROTECTED] Date: 2003/07/30 Wed PM 03:14:30 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: 9i-OCP Question I thought you need to preserve the groups, not (necessarily) the members. If you have each group on its own disk then if one disk goes, you've got a complete set of logs on the other. Or am I not thinking about this correctly? Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, July 30, 2003 11:10 AM To: Multiple recipients of list ORACLE-L Answe is 4 disks . to keep each member on different disks . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, July 30, 2003 10:19 AM Hi all, What is the correct answer for this? Q If you have 2 redo log groups with 4 members each, how many disks does Oracle recommend to keep the redo log files? 1. 8 2. 2 3. 1 4. 4 Which is the correct answer. TIA Senthil -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Senthil Kumar 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: AK 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: Pardee, Roy E 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: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command
RE: update statement ???
Have a look at the docs for the SUBSTR() and INSTR() functions--those should serve you. HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, July 30, 2003 2:24 PM To: Multiple recipients of list ORACLE-L Hi all, I need to update a column (brch_name) to be without (NO PHONE #S) string. So 3954 will be Denver, 3955 will be Golden, and 3963 will be LOVELAND 29TH, etc. How to write this update statement??? Thank you! 1* select brch_id, brch_name from cli_branches where upper(brch_name) like '%PHONE%' SQL / BRCH_ID BRCH_NAME -- -- 3954 DENVER (NO PHONE #S) 3955 GOLDEN (NO PHONE #S) 3956 ENGLEWOOD (NO PHONE #S) 3957 LITTLETON (NO PHONE #S) 3958 CHARLOTTE (NO PHONE #S) 3959 S LEMAY (NO PHONE #S) 3960 HARMONY ROAD (NO PHONE #S) 3961 ENGLEWOOD (NO PHONE #S) 3962 LOVELAND (NO PHONE #S) 3963 LOVELAND 29TH (NO PHONE #S) 3964 DENVER (NO PHONE #S) __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy 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: Pardee, Roy E 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: Emacs on SQLPlus, er uh... SQLPlus on emacs.
LOL--I'm the sole ultraedit fan in a den of textpad users have had many conversations that start with the phrase yeah, but can your editor do this I was of the opinion that they were pretty much equal until someone (Dennis Williams?) wrote in reminding me of ue's ability to open/edit/save a file on a remote host via ftp. It appears that tp does that only w/a supporting batch file (yuck!). Imagine my delighted cackling, having proven once and for all that ue is the one true windows text editor... 8^) -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 29, 2003 9:44 AM To: Multiple recipients of list ORACLE-L Hi I use vi on Unix and textpad on windoze. I like to write macros for vi so it types repetitive code for me...:-), although I should probably use an editor with more powerful macro facilities. cheers Pete Pete! Another textpad fan ... way to go! An outstanding text editor for windows (even better than ultraedit in my opinion) Ciao Fuzzy :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Grant Allen 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: Pardee, Roy E 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: Set Role in Trigger
the trusted procedure you have defined for that role. Scenario 2: All the tables have data for all the years. Inthis case you will have to use FGAC; but the FGAC policy will have to depend on the application context you defined earlier. You wil define another context attribute called school_year, whic is again set by the trusted procedure of the role. Since the user does not have the privilege to call dbms_Session, he will not be able to set the value of this attrbute to any other year at will. Summary: You will define several roles ideintified by procedure. All these roles are granted to the user but none is a default role. When a user logs in, all roles assigned to him are disabled, since none is a default role. Then he calls the procedure set_role(), no arguments. Inside the procedure set_role(), you will read the users table, see the role the user is supposed to have, enable this role via dbms_Session.set_role and then set the application context, if any. Since the user does not have execute privs on dbms_session, he will not be able to set the app context. Since the role is identified by a procedure, i.e. set_role(), the user will not be able to set the role himself using SET ROLE command in sql*plus. The only way he can do that is by calling the set_role procedure. If the user does not call the procedure, none of the roles are enabled; therefore he will not be able to do anything. In other words, you have a secured system. As an added bonus: you will have the application context attribute you can use any way you want to use. Right now you can use it for FGAC; but later when you are in 9i, you can use it in other cooler features such as Fine Grained Auditing. HTH. Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L Sent: Sunday, July 27, 2003 11:59 AM Short form of my question: How can I enable a Role for a User within a database trigger (owned by another Schema) on a table owned by yet another Schema? - The M's I've RTF'd indicate that a trigger (and any procedure it calls) can never execute with Invoker's Rights. - I can't find a way to execute Set Role for a User as another User, say, System. - I'm stuck. - Environment: 8.1.7 on Win2k and HP-UX. Longer form of my question: I'm in the process of adding extra security features to our 3rd Party Student Information System, whose code I can't touch. I've successfully implemented FGAC to keep Users at a School from accessing info.at other schools. Now I need to limit which School Year's data they can update (Past, Current, Next). The Application grants Sel, Ins, Upd, Del on its tables via a Role, so I thought I'd just switch Roles when the User switched School Years (via updating her record in a Users table). Seemed like a good idea, but now I can't see how to implement it. SQL and PL/SQL commands like Set Role, Alter Session, DBMS_Session.Set_Role, etc. only apply to the current User, which would be the Trigger Owner. I've used DBMS_System.Set_SQL_Trace_In_Session, but can't find an equivalent procedure to Set Role for another User. BTW, the fact that there's no Invoker_Rights_Clause in the Create Trigger syntax and a section in the PL/SQL User's Guide and Reference (Ch. 7 Subprograms / Invoker Rights vs Definer Rights / Using Views and Database Triggers) are the basis for my being stuck. The only possible way I see to do this is to create the trigger as System, then use Dynamic SQL to issue the Alter User ... Default Role ... command. However, I don't know if that takes effect immediately (within the User's current Session) or would take effect at the User's next login. Before I spend a bunch of time setting up a test, I thought I'd get some opinions from this very knowledgeable List. Can I do it? How? TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Oracle Banner
At the risk of stating the painfully obvious--you realize that those 'production' designations refer to the oracle db software itself, and not your use of the db, right? One other thing to consider would be setting your sql*plus prompt to include the SID of the db to which you are connected. Here's how you can do that: http://www.jlcomp.demon.co.uk/faq/sqlplus_prompt.html The advantage to that is that the info is always on your screen, long after the banner has scrolled off the edge of the earth. Disadvantages include that you've got to be disciplined to use @connect instead of connect (or else your prompt will be misleading) and that it's no longer easy to copy/paste multi-line sql commands. Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, July 28, 2003 2:39 PM To: Multiple recipients of list ORACLE-L Does anybody know if is possible to change the Oracle banner description? Oracle9i Enterprise Edition Release 9.0.1.3.0 - Production PL/SQL Release 9.0.1.3.0 - Production CORE9.0.1.2.0 Production TNS for Solaris: Version 9.0.1.3.0 - Production NLSRTL Version 9.0.1.3.0 - Production I would like to identify the environment as development environment instead of production. Sandro Augusto da Silva Technology Services Support NLA Technology Services Phone: +55 11 3398-8438 Fax: +55 11 3398-7522 Esta mensagem, incluindo seus anexos, pode conter informação confidencial e/ou privilegiada. Se você recebeu este e-mail por engano, não utilize, copie ou divulgue as informações nele contidas. E, por favor, avise imediatamente o remetente, respondendo ao e-mail, e em seguida apague-o. Este e-mail possui conteúdo informativo e não transacional. Caso necessite de atendimento imediato, recomendamos utilizar um dos canais disponíveis: Internet Banking , BankBoston por telefone ou agência/representante de atendimento de sua conveniência. Agradecemos sua colaboração. This message, including its attachments, may contain confidential and/or privileged information. If you received this email by mistake, do not use, copy or disseminate any information herein contained. Please notify us immediately by replying to the sender and then delete it. This email is for information purposes only, not for transactions. In case you need immediate assistance, please use one of the following channels: Internet Banking , BankBoston by phone or branch/relationship manager at your convenience. Thank you for your cooperation. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Set Role in Trigger
Guide and Reference (Ch. 7 Subprograms / Invoker Rights vs Definer Rights / Using Views and Database Triggers) are the basis for my being stuck. The only possible way I see to do this is to create the trigger as System, then use Dynamic SQL to issue the Alter User ... Default Role command. However, I don't know if that takes effect immediately (within the User's current Session) or would take effect at the User's next login. Before I spend a bunch of time setting up a test, I thought I'd get some opinions from this very knowledgeable List. Can I do it? How? TIA. Jack C. Applewhite Database Administrator Austin Independent School District Austin, Texas 512.414.9715 (wk) 512.935.5929 (pager) [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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).
possible to set continuation prompt in sql*plus?
Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? Thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: possible to set continuation prompt in sql*plus?
That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). Many thanks to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 22, 2003 11:15 AM To: Multiple recipients of list ORACLE-L I think so too. I think sqlcontinue and sqlnumber is what this guy is looking for. -Original Message- Sent: Tuesday, July 22, 2003 1:50 PM To: Multiple recipients of list ORACLE-L I got the impression that the question is if sql plus has the equivalent of the Unix PS1, PS2, PS3, PS4 prompts. -Original Message- The command is set sqlprompt. -Original Message- Greetings all, I've set up my login.sql so that my prompt shows my username the sid of the db to which I'm connected. This works well, but has made it tough to drag-select text b/c now the first line sticks way out relative to the continuation lines, like so: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; Is it possible to specify the prompt that shows on continuation lines (or otherwise pad it out) in sql*plus? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephen Lee 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: Richard Ji 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: Pardee, Roy E 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: possible to set continuation prompt in sql*plus?
Apologies--I wasn't clear in my original post. Right now I'm getting: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; What I'd *really* like to have is: [EMAIL PROTECTED] select username 2 from dba_users 3 where username like '%MC%' ; That way I could copy/paste sql commands as easily as I could when my prompt was just SQL . Setting sqlnumber off gets me: [EMAIL PROTECTED] select username [EMAIL PROTECTED] from dba_users [EMAIL PROTECTED] where username like '%MC%' ; Which isn't horrible, although I do miss the numbers. But no matter--I'll just live with it... Many thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 22, 2003 2:25 PM To: Multiple recipients of list ORACLE-L Setting SQLNUMBER OFF will make the SQLPROMPT continuation prompt, not completely eliminate it. Isn't it what the OP wanted in the first place? - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, July 22, 2003 3:59 PM sqlcontinue changes the prompt for the continuation of a SQL*Plus command, not a SQL command. sqlnumber off will mean that instead of having number prompts on the continuation of a SQL statement the SQL prompt will be continued (which I personally find annoying). set sqlnumber off should eliminate the problem of the indented first line, but you won't get a continuation prompt at all. Example (using SQL*Plus 8.1.7): SQL -- continuation of a SQL*Plus command. SQL prompt - Hello World Hello World SQL set sqlcontinue Next SQL prompt - Next Hello World Hello World SQL -- +++ SQL -- continuation of a SQL command SQL select * 2 from dual where 1 = 2 ; aucune ligne sélectionnée SQL set sqlnumber off SQL select * SQL from dual where 1 = 2 ; aucune ligne sélectionnée -Original Message- From: Arup Nanda [mailto:[EMAIL PROTECTED] Sent: mardi, 22. juillet 2003 12:44 To: Multiple recipients of list ORACLE-L Subject: Re: possible to set continuation prompt in sql*plus? I suggested using SET SQLNUMBER OFF. This sure works in SQL*Plus 8i and above; not sure if it does in 8.0.6 and I don't have a test executable to test it. But have you tried it? Arup Nanda - Original Message - That's right--I am looking for an analogue to PSx. I've been playing around w/sqlcontinue sqlnumber but so far no joy. I'm using sql*plus 8.0.6.0.0 (running against an 8.1.6 db). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Active Directory question
I've never tried it, but have questions for you anyway: 8^) So you want your clients to query AD for tns connect params a la' OID? Or do you have OID set up you want to add oracle db info to it, so you can synch OID w/ AD without losing your database info? If the latter, I trust you've seen metalink note 233096.1? http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOTp_id=233096.1 Have you tried the Active Directory Schema snap-in for MMC? See: http://support.microsoft.com/default.aspx?scid=kb;en-us;320337 for details on the latter. I *think* that should let you define new objects. I'd expect that to not be for the faint of heart tho... HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, July 21, 2003 8:25 AM To: Multiple recipients of list ORACLE-L I did not receive any replies to this posting. Does that mean that nobody has connected Oracle to Active Directory? Has anyone tried and given up on this? -Original Message- Sent: Wednesday, July 16, 2003 1:42 PM To: '[EMAIL PROTECTED]' We are trying to configure an Oracle LDAP connection to Microsoft Active Directory. This is so a client machine can get the connection information (an alternative to tnsnames.ora or Oracle Names) and connect to an Oracle9i database on Sun Solaris. Does anyone know how you create the container called Oracle Context Structure in Active Directory? My Active Directory administrator tells me this is not like a normal AD container. We've read the documents and are confused on this point. Thanks. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -- 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: Pardee, Roy E 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).
Why are SPFILEs binary anyway? (was RE: How to make SPFILE in sync with INIT.ORA ?)
-- 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Object Types in PL/SQL
This doesn't answer your question directly, but if you aren't able to override the
constructor explicitly, you can probably create a static function that creates,
initializes returns a tVNR as you would like it.
HTH,
-Roy
Roy Pardee
Programmer/Analyst/DBA
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Tuesday, July 15, 2003 3:49 AM
To: Multiple recipients of list ORACLE-L
Hi list
I have a problem regardint PL/SQL Object Types. According to the fine
manual, it should be
possible to override the default constructor (I'm on 9.2.0.3.0 Win2k). I did
that, Object Type
compiles without complaints:
CREATE OR REPLACE TYPE tVNR AS OBJECT
(
vVNR VARCHAR2(14),
CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2)
RETURN SELF AS RESULT,
MEMBER FUNCTION getVNR
RETURN VARCHAR2
) INSTANTIABLE FINAL;
CREATE OR REPLACE TYPE BODY tVNR AS
CONSTRUCTOR FUNCTION tVNR(piVNR VARCHAR2)
RETURN SELF AS RESULT IS
BEGIN
IF (LENGTH(piVNR)=11) THEN
SELF.vVNR := SUBSTR(piVNR,1,4) || '.' || SUBSTR(piVNR,5,4) || '.' ||
SUBSTR(piVNR,9,3);
ELSE
SELF.vVNR := 'invalid';
END IF;
RETURN;
END;
MEMBER FUNCTION getVNR RETURN VARCHAR2 IS
BEGIN
RETURN SELF.vVNR;
END;
END;
Now, everytime I want to create an object like this:
declare
vVNR tVNR;
begin
vVNR := new tVNR('12345678901');
dbms_output.put_line(vVNR.getVNR());
end;
I get the following error message:
ERROR at line 4:
ORA-06550: line 4, column 15:
PLS-00307: too many declarations of 'TVNR' match this call
ORA-06550: line 4, column 3:
PL/SQL: Statement ignored
Looks to me like the PL/SQL enginge isn't able to distinguish the default
constructor from the
overridden (my) version, since they have the same signature (of course).
Any input ? I couldn't find ANY descenct hints in the fine manual or the
Feuerstein book :(.
TIA,
Stefan
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stefan Jahnke
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: Pardee, Roy E
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: security without using different usernames
Could you maybe use v$session.osuser instead of program? That way you shouldn't even have to rename the exe. HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 15, 2003 4:19 PM To: Multiple recipients of list ORACLE-L yeah i think that might be 'viable'. its a big kludge. but sometimes you have to deal with that. send out page that alters the name of the executable, so each customer's executable has a different name use program in v$session in a logon trigger to get the customer hit a lookup table to see which schema to use execute immediate to set that up. thanks. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Tuesday, July 15, 2003 7:09 PM Ryan, It's alter session set current_schema = name; Tanel. - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, July 16, 2003 1:29 AM I know this is terrible design, but the GUI was created by a software engineering group that is seperate from the database group. Its not scalable. So Im trying to come up with a more scalable method. I have no power to change their gui. It rides on the database. I have to live with it. This is not a high enough transaction database to warrant seperate instances. We have a variety of customers. Each of them has their own versions of data. However, the schema is exactly the same. These tables can get huge, so we dont want to throw them all into the same schema. Right now, due to the fact that the GUI has a series of logins that are the same across clients, each client has its own instance. This isnt very scalable as we get more business. We have to create another instance and ingest data to it. Id like to find a way to get all the clients in the same instance with just different schemas and tablespaces. One thing I may have control over would be to slightly rename the executable. If you check v$session, in a client-server application the name of the product connecting to the database is recording. I can handle security based off of that. My question is what would be the best way? Cant do synonyms for this since its the same login. I think I saw somewhere that there is a session based 'set' command where you can say use this schema. I think it was on asktom and in reference to a question about public synonyms. I cant find it. Anyone know it? Also is it viable to base a context off of what is in v$sesion with a logon trigger? How would I 'redirect' all queries to a specific schema? To stress, I cant change the application. Different group with different skillsets. Any suggestions? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Question about VB and Oracle
This is awfully difficult to answer in the abstract--particularly so without knowing which version of vb you're using, or the app or environment (client/server, ASP). You're probably better off putting the question to a vb list--I recommend visbas-l: http://peach.ease.lsoft.com/archives/visbas-l.html HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, July 07, 2003 12:09 PM To: Multiple recipients of list ORACLE-L Hi!! What is the better why to return resultsets from Oracle to Visual Basic? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: How to install Oracle JVM on Oracle 8.1.7.
These metalink URLs may help: ORA KB for db-JVM How to Tell if Java Virtual Machine Has Been Installed Correctly HTH, -Roy Roy PardeeProgrammer/Analyst/DBASWFPAC Lockheed Martin ITExtension 8487-Original Message-From: Nirmal Kumar M [mailto:[EMAIL PROTECTED]]Sent: Thursday, July 03, 2003 7:16 AMTo: Multiple recipients of list ORACLE-LSubject: How to install Oracle JVM on Oracle 8.1.7.Hi all,I want to install, oracle JVM on oracle 817 databaserunning on NT2000 server.I hope that initjvm.sql script has to be run under syslogin. However i can't able to find that script filein my oracle home path %ORACLE_HOME%\RDBMS\ADMIN.Let know how to proceed futher and what are the prerequisities and post requiesties for thisintallation?.Thanks.Nirmal,.=fsdfsdfsdfsdfs__Do you Yahoo!?SBC Yahoo! DSL - Now only $29.95 per month!http://sbc.yahoo.com--Please see the official ORACLE-L FAQ: http://www.orafaq.net--Author: Nirmal Kumar M INET: [EMAIL PROTECTED]Fat City Network Services -- 858-538-5051 http://www.fatcity.comSan Diego, California -- Mailing list and web hosting services-To REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: Do Not Call
It could be Oracle running on unix--all we know for sure is that the web server is a windows box. I assume that even if the web server was a unix box, it could be hitting a database (of whatever stripe) that ran on windows... Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, July 01, 2003 12:10 PM To: Multiple recipients of list ORACLE-L http://uptime.netcraft.com/up/graph/?host=www.donotcall.gov Well, if it were unix then it couldn't be SQLserver. It is Windows, therefore I assume its SQLserver, but it could be Oracle. But who runs Oracle on Windows anyway *hahahaha* [EMAIL PROTECTED] 07/01/03 02:29PM Paradox for DOS -Original Message- Sent: Tuesday, July 01, 2003 2:00 PM To: Multiple recipients of list ORACLE-L There have been over 10,000,000 entries made in the National Do Not Call Registry since Friday June 27. Does anyone know the database engine in which this is stored? Curious in Bozeman, MT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve 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: Pardee, Roy E 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: Script to check filename continuously
Script would be good for this kind of thing, I think. Dig how similar this windows scripting host script is to your pseudocode: ' Dim FSO Const WatchForFile = c:\pretend.txt Const CopyFileTo = c:\copied.txt Set FSO = CreateObject(Scripting.FileSystemObject) Do While True If FSO.FileExists(WatchForFile) Then WScript.Echo Found WatchForFile ! Copying... Call FSO.CopyFile(WatchForFile, CopyFileTo) Exit Do Else WScript.Echo Still no WatchForFile --going to sleep for 10 seconds... WScript.Sleep 1 End If Loop Set FSO = Nothing WScript.Echo Finished running WScript.ScriptFullName ' Save that off to a text file w/extension .vbs execute it at the command line by typing cscript filename.vbs. There's documentation for the windows scripting host at http://www.microsoft.com/scripting. HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, July 03, 2003 3:31 AM To: Multiple recipients of list ORACLE-L Hi All Does anyone have script(DOS batch file or Unix Shell) to do the following stuff? loop if exist FILE_NAME begin do something; copy files to remote machine,etc... EXIT; end; end loop; Thanks Sami -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: calling an external procedure
://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: Pardee, Roy E 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: Automate an update
Do window_open's values depend on the time the record is inserted, or the time it is retrieved? If the former, you could do it in a trigger. If the latter, you could make window_open a calculated column in a view. What are you using for a user interface--oracle forms, sql*plus, java, vb...? HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, July 02, 2003 2:41 AM To: Multiple recipients of list ORACLE-L Hi all, I need help in order to create a following mechanism. I have a table where is a column called window_open and it has two values 'Y' and 'N' Now I need to automate the update a single row based on following rules: If time is between 08:00-16:00 the value on that window_open column should be 'Y' during other period the value should be 'N'. How can I do this and automate the task... Thanks in advance, Joshua Gå före i kön och få din sajt värderad på nolltid med Yahoo! Express -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Do Not Call
Yeah, it's an asp.net app (you can tell from the .aspx file extension on the URLs). But the db could be anything... Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, July 02, 2003 9:16 AM To: Multiple recipients of list ORACLE-L snip http://uptime.netcraft.com/up/graph/?host=www.donotcall.gov Well, if it were unix then it couldn't be SQLserver. It is Windows, therefore I assume its SQLserver, but it could be Oracle. But who runs Oracle on Windows anyway *hahahaha* ... We do ... groan -- Steve Wolfe [EMAIL PROTECTED] 07/01/03 02:29PM Paradox for DOS -Original Message- Sent: Tuesday, July 01, 2003 2:00 PM To: Multiple recipients of list ORACLE-L There have been over 10,000,000 entries made in the National Do Not Call Registry since Friday June 27. Does anyone know the database engine in which this is stored? Curious in Bozeman, MT -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Orr, Steve 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: Pardee, Roy E 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: SELECT Output Default Ordering ?
Curious--if you can specify hints, why not just specify an ORDER BY clause? Wouldn't that be more readable/maintainable/portable? I don't know where I got it, but I had the impression that row order was explicitly undefined (in one of the SQL standards?) when you don't do an explicit ORDER BY. A given version/brand of rdbms may act consistently, but you shouldn't rely on it, as the next version or brand may do something else. But maybe I'm making that up... Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, June 25, 2003 8:10 AM To: Multiple recipients of list ORACLE-L Vivek If you want the data returned in an order, you can create an index with the order you want, and in your query provide a hint for Oracle to use that index. If your query is such that Oracle actually uses that index, the data will be returned in that order. I work with a large application that entirely depends on this principle. Crude but nevertheless effective. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 8:33 AM To: Multiple recipients of list ORACLE-L When NOT Giving any Order by Clause , How is the Output of the SELECT Clause ordered by Default ? Assuming There exists a Unique index on the Table Is some Rule followed ? NOTE Records may have been INSERTED into the Table in some manner differing from the Order of the data of the Unique index Key fields. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: VIVEK_SHARMA 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: Pardee, Roy E 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: Outer join in SQL server - A very simple question
not be liable for any improper, untimely or incomplete transmission. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Naveen Nahata 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: Pardee, Roy E 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).
Wrox Press catalog purchased by APress (was RE: Book Recommendati on for Apache)
I believe it's true that wrox is out of business. But APress (www.apress.com) has purchased the bulk of their catalog, so hopefully they'll resume publishing these books... Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, June 19, 2003 1:44 PM To: Multiple recipients of list ORACLE-L Hi, Any recommendations on good Apache books? We will be bringing in Oracle 9iAS shortly. Third party application constraints require the use of Forms 6i, which in turn requires that we stay with 9iAS Release 1 (version 1.0.2.x.x). We are currently running Oracle Application Server. I want to understand how 9iAS works. For me, the steps to understanding this are something like: 1) Understand HTTP 2) Understand Apache 3) Understand 9iAS I have found useful information on the Internet describing the HTTP protocol, and I am comfortable I understand that well enough to move on. Now I want to get a handle on the Apache Server. I have had a brief look at a book from O'reilly called Apache, The Definitive Guide. This looks like a good book. Thanks, Sam p.s. A few months, I read on the List that the Publisher of Tom Kyte's book Expert One-On-One Oracle has gone bankrupt. Can anybody confirm if this is true? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: MySQL in the future?
I think the intro para quoted below is an oversimplification of the license policy--and one that (understandably) favors MySQL AB. My reading of that page is that it's the *distribution* of the MySQL source code (modified or not) or binaries that requires you to have a commercial license. I take this: 2. Free use for those who never copy, modify or distribute As long as you never distribute (internally or externally) the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL or other OSI approved license or not. More specifically: Modifying - You are allowed to modify MySQL Software source code any way you like. If you distribute the modified version, all changes, all interface code and all code that connects directly or indirectly to the interface code fall under GPL. Copying - You are allowed to copy MySQL binaries and source code, but when you do so, the copies will fall under the GPL license. to mean that apps that just run against MySQL do not have to be GPL'd. I'd bet you could even sell a commercial app that required MySQL to run, so long as you made your customers get install their own copies of MySQL (that is, you did not distribute it yourself). But I haven't actually read the GPL, so I could be wrong... Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, June 18, 2003 3:25 PM To: Multiple recipients of list ORACLE-L It seems (almost) clear to me from the text at http://www.mysql.com/products/licensing.html This is our licensing policy in brief: Our software is 100% GPL, and if yours is also 100% GPL (or OSI compliant), then you never have to pay us for the licences. In all other instances, you are better served by our commercial licence. If your application is 100% GPL (Gnu Public License) then you can use mySQL for free. If your application is NOT 100% GPL then you can use mySQL but you have to pay for it. -Original Message- From: Richard Ji [mailto:[EMAIL PROTECTED] What? Are you sure? My understanding is if I make changes to MySql code or some addon to MySql I need to submit my changes bakc to public and GPL. That what the whole dispute between MySQL the company and the community before, right? But my application too? For just using it? What about all those sites running Apache, will they have to make their appliction GPL because they are using Linux/Apache? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer 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: Pardee, Roy E 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: ora-1031
Does your schema owner have privs enough on hsd_navgrp_kw? If you swap that out for an explicitly specified datatype for l_active_trigger, does it make any difference? Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, June 17, 2003 2:10 PM To: Multiple recipients of list ORACLE-L I have 3 triggers that are giving an ora-1031, below is an example: ON AUTH_ADDTL_INFO * ERROR at line 16: ORA-01031: insufficient privileges The same schema owns the tables and the triggers. Let me repeat, the tables exist and are owned by the same schema as the triggers. I can create about 50 triggers just like this before I get the error. The 3 triggers are delete triggers, I don't know if that is something to keep in mind. Why would I get an ora-1031? CREATE OR REPLACE TRIGGER DIAMOND.trg_audit_aai_delete /*__ __ */ /* ___ */ AFTER DELETE ON AUTH_ADDTL_INFO FOR EACH ROW DECLARE l_seq_audit_id NUMBER; l_number NUMBER (1); l_active_trigger hsd_navgrp_kw.audit_trail%TYPE; l_old_data VARCHAR2 (32750); iINTEGER; l_done CHAR:= 'F'; l_found_splitCHAR:= 'F'; l_segment_cntINTEGER := 1; David Ehresmann -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ehresmann, David 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: Pardee, Roy E 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).
ADO and bind variables (was RE: Performance improvement required :-))
This is interesting--if I use ADO with the ODBC provider (as the code does below), I get the same results. But if I use just ADO (that is, ms' OLE DB provider for oracle (MSDAORA.1)) then I don't get bind vars. (I'm doing INSERTs in my code, not SELECTs). I wonder if oracle's native OLE DB provider works any differently--I would bet that it does... Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, June 13, 2003 11:05 AM To: Multiple recipients of list ORACLE-L I'm sure you can. You should see it in an ODBC trace log, or you can use trace events on the database. Here's a really simplistic test I did to verify it. I ran this VB code that executes a really dumb query that could not have come from anywhere else - SELECT DUMMY FROM DUAL WHERE DUMMY = 'X' but passed 'X' as a bind variable Private Sub Form_Load() Dim conn1 As New ADODB.Connection Dim cmd1 As New ADODB.Command Dim rs1 As New ADODB.Recordset Dim STRSQLSTRING As String Dim param1 As New Parameter strConnect = UID=produser;PWD=prodpass;DSN=WAREHOUSE; STRSQLSTRING = SELECT DUMMY FROM DUAL WHERE DUMMY = ? With conn1 .ConnectionTimeout = 0 .CommandTimeout = 0 .CursorLocation = adUseClient .Mode = adModeRead .Open strConnect End With If Err.Number Then MsgBox Err.Number Exit Sub End If With cmd1 .ActiveConnection = conn1 .CommandText = STRSQLSTRING .CommandType = adCmdText Set param1 = .CreateParameter(DummyValue, adChar, adParamInput, 1, X) param1.Value = X .Parameters.Append param1 Set rs1 = .Execute End With MsgBox rs1.Fields(DUMMY) End Sub Afterward, executed this on the database - SQL select sql_text from v$sqlarea where sql_text like 'SELECT DUMMY %'; SQL_TEXT SELECT DUMMY FROM DUAL WHERE DUMMY = :1 It shows the parameter was definitely passed as a bind variable. Check out this document on Metalink - Retrieving Record Set from Stored Proc Using ADO and VB (SCR 782) It appears to have an example of passing a cursor back to a recordset, though I've never tried it. HTH. Beth -Original Message- Sent: Friday, June 13, 2003 1:20 PM To: Multiple recipients of list ORACLE-L Why can't you use bind variables? I thought using .Parameters method (property?) of ADODB.Command would use bind variables. I thought it didn't. Any way of checking (other than to get the developers to try it? What function, and where can't you use it? Stored Procedure type function (i.e. user-written) called from VB. 'Cos it's a Stored Procedure it will use bind variables, but you can't return a result set to VB. Craig -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Seefelt, Beth 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: Pardee, Roy E 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: RAC, fail over and timeout .. Big Question
If your devs are using ADO, do you know if they've tried setting the .CommandTimeout property of the relevant Connection or Command object? I'm not sure what the analogue is in ADO.Net, tho I'd bet there is one... Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, June 12, 2003 12:02 PM To: Multiple recipients of list ORACLE-L Thanks KG, Greg, What I visualize is this ... Specify a (sort of) timeout value. If query doesn't come back within this value, raise alert and fail over to the other side. If you get the same problem, raise the hell out of everyone and go to cache mode (good for 3-5 minutes). Hope the problem gets fixed by then. The biggest question is how to timeout a query if it doesn't return in specified time? We got some good VB coders ... so any ideas with VB are also welcome. Pre-connecting is something we are looking at, but sometimes application performs DML as well. So ... the plot thickens ... I am still researching ... Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, June 12, 2003 2:35 PM To: Multiple recipients of list ORACLE-L It would be interesting to see if the pre-connect helps you out or not.. We are getting ready to work thru similar issues/testing... Greg -Original Message- Sent: Thursday, June 12, 2003 12:30 PM To: Multiple recipients of list ORACLE-L Raj: I am afraid, there are not much alternatives you can think of. Even the SELECT fail over will take around few seconds since it has to attach the PGA to the second instance and run the query from where it is failovered, though you can overcome this by enabling pre-connect. Pls let me know if you got any nice ideas ;) Best Regards, K Gopalakrishnan -Original Message- Jamadagni, Rajendra Sent: Thursday, June 12, 2003 7:50 AM To: Multiple recipients of list ORACLE-L Hi all, here is a (hopefully) tricky one ... We have a two node RAC (9202), two applications run on either side, no problems there. We have a business critical process that runs all the time on node T2. This process needs to have a set response time or it affects business. And it is written in VB. Currently we have already handled the situations when a node is down (or machine is down) it fails over to the other side and continues. What we need to cater for is when DB is up, but sick (i.e. not responding). We need to be able to specify a timeout in the queries and when we get no response in the specified time, we need to automatically fail over to the other side. The queries in the application are optimally written, they are sub-second or single digit second queries. So, a query taking a long time would be about 10-15 seconds. While we are fishing for ideas, has anyone implemented anything like this? Any ideas? Thanks in advance Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Form question
bell ; Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Saturday, June 07, 2003 11:44 PM To: Multiple recipients of list ORACLE-L Good morning, This a question for Oracle form and report I want to add a beep or sound to a form when a certain action occurs, e.g., result of a query. How can I make a beep (or sound) from within an Oracle Form. thanking you in advance Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ofer Harel 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: Pardee, Roy E 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: MS Access 97 to Oracle 8.1.7 Scheduler
You can specify the name of a macro for msaccess to run on startup w/the /x parameter of the msaccess executable. That macro can do a RunCode action to run a VBA procedure that you specify (which is where you can do your data loading) followed by an Exit action to shut down msaccess. IIRC, you can schedule a command like: c:\program files\microsoft office\office\1033\msaccess.exe path to the msaccess db file /x macLoadDataIntoOracle Alternatively, you can write a windows scripting host script that will build that command line up for you (after e.g., verifying that the db file msaccess executable are present, maybe doing some logging, etc.) and then schedule that. HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, June 05, 2003 2:30 PM To: Multiple recipients of list ORACLE-L Gurus, We would like to load data from MS Access 97 database to Oracle 8.1.7 database with MS scheduler? Is it feasible? It could be done in MS SQL server with the helps of DTS package. Please share your experience in this regard and let me know what could be the best method to automate loading data from MS Access 97 to Oracle 8.1.7. Thank, Bob __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Bob Robert 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: Pardee, Roy E 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 bind variables with ADO in VB
and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. Statements and opinions expressed in this e-mail may not represent those of the company. If you have received this email in error please notify [EMAIL PROTECTED] This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses (www.mimesweeper.com) *** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Craig Healey 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: Pardee, Roy E 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: example to use bind variables with OO4O/VB
http://www.doag.de/orafaq/faqoo4o.htm#BINDVART (B (BRoy Pardee (BProgrammer/Analyst/DBA (BSWFPAC Lockheed Martin IT (BExtension 8487 (B (B-Original Message- (BSent: Thursday, June 05, 2003 5:00 AM (BTo: Multiple recipients of list ORACLE-L (B (B (BGuys, (B (Bcan someone give a simple example ( piece of code ) for: (B (BHow to use bind variables with OO40/VB6 to connect to a 8.1.6 database ? (B (B..peeped into metalink too. (B (Bcan u give me any other example / URL for the same ???! (B (BTIA. (B (BJp. (B (B (B (B (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Prem Khanna J (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing). (B-- (BPlease see the official ORACLE-L FAQ: http://www.orafaq.net (B-- (BAuthor: Pardee, Roy E (B INET: [EMAIL PROTECTED] (B (BFat City Network Services-- 858-538-5051 http://www.fatcity.com (BSan Diego, California-- Mailing list and web hosting services (B- (BTo REMOVE yourself from this mailing list, send an E-Mail message (Bto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in (Bthe message BODY, include a line containing: UNSUB ORACLE-L (B(or the name of mailing list you want to be removed from). You may (Balso send the HELP command for other information (like subscribing).
RE: Sum of Previous Record
Depending on the structure of your table, you can also use plain SQL with a self-join, similar to: select v1.cust_id , v1.order_date , v1.order_total , sum(v2.order_total) cumulative_total from orders v1 , orders v2 where v1.cust_id = v2.cust_id AND v2.order_date = v1.order_date group by v1.cust_id, v1.order_date, v1.order_total ; Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, June 04, 2003 8:05 AM To: Multiple recipients of list ORACLE-L Walid You can use SUM as an analytic function like this: - SQL select credit, debit, sum(credit + debit) over (order by rowid) from foo; CREDIT DEBIT SUM(CREDIT+DEBIT)OVER(ORDERBYROWID) -- -- --- 3 0 3 0 -1 2 1 0 3 Obviously, you will need to order by something more sensible than the rowid (perhaps a timestamp). -Original Message- From: Walid Alkaakati [mailto:[EMAIL PROTECTED] Sent: 04 June 2003 14:10 To: Multiple recipients of list ORACLE-L Subject: Sum of Previous Record Hi list , Can you help me please . I have a report that show data as follows : debit credit balance 30-3 0 -1 2 1 0 3 Is their a way to get balance without using a separet query in a formula column,i .e i need the value of the previous record in the same repeating frame. Thanks -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Walid Alkaakati 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). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Lord, David - CSG 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: Pardee, Roy E 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: Interesting!
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: Stefan Jahnke 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: Pardee, Roy E 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: randomly generate unique key
There's an optional db package called dbms_random that you can use to get random numbers. Dig it: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=77326.1 There's also a sql function (in 9i only?) called sys_guid() that returns a globally unique identifier--big ugly things. See that at: http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90125/ functions122a.htm#84836 I think I'd try dbms_random first--keep using your sequence, but 'salt' the actual identifier by concatenating 3 or 4 random digits at the beginning or end of the sequence value. That way the sequence will guarantee uniqueness, but the numbers should not be guessable. Plus these would be numbers that human beings can reasonbly be expected to remember--I can't imagine asking people to remember the GUID I assign them... HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, May 30, 2003 8:45 AM To: Multiple recipients of list ORACLE-L Hi List, Originally, Our next generate directory group use sequence # to generate a unique key. (we can't use emplid or social s # as key, since students doesn't have emplid and some foreign students doesn't have ssn). That works fine until the policy changed, they need to publish the unique key which is trunk id. According to the developers, if publish those sequenced unique key, it will create some problems, since the community can guess the next sequence # and got unnecessary info associated with it. Now the question is how to create a random unique key? The idea is create a function call combine the 3 components (date, time, MAC address) to generate a random #. Does the date/time (client query system time)can always be unique or can be duplicated? Does someone has any idea or experience to generate those randomly unique key? Any info would be helpful. Thanks in advance, Joan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joan Hsieh 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: Pardee, Roy E 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: Oradesigner9i ERD Diagrams
(like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: is this DBA's only mailing list?
There are also dev-specific lists tho: Developer 2000: ODTUG-DEV2K-L, and Designer 2000: ODTUG-DES2K-L. Send an e-mail w/the text (for instance) SUB ODTUG-DEV2K-L To the address [EMAIL PROTECTED] to subscribe. HTH, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, May 29, 2003 6:40 AM To: Multiple recipients of list ORACLE-L Yes, we even let developers use this list. Heck, if you want to throw out a perl question cuz I bet you'll get an answer. ;o) Dave -Original Message- Sent: Thursday, May 29, 2003 7:46 AM To: Multiple recipients of list ORACLE-L HI everyone I recently joined the list, and I think it is meant only for DBAs. Is that true? Also, can anyone suggest any similar mailing list meant for Oracle developers (that is those working with SQL, PL/SQL, Forms, Oracle apps, etc.) and those who are not DBAs? Thanks in advance Ajay K. Garg -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: HELP - Microsoft Access error SQLSetConnectAttr failed error.
Need more info: What are you doing when the error occurs? If it's vb code, can you post it? Can you connect from the erroring machine via sql*plus? Can you link a table via the odbc connection open it interactively? Does it make a difference if you define a new odbc data source link through that? Is the error consistent accross client pcs? Which odbc driver are you using--oracle's or ms'? Cheers, -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, May 29, 2003 10:50 AM To: Multiple recipients of list ORACLE-L However, I was able to independently through odbctest and tnsping confirm that the odbc driver working and the alias working through Oracle's Net8. I also noticed with the odbctest that the user had a very small and limited amount of tables to view from all_tables. Could that be the reason that we are getting this error from Access? Thanks, Paula -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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).
index-organized table question
Greetings all, Say I have two main tables in a M:M relationship, and a junction table resolving the relationship, like so: create table staff (staff_id number PRIMARY KEY , name varchar2(50) , other cols) ; create table projects (proj_id number PRIMARY KEY , name varchar2(50) , mgr_id number , other cols) ; create table staff_projects (staff_id number , proj_id number , CONSTRAINT staff_projects_pk PRIMARY KEY (staff_id, proj_id) , FOREIGN KEY (staff_id) REFERENCES staff(staff_id) , FOREIGN KEY (proj_id) REFERENCES projects(proj_id) ) ; Queries that join staff_projects to projects to pull project info for a given staff_id should be really fast, since staff_id is the leading column in the index created to enforce the PK on staff_projects (right?)--and in fact, the staff_projects table itself shouldn't need to be touched, since all the needed info is in this index (also right?). If I want to speed lookups of staff info for a given proj_id, I can create another unique index on staff_projects(proj_id, staff_id). There again, the staff_projects table shouldn't need to be touched, b/c all the info is in the index. At this point, it seems like the table is sort of superflous--all the info in it is better accessible in the two indexes. If that's right--is there an advantage in making staff_projects an index organized table? Thanks! -Roy Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: dbshut script - shutdown or shutdown immediate
I am certainly not suggesting that recovery can't handle a crash--I'm just trying to make sure that I understand what shutdown abort does. Some posts have implied that it's no big deal, which is counter-intuitive to me. To me, crashing a program on purpose seems like a drastic measure. No doubt desperate times can call for desperate measures, but I would have guessed that optimally, you'd try immediate first then abort if immediate takes too long. But I'm just learning this stuff... Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, April 02, 2003 6:34 PM To: Multiple recipients of list ORACLE-L yeah so? are you suggesting that Oracle instance recovery can't handle a database crash? If so, better pray your server never crashes. --- Pardee, Roy E [EMAIL PROTECTED] wrote: Well... my official oracle instructor in dba larva school said that it's tantamount to crashing the db--or so I recall anyway. This isn't so? Peace, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, April 02, 2003 2:09 PM To: Multiple recipients of list ORACLE-L On Wed, 2 Apr 2003, Chris Berry wrote: Shutdown abort is pretty drastic, are you sure shutdown immediate didn't work? What is drastic about shutdown abort? Never one to opt out of a shutdown abort thread, -- Jeremiah Wilton http://www.speakeasy.net/~jwilton - Uses shutdown abort exclusively - successful shutdowns/startups: over 10,000 - problems with shutdown abort: 0 - versions used: 7.3.2.3 - 10.0 (yes I have a pre-beta) - still employed! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: dbshut script - shutdown or shutdown immediate
Alas, I'm until recently a prisoner of windows so I can't speak to shell scripts. On windows I'd probably try a windows script host vbscript like so: warning = air code Option Explicit Dim WinShell Dim jobImmediate Dim StartTime Dim ImmediateFailed Const WaitMinutes = 15 Set WinShell = CreateObject(WScript.Shell) ImmediateFailed = False StartTime = Now Set jobImmediate = WinShell.Exec(call to sqlplus w/shutdown immediate script) Do While jobImmediate.Status = WSHRunning WScript.Sleep 5000 If DateDiff(n, StartTime, Now) WaitMinutes And Not ImmediateFailed Then jobImmediate.Terminate ImmediateFailed = True End If Loop If ImmediateFailed Then similar code attempts a shutdown abort script End If /warning = air code I would guess that you could do something similar w/perl... Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, April 03, 2003 11:24 AM To: Multiple recipients of list ORACLE-L -Original Message- From: Pardee, Roy E [mailto:[EMAIL PROTECTED] I would have guessed that optimally, you'd try immediate first then abort if immediate takes too long. I've read that some people on the list have done this. I am curious as to how this is implemented. How long is too long? And how is this coded? I'm trying to think how you would write this with shell scripts for example. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: dbshut script - shutdown or shutdown immediate
Well... my official oracle instructor in dba larva school said that it's tantamount to crashing the db--or so I recall anyway. This isn't so? Peace, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, April 02, 2003 2:09 PM To: Multiple recipients of list ORACLE-L On Wed, 2 Apr 2003, Chris Berry wrote: Shutdown abort is pretty drastic, are you sure shutdown immediate didn't work? What is drastic about shutdown abort? Never one to opt out of a shutdown abort thread, -- Jeremiah Wilton http://www.speakeasy.net/~jwilton - Uses shutdown abort exclusively - successful shutdowns/startups: over 10,000 - problems with shutdown abort: 0 - versions used: 7.3.2.3 - 10.0 (yes I have a pre-beta) - still employed! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jeremiah Wilton 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: Pardee, Roy E 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: OT- Start a process after oracle on Win 2K
One other way to go would be to use a system startup script (see http://support.microsoft.com/default.aspx?scid=kb;en-us;198642 for details) that first started your db, and then followed up w/whatever other scripted tasks were necessary. Theoretically, anyway... HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, April 01, 2003 5:39 AM To: Multiple recipients of list ORACLE-L Hi Peter, Sounds like a job for an after startup database event trigger (check out CREATE TRIGGER doco). Cheers Richard - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, April 01, 2003 10:28 PM Hi Slightly OT I have a couple of programs that need to be run after Oracle has started and want to run them without a user logging in. The likely place seems to be in the scheduled tasks running at startup or as a program under the local run key in the registry. The processes are a couple of scripts and I would envisage running them as a batch file What is best? Cheers -- = Peter McLarty E-mail: [EMAIL PROTECTED] Technical ConsultantWWW: http://www.mincom.com APAC Technical Services Phone: +61 (0)7 3303 3461 Brisbane, AustraliaMobile: +61 (0)402 094 238 Facsimile: +61 (0)7 3303 3048 = A great pleasure in life is doing what people say you cannot do. - Walter Bagehot (1826-1877 British Economist) = Mincom The People, The Experience, The Vision = This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please delete it and notify the sender. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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: Pardee, Roy E 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: win2k system shutdown scripts--suitable for db shutdown?
This is gold--many thanks indeed. BTW, in case it's useful, it is possible to encode portions of a windows script file. See, e.g., http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/h tml/seusingscriptencoder.asp (pls watch for wrap). That's not the same as encrypting it of course, but it gives you one more layer of protection... Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, March 25, 2003 2:54 AM To: Multiple recipients of list ORACLE-L Hello, I'm reading that win2k supports shutdown scripts (w/the group policy mmc snap-in). Is anyone using these to shutdown their win2k-hosted oracle databases? Right now our netadmins are running shutdown scripts as a manual step, but if it's susceptible of scripting, we'd like to do it that way instead. Are there any gotchas? I have a TAR open with OSS regarding the shutdown behaviour of 8.1.7.x on Win2K. We have several Win2K servers running 8.1.7.3 or 8.1.7.4 which do not stop the database correctly during a server reboot. Investigation has shown that while a 'net stop oracleserviceDB_NAME' command entered into a command prompt completes successfully, shutting down / rebooting the server without first stopping the database service results in an instance recovery having to be performed during startup. The alert log shows that during a shutdown / reboot, there is *no* attempt made to stop the database. I have configured the servers and databases according to the various Metalink documents and the settings have been verified by OSS. This behaviour only occurs on our Win2K servers that run 8.1.7.3 and 8.1.7.4: other Win2K servers that run 8.1.7.2 and earlier do not exhibit the problem. Interestingly, we do have a single Win2K/8.1.7.4 server that does stop the database correctly during a server shutdown / reboot. OSS have recreated the error using 9iR2 on Win2K and have reported that the database stop during a server shutdown / reboot appears to work more reliably on NT. Investigation by Oracle Development suggests that the problem lies with the Service Control Manager (SCM) in Win2K that handles the starting and stopping of the system services. OSS have advised me to get in touch with Microsoft to persue the matter further. I have (unsuccessfully) attempted to persuade OSS to liase with Microsoft directly but they have so far refused. I digress ... Finally, to answer your question, a workaround provided to me by OSS is to use the Group Policy Editor to have Win2K run a VBS script during a server shutdown. The code provided is: Set WshShell = WScript.CreateObject(WScript.Shell) Return=WshShell.Run(sqlplus shutdownusr/passwordl as sysdba @C:\orashut, 1, true) I received the code only yesterday and as yet haven't tested it. As I would rather not have a password held in a text file on the server, I first plan to test the use of the GP shutdown script with the 'net stop' command (as described above) as this works correctly interactively and obviates the need for a user id and password to stored in a file. Before testing the use of the GP shutdown script, I advise you to investigate if the database(s) on your Win2K server are stopped correctly during a server shutdown / reboot. If the databases are stopped in the correct manner, then it is one less thing for you to worry about :) I'll do some of my own testing with the GP shutdown script and post feedback to the list. Please note that it will take me some weeks to provide the feedback as I am about to start a major installation of a new set of databases and servers for a customer of ours. I just love those night shifts and long hours ;) --- nigel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nigel Cemm 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: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP
RE: sort ip addresses
There's probably a neater solution, but here's a quick and dirty function that zero-pads each octet returns a value you should be able to use in an ORDER BY: create or replace function OrderIP(p_IP IN VARCHAR2) return VARCHAR2 is v_octet number ; v_start number ; v_end number ; v_currlen number ; v_padded varchar2(15) ; c_octet_length constant number := 3 ; begin v_octet := 1 ; v_start := 1 ; v_end := instr(p_IP, '.', v_octet) ; while v_end 0 loop v_currlen := (v_end - v_start) ; v_padded := v_padded || lpad(substr(p_IP, v_start, v_currlen), c_octet_length, '0') ; v_start := v_end + 1 ; v_octet := v_octet + 1 ; v_end := instr(p_IP, '.', v_octet) ; end loop ; -- Finally, get the last octet. v_padded := v_padded || lpad(substr(p_IP, v_start), c_octet_length, '0') ; return v_padded ; end OrderIP ; HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, March 25, 2003 9:54 AM To: Multiple recipients of list ORACLE-L Need a trick to sort ip addresses into numerical order. I have them stored as varchar2. If I select without a sort I get rows in order of their character value: 10.0.112.1 10.0.113.1 10.0.113.2 10.0.12.1 10.0.78.1 I'd like to order them numerically within the octets: 10.0.12.1 10.0.78.1 10.0.112.1 10.0.113.1 10.0.113.2 === Ray Stell [EMAIL PROTECTED] (540) 231-4109 KE4TJC28^D -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ray Stell 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: Pardee, Roy E 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).
FW: 9000x faster than Oracle?
Apropos of the 'Database Modeling- Normalization - Dinosaurs or What?' thread: Roy PardeeProgrammer/AnalystSWFPAC Lockheed Martin ITExtension 8487 -Original Message-From: Pardee, Roy E Sent: Monday, March 03, 2003 12:56 PMTo: Jane; Kim; Mike; Nancy; Paul; RickSubject: 9000x faster than Oracle? This looks interesting (from Slashdot.org): I wonder how long it would take to roll this thing forward after a server crash... === Object Prevalence: Get Rid of Your Database? Posted by Hemos on Monday March 03, @08:45AMfrom the throwing-it-out dept.A reader writes:" Persistence for object-oriented systems is an incredibly cumbersome task to deal with when building many kinds of applications: mapping objects to tables, XML, flat files or use some other non-OO way to represent data destroys encapsulation completely, and is generally slow, both at development and at runtime. The Object Prevalence concept, developed by the Prevayler team, and implemented in Java, C#, Smalltalk, Python, Perl, PHP, Ruby and Delphi, can be a great a solution to this mess. The concept is pretty simple: keep all the objects in RAM and serialize the commands that change those objects, optionally saving the whole system to disk every now and then (late at night, for example). This architecture results in query speeds that many people won't believe until they see for themselves: some benchmarks point out that it's 9000 times faster than a fully-cached-in-RAM Oracle database, for example. Good thing is: they can see it for themselves. Here's an article about it, in case you want to learn more." ( Read More... | 331 of 465 comments ) Roy PardeeProgrammer/AnalystSWFPAC Lockheed Martin ITExtension 8487
win2k system shutdown scripts--suitable for db shutdown?
Greetings all, I'm reading that win2k supports shutdown scripts (w/the group policy mmc snap-in). Is anyone using these to shutdown their win2k-hosted oracle databases? Right now our netadmins are running shutdown scripts as a manual step, but if it's susceptible of scripting, we'd like to do it that way instead. Are there any gotchas? Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Restricting the range of values in a field
How about:
alter table my_table add (constraint domain_ck check (my_column in ('A',
'B', 'C') ) ) ;
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Thursday, January 30, 2003 8:34 AM
To: Multiple recipients of list ORACLE-L
create or replace trigger my_restrictions on
my_table after insert or update as
declare
unacceptable_values exception;
begin
begin
if :new.my_restricted_column not in ('Blah1','blah2','Blah3') then
--- Grrr .. user errored
spank_user;
raise unacceptable_values;
end if;
end;
exception
when unacceptable_values then
raise_application_error(20001,'You entered incorrect values, go spank
yourself.');
when others then
raise;
end;
/
this should pretty much handle it for you ... don't take this code at its
face value, there could be syntactic errors .. I just wrote on the fly.
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!
-Original Message-
Sent: Thursday, January 30, 2003 11:01 AM
To: Multiple recipients of list ORACLE-L
Is there any way you can specify that the only permissible values (is it
called a domain?) that can be entered in varchar2 field in an Oracle
table to, for example, A, B and C?
We can restrict what values users can enter at the application level,
but it would be nice to be able to also restrict what can be entered at
the database level, in case other means of entering data are ever used
or if the application layer fails, for whatever reason, to trap an
unwanted value.
Thanks
--
Aidan Whitehall [EMAIL PROTECTED]
Macromedia ColdFusion Developer
Fairbanks Environmental Ltd +44 (0)1695 51775
This e-mail has been scanned for all viruses by Star Internet. The
service is powered by MessageLabs. For more information on a proactive
anti-virus service working around the clock, around the globe, visit:
http://www.star.net.uk
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Aidan Whitehall
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: Pardee, Roy E
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: comparing null values
I believe that's expected behavior, arising from the 'null means we don't know *what* the value is--it could be anything' nature of nulls. For all the db knows, those nulls represent unknown values that *are* indeed like '%STU%'. You should get the same result from select * from tester2 where whatever != 'STUFF'. HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, January 30, 2003 9:42 AM To: Multiple recipients of list ORACLE-L 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? _ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus -- 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: Pardee, Roy E 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: Follow-up: It's NOT possible to set role in db's logon
That's a great idea--many thanks. I bet I could put up a table of permitted username/client program combinations just do a SELECT from it translate the no_data_found exception into a 'connect via your program verboten!' message... Thanks again, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Wednesday, January 29, 2003 6:04 AM To: Multiple recipients of list ORACLE-L Hi Roy, I don't know if you solved your problem yet but I have a similar situation here. I have an Oracle account used by PHP programs (third party programs) to access some tables. I don't want anyone to log in to the database with this account unless the connection comes from apache and from our web server machine. So what I did is that I created a logon trigger on that schema and if the conditions are not met, then I raise an application error and the connection dies. As you know, these informations (program, machine, etc...) can be found in v$session and the SID of the current session can be found with select sid from v$mystat where rownum = 1. HTH. Louis At 15:13 2003-01-27 -0800, you wrote: In case anyone cares--it looks like it is *not* possible to set a role in an after logon trigger. Had I only looked at metalink: AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_data b ase_id=NOTp_id=106140.1 Bummer, that. Thanks again to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, January 13, 2003 7:42 AM To: 'ORACLE-L' Greetings all, I'm trying to support a COTS application that is back-end agnostic makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on off in conjunction with users opening closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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). Louis Brouillette Analyste en informatique (DBA) Universite du Quebec a Trois-Rivieres Tel: (819) 376-5011 ext. 2435 Email: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Louis BROUILLETTE 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: Pardee, Roy E 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
Follow-up: It's NOT possible to set role in db's logon trigger
In case anyone cares--it looks like it is *not* possible to set a role in an after logon trigger. Had I only looked at metalink: AFTER LOGON Triggers Don't Allow DBMS_SESSION.SET_ROLE to Keep Roles Enabled http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_datab ase_id=NOTp_id=106140.1 Bummer, that. Thanks again to all who responded. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, January 13, 2003 7:42 AM To: 'ORACLE-L' Greetings all, I'm trying to support a COTS application that is back-end agnostic makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on off in conjunction with users opening closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: Book Requested - Advanced SQL and tuning
It's not oracle-specific, but Celko's _SQL For Smarties_ is a great book on advanced SQL. http://www.amazon.com/exec/obidos/tg/detail/-/1558605762/qid=1043077107/sr=1 -1/ref=sr_1_1/102-5414817-8555301?v=glances=books (pls watch for line-wrap.) Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Sunday, January 19, 2003 3:49 PM To: Multiple recipients of list ORACLE-L Hi All, A developer I work with has asked if there are any books which cover both advanced SQL and SQL tuning in the one title. Does anyone know of such a book? Essentially we have several developers here who know the basics of SQL but want to understand how to write more advanced queries (I guess interesting use of subqueries, decode functions, and some of the lesser used syntaxes like intersect and minus). They also want to learn some basic performance tuning concepts - I guess learning about implicit conversion, the use of hints, and what indexes can and can't be used to achieve might be a good start. Any and all suggestions are welcome. The database can be assumed to be Oracle (currently 8, perhaps 9 in the next year) since most tuning is vendor specific. Thanks, Mark. Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender by reply e-mail or by telephone on (61 3) 9612-6999. Please advise immediately if you or your employer does not consent to Internet e-mail for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of Transurban City Link Ltd shall be understood as neither given nor endorsed by it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mark Richard 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: Pardee, Roy E 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: RE: Important - Oracle Pricing on Standby/DR/Failover dat
I'd argue that the business does get the 'insurance' value of knowing it's got redundancy in place in case something befalls the primary server. But all this talk about the equities of software pricing aside--I believe oracle is legally entitled to charge whatever they like. I think the theory is that we can all move to a competitor if we don't like their pricing. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, January 16, 2003 8:16 AM To: Multiple recipients of list ORACLE-L Tom, I'm going to agree with both of you, but with reservations. When you have a standby database during normal day to day operations, what value added does it provide to your business? Assuming all is well, nothing it's just overhead. Yes Oracle did do a pile of research and development to offer the capability and therefore yes they are due compensation for that, in relation to the amount of added value you extract from that standby. If your like many a shop where you keep the standby for the day when all hell breaks loose on the primary then the license fee I believe should be prorated to the possibility of that happening. If on the other hand you use it as a read-only reporting database all bets are off. Dick Goulet Reply Separator Author: Mercadante; Thomas F [EMAIL PROTECTED] Date: 1/16/2003 5:14 AM Jared, why doesn't it seem right? in the case where we are running a standby database, are we not using the software? sure, the users are not directly connected. but every transaction that they enter in the primary database is being posted to the standby. if we were not required to pay for this standy-by database, how would Oracle get paid for all the development time they put in to offer such a service? seems reasonable to me. as for the failover requirment (10 day limit), Oracle is wrong in this one - the database is always running on one server only. and they (Oracle) have done nothing to offer a better service that has not been already paid for. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, January 15, 2003 11:59 PM To: Multiple recipients of list ORACLE-L Thanks Tony. Looks like Larry E is trying to boost revenues in a down economy by any means necessary. You're right, this doesn't seem right. Jared On Wednesday 15 January 2003 19:08, [EMAIL PROTECTED] wrote: Hi All For those sites with either a standby, DR or failover database, the following information is very important to you. You could be in breach of Oracle's Licensing agreement and could cost you $100,000s if not millions $$ (Read the summary at the end if you want to skip the details) ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still 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: Mercadante, Thomas F 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: 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: Pardee, Roy E INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
RE: Important - Oracle Pricing on Standby/DR/Failover
Didn't the now-infamous State of California deal involve a 3rd-party reseller? Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, January 16, 2003 5:09 AM To: Multiple recipients of list ORACLE-L I just renewed our Cognos support. It took 2 emails and a 10 minute phone call (of which 8 minutes were spent talking football - go Eagles!). Contrast this with our Oracle support negotiations which have been going on since SEPTEMBER! I'll spare you the details, but let's just say that I am extremely frustrated with this licensing subject. I've watched the Software Investment Guide change several times during the last few months. It seems like the rules that you are trying to play by are constantly changing - now I see this reference to Price Hold for named users - where does that come from? I think management would switch to another DB vendor in a heartbeat just so they could understand what they're paying for. Is licensing for those *other* databases just as complicated (not that I advocate an attempted migration, mind you)? The one thing I've learned in this process is to always run your numbers. Our contract said a 10% discount, but the numbers didn't reflect it. Does anybody go through a 3rd party for buying their Oracle support? I know that vendors can resell licenses - can they resell support too? I'm thinking that it might be less of a headache to deal with a vendor than with Oracle. Jay [EMAIL PROTECTED] 01/16/03 12:13AM Hi Jared I have a reply from someone who does not want to be identified. This is his case. His company tried reasoning and discussing it with Oracle and even tried a compromised (which I would not be happy with) He company put forward to Oracle to pay for the full licence on the production server AND the minimum for the standby. In the case the standby was a single CPU and so the minimum licence is a 5 User Licence. This was to account for any DBA connection to check the integrity of the standby database. Even this was not acceptable to Oracle. How greedy can you be? Can you say Gordon Gekko? BTW The following information applies to all You need to know the difference between NAMED USER and NAMED USER PLUS. (extract from SELECT*Star) Gone also is the Named User license. In its place is Named User Plus. Companies wanting to purchase additional user licenses for the same machine will need to convert their Named User licenses to Named User Plus licenses if they do not have a Price Hold on the license. The minimum number of licenses must be the greater of either the actual number of users or the Minimum Named User Plus (25 per CPU) for the server. Minimum Named User (Enterprise Edition) per CPU used to be 10 but now the minimum Named User Plus per CPU is 25. In some instances, customers are forced to buy more licenses than is required when looking for additional licenses. The key difference between Named User and Named User Plus is that Named User does not allow for batch processing whereas Named User Plus does. ta tony At 08:57 PM 15/01/2003 -0800, Jared Still wrote: Thanks Tony. Looks like Larry E is trying to boost revenues in a down economy by any means necessary. You're right, this doesn't seem right. Jared **DISCLAIMER This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of DE except to the extent that it relates to their official business. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jay Hostetter 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: Pardee, Roy E 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
8.1.6: possible to set role in db's logon trigger?
Greetings all, I'm trying to support a COTS application that is back-end agnostic makes only minimal use of security on the db. In particular, it requires that users be granted a default role that has *very* heavy permissions--enough to do some major mischief should they ever figure out how to use odbc or sql*plus. My collegues I have devised a kludgy method for getting around this problem, involving a shill startup program that turns the default-ness of the role on off in conjunction with users opening closing the client program. This works, but is a pain to maintain. I've recently discovered the v$session.program field am now wondering whether it would be possible to use the new-fangled logon system trigger to set the role only for cases where v$session.program = the COTS client. Can anybody comment as to whether this is a viable approach on an 8.1.6 database if not, on a 9i db? In particular, there are two things I don't know--first, how to select just the one row in v$session that corresponds to the current connection. If a user was to start up the COTS client then connect to the same db via sql*plus, I would want the role set *only* for the COTS client session. My best thought so far here is to use the most recently started connection based on v$session.logon_time. Second, whether the SET ROLE statement is legal in a logon trigger. All help will be most welcome. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Pardee, Roy E 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: 8.1.6: possible to set role in db's logon trigger?
Woah--free code! A thousand thanks--this looks really close to what I'd
like to do. If I can wrestle some extra privs on our test db I'll report
back as to whether I was able to get this going on 8.1.6.
Thanks also to Lisa Thomas for responding.
Cheers,
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Monday, January 13, 2003 9:05 AM
To: Multiple recipients of list ORACLE-L
Roy,
this is in 9202 ... check the custom code for hash joins ... it has been
working fine for us for 2 months ...
CREATE OR REPLACE TRIGGER SYSTEM.DBT_USERS_LOGON
AFTER LOGON ON DATABASE
--
DECLARE
CURSOR cur_sess IS
SELECT *
FROM v$session
WHERE AUDSID = USERENV('SESSIONID')
AND USERNAME NOT IN ('HEARTBEAT');
--
recSess cur_sess%ROWTYPE;
--
PRAGMA AUTONOMOUS_TRANSACTION;
--
BEGIN
OPEN cur_sess;
FETCH cur_Sess INTO recSess;
CLOSE cur_sess;
--
INSERT INTO USER_LOGON_AUDIT
(SESS_AUDSID, DB_USER, OS_USER, TERMINAL, PROGRAM,
TRIGGER_EVENT,LOGON_TIME, LOGOFF_TIME)
VALUES
(USERENV('SESSIONID'), UPPER(ora_login_user), UPPER(recSess.osuser),
recSess.terminal, recSess.program, ORA_SYSEVENT, SYSDATE, NULL);
COMMIT;
--
IF UPPER(ORA_LOGIN_USER) = 'AFF_QUERY' AND
UPPER(recSess.machine) = 'IMAPPROD1' THEN
EXECUTE IMMEDIATE 'alter session set hash_join_enabled=false';
END IF;
--
EXCEPTION
WHEN OTHERS THEN
NULL;
END DBT_USERS_LOGON;
/
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.net
--
Author: Pardee, Roy E
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).
misc security questions
Greetings all, I'm a (fairly green) dev trying to define roles db object perms in Designer after doing some reading (chapters in FM oracle complete reference) still have some questions I thought y'all might be able to help me with. My db is version 8.1.6. Here they are: Do I need to grant users privs on tables referenced in an EXCEPTIONS INTO clause on a constraint? Do I need to grant EXECUTE privs on *both* a package and it's constituent procs/functions? If not, what are the implications of doing one or the other? If I understand things properly, a user is not able to create a view that references a table in another schema if the user has the SELECT priv only through a role--they've got to be granted that SELECT priv personally (right?). Are there any other privs that have to be granted to personally like this? (Bonus question: what is the rationale for this requirement?) Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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: FORMS - text_io - dynamic formating
Don't the text_io procs work with just plain text files? I wouldn't think those would support persistant formatting per se. But maybe you could write html tags inline w/your data values apply formatting that way (tho there's likely some other built-in more tailored to spitting out html and/or xml). hth, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, August 01, 2002 10:38 AM To: Multiple recipients of list ORACLE-L Hi I have used Text_io to export a datablock to csv, is there a way i can automatically set the text once in csv to Bold, or even change the colour? __ Do You Yahoo!? Yahoo! Health - Feel better, live better http://health.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Imran Ashraf 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: Pardee, Roy E 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: IN() question
Does this mean that you can't include Null as a value in an IN() clause?
That is, would something like:
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND pay_METHOD IN (Null, 'C','P');
count rows where pay_METHOD is Null? (Disregarding for the moment the
problem Lisa was actually trying to solve.)
Thanks!
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Friday, May 03, 2002 5:33 PM
To: Multiple recipients of list ORACLE-L
In any equivalence operation (which includes non-equivalence, too), NULL
never returns TRUE -- it just returns NULL which non-TRUE has the same
result as FALSE. Only IS and IS NOT operators can be used to evaluate
NULLs...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, May 03, 2002 5:43 PM
Slap me if this is a dumb question.
Here's my pay methods
SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*)
2 FROM LEISURE_PLAN_MASTER_TEMP
3 WHERE MEMBERSHIP_CLASS = 'D'
4 GROUP BY PAY_METHOD;
ASCII(PAY_METHOD) P COUNT(*)
- - --
67 C 42955
80 P 34373
11786
I expected this statement to return the 11,786 records that have null
values. However, it doesn't:
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND pay_METHOD NOT IN ('C','P');
COUNT(*)
--
0
But when I do this, I get the answer I expect.
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND PAY_METHOD IS NULL;
COUNT(*)
--
11786
This isn't exactly correct. There may be other values in this field, and
if
they show up I need to include them, not just records where this field is
null. What am I missing? Is it because the value is NULL that Oracle
excludes it from the IN() statement, because of the classic definition of
NULL (can't be defined, therefore can't be sure it's not a C or a P)?
This is easy enough to fix, I'll change my data load to populate the null
values with my own code. But still? Have I got the WHY correct?
Thanks for any light someone can shed on this stupid question.
Lisa Koivu
Oracle Database Monkey Mama
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Koivu, Lisa
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: Tim Gorman
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: Pardee, Roy E
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: IN() question
Dang, you're right--I just now tested it. I knew that MY_COL = NULL isn't
evaluable--I'm not sure why I thought IN() would act differently.
I guess it would be whiny of me to wish that I'd get a warning or error from
the db when issuing such statements...
Thanks!
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Monday, May 06, 2002 10:26 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Yes, that's what it means.
Using NULL in IN() would imply that NULL has equality with something.
NULL is never equal to anything. NULL can only be checked with
'IS NULL' , 'IS NOT NULL', and in DECODE() statements.
Jared
Pardee, Roy E [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/06/2002 09:53 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: IN() question
Does this mean that you can't include Null as a value in an IN() clause?
That is, would something like:
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND pay_METHOD IN (Null, 'C','P');
count rows where pay_METHOD is Null? (Disregarding for the moment the
problem Lisa was actually trying to solve.)
Thanks!
-Roy
Roy Pardee
Programmer/Analyst
SWFPAC Lockheed Martin IT
Extension 8487
-Original Message-
Sent: Friday, May 03, 2002 5:33 PM
To: Multiple recipients of list ORACLE-L
In any equivalence operation (which includes non-equivalence, too), NULL
never returns TRUE -- it just returns NULL which non-TRUE has the same
result as FALSE. Only IS and IS NOT operators can be used to evaluate
NULLs...
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, May 03, 2002 5:43 PM
Slap me if this is a dumb question.
Here's my pay methods
SQL SELECT ASCII(PAY_METHOD), PAY_METHOD, COUNT(*)
2 FROM LEISURE_PLAN_MASTER_TEMP
3 WHERE MEMBERSHIP_CLASS = 'D'
4 GROUP BY PAY_METHOD;
ASCII(PAY_METHOD) P COUNT(*)
- - --
67 C 42955
80 P 34373
11786
I expected this statement to return the 11,786 records that have null
values. However, it doesn't:
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND pay_METHOD NOT IN ('C','P');
COUNT(*)
--
0
But when I do this, I get the answer I expect.
SQL SELECT COUNT(*)
2 from leisure_plan_master_temp
3 where membership_class = 'D'
4 AND PAY_METHOD IS NULL;
COUNT(*)
--
11786
This isn't exactly correct. There may be other values in this field,
and
if
they show up I need to include them, not just records where this field
is
null. What am I missing? Is it because the value is NULL that Oracle
excludes it from the IN() statement, because of the classic definition
of
NULL (can't be defined, therefore can't be sure it's not a C or a P)?
This is easy enough to fix, I'll change my data load to populate the
null
values with my own code. But still? Have I got the WHY correct?
Thanks for any light someone can shed on this stupid question.
Lisa Koivu
Oracle Database Monkey Mama
Fairfield Resorts, Inc.
5259 Coconut Creek Parkway
Ft. Lauderdale, FL, USA 33063
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Koivu, Lisa
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: Tim Gorman
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: Pardee, Roy E
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
RE: Oracle - Access
Can you show us your code, call out the line that returns the error and give the exact error # message? Also, consider moving this to an msaccess list. One good one can be found at http://peach.ease.lsoft.com/archives/access-l.html Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, May 03, 2002 7:49 AM To: Multiple recipients of list ORACLE-L Hallo, anyone who has a good hint on this: I have an appplication which goes against Oracle and when I am inthe application and run VBA code which connects to linked tables everything works fine. But when I have an icon on the desktop and runs that icon as shortcut then I get error messagelike table not exist, but it really exists and it is the same code running both times. 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: Pardee, Roy E 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 help
This should get you a list of the combos of ID, Company Country that are repeated in the table: warning--air SQL! SELECT ID, Company, Country, COUNT(*) NumRecs FROMmy_table GROUP BY ID, Company, Country HAVING COUNT(*) 1 /warning--air SQL! Or if you need all the records that belong to repeated combos of ID, Company Country, you could say something like: warning--air SQL! SELECT t.* FROM my_table t, (SELECT ID, Company, Country, COUNT(*) NumRecs FROM my_table GROUP BY ID, Company, Country HAVING COUNT(*) 1) sq WHERE t.ID = sq.ID AND t.Company = sq.Company AND t.Country = sq.Country /warning--air SQL! Maybe that would suit? HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 29, 2002 3:12 PM To: Multiple recipients of list ORACLE-L Hello all, I need some SQL help .. I have a table with containing duplicate records but because they have differents status they really are duplicate .. i need to find these .. here is an example of what the table contains : IDCompany Country Status 5521 ABC US 1 5521 ABC US -1 8877 DEF UK 0 8877 DEF UK 1 I want to pull the records where all the columns are the same except for the status column . Any help is greatly apprecieted K _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: k k 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: Pardee, Roy E 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: Ms Access user forum
ACCESS-L is a good one. Here are particulars: -- The ACCESS-L list is hosted on a Windows NT(TM) machine running L-Soft international's LISTSERV(R) software. For subscription/signoff info and archives, see http://peach.ease.lsoft.com/archives/access-l.html . COPYRIGHT INFO: http://peach.ease.lsoft.com/scripts/wa.exe?SHOWTPL=COPYRIGHTL=ACCESS-L Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, April 26, 2002 7:13 AM To: Multiple recipients of list ORACLE-L Hallo, any one whom knows how where to find a good MsAccess user forum where I can ask questions? 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: Pardee, Roy E 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: Ms Access user forum
Is this just an expression of bewilderment, or is it unix-ese for 'you can ask right here in this forum' (and you feel strongly about that)? 8^) -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, April 26, 2002 7:44 AM To: Multiple recipients of list ORACLE-L .! -Original Message- Sent: 26 April 2002 15:13 To: Multiple recipients of list ORACLE-L Hallo, any one whom knows how where to find a good MsAccess user forum where I can ask questions? Thanks in advance Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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: How can we make any column of a table as case insensitive
It's not clear to me that putting the data in uniform case (e.g., all upper or all lower) will be equivalent to making it case-insensitive. Unless you can count on the criteria applied to the column always being in that same case, you could still have 'false' mismatches. The only thing that comes to my mind is rolling your own querying app--I don't suppose that appeals? But probably somebody else will have a better idea... -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, April 16, 2002 11:48 AM To: Multiple recipients of list ORACLE-L Create a view where you select the case-insensitive columns with upper(col-name), then create a public synonym on the view. The application will see the table through the view. Mandal, Ashoke To: Multiple recipients of list ORACLE-L ashoke.k.man[EMAIL PROTECTED] dal cc: @medtronic.coSubject: How can we make any column of a m table as case insensitive Sent by: root 04/16/2002 12:33 PM Please respond to ORACLE-L Greetings All, We have a requirement of making one or multiple or all columns of a table case insensitive. We can not modify the query as it comes from a 3rd party application. One option I can think of is that introduce a trigger on the table and before inserting or modifying the column's data force it to be all uppercase or all lowercase. Is there any other way of doing it? Can we do it using a constraint? This requirement is like the feature of ic(ignore case) in vi editor. Thanks, Ashoke -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mandal, Ashoke 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: Pardee, Roy E 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: Fairly Boring News Article on Oracle
Why, in-house developed applications, of course. Tailored to the requirements of your business by real people who actually asked you what your requirements are... 8^) Here's Oracle's take on web services: http://otn.oracle.com/tech/webservices/content.html -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, April 09, 2002 7:08 AM To: Multiple recipients of list ORACLE-L If people aren't using SAP, Oracle Applications or PeopleSoft, what are they using? PlumTree portals? Is that what they mean by Web services? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- Sent: Tuesday, April 09, 2002 9:53 AM To: Multiple recipients of list ORACLE-L http://www.infoworld.com/articles/ap/xml/02/04/08/020408aporacle.xml -Original Message- Sent: Tuesday, April 09, 2002 7:23 AM To: Multiple recipients of list ORACLE-L I think this is good news, Oracle accepting these user groups. It's probably the most cost-efficient, effective way for Oracle to get honest feedback on how to improve their products. The better their products, the more successful they will be it seems to me. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- Sent: Tuesday, April 09, 2002 4:28 AM To: Multiple recipients of list ORACLE-L http://story.news.yahoo.com/news?tmpl=storycid=581ncid=738e=2u=/nm/20020 409/tc_nm/tech_oracle_dc_7 Cheers, JoJo -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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: Fairly Boring News Article on Oracle
No way! I'd say that web services are a collection of standards that allow HTTP-borne remote procedure calls between clients and servers. It's all XML objects under the skin, if I understand it correctly. The theory is that neither client nor server need to run any specific brand of software, nor know about things like what OS is running on either end, etc. Servers just have to be able to listen for HTTP requests, act on them respond with XML streams of the expected format. Everybody codes to these standards and boom--instant firewall-permeable interoperability for all. So the server could be as you specify below, or could be IIS using .Net, or anything else that will emit properly formatted XML streams. Clients can be anything that can make sense of the server's output stream--Java, custom coded C++, or what-have-you. But I'm probably wrong about at least some of that... Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Tuesday, April 09, 2002 9:23 AM To: Multiple recipients of list ORACLE-L I read the info quickly, so Web services are Java apps served on iAS through Portal, Wireless Portal, or Apache. Would that be correct, in a nutshell? Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Boivin, Patrice J 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: Pardee, Roy E 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: pl/sql statement
If you've declared the variable usercnt_tmp, then you should be fine if you just leave out the from dual bit and re-order the statements, e.g., select count(*) intousercnt_tmp fromprod.consenid ; hth, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 08, 2002 9:57 AM To: Multiple recipients of list ORACLE-L How can I do something like this; select count(*) from prod.consenid into usercnt_tmp from dual I want to send the amount of COUNT(*) into a variable. I get the following error ORA-06550: line 5, column 36: PLS-00103: Encountered the symbol INTO when expecting one of the following: . , @ ; for an identifier a double-quoted delimited-identifier group having intersect minus order start union where connect ORA-06550: line 6, column 1: PLS-00103: Encountered the symbol END Is this something that I can do. I am probably overlooking something very obvious. Thanks, Dave -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farnsworth, Dave 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: Pardee, Roy E 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 Tuning - How to avoid TOCHAR function against a date
Could you maybe calculate a range of date values that encompasses the period you want and use BETWEEN on the raw date column? I'm thinking something along the lines of: SELECT DATE_KEY FROM DATE_DIM WHERE ORACLE_DATE BETWEEN TRUNC(:b1) AND TRUNC(:b1) + .9 ; but like, more elegant. 8^) HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, April 08, 2002 10:57 AM To: Multiple recipients of list ORACLE-L I've got the following SQL statement that is running very long on a nightly data load. The problem is the TO_CHAR function which is preventing me from using the index on this small (20,000-row table). This is an 8.0.4 database so it is not possible for me to use make this a function-based index. The problem is that the date field has minutes, etc. included and those need to be eliminated before the comparison can be made. That's why I can't just eliminate the TO_CHAR from both sides of the equation. Isn't there a way that I can pull this function out of the select statement and do it in a preceeding statement? Then I could just pass in both variables to this statement without the TO_CHAR and use my index. Is this realistic? How, exactly could it be done? SELECT DATE_KEY FROM DATE_DIM WHERE TO_CHAR(ORACLE_DATE,'DD-MON-') = TO_CHAR(:b1,'DD-MON-') SQL desc date_dim; NameNull?Type --- DATE_KEYNOT NULL NUMBER(5) ORACLE_DATE NOT NULL DATE DATACOM_DATE NUMBER(6) DATACOM_REVERSE_DATE NUMBER(6) DAY_OF_WEEK NOT NULL VARCHAR2(30) DAY_NUMBER_IN_MONTH NOT NULL NUMBER(3) DAY_NUMBER_OVERALL NOT NULL NUMBER(9) WEEK_NUMBER_IN_YEAR NOT NULL NUMBER(3) WEEK_NUMBER_OVERALL NOT NULL NUMBER(7) MONTH NOT NULL VARCHAR2(30) MONTH_NUMBER_OVERALLNOT NULL NUMBER(7) YEARNOT NULL NUMBER(5) WEEKDAY_IND NOT NULL CHAR(1) LAST_DAY_IN_MONTH_IND NOT NULL CHAR(1) DATA_WAREHOUSE_MOD_DATETIME NOT NULL DATE DATA_MART_MOD_DATETIME NOT NULL DATE SQL select oracle_date from date_dim where rownum=1; ORACLE_DA - 01-JAN-70 Thanks in advance for any help. Cherie Machler Oracle DBA Gelco Information Network -- 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: Pardee, Roy E 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).
Designer's Table API: Threat or Menace?
Greetings all, Is anyone out there using Oracle Designer's Table API? We're trying to decide whether to use it as opposed to writing our own packages triggers (for things like populating sequence-generated PKs, upcasing VARCHARs, enforcing domains, etc.). From my (admittedly selfish) perspective, any working line of code that I don't have to write/support is a good line of code, but it does look like a *lot* of code to do not so very much. Our DBA is concerned that it's creating unnecessary triggers, will suck down resources unecessarily, server will grind to a halt, etc. My concern is that we'll find out that the TAPI procs are bulky complicated for a reason--we'll decide to chuck them then wind up reinventing them (and maybe not as well as the folks at Oracle). Has anybody out there been over this ground care to make a reccomendation? Thanks! -Roy P.S. In case it's important, we're using Designer 6.0 to create OAS-deployed Oracle Forms Reports to run against a 9i db. Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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: Off Topic: PGP
NT freeware can be found at: http://web.mit.edu/network/pgp.html But be warned--I've heard some horror stories about the NT install. I've never had problems myself, but know of cases where machines have been rendered inoperable... HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, March 22, 2002 7:03 AM To: Multiple recipients of list ORACLE-L Does anyone know of any good PGP implementations for WinNt or Openvms? Are there any free ones? TIA, Beth -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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).
refer to SYSDATE function in a CHECK constraint--doable?
Greetings all, I've got a table with a date field on a 8.1.6.0 db. I'd like to constrain the values entered into this field to be less than or equal to the date on which the record was entered. I figured a table-level check constraint would serve so I ran: ALTER TABLE TLB_COPIES ADD (CONSTRAINT TLBCPY_DATE_LAST_LABEL_CK CHECK (DATE_LAST_LABEL = SYSDATE) EXCEPTIONS INTO COMN_EXCEPTIONS) And Oracle complained thus: ADD (CONSTRAINT TLBCPY_DATE_LAST_LABEL_CK CHECK (DATE_LAST_LABEL = SYSDATE) * ERROR at line 2: ORA-02436: date or system variable wrongly specified in CHECK constraint I've also tried using TO_DATE(SYSDATE) after running accross that expression in some code examples in the docs (does SYSDATE not return a date type?), but get the same error. I've searched through metalink for this error, but everything I've found deals with how you've got to to_date() a date literal (e.g., no implicit conversions) and be sure to use a full four-digit year (as of 8.x I think). But I'm not seeing anything that refers to the sysdate function--is it just not allowed? I'm pretty sure you can use it as a DEFAULT... Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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: Excel to Oracle
One way that you might consider is going through MS Access ODBC. Link your Oracle tables via ODBC; import the excel data, and then point-n-click up some Access action queries (aka INSERT statements). HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, March 18, 2002 7:44 AM To: Multiple recipients of list ORACLE-L Hi All, What is the best/quickest way to move data from excel to Oracle? Thanks Rick -- 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: Pardee, Roy E 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
Can you post the SQL? What version of the DB are you running it against? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, March 14, 2002 3:33 AM To: Multiple recipients of list ORACLE-L Hi, Please can anyone let me know on how to deal with the problem where you want to outer join a table to more than 1 tables, at the moment i get error , ORA-01417: a table may be outer joined to at most one other table. Is there another way the results can be achieved? 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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: help with a SQL self-join
If I understand correctly, it sounds like you need a *recursive* self-join. Depending on the version of your db (certainly in 8i or later) you may be able to use the CONNECT BY PRIOR syntax to get what you're after. Have a look at the SQL reference for 'hierarchical queries'. Here's a link to the 9i docs: http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90125/qu eries2.htm#2053937 (please watch for line wraps) for reference. HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, March 01, 2002 10:53 AM To: Multiple recipients of list ORACLE-L I need help with a self-join. I have two tables: DEPT_TBL and TREENODE. One table holds dept data. Depts report to one another at at different levels, and are rolled-up for different purposes. This roll-up information is stored in the TREENODE table. Each tree_node in TREENODE has a corresponding PARENT_TREE_NUM, which corresponds to it's roll-up level. DEPT_TBL holds dept ID and dept description, but no level information. TREENODE holds tree_descriptions. The a.deptid = b.tree_node, so that's a possible join. Question: How do I pull a dept ID and it's corresponding roll-up node/deptid? This is what I have so far; select DISTINCT a.tree_node DEPT, b.tree_node COLL from treenode a, treenode b where a.tree_node b.tree_node and a.tree_name = 'DEPT_SECURITY' and a.tree_node = 'H0086' and a.parent_node_num = b.parent_node_num; this pulls a dept and all the depts that roll-up at the same level, but not the roll-up level. For example, Dept 'H0086', and 27 depts that rollup at the same level. I simply want a single row with two columns: a deptID, and a rollup ID. How do I do this? Any help is really appreciated. Thanks-- Edward Lock [EMAIL PROTECTED] _ MSN Photos is the easiest way to share and print your photos: http://photos.msn.com/support/worldwide.aspx -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Edward Lock 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: Pardee, Roy E 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: LIKE and % operator
Are the results any different if you say select * from sonusrpt where subject like '%GENERAL%'; ? Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, March 04, 2002 12:33 PM To: Multiple recipients of list ORACLE-L I am running oracle8i on solaris8. I have a word ( General ) in my column named subject, I try to run SQL using LIKE and % to grep any data having the word ( General ) but it displayed no rows selected. Does someone have any idea why? Below is my SQL I used. SQL select * from sonusrpt where subject like '%general%'; no rows selected. Thanks, David -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Nguyen, David M 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: Pardee, Roy E 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: Tangetially ON Topic... ANSI SQL and Reusing SQL to avoid har
Perhaps this page will be of use? http://developer.mimer.com/validator/index.htm Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Monday, February 25, 2002 10:33 AM To: Multiple recipients of list ORACLE-L har Steve: To my knowledge the usage of bind variables is ansi 92 compliant. DB2 (my past life) it was a requirement for proper application development. MySQL uses something like a memory cache to eliminate re-parsing. Thank You Stephen P. Karniotis Technical Alliance Manager Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Monday, February 25, 2002 12:49 PM To: Multiple recipients of list ORACLE-L Subject:Tangetially ON Topic... ANSI SQL and Reusing SQL to avoid hard pa Since V7 Oracle has improved performance by limiting hard parses and implementing shareable, reuseable SQL via the shared pool. I'm curious what other database engines do to limit hard parses. Does DB2, Informix, Sybase, SQLServer, or Postgres implement shareable SQL via a shared pool? Of course we know that in Oracle, shareable SQL is dependent on the use of bind variables or cursor_sharing=FORCE. In that context, isn't the syntax for bind variables part of the ANSI SQL92 standard? I really need to find this out as I'm building the case for using bind variables in an ANSI SQL, multi-database development effort. TIA!!! Steve Orr Bozeman, MT -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Orr, Steve 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: Karniotis, Stephen 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: Pardee, Roy E 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: Compare, Merge and replicate between Oracle and MS Access
Can you not substitute say, Oracle Lite for Access on the 'downstream' sites use Oracle's native replication? (Or keep Access as a front-end, but use OL for the actual storage the replication.) I would guess you'd be in for less work/trouble that way (tho I know zip about Oracle's replication features). If you can't, I would try to find a third-party tool that can do it automagically (http://www.aardvark.on.ca/pd/replicate.html claim to have such a solution). If that fails, consider just doing a brute-force complete update of the Access db have your downstream sites FTP the entire new mdb file over top of the old version. HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Friday, February 22, 2002 11:54 AM To: Multiple recipients of list ORACLE-L James can you tell us more about the requirements of your environment/software that would cause such a solution to arise. I can see the possibility of writing a MS Access client that perhaps your sales staff uses that replicates with the database. Hopefully, you don't have too many tables. You really are not going to have a lot of success I think using anything but a PL/SQL and VBA solution in this regard. - Ethan -Original Message- Sent: Friday, February 22, 2002 1:24 PM To: Multiple recipients of list ORACLE-L Are you serious? Replication between Oracle and MS Access, why? -Original Message- Sent: Friday, February 22, 2002 2:09 PM To: Multiple recipients of list ORACLE-L Hi DBAs, I am researching for solution for a new project. We have a few tables in Oracle8i db, similar tables with more columns and more data in MS Access. The data is very static. Our plan is to synchronize the two dbs, from then on we will always put change(adding more columns, or insert new data) in Oracle first, then replicate the change to MS access db in batch process. I am looking for solutions 1) to compare the existing data in Oracle and MS Access 2) to merge the data into Oracle 3) to maintain the two db on ongoing basis. periodically check if they are in synch, then replicate data to MS access. Is there any tool to do the job? any ideas for solution? One solution I thought about is to use MS DTS to put Oracle into Access, then compare. TIA James -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan 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: Pardee, Roy E 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: Options instead of ODBC + MS-Access [Slightly? Off-topic]
You might want to make sure your developer is hip to Access' pass-through queries is thinking straight about when it makes sense to do calculations in Access, as opposed to having them done on the server. Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 5:53 AM To: Multiple recipients of list ORACLE-L Sean - I think that Access tends to be a wonderfully cheap and easy Oracle front-end for exactly the situation you described. Naturally, if your site has standardized on a different tool, then it isn't good, or if the staff is highly competent in another tool. I think the developer's concern is an excellent opportunity for you to introduce the subject of scalability. Think about it. The scalability problem in this situation isn't in Access, but in the Oracle data model and in the SQL statements that Access issues (okay technically that part is in Access). If the data model is well-designed and the SQL statements aren't doing something like full-table scans, then it should scale well. Okay, the other gotcha might be if the size of the data you are extracting from Oracle will eventually overwhelm Access. If someone else on the list knows any other Access points of concern, perhaps they will share them. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, February 21, 2002 6:48 AM To: Multiple recipients of list ORACLE-L A developer here has put together a reporting package which uses ODBC to interface to an 8.1.7 DB on W2K server and utilises MS-Access on client to extract data and generate nice GUI final presentation of data. The data extracted has various computations performed. The developer has asked what other Oracle or 3rd party options might be used to realise the same end results perhaps in a more efficient manner. Their concern is that as data volumes grow the performance will degredate substantially. Anyone any ideas?. - Seán O' Neill Organon (Ireland) Ltd. [subscribed: digest mode] This message, including attached files, may contain confidential information and is intended only for the use by the individual and/or the entity to which it is addressed. Any unauthorized use, dissemination of, or copying of the information contained herein is not allowed and may lead to irreparable harm and damage for which you may be held liable. If you receive this message in error or if it is intended for someone else please notify the sender by returning this e-mail immediately and delete the message. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: O'Neill, Sean 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: Pardee, Roy E 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: Anybody against using views?
It seems to me that you're not dinging views per se here--you're against the dev's intended use of production data. So if those same SELECT statements that make up the view were instead baked into the crystal report file sent anew every time the report was executed, it'd be the same problem (maybe worse, since now you're parsing the SQL planning execution more frequently?). You buy that? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 9:09 AM To: Multiple recipients of list ORACLE-L IMO views are often used as a substitute for creating reporting structures. Using views makes for easy report/SQL creation, but tends to be a tuning and performance nightmare. It's hard to tune, and will likely never perform well. I'm going through similar issues here right now. A number of users need to do reporting on production data. No way, no how will they be allowed to do it on the production database. It's a manufacturing database and performance is critical to this system. I've done some prototypes of the tables they need to report on. Basically a copy of the production tables in another database. Those that have a long refresh cycle ( 1+ days ) get bitmap indexes on most columns. Those that need to be close to realtime get Btree indexes instead and will be refreshed every few minutes ( refresh time pending negotiation with users :). This is not exactly a data mart as I would like to have it: no star schemas. But it's what I have time for right now, gets the reports off of production and is *much* faster to query. HTH Jared Smith, Ron L. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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: Pardee, Roy E 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: Anybody against using views?
You use that word like it's a *bad* thing to be. 8^) -Roy (Who was originally tempted to say: Look DBA, that SQL's coming to your server--we can do it easy, or we can do it hard, but it's coming. Do you want to have to sleuth out why your db is dog-slow every day at 3:30 when my users are running the report I gave them, or do you want to see what I'm planning to do up front have a chance to kibbitz? But who also knows better than to say things like that to the DBA.) Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 10:59 AM To: Multiple recipients of list ORACLE-L uh-oh... a PROGRAMMER has been lurking... :) -Original Message- Sent: Thursday, February 21, 2002 1:24 PM To: Multiple recipients of list ORACLE-L It seems to me that you're not dinging views per se here--you're against the dev's intended use of production data. So if those same SELECT statements that make up the view were instead baked into the crystal report file sent anew every time the report was executed, it'd be the same problem (maybe worse, since now you're parsing the SQL planning execution more frequently?). You buy that? Cheers, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 21, 2002 9:09 AM To: Multiple recipients of list ORACLE-L IMO views are often used as a substitute for creating reporting structures. Using views makes for easy report/SQL creation, but tends to be a tuning and performance nightmare. It's hard to tune, and will likely never perform well. I'm going through similar issues here right now. A number of users need to do reporting on production data. No way, no how will they be allowed to do it on the production database. It's a manufacturing database and performance is critical to this system. I've done some prototypes of the tables they need to report on. Basically a copy of the production tables in another database. Those that have a long refresh cycle ( 1+ days ) get bitmap indexes on most columns. Those that need to be close to realtime get Btree indexes instead and will be refreshed every few minutes ( refresh time pending negotiation with users :). This is not exactly a data mart as I would like to have it: no star schemas. But it's what I have time for right now, gets the reports off of production and is *much* faster to query. HTH Jared Smith, Ron L. [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 02/21/02 08:18 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Anybody against using views? We have several applications that use views extensively. On the other hand there are several apps that use no views at all. We have a new developer who wants to use views when writing reports in Crystal Reports. The application administrator is leery of using views and ask the DBA group what we think. I can see several reasons to use views and a few reasons not to use them. I was just wondering what the rest of the group thought. Ron Smith DBA Kerr-McGee Corp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Smith, Ron L. 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: Pardee, Roy E 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
add index to a unique-constrained column--how come?
The Oracle9i Database Administrator's Guide says: Creating a Unique Index Explicitly Indexes can be unique or nonunique. Unique indexes guarantee that no two rows of a table have duplicate values in the key column (or columns). Nonunique indexes do not impose this restriction on the column values. Use the CREATE UNIQUE INDEX statement to create a unique index. The following example creates a unique index: CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx; Alternatively, you can define UNIQUE integrity constraints on the desired columns. Oracle enforces UNIQUE integrity constraints by automatically defining a unique index on the unique key. This is discussed in the following section. However, it is advisable that any index that exists for query performance, including unique indexes, be created explicitly (See it at http://download-west.oracle.com/otndoc/oracle9i/901_doc/server.901/a90117/in dexes.htm#10069) If there's already an index there for the constraint, why do we want an additional one? Does it take up space? Will the implicit (is that the right word?) index not be used in queries if you don't also create an explicit one? Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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).
Call PL/SQL from MsAccess
Here's some code that sends two parameters--you should be able to adapt it to your needs. I'm using ADO 2.6. == S u b RunOracleSP() Dim cmd As ADODB.Command Dim con As ADODB.Connection Dim prm As ADODB.Parameter Dim Network As Object Set cmd = New ADODB.Command Set con = New ADODB.Connection Set Network = CreateObject(WScript.Network) With con .ConnectionString = Provider=MSDAORA.1; _ Password=***; _ User ID=rpardee; _ Data Source=devl; _ Persist Security Info=False .Open End With With cmd Set .ActiveConnection = con .CommandType = adCmdStoredProc .CommandText = RPARDEE.LOGLOGIN Set prm = .CreateParameter(M_NAME _ , adVarChar _ , adParamInput _ , 4000) .Parameters.Append prm Set prm = .CreateParameter(U_NAME _ , adVarChar _ , adParamInput _ , 4000) .Parameters.Append prm .Parameters(M_NAME).Value = Network.ComputerName .Parameters(U_NAME).Value = Network.UserName .Execute , , adAsyncExecute End With Set Network = Nothing Set cmd = Nothing con.Close Set con = Nothing End Sub == HTH, -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -Original Message- Sent: Thursday, February 14, 2002 3:43 AM To: Multiple recipients of list ORACLE-L Hallo, anyone whom can give me an example onhow to write an MsAccess procedure which make a call to a pl/sqlprocedure. I want the MsAccess procedure send 5 parametsrs through to pl/sql. 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: Pardee, Roy E 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).
'index only' table vs. 'index organized' table
Greetings all, Can anybody enlighten me as to whether these things are the same or different? In Designer (v 6.0), I'm seeing a spot to specify a 'percent threshold' and 'overflow tablespace' for my table, under the heading Index Only Tables. I've heard of index-organized tables, but never 'index only' tables. The help file has been unavailing. Thanks! -Roy Roy Pardee Programmer/Analyst SWFPAC Lockheed Martin IT Extension 8487 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pardee, Roy E 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).
