RE: Oracle 8i database ER diagram
There's also a tool from Embarcadero called ER Studio, which is my personal recommendation at the moment. I used to recommend ERWin, but then CA bought it and it has only been lackadaisically maintained since then. I think there are others...basically any decent ER modelling tool (and Visio is NOT that) can usually reverse engineer from the data dictionary. Now, if you don't have your foreign key constraints defined in the database, you're pretty much out of luck... :-) Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Rao, Maheswara Maheswara.Rao@SungTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ardp3.com cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: RE: Oracle 8i database ER diagram 06/12/2001 09:15 AM Please respond to ORACLE-L Magesh, You could use either of the following tools to build your ERD through reverse engineering. 1. Oracle Designer 2. ERWIN In my company we use both the above products. I personally prefer using Oracle Designer due to many reasons (My reasons are irrelevant here for the purpose of answering your question). If it is one time job, then you could download either of the products free (Please check whether you could do reverse engineering with a free downloaded version). Rao -Original Message- Sent: Tuesday, June 12, 2001 7:01 AM To: Multiple recipients of list ORACLE-L What about to use Oracle Designer?. Regards. Miguel Urosa. 12/06/2001 11.00 magesh [EMAIL PROTECTED] 12/06/2001 11.01 -- 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: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: SID on sqlplus prompt?
Neat! I knew there was some way of selecting that, but couldn't remember
it and also couldn't find them in the FM. Didn't someone complain about
the terrible indexes on Oracle manuals? Let me add my voice to that
particular clamor.
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: [EMAIL PROTECTED]
MacGregor,
Ian A. To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
[EMAIL PROTECTED]cc:
nford.EDU Fax to:
Sent by: Subject: RE: SID on sqlplus prompt?
root@fatcity.
com
06/09/2001
12:40 PM
Please
respond to
ORACLE-L
If the db_name will do just as well you can use
sys_context('USERENV', 'DB_NAME') to obtain the database name and not
have to grant access to any of the v$ tables. There's also
ora_database_name which is a function that takes no arguments and returns
db_name.domain_name Both of these can selected from dual
Ian MacGregor
Stanford Linear Accelerator Center
[EMAIL PROTECTED]
-Original Message-
Sent: Friday, June 08, 2001 2:30 PM
To: Multiple recipients of list ORACLE-L
How about something like this? Of course, it requires that the user have
select access to v$instance -- maybe there's another place to find this.
You could do some sort of batch to reference the $ORACLE_SID instead, but
the advantage of this is you can put it into glogin.sql or login.sql -- or
maybe not. I just tried doing that and it didn't affect anything. Hmm.
Oh, well, this may help you...
set heading off
set term off
set feedback off
spool set_sqlprompt.lst
select 'set sqlprompt ' || instance_name || ' '
from v$instance
/
spool off
@set_sqlprompt.lst
set heading on
set term on
set feedback on
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: [EMAIL PROTECTED]
Walter K
alden14004@yTo: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
ahoo.comcc:
Sent by: Fax to:
root@fatcity.Subject: SID on sqlplus
prompt?
com
06/08/2001
04:35 PM
Please
respond to
ORACLE-L
Hi,
Is there a way to get the SID or database name
displayed in the command prompt of SQL*Plus? Can this
be generated dynamically if I perform a CONNECT
user/pw ?
Thanks!
-w
__
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail - only $35
a year! http://personal.mail.yahoo.com/
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Walter K
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of
Re: SID on sqlplus prompt?
How about something like this? Of course, it requires that the user have select access to v$instance -- maybe there's another place to find this. You could do some sort of batch to reference the $ORACLE_SID instead, but the advantage of this is you can put it into glogin.sql or login.sql -- or maybe not. I just tried doing that and it didn't affect anything. Hmm. Oh, well, this may help you... set heading off set term off set feedback off spool set_sqlprompt.lst select 'set sqlprompt ' || instance_name || ' ' from v$instance / spool off @set_sqlprompt.lst set heading on set term on set feedback on Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Walter K alden14004@yTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ahoo.comcc: Sent by: Fax to: root@fatcity.Subject: SID on sqlplus prompt? com 06/08/2001 04:35 PM Please respond to ORACLE-L Hi, Is there a way to get the SID or database name displayed in the command prompt of SQL*Plus? Can this be generated dynamically if I perform a CONNECT user/pw ? Thanks! -w __ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail - only $35 a year! http://personal.mail.yahoo.com/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Walter K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: dumn unix script question
While on the one hand I completely agree, as in my opinion Perl is the absolutely bestest, yummiest and downright smartest language I've ever encountered -- if you learn it first, you'll never be satisfied or happy in any other language again ever. ;-) Also, as a learning language, it can be confusing because any given problem has as many possible solutions as there are programmers. Learners tend to want to know one way to do things at first. I taught a couple of Perl classes and all of my students suffered from information overload. That may have been that I was a poor teacher...yeah, that was probably it. I was also so enthusiastic about the logic and linguistics of Perl that I think I talked over their heads. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Jared Still jkstill@cybcTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] on.com cc: Sent by: Fax to: root@fatcity.Subject: Re: dumn unix script question com 06/08/2001 05:11 PM Please respond to ORACLE-L Let me start off by saying that I like Java. I took a class in it from Sun, and Java is easy. Learning the libs is hard but the language is easy. I'm going to say something now that may sound counter intuitive: Learn Perl first. The reason it may seem counter intuitive is that Perl is hard to learn, at least at first. Much easier if you've had exposure to C though. The reason for learning Perl is that there are many tasks that are easy in Perl that are hard in Java. It's the same old balance: with power comes complexity. The basics of Perl aren't really *too* hard, just take a little getting used to. The payoff is big. There is no better language for data munging than Perl. If you need to clean up data for SQL loader files, use Perl. Don't try to do it with PL/SQL or Korn as it is just too much work. While SQL*Loader allows you to do some very complex things in it, it is *very* difficult at times. Clean your data first. Need to deal with a lot of text files, search for errors in log files, etc., do it with Perl. You will also be able to develop Perl code much faster than you can develop Java code. Java is very wordy, Perl can be very terse if you want or require. Perl makes hard things easy, and impossible things possible. :) see www.perl.com for articles, downloads and tutorials. Good first books for the non-programmer are Learning Perl and/or Learning Perl for Win32. Jared On Friday 08 June 2001 04:10, Mark Leith wrote: A quick question - I have VERY limited scripting experience - for arguments sake, lets say - none:) Now, looking at the code below, and having started with java a short time ago to implement certain functions in to our web page, I have to say that they look similar in style.. Is the case? Could I learn one code intimatley - like java - and have a good head start when faced with others? If so, which would you reccomend starting to REALLY learn? I'm thinking java myself, and have already started as mentioned, but wanted to get your invaluable knowledge.. Cheers Dorothy Red Shoes -Original Message- Sent: Friday, June 08, 2001 01:46 To: Multiple recipients of list ORACLE-L Well, you've got the right idea, just in the wrong order. The korn
Re: SQL : Order by for varchar ???
Ooh, a fun one. If you are sure of the format of the data (as you'll see
in the following function) you can create a function to make the data
numeric. I can't think of another way to do it, but maybe someone else
can?...
create or replace function fractionToDecimal (str in varchar2) return
number is
fract varchar2(100);
dec number;
begin
-- This function takes numbers in the form below and returns a decimal
number
-- [whole-]numerator[/denominator][|']
-- For example:
-- 5/8
-- 1-1/2
-- 4'
--
-- Remove the symbol.
--
fract := rtrim(str, );
-- Replace occurences of '-' with '+'
--
fract := replace(fract, '-', '+');
-- Evaluate the resulting expression
--
execute immediate 'select ' || fract || ' from dual' into dec;
return dec;
end fractionToDecimal;
/
1 select width, fractionToDecimal(width) from test
2* order by 2 desc
SQL /
WIDTHFRACTIONTODECIMAL(WIDTH)
4 4
2 2
1-1/21.5
1 1
3/4 .75
5/8 .625
6 rows selected.
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: [EMAIL PROTECTED]
Apps Sol
apps_sol@hotTo: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
mail.comcc:
Sent by: Fax to:
root@fatcity.Subject: SQL : Order by for varchar ???
com
06/07/2001
01:56 PM
Please
respond to
ORACLE-L
One of our developers was looking for solution for his problem ..
Any idea folks .. ??
He wants to sort his data asc or desc for a varchar column ..
1 create table test(width varchar2(20));
2
insert into test values('5/8');
insert into test values('4');
insert into test values('3/4');
insert into test values('2');
insert into test values('1-1/2');
insert into test values('1');
3
select width from test order by width DESC;
5/8
3/4
1
1-1/2
2
4
Cheers
RK
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Apps Sol
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or
RE: Serious Question (believe it or not)...
I don't know about that...am I the only one that finds Feuerstein's books a bit too pithy and not real enough? (No disrespect intended) I've certainly toyed with the idea of a PL/SQL book with real-life situations and examples, especially in the use of triggers and packages for enforcing complex and interesting business rules. Just my $0.02 Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] carmichr@hotcc: mail.comFax to: Sent by: Subject: RE: Serious Question (believe it or not)... root@fatcity. com 06/07/2001 03:06 PM Please respond to ORACLE-L Jim, Smart man -- you can write a book anytime, your kids are young only once :) And before you think about writing that tips book -- Oracle Press has an 8i tips and techniques book out (I believe Douglas Scherer is one of the authors) There is really nothing new under the sun. Rachel From: Hawkins Family [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Serious Question (believe it or not)... Date: Thu, 07 Jun 2001 08:52:02 -0800 Thanks for the reply, Rachel, and all others who wrote back offline and through the list. Basically, here was my idea: Most DBA topics have been beaten to death, and I would have nothing new to contribute. There is one area, however, that I see could be explored... Here goes... Tips, Tricks, and Epiphanies by various authors. I was thinking about putting all those neat little tricks and tips I've learned over the last few years down in writing. Examples would be everything from dynamic SQL to connecting as a user without knowing their password via the encrypted string. Also, I've had epiphanies where about 8 things all the sudden come together at once in a moment of shining brilliance. One such thing happened a few weeks ago courtesy of Rachel herself in describing the recovery using a backup controlfile (open-ended SCN). These are the things I think could be useful - things not normally included in a DBA 101 book. So here's what we do: I think of 5 or 10 things like this, Jared thinks of his, Rachel of hers, Ross of his, April of hers, and anyone else. We then make sure there's no overlap. We then organize these into the following 5 sections: Administration, Backup/Recover, Tuning, SQL or PL/SQL, Miscellaneous. If we get 10 experts to contribute 10 unique tips, tricks, or epiphanies, then we have a book. Someone (me or Jonathan Gennick from O'Reilly) compiles them all and writes some wrapper material, and voila!a book! Easy, right? I do have a writing background, so this is why I became interested. Having said all this, having 3 kids under the age of 4 and a wife, I also have taken what Rachel said to heart. Right now, I think it would just be something to think about. ^ Jim Hawkins Lead SAPR/3 Oracle Database Administrator MEMC Electronic Materials, Inc. 501 Pearl Drive St. Louis, MO 63376 (636) 474-7832 [EMAIL PROTECTED] (work) [EMAIL PROTECTED] (personal) ^
RE: Serious Question (believe it or not)...
Oh, that would be great! Add some tips on how to spec out a system, and I'm buying it! Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] mohammed bhatti To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] mkb125@yahoocc: .comFax to: Sent by: Subject: RE: Serious Question (believe it or not)... root@fatcity. com 06/07/2001 04:39 PM Please respond to ORACLE-L Rachel/Jim, Yeah, you have a good point. Their are a bunch of Oracle books out there covering everything about the database. But how about a book that's a little different that covers implementation. Right now I'm in the middle of trying to setup HA possibly using Veritas Cluster Server and looking into BCV/TimeFinder with EMC and Oracle. Sounds like this combination would cover a lot of sites. Yeah, there are a lot of Oracle books and a lot of Unix admin books, but not a lot (if any) books that tie the implementation of all these pieces together into a neat little book. I know you're probably thinking this kinda leans more towards SA type stuff but a book like this would have helped me a whole bunch right now. I also figure there are quiet a few DBAs' that do sysadmin stuff aswell and a book of this nature could be very usefull. --- Rachel Carmichael [EMAIL PROTECTED] wrote: Jim, Smart man -- you can write a book anytime, your kids are young only once :) And before you think about writing that tips book -- Oracle Press has an 8i tips and techniques book out (I believe Douglas Scherer is one of the authors) There is really nothing new under the sun. Rachel From: Hawkins Family [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Serious Question (believe it or not)... Date: Thu, 07 Jun 2001 08:52:02 -0800 Thanks for the reply, Rachel, and all others who wrote back offline and through the list. Basically, here was my idea: Most DBA topics have been beaten to death, and I would have nothing new to contribute. There is one area, however, that I see could be explored... Here goes... Tips, Tricks, and Epiphanies by various authors. I was thinking about putting all those neat little tricks and tips I've learned over the last few years down in writing. Examples would be everything from dynamic SQL to connecting as a user without knowing their password via the encrypted string. Also, I've had epiphanies where about 8 things all the sudden come together at once in a moment of shining brilliance. One such thing happened a few weeks ago courtesy of Rachel herself in describing the recovery using a backup controlfile (open-ended SCN). These are the things I think could be useful - things not normally included in a DBA 101 book. So here's what we do: I think of 5 or 10 things like this, Jared thinks of his, Rachel of hers, Ross of his, April of hers, and anyone else. We then make sure there's no overlap. We then organize these into the following 5 sections: Administration, Backup/Recover, Tuning, SQL or PL/SQL, Miscellaneous. If we get 10 experts to contribute 10 unique tips,
Re: Help for Unix text file processing
Here are some sed commands that do what you want -- in the [] brackets, there is a space and a tab. $ cat removeblanks.sed /^[ ]*$/d s/^[]*// s/[ ]*$// Here's a test file. $ cat test.txt This is a real line, the lines above are a carriage return, a tab and spaces. This line starts and ends with tabs. This line starts and ends with spaces This line starts and ends with spaces and tabs Here's what you run: $ sed -f removeblanks.sed test.txt This is a real line, the lines above are a carriage return, a tab and spaces. This line starts and ends with tabs. This line starts and ends with spaces This line starts and ends with spaces and tabs HTH! Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Kumar, Dharminder To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Dharminder.Kumar@cc: bmonb.comFax to: Sent by: Subject: Help for Unix text file processing [EMAIL PROTECTED] 06/06/2001 10:55 AM Please respond to ORACLE-L Hey all, I am looking for some commands like ( grep, egrep, sed etc) to do the following on a Unix box. 1. Command to take out all the blank line from a text file. The blank line may include tabs and whitespaces. 2. Command to take out the trailing and starting blanks and tabs from the each line of the text file. 3. Command for the above two tasks combined. Thanks in advance. Dharminder Kumar This e-mail and any attachments may contain confidential and privileged information. If you are not the intended recipient, please notify the sender immediately by return e-mail, delete this e-mail and destroy any copies. Any dissemination or use of this information by a person other than the intended recipient is unauthorized and may be illegal. Unless otherwise stated, opinions expressed in this e-mail are those of the author and are not endorsed by the author's employer. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kumar, Dharminder INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql error
Harvinder, You must have a carriage return at the end of the enum variable. Try rtrim, like this: name2 :='t_pv_'||substr(rtrim(enum,chr(10),i+1)||'_2'; Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Harvinder Singh Harvinder.Singh@MetrTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] aTech.com cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: pl/sql error 06/06/2001 07:06 PM Please respond to ORACLE-L Hi, I am running the code containing following statements name1 :='t_pv_'||substr(enum,i+1)||'_1'; name2 :='t_pv_'||substr(enum,i+1)||'_2'; name3 :='t_pv_'||substr(enum,i+1)||'_3'; dbms_output.put_line(name1); dbms_output.put_line(name2); dbms_output.put_line(name3); str :='update'||' '||name2||' '||'set id_sess=id_sess+'||temp_id_sess_2; execute immediate str; it show output og name 1 as t_pv_ps_cc_credit _3 and not as t_pv_ps_cc_credit_3 .. and i feels it failing my execute immediate str statement and getting error: ORA-00911: invalid character ORA-06512: at line 37 How i can improve this code 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: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql problem
In addition to the reason for your error, which someone else has pointed out, this piece of code has another problem -- you never close your cursor. This will get you into no ends of trouble at some point in the future, so I though I should point it out. Also, a cursor for loop would be much cleaner, and you won't have to worry about closing the cursor. Like the following... declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); --enum t_enum_data.nm_enum_data%type;Don't need this variable declaration any more begin dbms_output.enable(90); -- This can be a value up to 99 for enumRec in c1 loop dbms_output.put_line(enumRec.nm_enum_data); end loop; end; / Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Harvinder Singh Harvinder.Singh@MetrTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] aTech.com cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: pl/sql problem 06/06/2001 06:05 PM Please respond to ORACLE-L Hi, i am running following code declare cursor c1 is select nm_enum_data from t_enum_data where id_enum_data in (select distinct id_view from t_acc_usage_1 union select distinct id_view from t_acc_usage_2 union select distinct id_view from t_acc_usage_3 ); enum t_enum_data.nm_enum_data%type; begin open c1; loop fetch c1 into enum; exit when c1%notfound; dbms_output.put_line(enum); end loop; end; / i am getting error: declare * ERROR at line 1: ORA-2: ORU-10027: buffer overflow, limit of 2000 bytes ORA-06512: at SYS.DBMS_OUTPUT, line 91 ORA-06512: at SYS.DBMS_OUTPUT, line 58 ORA-06512: at line 16 What might be the reason 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: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: How to prevent oracle from committing a transaction?
Yeah, that's what I was going to suggest. QUIT used to do an exit without a commit, but now it commits it just like EXIT does. I wonder why they changed that? Now they are just synonyms of one another. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] sfaroult@orio le.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: root@fatcity.Fax to: com Subject: Re: RE: How to prevent oracle from committing a transaction? 06/05/2001 05:40 AM Please respond to ORACLE-L Alternatively, you can also use QUIT. Stephane Faroult What you're seeing is an implicit commit !To prevent it - before exiting the session , issue ROLLBACK; then EXIT vikas -Original Message- Sent: Tuesday, June 05, 2001 12:30 AM To: Multiple recipients of list ORACLE-L Dear DBA Gurus, Can you tell me how to prevent oracle from committing a transaction unless I explicitly commit it. I have observed that when I insert a record into a table and type exit from the sql prompt without commiting the transaction and again open a new sqlplus session and select from that particular table the record is inserted. How do I prevent oracle from inserting into a table unless I explicitly specify commit? Please note that I have set my sqlplus session as autocommit off. I tried with alter table tablename nologging but it didn't do what I wanted. Can anybody help me in this regard? TIA and Regards, Ranganath -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ranganath K INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Vikas Kawatra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Diese E-Mail wurde mit http://de.mail-inspector.de verschickt Mail Inspector ist ein kostenloser Service von http://www.is-fun.net Der Absender dieser E-Mail hatte die IP: 195.25.229.12 -- 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
RE: How to prevent oracle from committing a transaction?
Richard, The discussion so far has not found a way to turn that off -- even set autocommit off will still commit when you exit (or quit, dang it), unless you EXIT ROLLBACK; Did you find another way? Or is that what you mean? Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Richard Ji [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] om cc: Sent by: Fax to: root@fatcity.Subject: RE: How to prevent oracle from committing a transaction? com 06/05/2001 01:20 PM Please respond to ORACLE-L No Oracle doesn't do commits for checkpoint, log switch or shutdown normal. It will only commit data explicitliy (using the commit command) or implicitly (DDL). SQL*PLUS by default auto commits for you if you just exit after doing some DML. But you can turn that off. Richard Ji [EMAIL PROTECTED] 06/05/01 11:47AM Wait a minute here. Why would oracle do a commit on your transaction just because it needs to do a checkpoint, logfile switch, or shutdown? What if errors occur during validation? Do you think Oracle will commit the data anyways? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mike J Kurth INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Richard Ji INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: querry..
select col1, col2 from table1 where (col1 = 'A' and col2 between 'A' and 'E') or (col1 = 'B' and col2 between 'A' and 'X') Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Saurabh Sharma To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] saurabhs@fcscc: ltd.com Fax to: Sent by: Subject: querry.. root@fatcity. com 05/31/2001 04:50 AM Please respond to ORACLE-L hi list, how can i select two columns from a table based on condition that they are selected in specified combinations. let me.. table 1 has 2 columns col1, col2. both cols have values , say, alphabets. a,b,c,d,e,f,... i'want to select like FOR VALUE OF COL1 IN A, col2 must fetch only between A-E for value of col1 in B, col2 must be between A-X -- and so on.. i want to define this combination, so i should get only these pair of values. any suggestions. thanks. saurabh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Implementing Stored Procedures
There's an excellent tool by the company Kintana that automates the deployment of software (which can include stored packages and procedures, as well as shell scripts, SQL*Loader, whatever). The developer creates a release with the file names and version numbers in PVCS or ClearCase, and the tool extracts and runs the approprate files on other servers as determined by workflows -- so you can use this tool to go from dev to QA to production, etc. Very nice tool, I'd highly recommend it. And no, I am not affiliated, just a happy user. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Bala, Prakash prakash.bala@cinTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] gular.com cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: RE: Implementing Stored Procedures 05/31/2001 02:46 PM Please respond to ORACLE-L Lisa, This is what we did in my last project: 1. Developers had access to the application schema where they created and modified procedures/functions/triggers as needed 2. After their unit testing is done, the QA team would test it using the front-end application. 3. Once QA team approves the new/enhanced functionality, the developers would check in the code using PVCS and notify the DBA vith the PVCS version number and the object name. 4. The DBA tracks the object version numbers along with the application releases and implements the necessary changes in the production box. Developers did not have any rights on the production box. HTH! Prakash -Original Message- Sent: Thursday, May 31, 2001 1:02 PM To: Multiple recipients of list ORACLE-L Hi - I'd like some advice on implementing stored procedures containing application logic (ie. written by developers). We have several applications where the developers use stored procedures for much of their coding. We let the developers create or replace their procedures in a development environment under their own schema (with access to all application tables, etc.) to test the logic, but it currently requires a DBA to implement the proc under the application schema. It has gotten to be a very time-consuming job. We don't want to give out the schema owner password to the developers, nor do we know of a way they could add them as the schema owner without giving them more privileges than we want. I am curious of how others are handling stored procedure additions and modifications.Do you somehow allow developers this access? If so, how do you restrict them from damaging other things? If not, does the DBA do it? Does anyone have an automated way? Also, do you keep track of the original source code for the procedure, or do you extract it out of the database as needed? Thanks so much for your input - Lisa -- 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:
Re: OT: Archiving not possible with SQL Server?
You mean like the programmers of the Oracle Applications? What would we need for that, a missile? (What kills me is that, they don't use declared foreign key constraints in the database. Presumably, since the code base is so old, if they introduced them now everything would break. You'd think they could at least put disabled ones in there...) Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Rachel Carmichael To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] carmichr@hotcc: mail.comFax to: Sent by: Subject: Re: OT: Archiving not possible with SQL Server? root@fatcity. com 05/31/2001 04:23 PM Please respond to ORACLE-L I want to know if it is allowable to kill programmers who say these are the rules for data in this column but then don't enforce those same rules. Rachel (rolling up her sleeves, putting on the rubber gloves to begin data scrubbing really BAD data) From: Jared Still [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Re: OT: Archiving not possible with SQL Server? Date: Thu, 31 May 2001 11:38:08 -0800 I thought everyone would realize this is a joke. It's been appearing with some regularity in a mocking tone of someone that posted it seriously a couple of weeks ago. Sigh... Maybe I am just having a bad day. Jared On Wednesday 30 May 2001 23:50, Jared Still wrote: Are you an idiot? Jared On Wednesday 30 May 2001 14:55, Gary Weber wrote: Guess what happens when a long running transaction marks the log near the end, and not too long afterward the log needs truncated? If memory serves, ( hasn't worked too well lately :) the database will hang. It may just truncate back to the mark, and start from there, but you always have the possibility of another long transaction starting. HELP Gary Weber Senior DBA Charles Jones, LLC 609-530-1144, ext 5529 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Get your FREE download of MSN Explorer at http://explorer.msn.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
OT: Effective Living
Isn't that sort of like Taoism? Accept your lot, there must be good in it somewhere... Also, it seems to be the basis of many major religions to essentially never ask why. So, I guess it's a pretty popular philosophy. (Oh no, I feel a discourse by Eric coming on...) Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Kevin Kostyszyn To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] kevin@dulciacc: n.com Fax to: Sent by: Subject: RE: root@fatcity. com 05/30/2001 11:02 AM Please respond to ORACLE-L This person was actually allowed to teach. Never ask why, yes that's a super philosophy on life. -Original Message- Patrice J Sent: Wednesday, May 30, 2001 8:31 AM To: Multiple recipients of list ORACLE-L That's a laugh, I went to a continuing education seminar once at Carleton University, in Ottawa. The seminar was called Effective Living. The teacher was a social worker, and he emphatically told everyone NEVER to ask why, just adapt to life and keep moving... I had unreconcilable disagreements with him, I guess I don't know how to live. : ) Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- From: Don Granaman [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 29, 2001 4:11 PM To: Multiple recipients of list ORACLE-L Subject: Re: when? But, more important, WHY??? -Don Granaman [certifiable OraSaurus] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, May 29, 2001 1:28 PM And Where. Terry Boivin, Patrice J wrote: When and how Patrice Boivin Systems Analyst (Oracle Certified DBA) -Original Message- From: Kevin Kostyszyn [SMTP:[EMAIL PROTECTED]] Sent: Tuesday, May 29, 2001 1:41 PM To: Multiple recipients of list ORACLE-L Subject:RE: what -Original Message- Andrea Sent: Tuesday, May 29, 2001 11:47 AM To: Multiple recipients of list ORACLE-L who -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Quaglio Andrea INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message
RE: Creating a sorted table
Sure, and thanks. I appreciate the corrections, as one problem with doing this for a while is the aggregation of useless and no longer true facts, which I'm finding I have more and more of. They feel like barnacles. :) I've finally decided to go get my OCP, which I hope will help scrape some of the buggers off. This list always helps, too. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Mohan, Ross MohanR@STARSTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] -SMI.comcc: Sent by: Fax to: root@fatcity.Subject: RE: Creating a sorted table com 05/30/2001 01:01 PM Please respond to ORACLE-L Diana, You might wanna check out www.kx.com Those guys have a lightning rocket of a dbms, and it is based, in part, on storing data in search/sort-dependent order. (There is a bunch of background data on the site itself.) In any case, reading up on other dbms technologies has a place in any DBAs self-education. Else, we're just sitting around quoting anecdotes to one another! Hope this helps, Cheers, Ross || -Original Message- || From: Christopher Spence [mailto:[EMAIL PROTECTED]] || Sent: Wednesday, May 30, 2001 12:10 PM || To: Multiple recipients of list ORACLE-L || Subject: RE: Creating a sorted table || || || This is totally NOT accurate. || || Yes there are performance gains storing index data ordered. || Perhaps great || on range scans. Yes you can reorder tables and indexes. || || Walking on water and developing software from a || specification are easy if || both are frozen. || || Christopher R. Spence || Oracle DBA || Fuelspot || || || || -Original Message- || Sent: Friday, May 25, 2001 7:41 PM || To: Multiple recipients of list ORACLE-L || || || || Whyever would you want data inserted in order? There is no || guarantee that || Oracle will actually store the records in order, there is || no performance || gain, and you can always retrieve the records in order by || using an order by || statement -- if you really need ordered data, you could use a || index-organized table with all of your columns, with the || date as the first || column. But methinks this would be dangerous for a heavy transaction || table. (Gurus, please correct me if I'm wrong here) || || However, if you are still keen, you could do this through a || PL/SQL block, || something like the following: || || declare || cursor get_data is || select col1, col2, col3, ... || from unordered_table || order by whatever; || begin || for dataRec in get_data loop || insert into ordered_table (col1, col2, col3, ...) || values (dataRec.col1, dataRec.col2, dataRec.col3, ...) || end loop; || end; || / || || Cheers! || || Diana || || || || || || Browett, Darren || || [EMAIL PROTECTED]To: || Multiple recipients || of list ORACLE-L [EMAIL PROTECTED] || lam.bc.ca cc: || || Sent by: Fax to: || || [EMAIL PROTECTED] Subject: || Creating a || sorted table || || || || || 05/25/2001 06:45 PM || || Please respond to || ||
Re: Problems with CharArrayType after upgrade from 7.3.4 to 8.1.7.1
Cherie, I have no idea if this could be the case, but I noticed that the type is based on the datatype VARCHAR, not VARCHAR2. According to the docs, this shouldn't make a difference, but they've been warning since the introduction of VARCHAR2 that they may do something different with VARCHAR at some point in the future, so it's best not to use it. From the PL/SQL manual: === VARCHAR2 Subtypes The VARCHAR2 subtypes below have the same range of values as their base type. For example, VARCHAR is just another name for VARCHAR2. STRING VARCHAR You can use these subtypes for compatibility with ANSI/ISO and IBM types. Note: Currently, VARCHAR is synonymous with VARCHAR2. However, in future releases of PL/SQL, to accommodate emerging SQL standards, VARCHAR might become a separate datatype with different comparison semantics. So, it is a good idea to use VARCHAR2 rather than VARCHAR. === Again, I don't see how that could affect the code...just a shot in the dark. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Cherie_Machler @gelco.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Fax to: omSubject: Problems with CharArrayType after upgrade from 7.3.4 to 8.1.7.1 05/30/2001 01:40 PM Please respond to ORACLE-L After upgrading from Enterprise Edition 7.3.4 to 8.1.7.1 on Sun Solaris 2.6, we are having problems executing a procedure with CharArrayType as shown below. We don't get any error message. The package simply does not return any rows when there are rows out there that should be returned. The developer is trying to turn on some debug but I thought I would also ask if anyone sees anything that is a no-no now under 8.1.7.1. Is there something that I need to do in order to migrate this code that wasn't done automatically? CREATE OR REPLACE PACKAGE CCFILECD AS TYPE NumArrayType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; TYPE CharArrayType IS TABLE OF VARCHAR(31) INDEX BY BINARY_INTEGER; PROCEDURE GETFILECODES(file_code OUT CharArrayType, file_prefixOUT CharArrayType, start_pos OUT NumArrayType, length OUT NumArrayType, num_lines_to_skip OUT NumArrayType, record_length OUT NumArrayType, cntry_cd_proc OUT NumArrayType, curr_cd_proc OUT NumArrayType, count OUT NUMBER); END CCFILECD; Thanks for any advice you can give, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists
Re: Is DBMS_OBFUSCATION free in Enterprise Edition?
It's free. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Cherie_Machler @gelco.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Fax to: omSubject: Is DBMS_OBFUSCATION free in Enterprise Edition? 05/30/2001 02:02 PM Please respond to ORACLE-L We have Enterprise Edition 8.1.7.1 on Sun Solaris 2.6. My manager needs a definitive answer. Is DBMS_OBFUSCATION free with this version of Enterprise Edition server or is it part of an extra-cost add-on. All definitive answers welcome. I have a call in to Oracle but they have not yet responded. My manager wants to know today. Thanks, Cherie Machler Oracle DBA Gelco Information Network -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Error
I think the error is in the call to the procedure, not in the procedure itself -- the procedure looks fine at a glance. Could you post the ASP code that makes the call? Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Bartolo, David To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] bartolo@USCOcc: LO.edu Fax to: Sent by: Subject: PL/SQL Error root@fatcity. com 05/30/2001 01:06 PM Please respond to ORACLE-L Hi all Can someone help me out here? I am getting this error when I try to run this PL/SQL code from an ASP page. The error manual just says to check speeling. I DID. Is there anything I am missing? The DB is V7.3 I am trying to return the resultset to the page. PLS-00306: wrong number or types of arguments in call to 'COURSE_SELECT' Here is the PL/SQL code create or replace package course.course_bulletin is TYPE tbl_call_no IS TABLE of cmcmcrst.call_no%type INDEX BY BINARY_INTEGER; TYPE tbl_schl_cd IS TABLE of cmcmcrst.schl_cd%type INDEX BY BINARY_INTEGER; TYPE tbl_dept_cd IS TABLE of cmcmcrst.dept_cd%type INDEX BY BINARY_INTEGER; PROCEDURE course_select(i_ccyy IN NUMBER, o_call_no OUT tbl_call_no, o_schl_cd OUT tbl_schl_cd, o_dept_cd OUT tbl_dept_cd); end; create or replace package body course.course_bulletin as PROCEDURE course_select(i_ccyy IN NUMBER, o_call_no OUT tbl_call_no, o_schl_cd OUT tbl_schl_cd, o_dept_cd OUT tbl_dept_cd) IS CURSOR c1 is SELECT call_no,schl_cd,dept_cd FROM COURSE.cmcmcrst WHERE ccyy = i_ccyy AND term_cd = '2' AND instrl_cd = '0'; crscount NUMBER DEFAULT 1; BEGIN FOR c IN c1 LOOP o_call_no(crscount) := c.call_no; o_schl_cd(crscount) := c.schl_cd; o_dept_cd(crscount) := c.dept_cd; crscount := crscount + 1; END LOOP; END; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bartolo, David INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
Re: SQL*LOADER problem
Satish, You can do a couple of things...in the query you could to a replace(column1, chr(10), '~') (or some other unlikely character or string), then do a replace again in the SQL*Loader script to get the carriage returns back in there. Or you could use the query to put a character in the front of each true record, something like select '#' || col1, col2, col3, and then use the SQL*Loader commands for concatenating records. Can't remember offhand how that one works, though... HTH, Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Satish Iyer [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] tle.wa.us cc: Sent by:Fax to: [EMAIL PROTECTED]Subject: SQL*LOADER problem 05/30/2001 08:05 PM Please respond to ORACLE-L Hi everyone. Having this typical problem with sql*loader. I am extracting data out from a table and this table has a field in which users have put in a new line feed character. Now when I extract the data out using SQL*PLUS it obviously has a problem eg. 1 First line of stt 2333232 Second line 2Full line 2323232 3 Again a partial 2323232 line. Now if such a table was extracted to a txt file, the sqlldr runs into a problem of getting the second half of the line as expected. Any ideas how this could be avoided ? Hope I explained that okay. Satish Iyer DBA CCSS Team 684-3016 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Creating a sorted table
Whyever would you want data inserted in order? There is no guarantee that Oracle will actually store the records in order, there is no performance gain, and you can always retrieve the records in order by using an order by statement -- if you really need ordered data, you could use a index-organized table with all of your columns, with the date as the first column. But methinks this would be dangerous for a heavy transaction table. (Gurus, please correct me if I'm wrong here) However, if you are still keen, you could do this through a PL/SQL block, something like the following: declare cursor get_data is select col1, col2, col3, ... from unordered_table order by whatever; begin for dataRec in get_data loop insert into ordered_table (col1, col2, col3, ...) values (dataRec.col1, dataRec.col2, dataRec.col3, ...) end loop; end; / Cheers! Diana Browett, Darren [EMAIL PROTECTED]To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lam.bc.ca cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: Creating a sorted table 05/25/2001 06:45 PM Please respond to ORACLE-L 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: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: SQLLDR Question
In 8.1.6, you don't have to have the one column in the database. Use
'FILLER' as the data type.
Diana Duncan
TITAN Technology Partners
One Copley Parkway, Ste 540
Morrisville, NC 27560
VM: 919.466.7337 x 316
F: 919.466.7427
E: [EMAIL PROTECTED]
[EMAIL PROTECTED]
ardier.com To: Multiple recipients of
list ORACLE-L [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED] cc:
Fax to:
Subject: Re: SQLLDR
Question
05/24/2001 11:31 AM
Please respond to ORACLE-L
You can combine two data fields into one column with sqlldr, but I think
both
fields have to be in the table.
Example:
SQL desc x
Name Type
-
TRANS_DATEDATE
F1CHAR(8)
LOAD DATA
INFILE *
APPEND
INTO TABLE x
FIELDS TERMINATED BY ','
(f1,
trans_date to_date(:f1||' '||:trans_date,'mm/dd/yy hh24:mi')
)
BEGINDATA
05/01/01,14:21
05/24/01,9:57
(After the load, you can ALTER TABLE table DROP COLUMN column to get rid of
the
f1 column,)
Jared Still [EMAIL PROTECTED] on 05/23/2001 11:16:00 PM
Please respond to [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:(bcc: Chaim Katz/Completions/Bombardier)
Instead of trying to do this in SQL Loader, why not just
try to clean up the data?
This can be done at least 2 methods:
1. Get the sender to send it to you properly
2. clean it up yourself.
The following Perl script will do it
Jared
#!/usr/bin/perl
my $file=data.txt;
open(DATA,$file) || die cannot open $file - $!\n;
while(DATA){
chomp;
my @data = split(/,/);
# append last field to penultimate field, remove last field
$data[ $#data -1] .= - . $data[ $#data ];
undef $data[ $#data ];
#print it all out
my $newData = join(',',@data);
# remove trailing comma
chop $newData;
print $newData\n;
}
On Wednesday 23 May 2001 14:41, Scott Canaan wrote:
I am trying to load a file that has the fields comma-delimited,
variable length. A sample line from the file looks like this:
503,414081660,Success,Auth,AH9042,z,4555,-,-,-,0,05/01/01,14:21
The problem I am having is putting the date and time together. The
control file looks like this:
load data
infile 'cyber_real.dat'
append
into table rit_cyber_hist
when order_id 'Order ID' and order_id ''
fields terminated by ','
(order_id,
trans_nbr,
cyber_status,
trans_type,
auth_code,
avs_code,
trans_amt,
cic_resp,
po_nbr,
ship_to_zip,
tax_amt,
trans_date date(14) mm/dd/yy,hh:mi)
It ignores the time portion of the file, I presume because it has a
comma before it and it assumes that it is a different field. I can't
figure out any way to get this loaded with the trans_date field
containing both the date and time.
This is on Oracle 8.1.6.0 on Sun Solaris.
Any suggestions? Thank you.
--
Scott Canaan ([EMAIL PROTECTED])
(716) 475-7886
Life is like a sewer, what you get out of it depends on what you put
into it - Tom Lehrer
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jared Still
INET: [EMAIL PROTECTED]
Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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
Re: [OT] Jobs in UK
My husband is Scottish, and he heard from a Scottish pal of his that top Oracle consultants can earn up to ?1000 a day in Edinburgh! Made me want to up and go, I'll tell you...then again, having a husband with a British passport means I can work without too many problems. Regards, Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] antonio.belloni@hst ern.com.br To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Fax to: Subject: [OT] Jobs in UK 05/24/2001 02:57 PM Please respond to ORACLE-L Hi fellows, Sorry for the offtopic but I had been in UK last week and my friends from there told me that there is a shortage of IT professionals and , that if you really got the skills , is very easy to migrate and find a job. But none of my friends are IT professionals and I don´t know how much they know from this market. So I wish to know , if possible , from the IT professionals working in UK if it is really easy to find a job and migrate ? What are the governamental restrictions ? How much we can earn in a year of work ? Where I can find more information about immigration to UK ? TIA, Antonio Belloni -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Uwin question ???
You probably don't have . in your path (which is good security). Preface the call to your script with ./ ./test.sh On the other hand, you may not have the magic comment at the top of the script, like this: #!/bin/ksh Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Leslie Lu leslie_y_lu@To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.com cc: Sent by: Fax to: root@fatcity.Subject: Uwin question ??? com 05/24/2001 03:20 PM Please respond to ORACLE-L Hi all, A while ago, I asked about running unix script on NT. I run into a problem in UWin that I got file not found message even if the file is there and is executable. I run a ksh file with and without sh, I kept getting not found. Then I wrote a test1 script, if run without sh, I still got not found, but if run with sh like sh test1, it works. All the scripts work find in Unix. Attached is upgrade_wm.ksh I'm trying to run and following shows the details from UWin: $ ls (ls shows upgrade_wm.ksh is in the current directory) ... upgrade_wfm_RefData.sql upgrade_wfm_RefData_401.ksh upgrade_wm.README upgrade_wm.ksh $ upgrade_wm.ksh (run it in the same directory, got not found message) -ksh: upgrade_wm.ksh: not found [No such file or directory] $ sh upgrade_wm.ksh (the same as above) upgrade_wm.ksh[9]: exec: upgrade_wm.ksh: not found In the same directory, I wrote a tiny script which echos here. That's all it does. Run it after chomd +x. $ test1 (still got cannot found message) -ksh: test1: not found [No such file or directory] $ sh test1 (Runs! but use sh still doesn't work for the upgrade_wm.ksh) here Thank you for any information! Leslie __ Do You Yahoo!? Yahoo! Auctions - buy the things you want at great prices http://auctions.yahoo.com/(See attached file: upgrade_wm.ksh) upgrade_wm.ksh
RE: Multiple schema's or multiple databases
In 8.1.x, as long as your schemas have their own tablespaces, you could to TSPITR (Tablespace Point in Time Recovery). Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] John Lewis jlewis@punchnetTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] works.com cc: Sent by:Fax to: [EMAIL PROTECTED]Subject: RE: Multiple schema's or multiple databases 05/22/2001 07:36 PM Please respond to ORACLE-L Something to ponder. The archive logs are tied to the system. Thus, if you want to recover to a point in time for a multi-schema /one instance system, if one schema gets rolled back - everything gets rolled back. We had this same issue. Perhaps someone knows a way around this. -Original Message- From: Christopher Spence [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 22, 2001 2:26 PM To: Multiple recipients of list ORACLE-L Subject: RE: Multiple schema's or multiple databases 1. Is that performance gain absolutely necessary? What happens if one company goes down and takes down them all. On another note, I tend to agree on lesser instances against more instances. Easier to tune, better perforamance due to what I call instance wastage and much easier to maintain. But if one system changes alot, has significiantly different access methods, or goes up and down more than anyone, I would evaluate seperate instances. -Original Message- From: Sam Roberts [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 22, 2001 1:41 PM To: Multiple recipients of list ORACLE-L Subject: Multiple schema's or multiple databases Oracle 8.1.6 and Solaris I'm going to inherit production databases when I start my new job next week. I gather that the production database consists of 8 schema's (8 companies) that are all in one database. Its an ERP package called Maximo and it interfaces to Financials 11i databases (don't know if this is multiple databases or schema's yet). Apparently there is some data passing between companies and multiple schema's perform better than using database links with multiple databases, and this is the reason for multiple schema's. Does anyone have an opinion on this. If I'd have done it I would have done multiple databases as they are separate companies, but I'm open to comments as not quite got my head round it yet, plus I've been vacationing (partying) for 3 weeks. Thanx Sam -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: 8.1.7. and OAS / iAS versions confusion
Oracle HAD to rename it -- they have consistently changed the name with each release, so they couldn't stop now! A brief history, from what I remember: Oracle Webserver 1.x Oracle Web Application Server 2.x Oracle Web Application Server 3.x? (whoops, I guess they kept it once, anyway I can't really remember what that one was called, it crashed pretty much constantly) Oracle Application Server 4.x WebDB (really built on OAS 4, an addon of sorts) 8i Oracle Application Server (from 4 to 8 :) 9i Application Server Oracle Portal (built on 9iAS, was it also built on 8iOAS?) These are all mostly the same in their foundation technology, and are really dot-releases of the same product. Funny. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Marc Perkowitz mperkowitz@twjconsuTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lting.com cc: Sent by:Fax to: [EMAIL PROTECTED]Subject: Re: 8.1.7. and OAS / iAS versions confusion 05/23/2001 04:30 PM Please respond to ORACLE-L 9i iAS == 8i OAS. Oracle renamed 8i 1.0 to 9i 1.0. Version numbers are consistent. Marc Perkowitz Senior Consultant TWJ Consulting, LLP 847-256-8866 x15 www.twjconsulting.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, May 23, 2001 10:27 AM OK, now I'm puzzled. About 3 weeks ago Oracle mailed me a copy of 8.1.7. at home, along with the 9i iAS. The iAS documentation refers to 8i OAS (version 1.0.1), it says we can migrate from 8i OAS to iAS. Does this mean if I had installed 8i OAS (v. 1.0.1), I could apply a patch to bring it up to v.1.02 (9i iAS?) Is iAS iAS, or is it OAS?? If iAS is not OAS, why do they have the same version numbers? Does iAS go with 8.1.7, or 9i? If 8i OAS goes with 8.1.7, how come Oracle never sent me a copy of 8i OAS? Weren't they excited about that product as well? When we receive the media for 8.1.7 here, will we receive from Oracle 8i OAS to go with it, or 9i iAS? When Oracle 9i is released, will there be another version of iAS to go along with it? I wish the versions matched, but I guess when you're a big company it's hard to release the different packages in synch. Regards, Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin Operations | Admin. et Exploit. des systèmes Technology Services| Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] Ph: (902) 426-4774 -Original Message- Sent: Wednesday, May 23, 2001 5:26 AM To: Multiple recipients of list ORACLE-L I wish that I could be like some of U where your job is to solely tune/backup database etc. Although I am a DBA, I feel that I am a 5% DBA and 95% developer. I've just migrated our in-house customized System to the newly acquired Oracle HR System (oracle workflow). In top of that, I am also maintaining certain in-house Oracle systems. 95% of my time is spent on development. I think that it's great to learn so many tools, Oracle Workflow, Forms, Reports, Graphics etc but I wish that I have more time to tune the database. Besides, cloning database, backup database, applying oracle patches to the HR System, all of my time is spent in development. Regds, New DBA -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 22, 2001 10:06 PM To: Multiple recipients of list ORACLE-L them!) I don't know about this development business. I think about this a
Re: sqlldr to_number
Example of a SQL*Load control file to load a file, skipping the first line, replacing the data in the table, with comma-separated, double-quote enclosed data, using the decode function on one of the columns. OPTIONS (SKIP=1) LOAD DATA INFILE data.dat INTO TABLE mytable REPLACE FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' ( REC_TYPEchar, VCIDchar, SHIP_TO_ID char decode(:SHIP_TO_ID, '?', '0') ) Maybe this is what you're looking for? Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Roland.Skoldb [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: root@fatcity.Fax to: com Subject: sqlldr to_number 05/22/2001 03:00 AM Please respond to ORACLE-L Hallo you DBA's: How can I write a script in ctl-file if I want to import a textfile. I want to use the command decode because I want to replace the character '?' with a 0 (zero), which is a number field inthe database. How can I do this. I want to do it while the sqlldrscript is running. Sincerely Roland Sköldblom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: job offer from SAUDI ARABIA
And sure, you can criticize, but only in America are you in danger of being run down or shot for having a bumper sticker expressing an opinion on politics or abortion or religion or NASCAR...I'm especially nervous now that Eric has told us that all these women are carrying guns now. :) I love this country, but it *is* scary sometimes. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] On Thu, 17 May 2001, Gene Sais wrote: Only in America, can you rant! Try to criticize China while living there. I have a friend there, her mail is read before she gets it. All she can write is general stuff, nothing negative, fearful of her life. The best thing about America is that you can b*tch if you want. I served for this country and love this country. *GO USA* As long as you don't criticize the church of scientology :) http://slashdot.org/article.pl?sid=01/05/17/0238223mode=nested -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL Query to combine multiple rows into 1 ?
The only way I know of to do this is with self-joins, and you have to have a known upper bound of the number of Relations per ID. An example (warning, untested, off the top of my head): select t1.id, t1.relation || ' ' || t2.relation || ' ' || t3.relation from relationtable t1, relationtable t2, relationtable t3 where t1.id = t2.id (+) and t1.id = t3.id (+); I've had problems in the past with multiple outer self-joins, but can't remember offhand how I fixed them...but hopefully this gives you and idea. Come to think of it, are outer joins ANSI SQL? I think so... Wow, I'm prevaricating quite a bit today. Better quit while I'm ahead. :) Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Thompson, Todd tthompso@bcharrTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ispub.com cc: Sent by:Fax to: [EMAIL PROTECTED]Subject: SQL Query to combine multiple rows into 1 ? 05/18/2001 10:56 AM Please respond to ORACLE-L I've got a simple table with 2 columns: ID and Relation E.X. ID Relation --- 123 Sam 123 Bobby 123 Dani 234 Mary 234 Cindy 345 Steve 456 Karen 456 Gary 456 Wayne I'm trying to create a report using only ANSI SQL (no PL*SQL, or SQL*PLUS enhancements) to create the following output: 123 Sam Bobby Dani 234 Mary Cindy 345 Steve 456 Karen Gary Wayne I'm totally stumped- Can anyone help?? Todd Thompson Architecture and Technology Team Harris Publishing 6363 Center Drive, Norfolk VA, 23502 Tel: 757.455.5434 Fax: 757.455.3010 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] (See attached file: winmail.dat)-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thompson, Todd INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). winmail.dat
RE: Check for number only
Ooh, I like this one better! Disregard mine, I hate all the characters you have to put into replace(). Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Jamadagni, Rajendra To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] rajendra.jamadagnicc: @espn.com Fax to: Sent by: Subject: RE: Check for number only [EMAIL PROTECTED] 05/18/2001 01:11 PM Please respond to ORACLE-L Glenn, something like ... where LTRIM (circ_num, '0123456789.') is not null HTH Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *4 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Check for number only
You could always write your own function. Or you could do something using the replace() function, maybe like this (again, untested): select circ_id, circ_num from circuit where replace(circ_num, 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890', 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ') is not null; Depending on your data, you may need to check for punctuation and special characters as well. HTH, Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Glenn Travis c-glenn.travisTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @wcom.com cc: Sent by: Fax to: [EMAIL PROTECTED]Subject: Check for number only m 05/18/2001 12:17 PM Please respond to ORACLE-L I have a table with a column of varchar2(50) datatype. We are building a new table and the values in this column should all be numbers. I need to query the table to find all rows which have a value in this column which is not a valid number. Table: circuit (circ_id (primary_key) number, circ_num varchar2(50)) Example: find all values in circ_num which are not numbers. how do I do something like 'select circ_id, circ_num from circuit where circ_num not valid to_number(circ_num)' Is there a function to check this? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Glenn Travis INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Can I partition my primary key constraint index?
Cherie, Hm, good question. I think I remember (unfortunately, this was at my previous job, so I don't have the scripts) that in order to specify LOCAL, you lost control of the storage parameters -- they automatically default to the same as the table. But it's certainly worth a try. I created separate data and index tablespaces for each partition, which made the dropping of a partition and it's associated space quite simple. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Cherie_Machler @gelco.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Fax to: omSubject: Re: Can I partition my primary key constraint index? 05/14/2001 10:17 AM Please respond to ORACLE-L Diana, Can I build the initial local primary key index using the storage and tablespace clause to place the index in the correct index tablespace to begin with. This index is multiple gigs and my database is tight on space right now. I'm not sure that I have enough room to put the index in the data tablespace and then move it to the appropriate index partition tablespaces? Do you put each of your primary key index partitions in a separate tablespace? The last DBA put all of our indexes into three tablespaces (small, medium, large) which doesn't work very well when you go to get rid of a partition. Thanks, Cherie Diana_Duncan@ttpa rtners.com To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: Re: Can I partition my primary key constraint index? 05/13/01 02:55 PM Please respond to ORACLE-L Yes, Cherie, you can. When creating the primary key constraint, use the USING INDEX clause to specify any storage parameters you want for the automatically created primary key index. I always use the clause, because I am a picky DBA and I want my indices in their own tablespace and with their own storage parameters. Also, if you specify that the index is LOCAL, the partitioning will follow the same algorithm as the table. Unfortunately, if you do LOCAL, the index partitions will automatically be placed in the same tablespace as the table, but you can move the partitions if you wish. HTH, Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Cherie_Machler @gelco.comTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Fax to: omSubject: Can I partition my primary key constraint index? 05/11/2001 04:51 PM Please respond to ORACLE-L Oracle will automatically create an index for your primary key constraint. I have a lot of large partitioned tables in my data warehouse. All of my primary key indexes are non-partitioned. I would like them to be partitioned. When I drop the constraint and re-enable it, can I specify at that time that I want the index to be partitioned? Or do I need to drop my index and rebuild it to be partitioned after the fact? Is there any problem with having
Re: sys.IDL_UB1$
I always wondered what DIANA stood for... :) Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Bill Pribyl bill@datacraTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] ft.com cc: Sent by: Fax to: root@fatcity.Subject: Re: sys.IDL_UB1$ com 05/14/2001 06:10 PM Please respond to ORACLE-L Miller, Jay wrote: Does anyone know what the SYS.IDL_UB1$ table is? It suddenly grew to app. 100Meg in my Development database. Someone probably loaded (or generated) a ton of PL/SQL into the database. Although I've never attempted a truncate on these tables, some Metalink sources claim that doing so might work. See http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FORp_id=58586.996 http://metalink.oracle.com/metalink/plsql/ml2_documents.showNOT?p_id=1021102.6p_showHeader=1p_showHelp=1 IDL_UB1$ is one of four tables that hold compiled PL/SQL code: IDL_UB1$ IDL_CHAR$ IDL_UB2$ IDL_SB4$ As to what is actually inside these tables, here's a related extract from the PL/SQL docs: PL/SQL is based on the programming language Ada. As a result, PL/SQL uses a variant of Descriptive Intermediate Attributed Notation for Ada (DIANA), which is a tree-structured intermediate language. It is defined using a meta-notation called Interface Definition Language (IDL). DIANA provides for communication internal to compilers and other tools. At compile time, PL/SQL source code is translated into machine-readable m-code. Both the DIANA and m-code for a procedure or package are stored in the database. At run time, they are loaded into the shared (memory) pool. The DIANA is used to compile dependent procedures; the m-code is simply executed. These four tables hold the DIANA and the so-code m-code. I think m-code is short for machine-dependent byte code but there is a sizable machine-indenpendent part as well. If you have a look at sql.bsq, you can see that Oracle documents the type column of these tables as follows: part number not null, /* part: 0 = diana, 1 = portable pcode, 2 = machine-dependent pcode */ Good luck Bill _ http://www.datacraft.com/http://plnet.org/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: OT - RE: PostGres
Nope, not yet. It's on my list of things to test and do with the system. Right now, I'm just doing the design and development -- I know, I know, I should probably architect the system better first, but this really is just a family project at the moment. When I do get to testing and scaling and backup and recovery and tuning and sizing and all that grand stuff, I'll make sure to post to the list. Even though we're all Oracle professionals, I know we're also getting tired of the bloat. It's nice to have an alternative sometimes. :) I certainly learned that when I was actually in the position of having the budget and having to pay for the darn stuff. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] [EMAIL PROTECTED] ms.osd.mil To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Fax to: Subject: OT - RE: PostGres 05/11/2001 03:12 PM Please respond to ORACLE-L Diana, Sounds like an excellent alternative to higher end systems (shoots self in oracle-foot). Have you tested how well it scales yet? Scott Shafer San Antonio, TX 210-581-6217 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PostGres WAS: RE: Oracle 9i Articles - self tuning, launch delaye
Yes, it's open source. My personal project uses Apache, PHP and Postgres on Linux servers -- pure open source. Guess how long the servers and the web server and hence the site have been up? Pretty much since we installed. Love it!!! For those of you who don't know, PHP is a server-side templating language based on Perl, and it is dreamy. I'm honestly not sure if your mail is arriving in MIME format -- it just looks like plain text to me. I'm unfortunately on a Lotus Notes email client now, and I haven't figured out how to turn off all the rich text, etc. Anyone else out there know how to do it? I humbly apologize if I'm cluttering up the list, and I'll switch to something else if that will help. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Mohan, Ross MohanR@STARSTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] -SMI.comcc: Sent by: Fax to: root@fatcity.Subject: PostGres WAS: RE: Oracle 9i Articles - self tuning, launch com delaye 05/10/2001 06:50 PM Please respond to ORACLE-L Is it Open Source? also, from your mail, is it true I am sending to you in MIME format? I thought I had turned that off || -Original Message- || From: [EMAIL PROTECTED] || [mailto:[EMAIL PROTECTED]] || Sent: Thursday, May 10, 2001 5:53 PM || To: Multiple recipients of list ORACLE-L || Subject: RE: Oracle 9i Articles - self tuning, launch delayed to || || || || Oooh, I *like* Postgres. I'm having super much fun with it || on a personal || project right now, and I must say they really trump Oracle || on a few things. || Of course, I don't know how it handles large amounts of data || or users or || transactions yet...and I know nothing about the tunability. || But it really || seems to make the easy things easy and the hard things easier which || Oracle certainly doesn't even attempt to do. || || I know, I know, it's an Oracle list, but it's nice to step || out of the box || occasionally. || || Diana Duncan || TITAN Technology Partners || One Copley Parkway, Ste 540 || Morrisville, NC 27560 || VM: 919.466.7337 x 316 || F: 919.466.7427 || E: [EMAIL PROTECTED] || -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Oracle 9i Articles - self tuning, launch delayed to
Oooh, I *like* Postgres. I'm having super much fun with it on a personal project right now, and I must say they really trump Oracle on a few things. Of course, I don't know how it handles large amounts of data or users or transactions yet...and I know nothing about the tunability. But it really seems to make the easy things easy and the hard things easier which Oracle certainly doesn't even attempt to do. I know, I know, it's an Oracle list, but it's nice to step out of the box occasionally. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Mohan, Ross MohanR@STARSTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] -SMI.comcc: Sent by: Fax to: root@fatcity.Subject: RE: Oracle 9i Articles - self tuning, launch delayed to com 05/10/2001 04:53 PM Please respond to ORACLE-L snip Some days, PostGres just *sounds* better. sigh /snip -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/sql loop assistance
Yeah, Stephane, that's actually one of the reasons I suggested not using a commit-within cursor loop thing. The suggestion I made wouldn't do that -- or am I missing something? Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] paquette stephane stephane_paquette@To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.com cc: Sent by: Fax to: [EMAIL PROTECTED] Subject: Re: Pl/sql loop assistance 05/09/2001 10:56 AM Please respond to ORACLE-L Jared, They are many causes for the famous ORA-1555 Snapshot too old, one of them is fetch across commit. It is when you're commiting and fetching the same data. it is not accept in ANSI SQl but it is by Oracle. In numerous place, developpers have complained that I sized the rbs too small because of the ORA-1555. Suddenly my rbs were ok after moving the commit outside the loop . --- Jared Still [EMAIL PROTECTED] a écrit : Stephane, This doesn't look like it will cause ORA-1002, at least I don't see it. What's the relationship between ORA-1002 and ORA-1555? I got up rather early with a headache this morning, so maybe I'm just not thinking clearly yet. :) Jared On Wednesday 09 May 2001 05:40, paquette stephane wrote: My observation is not on the elegancy of the code but why commit at 100 rows ? Are you updating 10 000 000 rows ? I've seen a lot of ORA-1555 because of fetch across commit. --- [EMAIL PROTECTED] a écrit : Linda, Might I suggest avoiding the elegant looping and try some inelegant looping? It should be faster, although I can't make any promises. Warning, untested, and you can probably do better than an in() -- but it should give you the gist... begin loop update reg.docalert_responses@ncp set campaign_response_handled = 1 where campaign_response_handled != 1 and rownum = 100 and docalert_response_id in (select docalert_response_id from docalert_emails_050401@ncc where sent = 1); commit; exit when sql%notfound; end loop; end; / Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Hagedorn, Linda To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] lindah@epocrcc: ates.comFax to: Sent by: Subject: Pl/sql loop assistance root@fatcity. com 05/08/2001 02:47 PM Please respond to ORACLE-L Hello, I'm having difficulty coding this loop and am hoping someone can see how this can be done. I have two tables, one on each instance reg.docalert_responses@ncp and reg.docalert_emails_05040@ncc The requirement is to set ncp.reg.docalert_responses.campaign_response_handled = 1 for all docalert_response_id's that exist in ncc.reg.docalert_emails_050401 where sent=1. Update 100 at a time and commit. The join column, docalert_response_id appears in both
Re: in addition -- ORA-04091: table mutating problem?
What's your version of Oracle? I usually have a stock answer on how to solve this type of problem, but I just tried out your problem on my little Personal Oracle here, and to my INCREDIBLE surprise, the trigger worked and I didn't get an error. Does anyone know if that's just because I'm using Personal Oracle, or did they actually manage to guarantee consistency when the tables are mutating in 8.1.6? This is the trigger text I used: create or replace trigger bir_tableB before insert on tableB for each row declare cursor get_max_date is select max(create_date) from tableB; maxDate date; begin open get_max_date; fetch get_max_date into maxDate; close get_max_date; if (:new.create_date nvl(maxDate, :new.create_date)) then raise_application_error(-20101, 'New date must be greater than any existing record.'); end if; end bir_tableB; Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Leslie Lu leslie_y_lu@To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] yahoo.com cc: Sent by: Fax to: root@fatcity.Subject: in addition -- ORA-04091: table mutating problem? com 05/03/2001 07:11 PM Please respond to ORACLE-L In addition to my previous question: supposed you cannot use Max(date), and MUST use the cursor. (may be my example is not a good one), anyway, I'd like to know how to get around this mutating problem. Thank you! --- Leslie Lu [EMAIL PROTECTED] wrote: Hi all, I got ORA-04091: table mutating problem. Here is the case: select from table A and insert them into table B. A trigger is used to check that new DATE must be later than every DATE in B. So I open an cursor, loop through cursor to compare new DATE value. If I tested using hard coded value, the trigger works; if do a insert ... select, I got ORA-04091. I looked through metalink, didn't see anything that can help. Thank you in advance. Leslie -- 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).
Removing Oracle products from NT
Hey all, I know that this has been addressed on the list before, so if there's an archive yet (I've been away for a bit) just point me to it. I made the terrible, heinous mistake of 1) installing Personal Oracle 8.1.6 on my laptop, which went well, then 2) installing Oracle Developer 6i on the same laptop. I was unaware that Dev does not play with 8.1, as I have been fortunate enough not to deal with bloody GUI's for many years now. Now, when I try to uninstall 6i, the installer crashes. I can't uninstall the DB, because of registry variables that point to the wrong Oracle home, and when I try to manually change them, nothing works at all. Can I just trash the directories, search for all registry variables and remove them? Or do I need to format the disk? :) Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] -- 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).
RESOLVED: Removing Oracle products from NT
Thanks so much to everyone who replied! It seems to be working now. And, I agree completely with the RedHat idea, but this is my consulting laptop, and Windoze is very much the name of the game. Actually, at home I have RedHat, but I don't have Oracle -- it's running Postgres, which I must say I am LOVING!!! I'm working on a cool little site using Postgres and PHP on Apache and Linux. Open Source Rules! Back to expensive Oracle-land... Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 VM: 919.466.7337 x 316 F: 919.466.7427 E: [EMAIL PROTECTED] Rodd Holman rodney.holman@lodTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] genet.comcc: Sent by: Fax to: [EMAIL PROTECTED] Subject: Re: Removing Oracle products from NT 05/03/2001 04:18 PM Please respond to ORACLE-L Diana, SMILE ON Get RedHat 7 CD-ROM Insert into CD-ROM drive Reboot and answer promts SMILE OFF In reality. To gut the machine of all Oracle stuff. 1. Delete all the stuff in the Oracle directories. 2. Remove all the Oracle stuff from the start menu. 3. BACKUP YOUR REGISTRY!! 4. If you skipped step 3 go back and do step 3. 5. In regedit, remove the HKEY_LOCAL_MACHINE/software/ORACLE key. Then start at the top of the registry and search for oracle, oradac, and orant (if that was the tree your Oracle was installed in). Delete the keys you come to that are associated with these things. 6. Reboot your machine. Oracle is gone now. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Constraint dependencies
Lisa, This is unfortunately quite difficult -- and if you have any circular dependencies it's pretty much impossible. Otherwise, what you need to do is follow the dependencies in the dba_constraints view. For the drop order. 1) Drop all of the tables for which their PK constraint is not referenced by any FK constraints select constraint_name from user_constraints c1 where not exists (select constraint_name from user_constraints c2 where c2.r_constraint_name = c1.constraint_name) 2) Now that you've dropped the first group, you can do the same query again. If you are just creating a drop script, you'll need to keep track of the ones you've dropped so you can restrict the query. The creation order is simpler -- you need to create all of the primary key constraints first, then the foreign keys. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 P: 919.466.7337 F: 919.466.7427 E: [EMAIL PROTECTED] Yttri, Lisa lisa.yttri@cTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] nh.com cc: Sent by: Fax to: root@fatcity.Subject: Constraint dependencies com 05/01/2001 05:50 PM Please respond to ORACLE-L Hi - When dropping and/or re-applying constraints for an entire schema, is there a dictionary table that can be queried (or some other way) to determine constraint dependencies - or do you just keep running the script until you do not get those errors? I would like to be able to identify the order that the constraints should be dropped (or added) so that I can write a script to be automated ( for example, to refresh test data from production). If anyone has any good ideas on how to do this, I'd really be interested in hearing from you. Thanks - Lisa -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: OT -- marketing (was Windows vs. UNIX)
Actually, Lee, you should enlighten us. The last time I was in the U.K. for any length of time (1998) the McD's there had far more non-beef and even vegetarian options, which are only now catching on here. So what can we expect in the next few years from the enlightened McD's of Europe? Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 P: 919.466.7337 F: 919.466.7427 E: [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: SQL question
But of course -- just use a negative one as the position to the instr function. From the SQL manual: INSTR searches string for substring. position is an integer indicating the character of string where Oracle begins the search. If position is negative, Oracle counts and searches backward from the end of string. Diana Duncan TITAN Technology Partners One Copley Parkway, Ste 540 Morrisville, NC 27560 P: 919.466.7337 F: 919.466.7427 E: [EMAIL PROTECTED] Glenn Travis c-glenn.travisTo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] @wcom.com cc: Sent by: Fax to: [EMAIL PROTECTED]Subject: SQL question m 05/01/2001 04:36 PM Please respond to ORACLE-L How would you take the same string /dir1/dir2/test/file.out, and return file.out. Assume you do not know where the last '/' is or how many there are. What I need is a reverse instr function. Find the last occurrance of '/', not the first. Can it be done in sqlplus??? -- 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).
