Re: SqlNet Response ports
Thank you very much for the information Don. We are using 9i and I think MTS, though I am not certain of that. I will pass this on to our DBA, I am sure he will find this helpful. At 06:03 PM 2/24/2003 -0800, you wrote: I guess I should have asked also: 1) what version of Oracle are you using? 2) Is this using MTS or dedicated servers? Since this is Linux, there are three cases: 1) Dedicated severs - there is no redirection. Client talks to dedicated server on listener port. 2) MTS Oracle 9i - ditto 3) MTS pre-9iR2 - MTS does redirect by default 0 to some (pseudo-)random port above 1024 but this may be overridden by adding a pfile (init.ora) entry like: mts_dispatchers=(address=(protocol=tcp)(host=yourhostname)(port=443))(dispa tchers=1) See: Metalink Bulletin: 1016349.102 Note: 163082.999 Go to advanced search and search on Doc ID with these IDs. There may be multiple mts_dispatchers= lines in the init file and there may be other parameters of interest. For the sake of this particular issue though, the pertinent item is the (port=443) clause. It would force MTS redirects to port 443. Actually, this parameter is obsoleted by the initiiation parameter: local_listener=listener_name_alias where tnsnames.ora has an entry like: listener_name_alias=(address=(protocol= tcp)(host= yourhostname)(port= 443))... and other appriopriate MTs initialization parameters. Please see the documentation for your version for options. Good luck! Don Granaman certified OraSaurus - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Monday, February 24, 2003 12:39 PM Redhat Linux 7.2 At 07:28 PM 2/21/2003 -0800, you wrote: What platform is this? Windows? Don Granaman OraSaurus - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, February 21, 2003 5:48 PM Hi listers, Here is a question my client asked me, that I can't seem to find the answer to on metalink, mostly because I am not sure how to frame the search query. If you don't know what NMCI is, just know that it is a government program that is establishing control over the network which our database servers are on. Anyone have any ideas or advice for me? TIA, Regina Then, one of the NMCI technical guys mentioned that NMCI PCs wouldn t allow inbound/outbound 1521 traffic (SQLNET). Hence, no NMCI user would be able to use any client/server application. Wow, that s a major problem. There are a few possible solutions. 1. Configure the database server to listen on port 443, because NMCI allows 443. But, SQLNET uses random high order ports on the return. Anybody know if you can configure SQLNET to use only 443 on the response? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regina Harter 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: Don Granaman 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: Regina Harter 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: Don Granaman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http
Re: SqlNet Response ports
Redhat Linux 7.2 At 07:28 PM 2/21/2003 -0800, you wrote: What platform is this? Windows? Don Granaman OraSaurus - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, February 21, 2003 5:48 PM Hi listers, Here is a question my client asked me, that I can't seem to find the answer to on metalink, mostly because I am not sure how to frame the search query. If you don't know what NMCI is, just know that it is a government program that is establishing control over the network which our database servers are on. Anyone have any ideas or advice for me? TIA, Regina Then, one of the NMCI technical guys mentioned that NMCI PCs wouldn t allow inbound/outbound 1521 traffic (SQLNET). Hence, no NMCI user would be able to use any client/server application. Wow, that s a major problem. There are a few possible solutions. 1. Configure the database server to listen on port 443, because NMCI allows 443. But, SQLNET uses random high order ports on the return. Anybody know if you can configure SQLNET to use only 443 on the response? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regina Harter 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: Don Granaman 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: Regina Harter 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).
SqlNet Response ports
Hi listers, Here is a question my client asked me, that I can't seem to find the answer to on metalink, mostly because I am not sure how to frame the search query. If you don't know what NMCI is, just know that it is a government program that is establishing control over the network which our database servers are on. Anyone have any ideas or advice for me? TIA, Regina Then, one of the NMCI technical guys mentioned that NMCI PCs wouldn t allow inbound/outbound 1521 traffic (SQLNET). Hence, no NMCI user would be able to use any client/server application. Wow, that s a major problem. There are a few possible solutions. 1. Configure the database server to listen on port 443, because NMCI allows 443. But, SQLNET uses random high order ports on the return. Anybody know if you can configure SQLNET to use only 443 on the response? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regina Harter 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).
To Anyone involved in Web apps
Hi I have a question for any of you involved in Web applications. I would like to know how many of you go for the single Oracle user for everyone approach, and how many of you create Oracle schemas for each user, and if you can, what was the major reason for choosing that approach. Any opinions you wish to contribute will be helpful. Thank you, Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regina Harter 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: To Anyone involved in Web apps
Thank you for the comments on this so far. Our situation is very similar to the one Mohammed describes here, IIS/ASP accessing Oracle as the DB. I like the idea of the database handling as much of the security as possible, especially as we have a number of applications accessing the same DB, and a good deal of overlap in the users of each, and we are requiring a username/password logon in each app. The only concrete argument I have seen so far in favor of the single oracle schema logon is the advantage of connection pooling. Since our applications are specialized use, and I doubt we'll ever have more than 50 concurrent users over all the apps, at what point does connection pooling become a significant performance benefit? Thank you Regina At 01:40 PM 1/21/2003 -0800, you wrote: Hi Regina, I'll my 2 cents here. We are creating a single Oracle user for each connection. Our app is using IIS/ASP and Oracle as the DB. We looked into using a single app user and controling security from the app. Since our is designed for a secure site, we wanted to keep as much control of security within the database as possible and leave as little to the IIS/ASP comboniation as we could. The security layer is built into the database and we only use the front end to authenticate to the database. We have also turned on autiditing so that we know who has logged on and what they are doing - again, a requriment for the project. Granted, we could have done this via the front end application but we felt much more comfortable putting the security into the hands of the database layer even though this requried the creation of a database user per connection. This is handled via stored procs called from the front end by a security officer so there is very little DBA intervention in managing database users. The disadvantage is obviously we can't use application connection pooling but we can use MTS; although on NT this seems to work not too well. We seem to see a lot of latency. Advantage is from the security perpective i.e. we let the datbase handle all the security, we know who, when and from where each user logged in and we can easliy control access by modifying roles and privs and they take effect immediately. hth mohammed --- Regina Harter [EMAIL PROTECTED] wrote: Hi I have a question for any of you involved in Web applications. I would like to know how many of you go for the single Oracle user for everyone approach, and how many of you create Oracle schemas for each user, and if you can, what was the major reason for choosing that approach. Any opinions you wish to contribute will be helpful. Thank you, Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: mkb INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Regina Harter 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 Application Server on .net
The way it was explained to me at a .NET upgrade conference is that .NET is sort of an overlay on top of XP. Anything that is going to run in the .NET framework must conform to certain coding conventions and standards. At 03:53 PM 12/17/2002 -0800, you wrote: .Net is not an OS. The developer (here it comes) doesn't know what he's talking about. -Original Message- From: Barbara Baker [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 17, 2002 3:34 PM To: Multiple recipients of list ORACLE-L Subject: Oracle Application Server on .net List: I have a developer trying to install Oracle9i Application Server Release 2 (9.0.3) on a .net server. He's using the install disk for Windows NT and 2000. Needless to say, it gives him an error and throws up. He considers .net just another operating system like np or 2000. I think of it more like a competitor for OAS. At any rate, I can't find any mention of .net on either Metalink or OTN (except how much better OAS is than .net) Does anyone know if Oracle has an application server installation for .net? If so, do you know how I might get it? Is this a silly question? Should I be hanging my head in shame?? Thanks in advance for any information. Barb Do you Yahoo!? http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.comYahoo! Mail Plus - Powerful. Affordable. http://rd.yahoo.com/mail/mailsig/*http://mailplus.yahoo.comSign up now -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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).
Fwd: Diets
The Japanese eat very little fat and suffer fewer heart attacks than the British or Americans. The French eat a lot of fat and also suffer fewer heart attacks than the British or Americans. from a friend,,, The Japanese drink very little red wine and suffer fewer heart attacks than the British or Americans. The Italians drink excessive amounts of red wine and also suffer fewer heart attacks than the British or Americans. The Germans drink a lot of beers and eat lots of sausages and fats and suffer fewer heart attacks than the British or Americans. CONCLUSION: Eat and drink what you like. Speaking English is apparently what kills you -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: IN() question
Yes, your why is correct, NULL is neither equal nor not equal to any value. I usually nvl to return a space for null values when I have to consider them, then a not equal will work. At 03:43 PM 5/3/02 -0800, you wrote: 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: Regina Harter 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: not a single-group group function error
Even if you got this to work, the three numbers would all be the same. Maybe you should explain what result you are attempting to show. At 03:05 PM 1/28/02 -0800, you wrote: Why when I use the following SQL it get a ORA-00937: not a single-group group function ORA-06512: at PRIMUS.LICENSE_USE_EVERY_30, line 14 ORA-06512: at line 1 Thank you in advance Lance SELECT ROUND(AVG(COUNT(Time_stamp))), MIN(COUNT(time_stamp)), MAX(COUNT(time_stamp)), Time_stamp FROM cp_license_use Where time_stamp = parmTime and time_stamp = SYSDATE-7 group by Time_Stamp) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lance Prais 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: Regina Harter 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: time issue
I'm pretty sure the earlier date must be referenced first. At 01:35 PM 1/24/02 -0800, you wrote: I am using the following Statement: I am getting no records returned. Any idea what is wrong? thanks again Lance Select count(User_name) users from cp_license_use where Time_Stamp between SYSDATE and (sysdate - 30/1440) group by Time_stamp To grab data from the following table: SYSDATE = 1/24/2002 9:33:16 PM User_Name Time_stamp dsilver 1/24/2002 9:31:33 PM cnelson 1/24/2002 9:31:33 PM eho 1/24/2002 9:31:33 PM mreza 1/24/2002 9:31:33 PM kjuneja 1/24/2002 9:31:33 PM sislam 1/24/2002 9:31:33 PM dkotha 1/24/2002 9:31:33 PM mbalthrop 1/24/2002 9:31:33 PM tchung 1/24/2002 9:31:33 PM cnifong 1/24/2002 9:31:33 PM sluc1/24/2002 9:31:33 PM dtrevino1/24/2002 9:31:33 PM ddobson 1/24/2002 9:31:33 PM echinwub1/24/2002 9:31:33 PM dmoses 1/24/2002 9:31:33 PM gpratt 1/24/2002 9:31:33 PM syahmed 1/24/2002 9:31:33 PM mreza 1/24/2002 9:31:33 PM -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lance Prais 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: Regina Harter 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: UNION
Use ORDER BY 1 (ie, the first column) At 10:31 AM 1/17/02 -0800, you wrote: Hi, I try to use union and order by first column of first select statment and also first column of second select statment but get error, Any Idea how to do this?? SELECT A,B,C FROM TABLEABC UNION SELECT D,E,F FROM TABLEDEF ORDER BY A,D Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Regina Harter 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).
Role Privileges
Okay, I knew this was going to happen one day, but I kept hoping as we upgraded the problem would be corrected eventually. ROLE_TAB_PRIVS will only show me tables privileges granted to a role by the logged on user. It wasn't such a problem before because I created most of the roles and knew who was doing the granting. Now I have a couple of roles I didn't create and need to know what has been granted to them. How do I find out without knowing who did the granting? Thank you, any help will be appreciated. Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Role Privileges
At 12:25 PM 1/15/02 -0800, you wrote: dba_tab_privs will show you privileges granted to anyone, including roles Yes, it will show privileges granted TO anyone, but only those privileges granted BY me (or whoever I am logged in as). I need to know how to see the privileges granted even when I don't know who they were granted by. select table_name, privilege from dba_tab_privs where grantee='role'; --- Regina Harter [EMAIL PROTECTED] wrote: Okay, I knew this was going to happen one day, but I kept hoping as we upgraded the problem would be corrected eventually. ROLE_TAB_PRIVS will only show me tables privileges granted to a role by the logged on user. It wasn't such a problem before because I created most of the roles and knew who was doing the granting. Now I have a couple of roles I didn't create and need to know what has been granted to them. How do I find out without knowing who did the granting? Thank you, any help will be appreciated. Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Role Privileges
At 02:51 PM 1/15/02 -0800, you wrote: Wrong... DBA_TAB_PRIVS will show you ANYONE who granted privs to the grantee you specify... I'm sure it's supposed to, but it does not. I need another way. -Original Message- Sent: Tuesday, January 15, 2002 5:29 PM To: Multiple recipients of list ORACLE-L At 12:25 PM 1/15/02 -0800, you wrote: dba_tab_privs will show you privileges granted to anyone, including roles Yes, it will show privileges granted TO anyone, but only those privileges granted BY me (or whoever I am logged in as). I need to know how to see the privileges granted even when I don't know who they were granted by. select table_name, privilege from dba_tab_privs where grantee='role'; --- Regina Harter [EMAIL PROTECTED] wrote: Okay, I knew this was going to happen one day, but I kept hoping as we upgraded the problem would be corrected eventually. ROLE_TAB_PRIVS will only show me tables privileges granted to a role by the logged on user. It wasn't such a problem before because I created most of the roles and knew who was doing the granting. Now I have a couple of roles I didn't create and need to know what has been granted to them. How do I find out without knowing who did the granting? Thank you, any help will be appreciated. Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Johnston, Tim 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: Regina Harter 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: Role Privileges
I'm sorry, I apologize. Someone else was messing with the role I was testing with, which is why it returned only some of the roles I expected to see.Testing it again, it seems to return exactly what I need. Thank you, everyone. At 04:15 PM 1/15/02 -0800, you wrote: You are either encountering a bug or doing it incorrectly... If it is a bug, you should call support... But, I'm guessing that you are doing it incorrectly... Run the following test... Log on as system... Create Role DeleteMe; Grant Select On SQLPLUS_PRODUCT_PROFILE To DeleteMe; Log on as a different dba id... Select * From Dba_Tab_Privs Where Grantee = 'DELETEME'; You should see the following... SQL SQL connect system/system_pass@yourdb Connected. SQL Create Role DeleteMe; Role created. SQL SQL Grant Select On SQLPLUS_PRODUCT_PROFILE To DeleteMe; Grant succeeded. SQL SQL connect other_dba_id/other_dba_id_pass@yourdb Connected. SQL SQL Select * From Dba_Tab_Privs Where Grantee = 'DELETEME'; GRANTEEOWNER -- -- TABLE_NAME GRANTOR -- -- PRIVILEGEGRA --- DELETEME SYSTEM SQLPLUS_PRODUCT_PROFILESYSTEM SELECT NO SQL In this case, the table owned by system is granted to the DELETEME role but you can see it from another dba account... If you do not get these results, then you are encountering a bug of some sort... If you do see these results, you are probably doing something wrong with your other lookup... Maybe the role your are concerned with has do privs granted to it??? Tim PS - FYI... This assumes you have run pupbld.sql... And, don't forget to drop the DELETEME role when you are done... -Original Message- Sent: Tuesday, January 15, 2002 6:31 PM To: Multiple recipients of list ORACLE-L At 02:51 PM 1/15/02 -0800, you wrote: Wrong... DBA_TAB_PRIVS will show you ANYONE who granted privs to the grantee you specify... I'm sure it's supposed to, but it does not. I need another way. -Original Message- Sent: Tuesday, January 15, 2002 5:29 PM To: Multiple recipients of list ORACLE-L At 12:25 PM 1/15/02 -0800, you wrote: dba_tab_privs will show you privileges granted to anyone, including roles Yes, it will show privileges granted TO anyone, but only those privileges granted BY me (or whoever I am logged in as). I need to know how to see the privileges granted even when I don't know who they were granted by. select table_name, privilege from dba_tab_privs where grantee='role'; --- Regina Harter [EMAIL PROTECTED] wrote: Okay, I knew this was going to happen one day, but I kept hoping as we upgraded the problem would be corrected eventually. ROLE_TAB_PRIVS will only show me tables privileges granted to a role by the logged on user. It wasn't such a problem before because I created most of the roles and knew who was doing the granting. Now I have a couple of roles I didn't create and need to know what has been granted to them. How do I find out without knowing who did the granting? Thank you, any help will be appreciated. Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http
RE: Role Privileges
Actually, one of the ones I was looking at had no privileges, it was there as an application check only, didn't need privileges. The other ones really do have privileges, though. At 05:50 PM 1/15/02 -0800, you wrote: or maybe it got its privs from a role that was granted to it? --- Johnston, Tim [EMAIL PROTECTED] wrote: Maybe the role your are concerned with has do privs granted to it??? Oops... Make that no privs granted to it... :-) -Original Message- Sent: Tuesday, January 15, 2002 7:15 PM To: Multiple recipients of list ORACLE-L You are either encountering a bug or doing it incorrectly... If it is a bug, you should call support... But, I'm guessing that you are doing it incorrectly... Run the following test... Log on as system... Create Role DeleteMe; Grant Select On SQLPLUS_PRODUCT_PROFILE To DeleteMe; Log on as a different dba id... Select * From Dba_Tab_Privs Where Grantee = 'DELETEME'; You should see the following... SQL SQL connect system/system_pass@yourdb Connected. SQL Create Role DeleteMe; Role created. SQL SQL Grant Select On SQLPLUS_PRODUCT_PROFILE To DeleteMe; Grant succeeded. SQL SQL connect other_dba_id/other_dba_id_pass@yourdb Connected. SQL SQL Select * From Dba_Tab_Privs Where Grantee = 'DELETEME'; GRANTEEOWNER -- -- TABLE_NAME GRANTOR -- -- PRIVILEGEGRA --- DELETEME SYSTEM SQLPLUS_PRODUCT_PROFILESYSTEM SELECT NO SQL In this case, the table owned by system is granted to the DELETEME role but you can see it from another dba account... If you do not get these results, then you are encountering a bug of some sort... If you do see these results, you are probably doing something wrong with your other lookup... Maybe the role your are concerned with has do privs granted to it??? Tim PS - FYI... This assumes you have run pupbld.sql... And, don't forget to drop the DELETEME role when you are done... -Original Message- Sent: Tuesday, January 15, 2002 6:31 PM To: Multiple recipients of list ORACLE-L At 02:51 PM 1/15/02 -0800, you wrote: Wrong... DBA_TAB_PRIVS will show you ANYONE who granted privs to the grantee you specify... I'm sure it's supposed to, but it does not. I need another way. -Original Message- Sent: Tuesday, January 15, 2002 5:29 PM To: Multiple recipients of list ORACLE-L At 12:25 PM 1/15/02 -0800, you wrote: dba_tab_privs will show you privileges granted to anyone, including roles Yes, it will show privileges granted TO anyone, but only those privileges granted BY me (or whoever I am logged in as). I need to know how to see the privileges granted even when I don't know who they were granted by. select table_name, privilege from dba_tab_privs where grantee='role'; --- Regina Harter [EMAIL PROTECTED] wrote: Okay, I knew this was going to happen one day, but I kept hoping as we upgraded the problem would be corrected eventually. ROLE_TAB_PRIVS will only show me tables privileges granted to a role by the logged on user. It wasn't such a problem before because I created most of the roles and knew who was doing the granting. Now I have a couple of roles I didn't create and need to know what has been granted to them. How do I find out without knowing who did the granting? Thank you, any help will be appreciated. Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Send FREE video emails in Yahoo! Mail! http://promo.yahoo.com/videomail/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego
RE: Problem Setting Up User
I don't know about default roles, but if you have none set as default, all are active. At 10:00 AM 1/3/02 -0800, you wrote: I think there is a command like: alter user ??? default role all -Original Message- Sent: Thursday, January 03, 2002 12:30 PM To: Multiple recipients of list ORACLE-L I beg to differ. A default role is simply one that is automatically enabled (or set) upon login. Any or all of a user's roles may be default (see the definition of user_role_privs). May I suggest, Rachel my friend, that *you* RTFM? :-D --- Rachel Carmichael [EMAIL PROTECTED] wrote: third, you can only have ONE default role. Think about it. If you really want the user to have the privs of several roles at once, create another role, a superrole that is granted both DB and RESOURCE and the grant that one as default. And you really do have to RTFM __ Do You Yahoo!? Send your FREE holiday greetings online! http://greetings.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Khedr, Waleed 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: Regina Harter 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: Trigger
I would suggest you use an ON INSERT trigger rather than BEFORE INSERT and just select nextval into a variable then set :new.col1 = variable. At 09:55 AM 12/20/01 -0800, you wrote: List, I need some help for creating a trigger, here is my question? I have a table TABLEA(col1,col2,col3) col1 is Primary Key for this table and an application insert records into this table, but I want to write a trigger on this table to generate primary key with using nexval of a sequence I have tried but doesn't work , if any of you have some sample or some links I really appreciate, This is my trigger: Create or replace trigger cre_PK before insert insert into tableA(col1) values(seqname.nextval) end Thanks in Advance Hamid Alavi Office 818 737-0526 Cell818 402-1987 The information contained in this message and any attachments is intended only for the use of the individual or entity to which it is addressed, and may contain information that is PRIVILEGED, CONFIDENTIAL and exempt from disclosure under applicable law. If you have received this message in error, you are prohibited from copying, distributing, or using the information. Please contact the sender immediately by return e-mail and delete the original message from your system. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Hamid Alavi 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: Regina Harter 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: NUMBER PROBLEM
When I encounter a situation like this, I usually make a temporary table containing the primary key and the field in question, load it with data from the table, update the field in the real table with null, modify the field type, then reload from the temporary table. At 09:25 AM 12/14/01 -0800, you wrote: Hi, We have created our table column with datatype NUMBER (where it should be NUMBER(38,0)) Now ODBC is treating NUMBER as double precision instead of integer which is breaking our code. Does there any way to convert NUMBER to number(38)..without export/import utility??? Alter table is not working as it it giving error table column should be empty. Tables have more than 10 million rows. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Regina Harter 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: NUMBER PROBLEM
Thanks, Tom! At 11:55 AM 12/14/01 -0800, you wrote: me too! nicely said Regina... Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 14, 2001 1:40 PM To: Multiple recipients of list ORACLE-L When I encounter a situation like this, I usually make a temporary table containing the primary key and the field in question, load it with data from the table, update the field in the real table with null, modify the field type, then reload from the temporary table. At 09:25 AM 12/14/01 -0800, you wrote: Hi, We have created our table column with datatype NUMBER (where it should be NUMBER(38,0)) Now ODBC is treating NUMBER as double precision instead of integer which is breaking our code. Does there any way to convert NUMBER to number(38)..without export/import utility??? Alter table is not working as it it giving error table column should be empty. Tables have more than 10 million rows. Thanks --Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: 9ias Vs IIS
We are uploading files into our database using pretty much the same setup they are proposing. It works quite well, and have yet to encounter a file it couldn't upload almost instantly, however I have my doubts about how secure it is. We have had no problems so far, but we are still in beta test, so take that with a grain of salt. At 12:14 PM 11/28/01 -0800, you wrote: Briefly, our current setup includes Web enabled forms (PL/SQL Cartridges) accessing an 8i database via 9ias (currently OAS 4.2 but will be moving to 9ias in a month). Btw, we also use ORACLE APPS (11i) using the same Web Server (apps and ias handled by another dba... thankfully :-) ..) For a new system (requirement : ability for customers to upload files (xml, fixed format text file or spreadsheet, or enter data via a form. Need only specific people to be able to upload these files. Files need to be transmitted and saved securely.. Digital signature ?. These files could be required later (Law suit)) that we are looking at, a couple of new guys (who believe that the Sun rises and sets because of Microsoft ) are proposing using IIS -- ASP -- OEMDB -- ORACLE database (existint DB). They also have a problem with IIS -- JSP -- JDBC -- ORACLE DB (they claim JSP would be an overhead on IIS and would slow it down) I don't know the web stuff well enough (Obviously :-) !!!) to see the holes (if any) in this approach. Their complaint is that 9ias is slow (or in their words, ORACLE should stay with databases and not get into the Web server world !!!) Opinions / Info that would help ? Thanks, Sunny _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sunny Verghese 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: Regina Harter 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:Operating Systems
Thanks for your input, everyone. To recap your responses: 8 recommendations for Linux (2 for RedHat in particular) 2 recommendations for Solaris 2 recommendations against Solaris 1 recommendation for NT 1 recommendation against NT 2 saying NT is OK with qualifications. Did I miss anyone? Reply Separator Subject:Operating Systems Author: Regina Harter [EMAIL PROTECTED] Date: 11/14/2001 9:45 AM Hi Guys, Our SA/DBA is considering dumping Unixware and moving to a different OS. Sun is out for reasons that are not being shared with me, probably because they don't want to buy new hardware, so aside from that, would anyone care to weigh in with opinions on what OS is most stable with Oracle? AAnd will run on an intel based box? Ours is not a high transaction system, mostly reads, maybe 100 users a day. Thanks for your opinions, Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Regina Harter 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).
Operating Systems
Hi Guys, Our SA/DBA is considering dumping Unixware and moving to a different OS. Sun is out for reasons that are not being shared with me, probably because they don't want to buy new hardware, so aside from that, would anyone care to weigh in with opinions on what OS is most stable with Oracle? AAnd will run on an intel based box? Ours is not a high transaction system, mostly reads, maybe 100 users a day. Thanks for your opinions, Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: update query??? HELP!!!
update SERVICE_LOCATION a set CENTRAL_OFFICE_CODE = (select FRANCHISE_NAME from FRANCHISE_AREA c, SERVICE_LOC b where a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and b.FRANCHISE_ID = c.FRANCHISE_ID) At 02:55 PM 11/14/01 -0800, you wrote: Hi all, How to do this query, I have three tables: SERVICE_LOCATION a, SERVICE_LOC_AREA b, FRANCHISE_AREA c. The relationship between them is: a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and b.FRANCHISE_ID = c.FRANCHISE_ID I need to update CENTRAL_OFFICE_CODE column in table SERVICE_LOCATION a, using a value selected from FRANCHISE_AREA c. a and c are related through b. update SERVICE_LOCATION set CENTRAL_OFFICE_CODE = (select FRANCHISE_NAME from FRANCHISE_AREA) where a.SERVICE_LOCATION_ID = b.SERVICE_LOCATION_ID and b.FRANCHISE_ID = c.FRANCHISE_ID) I don't know where to specify a, b, c tables. Thank you! Janet __ Do You Yahoo!? Find the one for you at Yahoo! Personals http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Janet Linsy 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: Regina Harter 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: gnarly delete statement?
t2.timestamp t1.timestamp will delete the latest. It will actually delete anything greater than the earliest. You will notice I wrote Delete where exists ... which means delete anything where there was a match with an earlier timestamp. At 06:10 PM 11/1/01 -0800, you wrote: t2.timestamp t1.timestamp? he wants to delete the LATEST insert timestamp, right Paul? --- Regina Harter [EMAIL PROTECTED] wrote: Try this: Delete from my_table t1 where a = 2 and exists (select 'X' from my_table t2 where t2.a = t1.a andt2.b = t1.b andt2.c = t1.b andt2.d = t1.d andt2.timestamp t1.timestamp) At 12:55 PM 11/1/01 -0800, you wrote: Given a business rule that says a combination of three columns must be unique if and only if a fourth column equals a certain value, and if the table in question contains rows that violate this requirement, I'm trying to write a single SQL statement that will remove the duplicates. Of each set of duplicate rows, I'll delete the one with the latest insert timestamp. Let's call the columns a b c d timestamp The combination of b, c, and d must be unique if a = 2. I can get the duplicate row values along with the latest timestamp via select max(tstamp), a, b, c from my_table group by a, b, c having count(*) 1 But I can't figure out how to use that in a delete statement. Suggestions greatly appreciated! Paul Baumgartel __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Paul Baumgartel INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Find a job, post your resume. http://careers.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 7 documentation - Good One
Yeah, we still have a server on 7.1.3, mostly because we have some C reports, and the C compiler has changed enough in 8 to require that we would have to rewrite them, which we don't have the budget for. At 11:55 AM 10/31/01 -0800, you wrote: no offense.. just a sincere question. are people still using o7 out there? talking to oracle support regarding ECS must a be real pain in the butt as i guess they might have stopped supplying patches for o7. in our shop people are jittery continuing on 816 as its de-supported from TODAY (officially with no ECS support .. just workarounds) and we are thinking of hopping on 817 until 9.2 or 9.3 is released and proves stable just some thoughts... as i was pleasently surprised looking the the hits the o7 doc thread has recieved Deepak PS: ok now no holy war here please; --- Eric D. Pierce [EMAIL PROTECTED] wrote: Are you sure? I wasted time trying to find some minor documentation (platform specific release notes?) there a while ago, and after complaining that I couldn't find it, others said everything *isn't* there. Frustrating considering that they have a huge number of CD packs etc that appear to be small incrementals. It does appear that all the major documentation is there. brgrds, ep ORACLE-L Digest -- Volume 2001, Number 304 -- From: [EMAIL PROTECTED] Date: Tue, 30 Oct 2001 09:21:35 -0800 Subject: RE: Oracle 7 documentation - Good One Thanks for sharing this! Finally, complete documentation for Oracle 7 on. ... ---original--- | Dear All, | I hit upon this site. I thought its worth sharing | http://docs.oracle.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). __ Do You Yahoo!? Make a great connection at Yahoo! Personals. http://personals.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Deepak Thapliyal 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: Regina Harter 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: New Oracle Install and Old Data Files
Yes, you are absolutely right, we have everything except the system tablespace. The two disks that crashed contained all the program files, including unix and oracle, and the system tablespace. The disks containing all the other datafiles were untouched. However, I did forward Randy's solution to our DBA, even though I have my own doubts about it being sufficient. At 08:45 AM 10/29/01 -0800, you wrote: Yes, but she said that she everything EXCEPT the system tablespaces. If this is true then they're SOL, except for Data Unloader. Randy Kirkpatrick To: Multiple recipients of list ORACLE-L randywk [EMAIL PROTECTED] @usa.netcc: Sent by: rootSubject: RE: New Oracle Install and Old Data Files 10/29/2001 10:50 AM Please respond to ORACLE-L The datafiles contain the tablespaces which contain the data for the tables, indexes, etc. If she has all the datafiles, then she'll have access to all the data. I haven't tried it, but this should work for the system tablespace as well. Randy Kirkpatrick -Original Message- [EMAIL PROTECTED] Sent: Monday, October 29, 2001 7:00 AM To: Multiple recipients of list ORACLE-L But does she just want the old tablespaces or does she want the tables and indexes that were in those tablespaces? I don't think that simply recovering the tablespaces will recover the tablespace contents, though it is the way to start. Doesn't Oracle have a recovery tool -- pay big bucks, they come in and recover your database and then remove the tool -- that will do this? Randy Kirkpatrick To: Multiple recipients of list ORACLE-L randywk [EMAIL PROTECTED] @usa.netcc: Sent by: rootSubject: RE: New Oracle Install and Old Data Files 10/26/2001 09:00 PM Please respond to ORACLE-L Regina, Yes there is - but you probably don't want to do it yourself if you're not a DBA ... do a backup control file to trace: alter database database backup controlfile to trace; shut down the database and modify the sql in the file you created so it contains the information for the tablespaces you need. use the sql file to start up the database hopefully everything starts ok. Can't guarantee what happens to integrity, etc. if you don't have all the tablespaces. Randy Kirkpatrick -Original Message- Harter Sent: Friday, October 26, 2001 5:10 PM To: Multiple recipients of list ORACLE-L Okay People, I am not the DBA and don't have all the details but this is our situation the way it was explained to me: We had a server crash to the point where Unix and Oracle had to be reinstalled, however the disks containing our data tablespaces (that is, everything but the system tablespaces) are still good, and apparently for several reasons, we have not had a good backup for at least a week. Is there anyway to get a new Oracle install to recognize old tablespaces? TIA, Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Randy Kirkpatrick INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California
RE: New Oracle Install and Old Data Files
Well, we already had Oracle Support tell us there was NO WAY to get at that data. Really, I expect how hard we work at getting it will be directly proportional to how may users call moaning about how hard it will be to redo the data they entered last week. I will pass this information along, however. At 02:10 PM 10/29/01 -0800, you wrote: data unloader WILL work without a system tablespace, although obviously it's easier if it exists. in my case we lost the online redo logs and could not force the database to open no matter WHAT we did (and yes, I spent two hours on the phone with Oracle Support trying to force it open). we had the system tablespace. we were lucky. ask Tech Support about Data Unloader and get a number for Field Support. There are about 5 or 6 techs in the country who know how to use it. Three on the East Coast, we forced Oracle to pull someone in from vacation. Since he was getting quintuple time at that point, he didn't really care :) --- Regina Harter [EMAIL PROTECTED] wrote: Yes, you are absolutely right, we have everything except the system tablespace. The two disks that crashed contained all the program files, including unix and oracle, and the system tablespace. The disks containing all the other datafiles were untouched. However, I did forward Randy's solution to our DBA, even though I have my own doubts about it being sufficient. At 08:45 AM 10/29/01 -0800, you wrote: Yes, but she said that she everything EXCEPT the system tablespaces. If this is true then they're SOL, except for Data Unloader. Randy Kirkpatrick To: Multiple recipients of list ORACLE-L randywk [EMAIL PROTECTED] @usa.netcc: Sent by: rootSubject: RE: New Oracle Install and Old Data Files 10/29/2001 10:50 AM Please respond to ORACLE-L The datafiles contain the tablespaces which contain the data for the tables, indexes, etc. If she has all the datafiles, then she'll have access to all the data. I haven't tried it, but this should work for the system tablespace as well. Randy Kirkpatrick -Original Message- [EMAIL PROTECTED] Sent: Monday, October 29, 2001 7:00 AM To: Multiple recipients of list ORACLE-L But does she just want the old tablespaces or does she want the tables and indexes that were in those tablespaces? I don't think that simply recovering the tablespaces will recover the tablespace contents, though it is the way to start. Doesn't Oracle have a recovery tool -- pay big bucks, they come in and recover your database and then remove the tool -- that will do this? Randy Kirkpatrick To: Multiple recipients of list ORACLE-L randywk [EMAIL PROTECTED] @usa.netcc: Sent by: rootSubject: RE: New Oracle Install and Old Data Files 10/26/2001 09:00 PM Please respond to ORACLE-L Regina, Yes there is - but you probably don't want to do it yourself if you're not a DBA ... do a backup control file to trace: alter database database backup controlfile to trace; shut down the database and modify the sql in the file you created so it contains the information for the tablespaces you need. use the sql file to start up the database hopefully everything starts ok. Can't guarantee what happens to integrity, etc. if you don't have all the tablespaces. Randy Kirkpatrick -Original Message- Harter Sent: Friday, October 26, 2001 5:10 PM To: Multiple recipients of list ORACLE-L Okay People, I am not the DBA and don't have all the details but this is our situation the way it was explained to me: We had a server crash to the point where Unix and Oracle had to be reinstalled, however the disks containing our data tablespaces (that is, everything but the system tablespaces) are still good, and apparently for several reasons, we have not had a good backup for at least a week. Is there anyway to get a new Oracle install to recognize old tablespaces? TIA, Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538
New Oracle Install and Old Data Files
Okay People, I am not the DBA and don't have all the details but this is our situation the way it was explained to me: We had a server crash to the point where Unix and Oracle had to be reinstalled, however the disks containing our data tablespaces (that is, everything but the system tablespaces) are still good, and apparently for several reasons, we have not had a good backup for at least a week. Is there anyway to get a new Oracle install to recognize old tablespaces? TIA, Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: ampersand problem
Well, for one thing, you have MOM instead of MON in the date format. Besides that, it might help if you describe what problem you are having. At 12:10 AM 10/5/01 -0800, you wrote: sorri i have pasted the wrong one here is the correct on declare code number(3):=0; edate date; begin code:=111; edate:=to_date('01-jan-2001','dd-mom-'); dbms_output.put_line('actual data '||code ||','||edate); @abc code edate dbms_output.put_line('hello'); end; / abc.sql declare my_code number(3); my_date date; begin my_code:=1; my_date:='2'; dbms_output.put_line('date in abc '||my_code ||','||my_date); end; / -Original Message- From: Swapna_Chinnagangannagari Sent: Friday, October 05, 2001 12:27 PM To: '[EMAIL PROTECTED]' Subject:ampersand problem Why is this code not working for me declare code number(3):=0; edate date; begin code:=111; edate:=to_date('01-01-2001','dd-mm-'); dbms_output.put_line('actual data '||code ||','||edate); @abc code edate dbms_output.put_line('hello'); end; / abc.sql declare my_code number(3); my_number number(3); begin my_code:=1; my_number:='2'; dbms_output.put_line('data in abc '||my_code ||','||my_number); end; / -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Dynamic Sql
Try looking in the Application Developers Guide. For 7.3 it was Chapter 10, though this may have changed. At 05:55 AM 10/3/01 -0800, you wrote: Hi Rajendra Jamadagni, Can U explain about dbms_sql.open_cursor dbms_sql.parse dbms_sql.define_column dbms_sql.execute_and_fetch dbms_sql.column_value dbms_sql.close_cursor I tried in Oracle PLSQL Documents, but couldn't trace about these. Hope U can help me. Regards Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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: Regina Harter 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: From Access to Oracle
I haven't seen an answer to your question yet, so I will give it a shot. I am not extremely familiar with Access, but I believe inner join is just a regular join, so: SELECT RIK2.LEV.LEVNR, RIK2.VARE.EAN_NR, RIK2.VARE.VARENAVN, RIK2.AVD.KJEDE, Sum(BRT.BRTOMS_DAG.ANT) AS ANT, Sum(BRT.BRTOMS_DAG.UTVERDI) AS UTVERDI, BRT.BRTOMS_DAG.DATO, Left([RIK2.VARE].[HYLLETXT2],12) AS VARUMARKE, RIK2_VARE.STR FROM RIK2.LEV, RIK2.VARE, BRT.BRTOMS_DAG, RIK2.AVD WHERE RIK2.LEV.LEVNR = RIK2.VARE.LEVNR AND (RIK2.VARE.VARE_SNR = BRT.BRTOMS_DAG.VARE_SNR) AND (RIK2.VARE.VARENR = BRT.BRTOMS_DAG.VARENR) AND (RIK2_VARE.SORTIMENT = BRT_BRTOMS_DAG.SORTIMENT)) AND BRT.BRTOMS_DAG.AVDNR = RIK2.AVD.AVDNR AND (((RIK2.LEV.SELSKAP)=11) AND ((RIK2.VARE.SELSKAP)=11) AND ((RIK2.AVD.SELSKAP)=11)) GROUP BY RIK2.LEV.LEVNR, RIK2.VARE.EAN_NR, RIK2.VARE.VARENAVN, RIK2.AVD.KJEDE, BRT.BRTOMS_DAG.DATO, RIK2.VARE.STR, RIK2.VARE.HYLLETXT2 HAVING (((RIK2.AVD.KJEDE) In (30,31,32,33,34))) ORDER BY RIK2.VARE.EAN_NR; At 11:45 PM 9/11/01 -0800, you wrote: Hallo, Anyone who can help me, I have this query in Ms Access: How should I write this sql statement in oracle. SELECT RIK2.LEV.LEVNR, RIK2.VARE.EAN_NR, RIK2.VARE.VARENAVN, RIK2.AVD.KJEDE, Sum(BRT.BRTOMS_DAG.ANT) AS ANT, Sum(BRT.BRTOMS_DAG.UTVERDI) AS UTVERDI, BRT.BRTOMS_DAG.DATO, Left([RIK2.VARE].[HYLLETXT2],12) AS VARUMARKE, RIK2_VARE.STR FROM ((RIK2.LEV INNER JOIN RIK2.VARE ON RIK2.LEV.LEVNR = RIK2.VARE.LEVNR) INNER JOIN BRT.BRTOMS_DAG ON (RIK2.VARE.VARE_SNR = BRT.BRTOMS_DAG.VARE_SNR) AND (RIK2.VARE.VARENR = BRT.BRTOMS_DAG.VARENR) AND (RIK2_VARE.SORTIMENT = BRT_BRTOMS_DAG.SORTIMENT)) INNER JOIN RIK2_AVD ON BRT.BRTOMS_DAG.AVDNR = RIK2.AVD.AVDNR WHERE (((RIK2.LEV.SELSKAP)=11) AND ((RIK2.VARE.SELSKAP)=11) AND ((RIK2.AVD.SELSKAP)=11)) GROUP BY RIK2.LEV.LEVNR, RIK2.VARE.EAN_NR, RIK2.VARE.VARENAVN, RIK2.AVD.KJEDE, BRT.BRTOMS_DAG.DATO, RIK2.VARE.STR, RIK2.VARE.HYLLETXT2 HAVING (((RIK2.AVD.KJEDE) In (30,31,32,33,34))) ORDER BY RIK2.VARE.EAN_NR; I guess problem with left join, inner join and outer join 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: Regina Harter 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: User Query Tools
G-d, yes, we have users like that. Most of the products we looked at required too much space, were too expensive, or required too much initial set up time. We ended up writing our own Adhoc Query tool in PowerBuilder. At 02:52 PM 9/10/01 -0800, you wrote: Hey all, I just got out from a meeting where I was told that some of our users are asking for a tool to do their own queries against our database so they wouldn't have to request reports and such from IS. Oh BTW, they don't know (or feel the need to learn) SQL so they will be needing something with either a good GUI QBF or some kind of natural language interface. We are leaning against MS Query to avoid ODBC performance problems. Does anyone out there have users like this? Any product referrals with pros/cons? TIA If you have any questions, please feel free to call me or drop me a note. Stephen Andert 480-445-2506 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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 problem
Why are you trying to join three different tables to values which may not exists? Are those three tables related to each other outside of what you show here? If not, and this statement worked, you would end up with a matrix join between those three tables. Is that what you are looking for? At 09:26 AM 9/6/01 -0800, you wrote: Hi, In one of our queries a table is participating in 3 outer joins ..but oracle only allows one outer join per table how can we achieve to have more than 1 outer join for a particular table : for example like : t_recur.id_prop = t_pl_map.id_pi_template(+) and t_discount.id_prop = t_pl_map.id_pi_template(+) and t_aggregate.id_prop = t_pl_map.id_pi_template(+) and Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: what is wrong in query
Well, I don't believe this: {oj t_po LEFT OUTER JOIN t_ep_po on t_ep_po.id_prop = t_po.id_po} is valid in Oracle, unless there has been some drastic change I wasn't informed of. At 09:46 AM 9/6/01 -0800, you wrote: Hi, I am trying to run following query thru ODBC and getting error: 09/06/01 11:10:22 [DBAccess][ERROR] Database Execute() failed. Error Description = [Microsoft][ODBC driver for Oracle][Oracle]ORA-00920: invalid relational operator Which relational operator it is referring?. select DISTINCT(t_po.id_po), t_po.id_eff_date, t_po.id_avail, t_po.b_user_subscribe, t_po.b_user_unsubscribe, t_base_props.n_name, t_base_props.n_desc,t_base_props.n_display_name,t_base_props.nm_name, t_base_props.nm_desc, t_base_props.nm_display_name,te.n_begintype as te_n_begintype, te.dt_start as te_dt_start, te.n_beginoffset as te_n_beginoffset,te.n_endtype as te_n_endtype, te.dt_end as te_dt_end, te.n_endoffset as te_n_endoffset, ta.n_begintype as ta_n_begintype, ta.dt_start as ta_dt_start, ta.n_beginoffset as ta_n_beginoffset, ta.n_endtype as ta_n_endtype, ta.dt_end as ta_dt_end, ta.n_endoffset as ta_n_endoffset, decode(sign((select count(id_pi_type) from t_pl_map,t_base_props tb where tb.id_prop = t_pl_map.id_pi_type AND tb.n_kind = 20 and t_po.id_po = t_pl_map.id_po)),1,'Y','N') as b_RecurringCharge ,t_ep_po.c_ExternalInformationURL t_ep__c_ExternalInformationURL,t_ep_po.c_glcode t_ep_po_c_glcode,t_ep_po.c_InternalInformationURL t_ep__c_InternalInformationURL from t_av_internal tav,t_effectivedate te,t_effectivedate ta,t_base_props,t_pricelist, t_base_props template_base,t_acc_usage_cycle,t_usage_cycle,(select GetUTCDate() now from dual) cdate, t_pl_map,t_recur,t_discount,t_aggregate where {oj t_po LEFT OUTER JOIN t_ep_po on t_ep_po.id_prop = t_po.id_po}, t_recur.id_prop = t_pl_map.id_pi_template(+) and t_discount.id_prop = t_pl_map.id_pi_template and t_aggregate.id_prop = t_pl_map.id_pi_template and t_pl_map.id_po = t_po.id_po AND t_pl_map.id_paramtable is not NULL AND t_pl_map.id_sub is NULL AND tav.id_acc = 136 AND t_pricelist.id_pricelist = t_pl_map.id_pricelist AND tav.c_currency = t_pricelist.nm_currency_code AND te.id_eff_date = t_po.id_eff_date AND ta.id_eff_date = t_po.id_avail AND t_base_props.id_prop = t_po.id_po AND template_base.id_prop = t_pl_map.id_pi_template AND t_po.id_po not in (select id_po from t_sub,t_effectivedate tesub where id_acc = 136 AND t_sub.id_eff_date =tesub.id_eff_date AND (tesub.dt_end is NULL AND tesub.dt_start = cdate.now)) AND ((ta.dt_start = cdate.now or ta.dt_start is null) AND (cdate.now = ta.dt_end or ta.dt_end is null)) AND t_acc_usage_cycle.id_acc = 136 AND t_usage_cycle.id_usage_cycle = t_acc_usage_cycle.id_usage_cycle AND (t_recur.id_cycle_type is null or t_recur.id_cycle_type = t_usage_cycle.id_cycle_type) AND (t_discount.id_cycle_type is null or t_discount.id_cycle_type = t_usage_cycle.id_cycle_type) AND (t_aggregate.id_cycle_type is null or t_aggregate.id_cycle_type = t_usage_cycle.id_cycle_type) AND te.n_begintype 0 AND ta.n_begintype 0 Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Regina Harter INET: [EMAIL PROTECTED] Fat City Network
Re: ms acces
If you can get an ODBC connection from MS Access to Oracle, Access itself will do it. I have only done it myself once, but I might be able to get together with someone and try to remember how it is done... At 11:58 AM 9/6/01 -0800, you wrote: is there any tool that imports directly form ms acces to oracle? with out having to export ms acces files to flat text files and then load them in to oracle but some sort of direct importation? cheers The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system. Please do not re-send by any reazon in any way or form any of the informatino here contained. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: agc 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: Regina Harter 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: converting case in sqlserver to oracle
It's not pretty, but something like this should work: select au.id, decode(au.id_pi_instance,null,id_view, decode(pi_props.n_kind,15, decode(child_kind.nm_productview,ed.nm_enum_data,au.id_pi_instance + 0x4000, au.id_pi_instance), au.id_pi_instance), au.id_pi_instance), au.instance, ... At 09:30 AM 9/4/01 -0800, you wrote: Hi, I have a procedure in sql server containg following case statement...(part of select clause) how to convert this statement is oracle format: select au.id, viewID = case when au.id_pi_instance is NULL then id_view else (select viewID = case when pi_props.n_kind = 15 AND child_kind.nm_productview = ed.nm_enum_data then -(au.id_pi_instance + 0x4000) else -au.id_pi_instance end) end,au.instance, Thanks -Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh 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: Regina Harter 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 do they get the answer?
The structure of the table indicates to me that they began with the assumption that each teacher would only be teaching one subject. At 10:52 AM 8/21/01 -0800, you wrote: i still stand by(reading into the question) that it could be only 2 teachers get a raise, if the same 2 teachers teach both of those courses. would they get twice as much of a raise? :) joe [EMAIL PROTECTED] 08/21/01 12:52PM in the where clause, the IN says any teachers that are in any of these subjects. Since there are two subjects in the IN set and at least 2 teachers have to teach each subject, 2*2=4. Basic mathematics: at least 4 teachers will get a raise. Jon Walthour From: [EMAIL PROTECTED] Date: 2001/08/21 Tue PM 12:25:54 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: How do they get the answer? I am taking the Self-Test software test for the SQL exam and don't see how they get the answer to this problem. They don't explain how it is arrived at. Any help you can give me will be appreciated. Thanks, Ken Janusz, CPIM -- Examine the structure of the TEACHER table: Name Null? Type ID NOT NULL NUMBER(9) SALARY NUMBER (7,2) SUBJECT_ID NOT NULL NUMBER(3) SUBJECT_DESCRIPTION VARCHAR2(2) There are 200 teachers and 15 subjects. Each subject is taught by at least 2 teachers. Evaluate this PL/SQL block: DECLARE v_pct_raise number := 1.10; BEGIN UPDATE teacher SET salary = salary * 1.10 WHERE subject_id IN (102, 105); COMMIT; END; Which result will the PL/SQL block provide? (A) Only two teachers will receive a 10% salary increase. (B) All of the teachers will receive a 10% salary increase. (C) At least four teachers will receive a 10% salary increase. (D) A syntax error will occur. Answer: (C) -- 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: Jon Walthour 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: Avoiding Duplicates and Transitive Operations
... What I want to know is if SQL SELECT C1, C2 FROM ian.TEST1 2 WHERE EXISTS (SELECT C1 FROM ian.TEST2) undergoes a transitive operation to SELECT C1, C2 FROM ian.TEST1 2 WHERE EXISTS (SELECT C1 FROM ian.TEST2 where test1.c1 = test2.c1) / Of course not, why would it do that? All you asked for was the records from TEST1 as long as there exist ANY records in TEST2, and that's what you got. If you add more records to TEST1, you would see that you have established no links between the tables. If you want the second statement, you have to ask for it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Why this SQL does not work?
The way I understand it is rownum does not exist until the data set is returned. The first row returned therefore is rownum = 1 because it is the first row in the dataset, but you are looking for rownum = 5, so it throws that one away. The next row is returned and again is rownum = 1 because it is now the first row in the dataset, but again you are looking for rownum = 5, so it throws that away too. And so on and so on. Basically, it never gets to rownum = 5. So you can use rownum = 1 or rownum anything more than 1, but nothing else. At 01:44 PM 7/11/01 -0800, you wrote: List, Following are two SQL statements. The first SQL statement works OK (where ROWNUM 5 is used). The second SQL statement does not return any rows (where ROWNUM = 5 is used). This table contains 200 records. First SQL statement --- SELECT * FROM (SELECT PROCESSED_DATE FROM TRADES WHERE PROCESSED_STATUS = 0 ORDER BY ENTRY_DATE) WHERE ROWNUM 5 Second SQL statement SELECT * FROM (SELECT PROCESSED_DATE FROM TRADES WHERE PROCESSED_STATUS = 0 ORDER BY ENTRY_DATE) WHERE ROWNUM = 5 --- Question: Why second SQL statement does not work? I would be thankful for the clarification. Thanks, Rao [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rao, Maheswara 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: Regina Harter 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).
Restrictions returning a Long field
Hi People, Does anyone know of a database parameter (or any other reason) in a 7.1.6 database that would restrict the amount of data returned from a long field? We have an application that won't show any data over 32K from a long field. I'm thinking the problem is on the application side somewhere, but the programmer responsible for it claims Oracle is only delivering 32K of the long field to the application and we should look to the database. The only thing I could find that sounds like it might even remotely affect this is the db_block_buffers, and that's already at 5000, but I don't have all the books and could have easily overlooked something. Anyone have any ideas? Regina -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Query help !!!
Here is one way: select distinct customer_id c1 where exists (select 'X' from customer_id where customer_id = c1.customer_id and status = 'F') and exists (select 'X' from customer_id where customer_id = c1.customer_id and status 'F') At 10:05 AM 6/22/01 -0800, you wrote: Just to clearfy my previous question (as follow): if 1 has F and A and B, that what I want. If 1 has F all the time, that's not what I want. If 1 has A, B, C, but never F, that's not what I want either. --- Leslie Lu [EMAIL PROTECTED] wrote: Hi, If I have this: Customer_id Status -- --- 1 F 1 A 1 B 2 F 2 F 3 A 3 B How do I found out a customer who has both F and not F for them. (If he only gets F, or gets other than F, that's fine). In this case, I should get 1. Thank you! I need this badly! __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Leslie Lu 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: Regina Harter 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: Enforced Costraints ??
) 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: Rahul 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: Daemen, Remco 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: Rao, Maheswara 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: Grabowy, Chris 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: Christopher Spence 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: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT - SSN( Enforced Constraints (sic) ??
When the DMV first asked for my SSN, I gave them an incorrect one, because as far as I'm concerned they don't need to know it. Now that they actually check the number with the SSA I was forced to correct it. Most places are forbidden to require that you supply your SSN unless they need it to report taxable earnings. I regularly refuse to supply mine. At 10:58 AM 6/12/01 -0800, you wrote: It's interesting that SSNs are not recycled. My mother works for the SSA and says they are after the person is deceased for a certain period of time (I forget the specifics). Also, a lot of people get nervous when you use their SSN for an identifier. I've noticed that most State Driver's Licenses do not use the SSN at the license number anymore because people were getting their identities stolen. The same thing goes for having it printed permanently on your checks. Let's calculate quickly on this one: 250,000,000 million in the U.S. (Legal) 999,999,999 possible numbers (xxx-xx-) - equals mandatory recycling. --Michael -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, June 12, 2001 2:18 PM To: Multiple recipients of list ORACLE-L There is also a problem with SSNs, if you deal with Foreign nationals. We had that problem with a US Army program in Europe and Korea. We had to make up Unique numbers to fit the program. Georgette -Original Message- Sent: Tuesday, June 12, 2001 12:41 To: Multiple recipients of list ORACLE-L SSN's are not recycled. From the SSA OIG's testimony on SSN use and misuse before congress - The SSN is a unique identifier http://www.ssa.gov/oig/Testimony05222001.htm SSN is a perfectly good PK for personnel systems. If you find duplicates your employer certainly needs to know about it. I once had a personnel database with 25 years of personnel data (about 1,200,000 individuals). When we loaded it into Oracle with the SSN as PK, there was one duplicate SSN. Of course, that employer was very conscientious about requiring a valid SSN. -- 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: Cornio, Georgette Ms USACFSC 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: Jenkins, Michael 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: Regina Harter 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: Bad SQL.....
I think it's just Friday. You need to rewrite your statement to read something like this: UPDATE AVAILABLE_POWER SET available_power_driver_terminated = 'Y' where available_power.driver_id in (select manpowerprofile.mpp_id from manpowerprofile where manpowerprofile.mpp_terminationdt to_date('31-DEC-2049','DD-MON-') ) At 09:01 AM 5/25/01 -0800, you wrote: I am trying to write an update statement but seem to be having problems. Below is my statement and the error in SQL*Plus. 1 UPDATE AVAILABLE_POWER SET available_power.driver_terminated = 'Y' 2WHERE available_power.driver_id = manpowerprofile.mpp_id 3*and manpowerprofile.mpp_terminationdt TO_DATE('31-DEC-2049','DD-MON-') SQL / and manpowerprofile.mpp_terminationdt TO_DATE('31-DEC-2049','DD-MON-') * ERROR at line 3: ORA-00904: invalid column name I know that manpowerprofile.mpp_terminationdt is a valid table column. Do I need to do some type of join on these two tables? Or is it just Friday and I should give up and go have a cold beer!!:o) Any advice would be appreciated. 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: Regina Harter 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: Creating a sorted table
Well, it won't work in all cases, but I have on occasion used as a shortcut: INSERT INTO ... SELECT DISTINCT transaction_date, ... since the distinct will order it for you, beginning with the first item in the select. A more reliable way would be to use pl/sql, select the ordered data into a cursor, then insert one by one. At 02:45 PM 5/25/01 -0800, you wrote: We have un-ordered data in a table that needs to be inserted into a transaction table in order of the date that the transaction took place. Oracle does not allow INSERT . AS SELECT . ORDER BY. or CREATE TMP_TABLE . AS SELECT . ORDER BY.. Is there a method by which I can accomplish this. Thank you in advance Darren Browett Sys Admin City of Coquitlam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren 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: Regina Harter 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: job offer from SAUDI ARABIA
command for other information (like subscribing). __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Brian Wisniewski 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: Regina Harter 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: job offer from SAUDI ARABIA
Statistically, handguns bought for protection are more likely to be used against a family member (intentionally or otherwise) than against any criminal element. At 04:05 PM 5/17/01 -0800, you wrote: reality check. Consider the poignant implications of the recent anniversary of the Oklahoma city terrorism. The simple fact is that a very high percentage of professional women that have jobs like real estate sales requiring isolated contact with strangers *are* armed (as you state, many with boutique designer guns). it is because they have been assaulted or threatened, or are close to women that have told vivid stories of such. Employees at this campus are required to take *mandatory* campus violence workshops now. Last year a professor was severly beaten by some petty thieves after leaving a night class about 2 minutes walk from my office. Several years ago, someone held an admission counselor hostage at gun point in the building I work in. The person was upset that they weren't admitted to the university. regards, ep (unarmed, but unrepentantly politically incorrect) On 17 May 2001, at 13:16, Marianne Brooks wrote: Date sent: Thu, 17 May 2001 13:16:06 -0800 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] SET MODE SILLY ON Damn, I *knew* I forgot to buy something on my last shopping trip to Nordstroms! Note to self: - buy gun I didn't know I needed. Remember to purchase extras for friends who don't have them either(the gift that keeps on taking?) ... -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce 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: Regina Harter 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: Basic logon architecture for multiple apps in a db
We have the users log in as themselves, because that was the only way to handle the permissions properly. We also use fully qualified table names rather than synonyms, though that was mostly because we have data split among several schemas using identical table names. We just make the table owner dynamic and swap in the proper name at runtime. Works much better than trying to handle swapping and redefining synonyms. At 12:40 PM 4/11/01 -0800, you wrote: O Esteemed and Wise Colleagues, (My first sending of this didn't seem to make it to the list... Knowing our mail server it may show up in a few weeks!) How do application (Forms or other) users access your tables? Do they logon as themselves? Do you switch their logon behind their backs to that of the app owner (like Oracle Apps does?) I'm wrestling with this now. The way I see it, I've got two choices, with several subchoices: 1. User logs in as self and accesses the tables either: a. via synonyms (to tables or to table API package), or b. via full table path qualification, i.e., GL.ACCOUNT or GL.ACCOUNT_API (package). 2. User logs in (knowingly or unknowingly via behind the scenes smoke-and-mirrors) as app owner, and accesses tables directly. Peronally, I much prefer the logging in as self route. It's easier to trace users, sessions, security, access, performance, etc. I also prefer using synonyms, since most application design environments - including Forms - don't fully qualify tables or views by default. The problem is that synonym names can conflict between applications. One solution is to prefix the app_short_name to the name of each table or view. I hate that. Another thought is to create synonyms dynamically as the user logs on to an application. That's no good if the user logs on to two apps at the same time. If you go with relogging in as the app owner, you somehow have to keep track of who the user really is (some common package variable, most likely) and then use that info as needed. That sounds like lots of extra code. So, how do YOUR users access your apps? Any ideas? I need guidance, and I'll really, truly, honestly, very much appreciate any you can send my way. TIA, Yosi -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yosi Greenfield 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: Regina Harter 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: Optimizer theory: Question on access paths for outer joins
I am a little confused. Is not PHONE, in this query, the one WITHOUT the +? At 11:25 AM 4/10/01 -0800, you wrote: Hi all, I was just trying to figure out why in an outer join Oracle prefers to access the table with the (+) first. I would have thought that the table from which all the data was coming would come first and then appropriate rows would come from the second table with nulls being generated for non-existent rows. I started looking at it because of the following query: Simplified SQL: select stuff from inlineview1 T0, inlineview2 T1, inlineview3 T2, account ac, phone ph where T0.generic_id = T1.account_id and T1.account_id = T2.account_id (+) and T1.valid_flag = T2.valid_flag (+) and T0.generic_id = ac.id and ph.id = ac.current_phone_id(+) In this query the inline views are rather complicated but apply substantial restrictions on ACCOUNT (a huge table, as is PHONE). Logically, it is faster to run the inline views first, join them to ACCOUNT and then go to phone. The Optimizer kept doing a full table scan on PHONE first, and then joining to Account. I tried ORDERED, FIRST_ROWS and INDEX hints to no avail. The hints work if I take away the outer join symbol (but of course this gives incomplete results). I finally tricked Oracle into going in the correct order by adding a WHERE clause to the ACCOUNT of AND ac.id 0 (presumably causing the Optimizer to think there's more of a restriction on ACCOUNT and therefore taking it first). Since id is always greater than 0 this doesn't change the results but makes the query run much faster. So I have it working the way I want, but I'm still wonderinG why the Optimizer prefers to read the (+) table first? From the "Everything you always Wanted to Know About the Oracle Optimizer" book I know that the Optimizer tries to sort the join orders in ascending order of their computed cardinality. I'd guess that the Optimizer assumes an outer joined table will be returning some default percentage of the other table and therefore should always be accessed first? Can anyone confirm or refute this? Thanks! Jay Miller -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Miller, Jay 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: Regina Harter 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: order by non-unique column, repeatable?
You are correct. Even though 95% of the time they WILL be returned in the same order, it is definitely not guaranteed. At 09:50 AM 4/6/01 -0800, you wrote: If a select statement orders by a column whose values are not unique, are the records returned in an order that is guaranteed to be repeatable from query to query? (Assuming no updates, deletes, or inserts between queries.) I would think not, that guaranteed repeatable ordering requires ordering on a unique column or combination of columns, but I've been unable to generate a test case that proves it. Thanks Bill -- __ http://www.datacraft.com/http://plnet.org/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Pribyl 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: Regina Harter 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 Question
You can do something like select to_char(date_column,'YYWW'), count(*), sum(other_column) from table where date_column between date1 and date 2 group by to_char(date_column,'YYWW') where WW returns the week of the year ( 1 to 53) keeping in mind, of course, that you must include in the group by any column in the select which does not have a group function on it (sum, max, min, avg, etc). At 09:35 AM 4/3/01 -0800, you wrote: Hello all, Can someone tell me whether or not there is a way do this in SQL? I want to select some data given a certain date range,i.e where some_date between start_date and end_date. Is there a way to group the output by week? In other words, something like: SELECT column_a, column_b, count(*), sum(column_c) FROM table_t WHERE some_date_column BETWEEN :start_date and :end_date GROUP BY ...? How this be grouped by week? Thanks a lot. __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Viktor 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: Regina Harter 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: why is short bread short? / Re: OT -- Friday Recipe was:
At 11:56 AM 3/29/01 -0800, you wrote: Anyway, instead of sending any more "cultural enricment friday" stuff, I would instead like to ask why is short bread "short"? If I am recalling correctly, it was originally "shortening bread". The basic recipe is butter plus enough flour and sugar that is doesn't melt all over the pan. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: why is short bread short? / Re: OT -- Friday Recipe was:
The best definition I found is from the Encyclopedia Brittanica, which says shortening is a product with "shortening power, or the ability to weaken and lubricate the structure of baked products to produce tenderness". Another theory I heard is shortening is a product which makes you look shorter because it makes you rounder. At 02:36 PM 3/29/01 -0800, you wrote: lwd have mercy. now you have to explain the original meaning of "shortening" please! regards, ep On 29 Mar 2001, at 13:14, Regina Harter scribbled with alacrity and cogency: ... If I am recalling correctly, it was originally "shortening bread". The basic recipe is butter plus enough flour and sugar that is doesn't melt all over the pan. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Eric D. Pierce 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: Regina Harter 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: SQLCode in 8i
Thanks for the suggestion, Prakash, but SQLDBCode is also returning 0 for no rows found. At 08:25 AM 3/28/01 -0800, you wrote: Regina, try sqldbcode. This will give the exact Oracle return code. Prakash -Original Message- Sent: Wednesday, March 21, 2001 9:05 PM To: Multiple recipients of list ORACLE-L We have a number of Powerbuilder applications for which we have been using the SQLCode returned from a sql statement execution to determine the next action, depending on if the statement failed (SQLCode 0), didn't return any rows (SQLCode = 100) or successfully returned data (SQLCode = 0). We upgraded to 8.1.6 two weeks ago, and just discovered that, outside of an exception block, SQLCode now always returns 0. Do any of you know a way to return to the old codes, or make it treat Powerbuilder like an exception block, or know of some other code we could use to gather this information? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Bala, Prakash 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: Regina Harter 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: SQLCode in 8i
We're not connecting via ODBC, we're using the native drivers, but yes, we have tried both the 7.3 driver we used to use and both the 8.0 and 8.1 drivers. At 12:12 PM 3/28/01 -0800, you wrote: Regina, did you try using the ODBC driver that you used earlier. -Original Message- Sent: Wednesday, March 28, 2001 1:46 PM To: Multiple recipients of list ORACLE-L Thanks for the suggestion, Prakash, but SQLDBCode is also returning 0 for no rows found. At 08:25 AM 3/28/01 -0800, you wrote: Regina, try sqldbcode. This will give the exact Oracle return code. Prakash -Original Message- Sent: Wednesday, March 21, 2001 9:05 PM To: Multiple recipients of list ORACLE-L We have a number of Powerbuilder applications for which we have been using the SQLCode returned from a sql statement execution to determine the next action, depending on if the statement failed (SQLCode 0), didn't return any rows (SQLCode = 100) or successfully returned data (SQLCode = 0). We upgraded to 8.1.6 two weeks ago, and just discovered that, outside of an exception block, SQLCode now always returns 0. Do any of you know a way to return to the old codes, or make it treat Powerbuilder like an exception block, or know of some other code we could use to gather this information? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Bala, Prakash 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: Regina Harter 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: Bala, Prakash 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: Regina Harter 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: Inserting data from long table into wide table
I believe a decode and group by would work well for you, something like: insert into tableb select id, sum(decode(name,'P1',value,0)), sum(decode(name,'P2',value,0)), ... from tablea group by id; At 11:19 AM 3/22/01 -0800, you wrote: I have a table A with the following data (Table A has 3 columns id, name, value) id name value 10 P1 20 10 P2 60 10 P3 12.5 10 P4 26 20 P1 100 20 P2 90 20 P3 15 20 P4 36 30 P1 60 30 P2 50 30 P3 11.5 30 P4 13 more rows I want to insert this data into table B as follows (Table B has 4 columns id, P1, P2, P3, P4) id P1 P2 P3 P4 10 20 60 12.526 20 100 90 15 36 30 60 50 11.513 ... more rows Can you suggest a Sql statement for the insert into table B. Thank you very much in advance. Erma _ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erma Fernando 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: Regina Harter 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).
SQLCode in 8i
We have a number of Powerbuilder applications for which we have been using the SQLCode returned from a sql statement execution to determine the next action, depending on if the statement failed (SQLCode 0), didn't return any rows (SQLCode = 100) or successfully returned data (SQLCode = 0). We upgraded to 8.1.6 two weeks ago, and just discovered that, outside of an exception block, SQLCode now always returns 0. Do any of you know a way to return to the old codes, or make it treat Powerbuilder like an exception block, or know of some other code we could use to gather this information? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter 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: Insert without telling column names
At 08:36 AM 3/20/01 -0800, you wrote: Roland, You can't. There are a number of ways to really cut down on the code required to do an insert, but if you think about it, how will Oracle know where the data is supposed to go if you don't give it the column name(at some point)? Of course you can. The only caveat is Oracle will assume you are filling all the columns in order, therefore you must provide a value for every column, ie For Table T1(firstname, lastname, telephone, fax, email) insert into T1 values('John','Smith','4849910','','[EMAIL PROTECTED]') Note the '' in place of a fax number. I could also have used null. I never use this method in applications or procedures, of course, because then if you change the table definition it no longer works, but for testing stuff, or something I'm only going to run once it's a decent shortcut. If you're using 8i, there is a direct load insert, but as far as I'm aware, this will work only for a select * from another table. Dynamic SQL works pretty well for this kind of stuff. What are you trying to do? Regards, David A. Barbour Oracle DBA Formerly with the now defunct and bankrupt ConnectSouth [EMAIL PROTECTED] wrote: Hallo, How can I create an insert statement without telling all the names of the columns? Give an example, please. Roland Skldblom -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT -- A humorous reply to the Canada rulz
At 10:07 AM 3/20/01 -0800, you wrote: U, Is this kinda like Mr. Sulu (that was Mr Chekov) on Star Trek who was sure that the Russians invented everything? I might be wrong (happens all the time), but I thought Baseball and Basketball were distinctly inventions of the USA. You are correct, though, as far as basketball goes, they might be referring to the fact that the gentleman who invented basketball (in New Hampshire) was born in Canada (and apparently beat feet to the US as soon as he could ;) ) You might be able to tell I'm a rabid fan of neither. Penicillin and the telephone?? Other delusions of grandeur? As for penicillin: The action of natural penicillin was first observed in 1928 by British bacteriologist Sir Alexander Fleming, but another ten years passed before penicillin was concentrated and studied by British biochemist Ernst Chain, British pathologist Sir Howard Florey, and other scientists. And the telehpone: On March 10, 1876, in Boston, Massachusetts, Alexander Graham Bell invented the telephone. Thomas Watson fashioned the device itself; a crude thing made of a wooden stand, a funnel, a cup of acid, and some copper wire. But these simple parts and the equally simple first telephone call -- Mr. Watson, come here, I want you! -- belie a complicated past. Bell filed his application just hours before his competitor, Elisha Gray, filed notice to soon patent a telephone himself. What's more, though neither man had actually built a working telephone, Bell made his telephone operate three weeks later using ideas outlined in Gray's Notice of Invention, methods Bell did not propose in his own patent. Elisha Gray was from Ohio, Alexander Graham Bell was from Scotland, though he did live in Canada 2 years before moving to Boston. How about the only country that can't decide if they're English or French? How about the US's biggest colony via cultural conquest(ohh, that's a low blow). How about never saw a bikini except in National Geographic? I could go on, but I'm not sure my humor translates all that well, and the firewall has Babelfish blocked. Dan not about to give up my day job -Original Message- Sent: Tuesday, March 20, 2001 12:37 PM To: Multiple recipients of list ORACLE-L Sorry about this, but someone sent me this and I thought it might be relevant to the Canada and US discussion that took place last week. With all the recipes going around, I thought it wouldn't do any harm. Regards, Patrice Boivin Systems Analyst (Oracle DBA) Bedford Institute of Oceanography Fisheries and Oceans Canada -Original Message- So, what do Canadians have to be proud of? *Smarties *Crispy Crunch Coffee Crisp *The size of our footballs fields and one less Down *Baseball is Canadian *Lacrosse is Canadian *Hockey is Canadian *Basketball is Canadian *Apple pie is Canadian *Mr. Dress-up kicks Mr. Rogers ass *Tim Hortons kicks Dunkin' Donuts ass *In the war of 1812, started by America, Canadians pushed the Americans back...past their 'White House'. Then we burned it... and most of Washington, under the command of William Lyon McKenzie who was insane and hammered all the time. We got bored because they ran away, so we came home and partied ... Go figure... *Canada has the largest French population that never surrendered to Germany. *We have the largest English population that never ever surrendered or withdrew during any war to anyone, anywhere. *Our civil war was a bar fight that lasted a little over an hour. *The only person who was arrested in our civil war was an American mercenary, who slept in and missed the whole thing...but showed up just in time to get caught. *We knew plaid was cool far before Seattle caught on. *The Hudsons Bay Company once owned over 10% of the earth's surface and is still around as the worlds oldest company *The average dog sled team can kill and devour a full grown human in under 3 minutes. *We still know what to do with all the parts of a buffalo. *We don't marry our kin-folk. *We invented ski-doos, jet-skis, velcro, zippers, insulin, penicillin, zambonis, the telephone and short wave radios that save countless lives each year *We ALL have frozen our tongues to something metal and lived to tell about it. *BUT MOST IMPORTANT! *the handles on our beer cases are big enough to fit your hands with mitts on. ooh Canada!! Oh yeah... and our elections only take one day. -- 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
Re: OT: Oracle *Chocolate* Monitoring Tools/Friday Recipe
ity 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). = [EMAIL PROTECTED] O'Reilly's "Oracle and Open Source": = http://www.oreilly.com/catalog/oracleopen/ Orac, Perl/Tk and Perl DBI Database DBA Development Tool: = http://www.perl.com/CPAN-local/modules/by-module/DBI/ANDYDUNC/ __ Do You Yahoo!? Get email at your own domain with Yahoo! Mail. http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mark Leith 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: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT: Oracle *Chocolate* Monitoring Tools/Friday Recipe
Are you saying 2 eggs means something different in England? ;) At 01:23 PM 3/16/01 -0800, you wrote: not one of those measurements (especially not "gas mark 5") will work for an American :) From: "Mark Leith" [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: OT: Oracle *Chocolate* Monitoring Tools/Friday Recipe Date: Fri, 16 Mar 2001 07:26:41 -0800 Well here goes then - Good job I went and got it just in case at lunch huh :0) I just knew that SOMEBODY would have to have a chocolate fetish on this list!! You guys are gonna love this one, it is soft moist - a little like the good old American brownie, but it is essentially a Loaf Cake. Ingredients: 225g soft unsalted butter 375g dark muscovado sugar 2 large eggs - beaten 1 teaspoon vanilla extract 100g best dark chocolate - melted 200g plain flour 1 teaspoon bicarbonate of soda 250ml boiling water 23*13*7cm loaf tin Preheat the oven to 190c/gas mark 5, put in a baking sheet case of gooey sticky drips later;) and grease and line the loaf tin. The lining is important as this is a very damp cake: use parchment, Bake-O-Glide or one of those loaf-tin-shaped paper cases. Cream the butter and sugar, either with a wooden spoon or an electric hand held mixer, then add the eggs and vanilla, beating in well. Next fold in the melted and now slightly cooled chocolate, taking care to blend well, but being careful not to over beat. You want the ingredients combined: you DON'T want a light airy mass. Then gently add the flour, to which you've added the bicarb, alternately spoon by spoon, with the boiling water until you have a smooth and fairly liquid batter. Pour into the lined loaf tin and bake for 30 mins. turn the oven down to 170c/gas mark 3 and continue to cook for another 15 mins. The cake will still be a bit squidgy inside, so an inserted cake-tester or skewer won't come out completely clean. Place the loaf tin on a rack, and leave to get completely cold (if you can resist it G) before turning it out. (The *Author* recommends leaving it for a day - hhuummm you won't get that far I can guarantee). Don't worry if it sinks in the middle: indeed it WILL do so because its such a dense and damp cake. She goes on to say it makes 8-10 slices. My advice is share it with you nearest and dearest only and cut it in to about 3! :0) There you go Friday Recipe has returned, now who's turn is it next week? For all those chocoholics - you have simply GOT to try this. If anybody bakes it over the weekend - let me know what you think? I think it is THE best choccy treat out! Regards Mark OCP Certified (Original Chocolate Professional) MCSE (Main Chocolate Supervisory Eater) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Regina Harter INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
OT for Lee Roberson
t 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: Regina Harter 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: sequence generator
At 04:30 PM 2/23/01 -0800, you wrote: Can anyone correct/confirm the following: A sequence generated number is auto committed. i.e if the transaction that generates the sequence number fails which eventually gets rolledback the generated sequence number generated is lost and not available for the next transaction. You are correct, sequences are auto committed. Is there by anyway prevent lossing of the sequence generated number in case of the transaction that generated it fails.Any work around? The problem is, with this happening finnally the column that will have the sequence genarted number will not be continuous set of numbers. Generally, sequences are not good options for values which must be a continuous set of numbers. I know of no good way to recover lost sequence values. On the few occasions that we have required a continuous set of numbers, we have used a separate table which contains the next number in the sequence, which is then updated after use, which operation will of course be rolled back in case of failure. Another option is just to user max + 1 of the column in question, but coding that can sometimes get messy, depending on what else you are doing. I needed a second opinion on the above points. Thanks Ravindra -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ravindra Basavaraja 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: Regina Harter 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: May be Oracle Bug in View
I am uncertain why you believe this to be a problem. Its using the default number size (38 I think) but it's not really storing it anywhere, it's just keeping the definition, so what do you care how big it thinks it is? Its plenty large enough to handle any sum of to number(20)s. At 08:31 AM 2/16/01 -0800, you wrote: I have table which has two cols in it like below: create table test (col1 number(20), col2 number(20)); Table created. Now, I have a situation where I need to create a view on this table with three columns and third column is sum of first and second columns. create view test_view (col1, col2, col3) as select col1, col2, col1+col2 from test; View created. check this out when I describe the view : desc test_view Name Null? Type - COL1 NUMBER(20) COL2 NUMBER(20) COL3 NUMBER The Length of third column is missing , could anybody throw some light on this issue .. Please advice me how the handle this problem Veera -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Veera Prasad 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: Regina Harter 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: Silly question perhaps -- iAS vs. IIS
We use IIS very successfully connecting to our Oracle database. I can't really give you impact because I don't know what a OAS connection is like, but we generally get very good response from the Web Server, and always get a good response from the database. However, it is rare that we have more than 20 users connected at once, though the web or otherwise. The only thing we have had to be very careful about is being sure to close all connections on the same page that we open them because the connection doesn't die just because the session was closed. At 04:37 AM 2/8/01 -0800, you wrote: I don't know if this is a silly question, but I am playing devil's advocate because one of the managers here made a comment re. this. Why would someone bother using the Oracle Web Server anyway? We have two boxes that use OWS 3.0.1.1 (we will upgrade very soon to OAS 4.0, Oracle doesn't support 3.0.1.1 anymore), with JInitiator and Forms / Reports server. Another box is using the Oracle Application Web Toolkit to access my database, generating thousands of separate sessions per day, each about one second in length. I don't know about the other Web server options out there, is there a way to do the same thing with IIS and OLE DB, for example? What would the impact be on Web server performance, the network, and the back end database? Just wondering, please enlighten me. I asked the ODTUG listserv but there was resounding silence. TIA Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Rgion des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- 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: Regina Harter 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).