Re: pl/sql open cursor question
Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: Functions: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close c1; return v_dummy; end; / create or replace function b2 return varchar2 is v_dummy varchar2(1) := null; begin select dummy into v_dummy from ctest; return v_dummy; exception when no_data_found then return null; end; / Testrun: set serveroutput on size 2 exec runstats_pkg.rs_start; declare l_loop number := 0; l_dummy varchar2(1); begin for l_loop in 1..1000 loop l_dummy := a; end loop; end; / exec runstats_pkg.rs_middle; declare l_loop number := 0; l_dummy varchar2(1); begin for l_loop in 1..1000 loop l_dummy := b; end loop; end; / exec runstats_pkg.rs_stop(1); For the test of b2 b was simply replaced by b2) The results: a vs b: Run1 ran in 18 hsecs Run2 ran in 18 hsecs run 1 ran in 100% of the time Name Run1 Run2 Diff LATCH.enqueue hash chains 28 26 -2 LATCH.enqueues 28 26 -2 LATCH.library cache 2,067 2,069 2 LATCH.redo allocation 33 31 -2 LATCH.library cache pin 2,046 2,048 2 STAT...enqueue requests 16 14 -2 STAT...enqueue releases 16 14 -2 STAT...calls to get snapshot s 4,011 4,009 -2 STAT...active txn count during 16 8 -8 STAT...consistent gets - exami 16 8 -8 STAT...calls to kcmgcs 16 8 -8 STAT...cleanout - number of kt 16 8 -8 STAT...CPU used by this sessio 33 23 -10 STAT...consistent gets 3,026 3,016 -10 STAT...CPU used when call star 33 23 -10 STAT...redo entries 46 34 -12 LATCH.cache buffers chains 6,226 6,212 -14 STAT...db block changes 63 49 -14 STAT...db block gets 100 68 -32 STAT...session logical reads 3,126 3,084 -42 STAT...redo size 30,224 29,720 -504 STAT...recursive calls 1,001 2,001 1,000 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 11,543 11,525 -18 100.16% a vs b2: Run1 ran in 17 hsecs Run2 ran in 23 hsecs run 1 ran in 73.91% of the time Name Run1 Run2 Diff LATCH.enqueue hash chains 28 26 -2 LATCH.enqueues 28 26 -2 LATCH.library cache 2,067 2,069 2 STAT...bytes received via SQL* 984 986 2 LATCH.library cache pin 2,046 2,048 2 LATCH.redo allocation 34 31 -3 STAT...CPU used by this sessio 32 29 -3 STAT...enqueue releases 17 14 -3 STAT...enqueue requests 17 14 -3 STAT...CPU used when call star 32 29 -3 STAT...calls to get snapshot s 4,013 4,009 -4 STAT...active txn count during 17 8 -9 STAT...cleanout - number of kt 17 8 -9 STAT...calls to kcmgcs 17 8 -9 STAT...consistent gets - exami 17 8 -9 STAT...consistent gets 3,029 3,016 -13 STAT...recursive cpu usage 12 25 13 STAT...redo entries 49 34 -15 STAT...db block changes 69 48 -21 LATCH.cache buffers chains 6,235 6,207 -28 STAT...db block gets 111 66 -45 STAT...session logical reads 3,140 3,082 -58 STAT...redo size 30,648 29,660 -988 STAT...recursive calls 1,001 2,001 1,000 Run1 latches total versus runs -- difference and pct Run1 Run2 Diff Pct 11,557 11,519 -38 100.33% (9.2.0.2/SuSE 8.1) I find it quite strange that results vary from run to run. E.g. in a vs b active txn count was 16 for a, whilst it was 17 for a vs. b2. Why this difference? B variants are consequently cheaper in redo size, session logical reads. Recursive calls is conseqently 1000 higher for b/b2. What counts is runtime, and a and b have no differences, b2, is appr. 25% slower. b/b2 consume slightly less latches, but the diff is 1 %. So, IMHO what remains is the question of taste. I started 22 years ago as software engineer, worked in RD departments (of commercial software firms) and built a 4GL, including a compiler and a universal interpreter for the code produced. Maybe bearing the burdens of my career as a stone-age 'C'-developer influences my 'taste' of beautiness of code (or the lack off it). Unless I've put some flaws in my testruns (which I'm glad to hear of), I rest my case. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 17:44 28-12-03 -0800, you wrote: Carel, It might seem that the loop construct would be more expensive, but it didn't appear that way on my test system. ( 9.2.0.4, RH 8.0 ) function a: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / function b: create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close
Re: pl/sql open cursor question
There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql open cursor question
There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 03:29 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Re: pl/sql open cursor question There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql open cursor question
I wasn't thinking of the boundary conditions, I was thinking of the totally different mechanisms that appear because you are running pl/sql rather than (say) a loop in Pro*C that sends a pure SQL statement 1,000 times to the database. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:49 PM There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql open cursor question
At 09:49 30-12-03 -0800, you wrote: There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. That isn't a factor, as I never use the results from the first run for that very reason. Jared Neither did I. But what wonders me is that even after the firest run (preceded by a flush of the shared_pool) every subsequent run (whithout flush, of course) gave other figures. The ones I included in my message where the most representative figures of over 20 runs. I stopped processes like vmware etc to get the system as stable as possible, but differences stayed pretty significant. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === Jonathan Lewis [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 03:29 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject: Re: pl/sql open cursor question There are times when running a test harness through a single pl/sql is going to give you a spurious result because of extra pinning (of data blocks and library cache material) may confuse the issue. Technically, if the implicit code and the explicit code were written to do exactly the same thing, then the implicit code should be faster because of a couple of under-cover optimisations. (This has been true for several years, I believe). Currently (9.2.0.X-ish) there is a bug that I recently found on metalink which says something about the FETCHes from an implicit cursor using more CPU than the FETCHes from an explicit cursor. Bottom line - test it in the environment where you are using it, and on the version you are running in production. In almost all cases, the difference will probably be imperceptible, anyway. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:49 AM Jared, Point taken. I should do some testing instead of publish an opinion. I still do not like the constraction, but that's a matter of taste. I have done some testing as well, because I think you were somehow comparing apples and oranges: function a uses an implicit cursor, whereas function b has an explicit cursor. So I ran a, b and b2 through Tom Kyte's runstats harness, but found no significant differences: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql open cursor question
cursor for loops automatically close cursors. dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Sunday, December 28, 2003 10:54 AM I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ryan INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql open cursor question
Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram MiracleA/S Kratvej 2 DK - 2760 Mlv Cell:(+45) 2527 7107 Phone:(+45) 4466 8855 Fax:(+45) 4466 8856 Home:(+45) 3874 5696 Email:[EMAIL PROTECTED]
Re: pl/sql open cursor question
I thought just the execution plan was shared? I thought the definition of a cursor, was the memory area used to store the data. That data does not stay persistent in memory with a cursor for loop it closes. correct me if Im wrong? - Original Message - From: Peter Gram To: Multiple recipients of list ORACLE-L Sent: Sunday, December 28, 2003 12:39 PM Subject: Re: pl/sql open cursor question Hi if we assume it is implements this way (see below) there will only be one cursor since c_gidis a bind variable and there for the cursor will be sharded from call to call of the function.create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid;begin for x in cur1(gid) loop return x.c1; end loop; return null;exception when others then return null;end;It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram MiracleA/SKratvej 2DK - 2760 Måløv Cell:(+45) 2527 7107Phone:(+45) 4466 8855Fax:(+45) 4466 8856Home:(+45) 3874 5696Email:[EMAIL PROTECTED]
Re: pl/sql open cursor question
Hi: I thought in the orginal code (cursor cur1 is select C1 from tab1 where ID = gid;), gid is a parameter passed in so it is already a bind variable. I don't see any difference to what you proposed. Your method is just make cur1 take a paramter? Am I wrong here? Also what happens when your function is called from different sessions? Is cursor_shared = force need to be set in init.ora? Guang On Sun, 28 Dec 2003, Peter Gram wrote: Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen /*Peter Gram*/ mailto:[EMAIL PROTECTED] Miracle A/S http://www.miracleas.dk/ Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Guang Mei INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql open cursor question
What I don't understand is the loop construction: Actually only one (row) is read form the cursor, and then the function is left with a return. Because it's an unconditional return, the code within the loop will either execute once, or never. When no data is found NULL is returned. When an error occurs NULL is returned as well. So, why a loop? Wouldn't it be better to have something like: create or replace function XYZ(gid in number) return varchar2 is l_c1 tab1.C1%TYPE; /* local variable to store C1 */ begin select c1 into l_c1 from tab1 where id = gid; return l_c1; exception when no_data_found then return some_error_code; /* let the caller know that no data is found */ when others then return 'ERROR: '||TO_CHAR(SQLCODE); /* return the error-code, preceded by the text ERROR for identification */ end; Sure, a loop prevents an ORA-1422, but I don't think a loop construction should be abused for this. Just think about all loop controlling code that needs to be set up by the interpreter. tab1.ID should be unique, so a 1422 normally cannot occur. Robust programming however asks us to prevent any error. I would prefer to think about how a 1422 should be handled, and write some code accordingly. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 09:39 28-12-03 -0800, you wrote: Hi if we assume it is implements this way (see below) there will only be one cursor since c_gid is a bind variable and there for the cursor will be sharded from call to call of the function. create or replace function XYZ (gid in number) return varchar2 is cursor cur1(c_gid number) is select C1 from tab1 where id = c_gid; begin for x in cur1(gid) loop return x.c1; end loop; return null; exception when others then return null; end; It will only be one coursor Guang Mei wrote: I have a function like below (psudo code). If cursor cur1 have multiple rows, would the code leave the cursor open when this function is called? So if this function is called 1000 times, I would have 1000 open cursors? function XYZ(gid in number) return varchar2 is cursor cur1 is select C1 from tab1 where ID = gid; begin for x in cur1 loop return x.c1; end loop; return null; exception when others then return null; end; -- Best regards/Venlig hilsen Peter Gram Miracle A/S Kratvej 2 DK - 2760 Måløv Cell: (+45) 2527 7107 Phone: (+45) 4466 8855 Fax: (+45) 4466 8856 Home: (+45) 3874 5696 Email: [EMAIL PROTECTED]
Re: pl/sql open cursor question
dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. One place where I have found it justified, is in logon trigger where users must be able to log on, despite any errors which occur in a logon trigger... (continued) ..of course with some kind of error logging mechanism implemented. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql open cursor question
dont use when others then null on code you are putting in an application. if you have a bug you will have a hard time finding it. Its a fundamental flaw. One place where I have found it justified, is in logon trigger where users must be able to log on, despite any errors which occur in a logon trigger... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql open cursor question
Carel, It might seem that the loop construct would be more expensive, but it didn't appear that way on my test system. ( 9.2.0.4, RH 8.0 ) function a: create or replace function a return varchar2 is begin for srec in (select dummy from ctest) loop return srec.dummy; end loop; return null; end; / function b: create or replace function b return varchar2 is cursor c1 is select dummy from ctest; v_dummy varchar2(1) := null; begin open c1; fetch c1 into v_dummy; close c1; return v_dummy; end; / The resource consumption for a 1000 iterations of each: ( a is the first column ) 17:38:42 poirot.jks.com - [EMAIL PROTECTED] SQL @run_stats NAME RUN1 RUN2 DIFF -- -- -- LATCH.Consistent RBA 0 1 1 LATCH.cache buffers lru chain 1 0 -1 LATCH.lgwr LWN SCN0 1 1 LATCH.mostly latch-free SCN 0 1 1 LATCH.session idle bit0 1 1 STAT...calls to get snapshot scn: kcmgss 3012 3013 1 STAT...calls to kcmgcs7 6 -1 STAT...cleanout - number of ktugct calls 0 1 1 STAT...consistent gets - examination 0 1 1 STAT...session cursor cache hits 1 2 1 STAT...parse count (total)1 2 1 STAT...opened cursors current 1 2 1 STAT...opened cursors cumulative 1 2 1 STAT...messages sent 0 1 1 STAT...free buffer requested 1 0 -1 STAT...execute count 1003 1004 1 STAT...deferred (CURRENT) block cleanout 4 3 -1 applications STAT...calls to kcmgas0 1 1 STAT...user commits 0 1 1 STAT...active txn count during cleanout 0 1 1 LATCH.enqueues0 1 1 LATCH.dml lock allocation 0 2 2 LATCH.session allocation 0 2 2 STAT...db block changes 25 27 2 STAT...enqueue releases 0 2 2 STAT...consistent gets 3010 3012 2 LATCH.cache buffers chains 6130 6133 3 STAT...redo entries 17 20 3 STAT...recursive cpu usage4 7 3 STAT...db block gets 30 33 3 LATCH.redo writing0 3 3 LATCH.undo global data1 4 3 LATCH.library cache 7 4 -3 LATCH.enqueue hash chains 0 4 4 LATCH.redo allocation18 22 4 LATCH.library cache pin 7 3 -4 LATCH.messages0 5 5 STAT...session logical reads 3040 3045 5 STAT...commit cleanouts 0 7 7 STAT...commit cleanouts successfully com 0 7 7 pleted STAT...redo size 27184 27820636 STAT...recursive calls 2004 3007 1003 42 rows selected. The for loop actually appears to be somewhat less expensive in terms of database resources. Jared On Sun, 2003-12-28 at 11:39, Carel-Jan Engel wrote: What I don't understand is the loop construction: Actually only one (row) is read form the cursor, and then the function is left with a return. Because it's an unconditional return, the code within the loop will either execute once, or never. When no data is found NULL is returned. When an error occurs NULL is returned as well. So, why a loop? Wouldn't it be better to have something like: create or replace function XYZ(gid in number) return varchar2 is l_c1 tab1.C1%TYPE;/* local variable to store C1 */ begin select c1 into l_c1 fromtab1 where id = gid; return l_c1; exception when no_data_found then return some_error_code; /* let the caller know that no data is found */ when others then return 'ERROR: '||TO_CHAR(SQLCODE); /* return the error-code, preceded by
RE: pl/sql question and owa_pattern question
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: pl/sql question and owa_pattern question
Hi Stephane:
Thanks for your good suggestion. I compared the method you suggested and the orginal
one and it indeed boosted the performance (in my simple test). However the ONLY
problem I am having is that by doing TRANSLATE, I lost the original delimits. The new
method (you suggested) correctly extract the words (and sent for processing), But
after processing I need to put processed-words back to the orginal string with orginal
demilters un-changed. I tried to track to position of delimit from the orginal string
by doing
global_pos := global_pos + pos ;
in my while loop, but ltrim(substr(string, pos + 1), '#') will make global_pos
wrong when ltrim trims '#'. Any work-around?
TIA.
Guang
-Original Message-
Stephane Faroult
Sent: Friday, November 21, 2003 4:19 AM
To: Multiple recipients of list ORACLE-L
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is
much slower than direct
string manupilation. But I would like to try it in
this case if I could
easily get the wrods from the string. Any
suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stephane Faroult
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
RE: pl/sql question and owa_pattern question
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an
RE: pl/sql question and owa_pattern question
Guang,
Well you are almost there ... you need fifo structure namely a pl/sql array
1. create a local pl/sql array to store the delimiter (store the ascii value of the
delimiter to be safe) my_array (varchar2(5))
2. as you find a delimiter insert into the first position in the array and replace the
delimiting character with #
3. lather.rinse.repeat.
when it is time to put it back
use a loop
nIndex := 0;
nPos := 0;
loop
npos := instr(my_str,'#',1);
exit when npos := 0;
nIndex := nindex + 1;
my_str := substr(my_str,1,nPos-1) || chr(my_array(nIndex)) || sybstr(my_str, nPos+1);
end loop;
something like this should help, proof-read though ...
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly personal.
QOTD: Any clod can have facts, having an opinion is an art !
-Original Message-
Sent: Friday, November 21, 2003 11:44 AM
To: Multiple recipients of list ORACLE-L
Hi Stephane:
Thanks for your good suggestion. I compared the method you suggested and the orginal
one and it indeed boosted the performance (in my simple test). However the ONLY
problem I am having is that by doing TRANSLATE, I lost the original delimits. The new
method (you suggested) correctly extract the words (and sent for processing), But
after processing I need to put processed-words back to the orginal string with orginal
demilters un-changed. I tried to track to position of delimit from the orginal string
by doing
global_pos := global_pos + pos ;
in my while loop, but ltrim(substr(string, pos + 1), '#') will make global_pos
wrong when ltrim trims '#'. Any work-around?
TIA.
Guang
-Original Message-
Stephane Faroult
Sent: Friday, November 21, 2003 4:19 AM
To: Multiple recipients of list ORACLE-L
Guang,
I agree with your analysis, looping on characters is not the faster you can do,
simply because there is a significant overhead (compared to C code for instance) in a
language such as PL/SQL - which might be perfectly acceptable in some circumstances,
much less so in very repetitive tasks. 'Native compiling', ie turning PL/SQL in C,
might improve performance. However, in my view the best performance gains you may get
is by, so to speak, pushing the bulk of the processing deeper into the kernel (which
isn't by the way exclusive of native compiling). Using a function such as INSTR() will
be much more efficient than looping on characters.
I would suggest something such as :
- First use TRANSLATE() to replace all the characters you want to get rid of by a
single, well identified character, say # (use CHR() || ... for non printable
characters - you can build up the string of characters to translate in the
initialisation section of a package rather than typing it).
- Start with initializing your string to LTRIM(string, '#')
- Then as long as pos := INSTR(string, '#') isn't 0,
get your token as substr(string, 1, pos - 1) then assign ltrim(substr(string, pos
+ 1), '#') to string (very similar to what you were planning to do with owa).
This will be probably much faster than a character-by-character loop and calls to an
owa package.
HTH,
Stephane Faroult
- --- Original Message --- -
From: Guang Mei [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
Sent: Thu, 20 Nov 2003 19:39:55
Hi:
In my pl/sql program, I want to process each word
in a string. The
string is selected from a varchar2(300) column. The
delimit that separates
the words is not necessary space character. The
definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9,
A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each
character, find the delimit, and
rebuild each word. After that I process each
word. The code looks like
this:
---
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and
instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops
through each characters. I
hope I could find a way to speed it up. I don't
have experiience in
owa_pattern, but I thought there might be a way to
do it here:
str := This will be a long string with length
upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');
-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0
Re: pl/sql question and owa_pattern question
PL/SQL is the fastest thing of them all when it comes to executing
SQL commands, but there are things which simply aren't practical
in 9.2 PL/SQL. Regular expression processing is one of those things.
Fortunately, you can mix the two. Without DBI, perl scripts simply
woudn't be very useful. Of course, there are things that are faster
then even the fastest perl script. Lexer written in C is one of them
and you don't need much work to write one, either, but using OCI is
not easy. OCI is a library written to confuse the enemy, not to help
developer. Using plain and simple regex or PCRE within a C program
is the same thing as above, but slightly more complicated then a lexer.
For the specific task of manipulating patterns and resolving regular
expressions, I use perl almost exclusively because I find it an optimal
tradeoff between ease of use and performance. If performance is a
paramount, as in real time application processing, then you'll have to
resort to C and, possibly, write an external procedure and, thus,
enabling oracle to use C regex calls or even pcre. I was toying with the
idea of enabling oracle to use PCRE but I gave up when I read that 10g
will have that included.
On 11/21/2003 11:59:31 AM, Guang Mei wrote:
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this
Re: pl/sql question and owa_pattern question
Would extproc_perl fit well enough, though, until 10g is here?
On Fri, 21 Nov 2003, Mladen Gogala wrote:
PL/SQL is the fastest thing of them all when it comes to executing
SQL commands, but there are things which simply aren't practical
in 9.2 PL/SQL. Regular expression processing is one of those things.
Fortunately, you can mix the two. Without DBI, perl scripts simply
woudn't be very useful. Of course, there are things that are faster
then even the fastest perl script. Lexer written in C is one of them
and you don't need much work to write one, either, but using OCI is
not easy. OCI is a library written to confuse the enemy, not to help
developer. Using plain and simple regex or PCRE within a C program
is the same thing as above, but slightly more complicated then a lexer.
For the specific task of manipulating patterns and resolving regular
expressions, I use perl almost exclusively because I find it an optimal
tradeoff between ease of use and performance. If performance is a
paramount, as in real time application processing, then you'll have to
resort to C and, possibly, write an external procedure and, thus,
enabling oracle to use C regex calls or even pcre. I was toying with the
idea of enabling oracle to use PCRE but I gave up when I read that 10g
will have that included.
On 11/21/2003 11:59:31 AM, Guang Mei wrote:
Perl is a good tool for text processing. But our program is already written
in pl/sql long time ago and there are intensive db calls in this pl/sql
program. (text processing is only part of it). So I can not change that.
BTW I did a comparison study a while ago for some of our pl/sql packages
(specifically for our application). When there are lots of db calls (select,
insert, update and delete), pl/sql package is faster than correponding perl
program (I made sure sqls are prepared once and used bind variables in perl.
All code were executed on the unix server, no other programs were running,
etc). That's why we stick to pl/sql because our app need the performance.
Others may have different results, it all depends on what the code does.
Guang
-Original Message-
Mladen Gogala
Sent: Thursday, November 20, 2003 11:14 PM
To: Multiple recipients of list ORACLE-L
I don't know about PL/SQL but here is how I would get separate words from a
big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words
into the database, instead
of printing them. The bottom line is that perl is an excellent tool for
parsing strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City
Re: pl/sql question and owa_pattern question
I don't know about PL/SQL but here is how I would get separate words from a big string:
#!/usr/bin/perl -w
use strict;
my (@ARR);
while () {
chomp;
@ARR = split(/[^0-9a-zA-Z_\.,]/);
foreach (@ARR) {
print $_\n;
}
}
There is something called DBI and it can be used to insert separated words into the
database, instead
of printing them. The bottom line is that perl is an excellent tool for parsing
strings and all sorts of string
manipulation.
On 2003.11.20 22:39, Guang Mei wrote:
Hi:
In my pl/sql program, I want to process each word in a string. The
string is selected from a varchar2(300) column. The delimit that separates
the words is not necessary space character. The definition of the delimit
in this program is set as
1. Any character that is NOT AlphaNumerical (0-9, A-Z,a-z)
and
2. the character is not one of these: '-.,/*_'
Now my program is basically checking each character, find the delimit, and
rebuild each word. After that I process each word. The code looks like
this:
---
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
len := length(str)+1;
for i in 1..len loop
ch := substr(str,i,1);
if (not strings.isAlnum(ch) and instr('-.,/*_', ch)1) then
if word is not null then
-- do some processing to variable word !
word := null;-- reset it
end if;
else
word := word || ch; -- concat ch to word
end if;
end loop;
---
I think It's taking too long because it loops through each characters. I
hope I could find a way to speed it up. I don't have experiience in
owa_pattern, but I thought there might be a way to do it here:
str := This will be a long string with length upto 300 characters, it
may contain some invisible characters';
newstr := str;
pos := 1;
while pos != 0 loop
pos := owa_pattern.amatch(newstr, 1, '\W');-- how can I mask out
these '-.,/*_' ???
word := substr(newstr, 1, pos-1);
-- do some processing to variable word !
if pos != 0 then
newstr := substr(newstr, pos+1);
end if;
end loop;
--
My simple tests showed that owa_pattern call is much slower than direct
string manupilation. But I would like to try it in this case if I could
easily get the wrods from the string. Any suggestions?
TIA.
Guang
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Guang Mei
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Mladen Gogala
Oracle DBA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Mladen Gogala
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: Pl/SQL-statement
RTFM RTFM RTFM RTFM Do your homework! Read the oracle documents, buy a book from Amazon/Bookpool/Softpro and put in a little effort. If has_done_homework = 'Y' then list_answer = 'Y'; else list_answer = 'NO!'; end if; [EMAIL PROTECTED] wrote: Hallo, I would like to do the following with an sql( pl/sql) statement. I have table1 and table2 andtable3. I want to check whether field1 in table1 exists in table3. If so then I want an insert statement to be run...insert into table3. If it doesnt find that value then th escript will go to table2 and check if the vaules exists in that table, if it finds it then I want another insert statement to be run. Please help me with an easy example, i dont know if this is so simply but I cantget it right though. Thanks Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Daniel Fink INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Pl/SQL-statement
[EMAIL PROTECTED] scribbled on the wall in glitter crayon: Hallo, I would like to do the following with an sql( pl/sql) statement. the answer to your question can be found by reading either the manuals or any pl/sql book, with examples. this is the same thing as all the other questions you've asked for over a year on this list. you will not make the effort to learn, we will not make the effort to help. -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] In the beginning I was made. I didn't ask to be made. No one consulted me or considered my feelings in this matter. But if it brought some passing fancy to some lowly humans as they haphazardly pranced their way through life's mournful jungle then so be it.- Marvin the Paranoid Android -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/SQL-statement
begin select 'y' into dummy where table3.column = table1.column; insert into table3 blah blah; exception when no_data_found then begin select 'y' into dummy where table2.column = table1.column; insert into table2 blah blah; exception when no_data_found then do reqd tasks; when others then blah blah; end ; when others then do reqd tasks; end; HTH GovindanK On Mon, 10 Nov 2003 06:54:25 -0800, [EMAIL PROTECTED] said: Hallo, I would like to do the following with an sql( pl/sql) statement. I have table1 and table2 andtable3. I want to check whether field1 in table1 exists in table3. If so then I want an insert statement to be run...insert into table3. If it doesnt find that value then th escript will go to table2 and check if the vaules exists in that table, if it finds it then I want another insert statement to be run. Please help me with an easy example, i dont know if this is so simply but I cantget it right though. Thanks Roland -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- http://www.fastmail.fm - Email service worth paying for. Try it for free -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: GKatteri INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Pl/SQL-statement
Hello Roland, Do you even need PL/SQL for what you want to do? rsis I want to check whether field1 in table1 exists in rsis table3. If so then I want an insert statement to be rsis run...insert into table3 If the field *is* in table 3, you want to insert it into table 3 again? I find that an odd requirement. However, you might try something like: INSERT INTO table3 SELECT field1 FROM table1 WHERE field1 IN (SELECT DISTINCT field1 FROM table3); EXISTS might work better than IN. You might need to adjust your SELECT column list to match your target table. I don't know what other columns are in table3. If table3 is really large, I'd consider using EXISTS and getting rid of DISTINCT. Well, you might need to try a few variations to figure out which performs the best. Best regards, Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:[EMAIL PROTECTED] Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to [EMAIL PROTECTED] and include the word subscribe in either the subject or body. Monday, November 10, 2003, 9:54:25 AM, [EMAIL PROTECTED] ([EMAIL PROTECTED]) wrote: rsis Hallo, rsis I would like to do the following with an sql( pl/sql) statement. rsis I have table1 and table2 andtable3. rsis I want to check whether field1 in table1 exists in table3. If so then I want an insert statement to be run...insert into table3. rsis If it doesnt find that value then th escript will go to table2 and check if the vaules exists in that table, if it finds it then I want another insert statement to be run. rsis Please help me with an easy example, i dont know if this is so simply but I cantget it right though. rsis Thanks rsis Roland rsis -- rsis Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Gennick INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL - can't accept user input - then how?
If you already have shell script front end, would it be acceptable to prompt
for the input in the shell script rather than in the procedure?
#!/bin/ksh
echo ENTER LOGIN
read USER
echo ENTER PASSWORD
stty -echo
read PASS
stty echo
echo ENTER WHAT IT IS
read INPUT
sqlplus -s -XXX
${USER}/${PASS}
exec THE_PROCEDURE('$INPUT')
XXX
-Original Message-
List,
Please forgive the repetitious nature of this query, but I haven't yet
found an answer that satisfied me.
Environment: AIX 5.1 Oracle 8.1.7
Trying to create an SQL script which calls a procedure to update a
record based on information provided by the user via a screen
prompt. I
know PL/SQL is not interactive by nature.
I have tried the ACCEPT command in the .sql script before the
procedure
call, which is wrapped in a shell script but it doesn't wait for my
input, just carries on executing the rest of the .sql script.
I am now thoroughly confused about how to do this. And I
doubt I am the
only one. I do need the user to provide me with a parameter so I can
locate the record for update.
Don't hesitate to tell me to RTFM or book or website, just
tell me WHICH
ONE(S) to read :)
Thanks much,
Saira
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: PL/SQL - can't accept user input - then how?
This should get you started
HTH
Jared
SQLPATH=''
USER_INPUT=''
while [ -z $USER_INPUT ]
do
echo Please enter a table owner:
read USER_INPUT
done
echo $USER_INPUT
sqlplus /nolog EOF
set echo on
connect scott/tiger
select table_name
from all_tables
where owner = upper('$USER_INPUT');
EOF
Saira Somani-Mendelin [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
11/04/2003 01:49 PM
Please respond to ORACLE-L
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:PL/SQL - can't accept user input - then how?
List,
Please forgive the repetitious nature of this query, but I haven't yet
found an answer that satisfied me.
Environment: AIX 5.1 Oracle 8.1.7
Trying to create an SQL script which calls a procedure to update a
record based on information provided by the user via a screen prompt. I
know PL/SQL is not interactive by nature.
I have tried the ACCEPT command in the .sql script before the procedure
call, which is wrapped in a shell script but it doesn't wait for my
input, just carries on executing the rest of the .sql script.
I am now thoroughly confused about how to do this. And I doubt I am the
only one. I do need the user to provide me with a parameter so I can
locate the record for update.
Don't hesitate to tell me to RTFM or book or website, just tell me WHICH
ONE(S) to read :)
Thanks much,
Saira
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Saira Somani-Mendelin
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: PL/SQL - can't accept user input - then how?
Thank you all. Your suggestions have clarified A LOT of grey areas for me. I'm not an expert shell programmer but I can certainly get by on these suggestions! Thanks again. Saira -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Saira Somani INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question:Eliminate duplicate rows
Ron, Doesn't matter. On the way to your car, or the gym, or to watch your kids play ball - my Uncle Guido can find you anywhere you go!! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 5:20 PM To: Multiple recipients of list ORACLE-L Hi Tom- Submitting to a listserve is like living in a small town. Make 1 little oops and everybody knows about it. Do I need to look over my shoulder on the way to my car tonight??? ;) Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 02:29 PM Please respond to ORACLE-L You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information
RE: PL/SQL Question:Eliminate duplicate rows
Job well done, Tom, the embarrasment is of no consequence. The routine was modified to include Ron's pointer. Stats for Tom's first routine below: load table 1.3 million rows, results table (deduped rows) 48,307. Completion time (via sqlplus over network) 63 seconds. Second routine (same tables) : 21 seconds! (Oracle 8.1.7 on AIX 4.3 IBM M80 (6 way Risc 6000 4 Gig Ram 1.2 TB IBM Shark attached array) --- Mercadante, Thomas F [EMAIL PROTECTED] wrote: You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL
RE: PL/SQL Question:Eliminate duplicate rows
Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question:Eliminate duplicate rows
Easy way: delete from table where rowid not in (select max(rowid) from table group by PK); Complicated way: Alter table mytab enable constraint PK exceptions into exceptions; Then, you should see how many rows are duplicated and use the method 1 on that set of rowids. If the table in question is a multi-gigabyte table and the number of rows is relatively small, then the second method is much, much faster. -- Mladen Gogala Oracle DBA -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Johan Muller Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Subject: PL/SQL Question:Eliminate duplicate rows Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Note: This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question:Eliminate duplicate rows
except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: PL/SQL Question:Eliminate duplicate rows
You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E
RE: PL/SQL Question:Eliminate duplicate rows
Check SQL Reference for exception_clause when creating Primary Key. Could help to do what you need just using SQL (no PL/SQL). Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Mercadante, Thomas F Sent: Friday, September 19, 2003 2:55 PM To: Multiple recipients of list ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Johan Muller INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Question:Eliminate duplicate rows
Hi Tom- Submitting to a listserve is like living in a small town. Make 1 little oops and everybody knows about it. Do I need to look over my shoulder on the way to my car tonight??? ;) Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 02:29 PM Please respond to ORACLE-L You know, I never use that exception, so I can't remember it correctly. You are correct, of course - thanks for embarrasing me in front of thousands and thousands and thousands (how many Jared??) of people! :) Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 4:10 PM To: Multiple recipients of list ORACLE-L except your too_many_rows exception should be dup_val_on_index... Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] e.ny.us To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED]Subject: RE: PL/SQL Question:Eliminate duplicate rows .com 09/19/2003 01:54 PM Please respond to ORACLE-L Johann, how about the following. what this does is, using the inner begin/end block, catches when an insert would fail because of the PK failure and ignores the error. This is very quick and dirty - it will work fine if you are not working with a huge amount of data. declare cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); exception when too_many_rows then null; end; end loop; end; / If you are talking about lots and lots of data, you could easily query the table you are inserting into, testing for the existence of the value you are attempting to insert. If you find it, skip the insert. Like this: declare rec_count number; cursor c1 is select col1, col2 from some_table; begin for c1_rec in c1 loop begin select count(*) into rec_count from new_table where col1 = c1_rec.col1; -- this assumes that col1 is the pk! if rec_count = 0 then insert into new_table(col1, col2) values (c1_rec.col1, c1_rec.col2); end if; exception when too_many_rows then null; end; end loop; end; / Good Luck! Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, September 19, 2003 3:05 PM To: Multiple recipients of list ORACLE-L Looking for an a sample cursor routine to load a PK enabled table to eliminate any dupes from the load table. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http
RE: PL/SQL statement help
Title: Message quantity is neither a column name or a valid column alias. That's what your error is telling you. Allan -Original Message-From: Milton C. Craighead, Jr. [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: PL/SQL statement help I'm having no luck resolving the following PL/SQL errorsfor afinal examclass project.We do not have access to metalinknor do we have access to Oracle PL/SQL documentation. We are being taught from a PL/SQL text book that does not provide ora error message information or where to findthem...Any and all assistance will be greatly appreciated. 1) where am I going wrong SQL SELECT order#, customer#, address, city, state, zip, 2 orderdate, shipdate, shipstreet, shipcity, shipstate, 3 shipzip, item#, isbn, quantity 4 FROM customers NATURAL JOIN orders;shipzip, item#, isbn, quantity *ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier 2) I'm trying to format a report where the output should look like the following: I'm able to format the item#, isbn, quanty, retail, and total line with out error but Im getting an error message in my veiw... Order# Customer# Customer Address Customer City, State, Zip Order Date Ship Date Ship Street Ship City, Ship State, Ship Zip ITEM# ISBN QUANTY RETAIL TOTAL LINE ITEM#ISBN QUANTY RETAIL TOTAL LINE SET SERVEROUTPUT ONSQL set linesize 300SQL set pagesize 20SQL ttitle CENTER 'The Law Report' SKIP 2SQL btitle '(Confidential)' SKIP 2SQL SQL column item# heading 'ITEM#' format a10 truncateSQL column isbn heading 'ISBN' format a15 truncateSQL column quantity heading 'QUANTITY' format a6 truncateSQL column retail heading 'RETAIL' format 990.00SQL column total line heading 'TOTAL|LINE' format 990.00SP2-0158: unknown COLUMN option "line"SQL SQL Drop view bookPub; View dropped. SQL Create view bookPub 2 AS select count(b.title) as bookNum, b.cost, b.pubid 3 from books b natural join orderitems o 4 group by b.pubid, b.cost; View created. SQL SQL SQL SQL SQL Drop view bookRev; View dropped. SQL Create view bookRev 2 As select count(b.title) as bkNum, sum(b.retail - b.cost) as rev, o.customer# 3 from orderitems i natural join books b natural join orders o 4 group by o.customer#; View created. SQL SQL SQL SQL Drop view orderInfo; View dropped. SQL Create view orderInfo 2 As 3 Select c.customer#, o.order#, c.lastname, c.firstname, c.address, 4 o.shipdate, o.shipstreet, o.shipcity, o.shipstate, 5 o.shipzip, i.item#, i.isbn, i.quantity 6 from customers c, orders o, orderitems i Where c.customer# = o.customer#(+) 7 and o.order# = i.order#; View created. SQL SQL CLEAR BREAKbreaks clearedSQL CLEAR COLUMNcolumns clearedSQL SQL SELECT order#, customer#, address, city, state, zip, 2 orderdate, shipdate, shipstreet, shipcity, shipstate, 3 shipzip, item#, isbn, quantity 4 FROM customers NATURAL JOIN orders;shipzip, item#, isbn, quantity *ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier SQL SQL SELECT item#, isbn, quantity, retail, sum(retail*quantity) "Total Line" 2 FROM customers NATURAL JOIN orders NATURAL JOIN orderitems 3 NATURAL JOIN books 4 GROUP BY item#, isbn, quantity, retail; 3) last but not least I can not figure out how to create this statement at all: Deletes are not allowed, but you will be allowed to de-activate orders. You will need to add a column to the orders table to maintain the status, and then only allow updates of that colum through a procedure. Again thanks for any and all assiatance Regards,Milton C. Craighead, Jr. __ This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]
Re: PL/SQL statement help
Milton, do yourself a favor and sign up for OTN(http://otn.oracle.com) you got access to full oracle docs online. joe Milton C. Craighead, Jr. wrote: I'm having no luck resolving the following PL/SQL errors for a final exam class project. We do not have access to metalink nor do we have access to Oracle PL/SQL documentation. We are being taught from a PL/SQL text book that does not provide ora error message information or where to find them... Any and all assistance will be greatly appreciated. 1) where am I going wrong SQL SELECT order#, customer#, address, city, state, zip, 2 orderdate, shipdate, shipstreet, shipcity, shipstate, 3 shipzip, item#, isbn, quantity 4 FROM customers NATURAL JOIN orders; shipzip, item#, isbn, quantity * ERROR at line 3: ORA-00904: QUANTITY: invalid identifier 2) I'm trying to format a report where the output should look like the following: I'm able to format the item#, isbn, quanty, retail, and total line with out error but Im getting an error message in my veiw... Order# Customer# Customer Address Customer City, State, Zip Order Date Ship Date Ship Street Ship City, Ship State, Ship Zip ITEM#ISBNQUANTYRETAILTOTAL LINE ITEM#ISBNQUANTYRETAILTOTAL LINE SET SERVEROUTPUT ON SQL set linesize 300 SQL set pagesize 20 SQL ttitle CENTER 'The Law Report' SKIP 2 SQL btitle '(Confidential)' SKIP 2 SQL SQL column item# heading 'ITEM#' format a10 truncate SQL column isbn heading 'ISBN' format a15 truncate SQL column quantity heading 'QUANTITY' format a6 truncate SQL column retail heading 'RETAIL' format 990.00 SQL column total line heading 'TOTAL|LINE' format 990.00 SP2-0158: unknown COLUMN option line SQL SQL Drop view bookPub; View dropped. SQL Create view bookPub 2 AS select count(b.title) as bookNum, b.cost, b.pubid 3 from books b natural join orderitems o 4 group by b.pubid, b.cost; View created. SQL SQL SQL SQL SQL Drop view bookRev; View dropped. SQL Create view bookRev 2 As select count(b.title) as bkNum, sum(b.retail - b.cost) as rev, o.customer# 3 from orderitems i natural join books b natural join orders o 4 group by o.customer#; View created. SQL SQL SQL SQL Drop view orderInfo; View dropped. SQL Create view orderInfo 2 As 3 Select c.customer#, o.order#, c.lastname, c.firstname, c.address, 4 o.shipdate, o.shipstreet, o.shipcity, o.shipstate, 5 o.shipzip, i.item#, i.isbn, i.quantity 6 from customers c, orders o, orderitems i Where c.customer# = o.customer#(+) 7 and o.order# = i.order#; View created. SQL SQL CLEAR BREAK breaks cleared SQL CLEAR COLUMN columns cleared SQL SQL SELECT order#, customer#, address, city, state, zip, 2 orderdate, shipdate, shipstreet, shipcity, shipstate, 3 shipzip, item#, isbn, quantity 4 FROM customers NATURAL JOIN orders; shipzip, item#, isbn, quantity * ERROR at line 3: ORA-00904: QUANTITY: invalid identifier SQL SQL SELECT item#, isbn, quantity, retail, sum(retail*quantity) Total Line 2 FROM customers NATURAL JOIN orders NATURAL JOIN orderitems 3 NATURAL JOIN books 4 GROUP BY item#, isbn, quantity, retail; 3) last but not least I can not figure out how to create this statement at all: Deletes are not allowed, but you will be allowed to de-activate orders. You will need to add a column to the orders table to maintain the status, and then only allow updates of that colum through a procedure. Again thanks for any and all assiatance Regards, Milton C. Craighead, Jr. -- Joseph S Testa Chief Technology Officer Data Management Consulting p: 614-791-9000 f: 614-791-9001 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL statement help
For the error: SP2-0158: unknown COLUMN option "line", "column total line" must be one word.. no spaces allowed. Can be total_line or just total. Shamita Shamita "Nelson, Allan" [EMAIL PROTECTED] wrote: quantity is neither a column name or a valid column alias. That's what your error is telling you. Allan -Original Message-From: Milton C. Craighead, Jr. [mailto:[EMAIL PROTECTED] Sent: Thursday, July 24, 2003 11:39 AMTo: Multiple recipients of list ORACLE-LSubject: PL/SQL statement help I'm having no luck resolving the following PL/SQL errorsfor afinal examclass project.We do not have access to metalinknor do we have access to Oracle PL/SQL documentation. We are being taught from a PL/SQL text book that does not provide ora error message information or where to findthem...Any and all assistance will be greatly appreciated. 1) where am I going wrong SQL SELECT order#, customer#, address, city, state, zip, 2 orderdate, shipdate, shipstreet, shipcity, shipstate, 3 shipzip, item#, isbn, quantity 4 FROM customers NATURAL JOIN orders;shipzip, item#, isbn, quantity *ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier 2) I'm trying to format a report where the output should look like the following: I'm able to format the item#, isbn, quanty, retail, and total line with out error but Im getting an error message in my veiw... Order# Customer# Customer Address Customer City, State, Zip Order Date Ship Date Ship Street Ship City, Ship State, Ship Zip ITEM# ISBN QUANTY RETAIL TOTAL LINE ITEM#ISBN QUANTY RETAIL TOTAL LINE SET SERVEROUTPUT ONSQL set linesize 300SQL set pagesize 20SQL ttitle CENTER 'The Law Report' SKIP 2SQL btitle '(Confidential)' SKIP 2SQL SQL column item# heading 'ITEM#' format a10 truncateSQL column isbn heading 'ISBN' format a15 truncateSQL column quantity heading 'QUANTITY' format a6 truncateSQL column retail heading 'RETAIL' format 990.00SQL column total line heading 'TOTAL|LINE' format 990.00SP2-0158: unknown COLUMN option "line"SQL SQL Drop view bookPub; View dropped. SQL Create view bookPub 2 AS select count(b.title) as bookNum, b.cost, b.pubid 3 from books b natural join orderitems o 4 group by b.pubid, b.cost; View created. SQL SQL SQL SQL SQL Drop view bookRev; View dropped. SQL Create view bookRev 2 As select count(b.title) as bkNum, sum(b.retail - b.cost) as rev, o.customer# 3 from orderitems i natural join books b natural join orders o 4 group by o.customer#; View created. SQL SQL SQL SQL Drop view orderInfo; View dropped. SQL Create view orderInfo 2 As 3 Select c.customer#, o.order#, c.lastname, c.firstname, c.address, 4 o.shipdate, o.shipstreet, o.shipcity, o.shipstate, 5 o.shipzip, i.item#, i.isbn, i.quantity 6 from customers c, orders o, orderitems i Where c.customer# = o.customer#(+) 7 and o.order# = i.order#; View created. SQL SQL CLEAR BREAKbreaks clearedSQL CLEAR COLUMNcolumns clearedSQL SQL SELECT order#, customer#, address, city, state, zip, 2 orderdate, shipdate, shipstreet, shipcity, shipstate, 3 shipzip, item#, isbn, quantity 4 FROM customers NATURAL JOIN orders;shipzip, item#, isbn, quantity *ERROR at line 3:ORA-00904: "QUANTITY": invalid identifier SQL SQL SELECT item#, isbn, quantity, retail, sum(retail*quantity) "Total Line" 2 FROM customers NATURAL JOIN orders NATURAL JOIN orderitems 3 NATURAL JOIN books 4 GROUP BY item#, isbn, quantity, retail; 3) last but not least I can not figure out how to create this statement at all: Deletes are not allowed, but you will be allowed to de-activate orders. You will need to add a column to the orders table to maintain the status, and then only allow updates of that colum through a procedure. Again thanks for any and all assiatance Regards,Milton C. Craighead, Jr. __This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or privileged information. Copying, forwarding or distributing this message by persons or entities other than the addressee is prohibited. If you have received this email in error, please contact the sender immediately and delete the material from any computer. This email may have been monitored for policy compliance. [021216]Shamita Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software
Re: PL/SQL- cursors and commits
Kurt: It appears that you are committing inside the loop in the same inner block that the update is in. The commit is freeing up the rows. I find it a good practice sometimes to do commits inside the inner block like that, on long transactions. Reginald W. Bailey Your Friendly Neighborhood DBA [EMAIL PROTECTED] WUSA.COM To: [EMAIL PROTECTED] Sent by: cc: [EMAIL PROTECTED] Subject: PL/SQL- cursors and commits om 04/04/2003 08:39 AM Please respond to ORACLE-L I've been 'experimenting' with the following code in 8.1.5 and it seems to work fine. However, my ORACLE PL/SQL book from O'REILLY (Steven Feuerstein Bill Pribyl 1997) leads me to believe that it should not work. They state As soon as a cursor with a FOR UPDATE is OPENed, all rows...are locked. When [a COMMIT]..occurs, the locks...are released. As a result, you cannot execute another FETCH against a FOR UPDATE cursor after you COMMIT.. They go further to suggest an ORA-01002 would be returned. Any comments? Thanks. Kurt Wiegand [EMAIL PROTECTED] declare local_f1 ctest.f1%TYPE := 0; local_f2 ctest.f2%TYPE := 0; batch_count number(6) := 0; cursor c_select is select f1,f2 from ctest for update; begin open c_select; loop fetch c_select into local_f1, local_f2; exit when c_select%NOTFOUND; update ctest set f2 = f2 + 1 where current of c_select; batch_count := batch_count + 1; if batch_count 99 then batch_count := 0; commit; end if; end loop; close c_select; commit; end; -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL- cursors and commits
Title: PL/SQL- cursors and commits As the book says, it fails with following error(9.2.0.1 on Win2k). declare*ERROR at line 1:ORA-01002: fetch out of sequenceORA-06512: at line 12 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Wiegand, KurtSent: Friday, April 04, 2003 9:39 AMTo: Multiple recipients of list ORACLE-LSubject: PL/SQL- cursors and commits I've been 'experimenting' with the following code in 8.1.5 and it seems to work fine. However, my "ORACLE PL/SQL" book from O'REILLY (Steven Feuerstein Bill Pribyl 1997) leads me to believe that it should not work. They state "As soon as a cursor with a FOR UPDATE is OPENed, all rows...are locked. When [a COMMIT]..occurs, the locks...are released. As a result, you cannot execute another FETCH against a FOR UPDATE cursor after you COMMIT.." They go further to suggest an ORA-01002 would be returned. Any comments? Thanks. Kurt Wiegand [EMAIL PROTECTED] declare local_f1 ctest.f1%TYPE := 0; local_f2 ctest.f2%TYPE := 0; batch_count number(6) := 0; cursor c_select is select f1,f2 from ctest for update; begin open c_select; loop fetch c_select into local_f1, local_f2; exit when c_select%NOTFOUND; update ctest set f2 = f2 + 1 where current of c_select; batch_count := batch_count + 1; if batch_count 99 then batch_count := 0; commit; end if; end loop; close c_select; commit; end;
RE: PL/SQL- cursors and commits
Title: Message This is the last thng u will be writing .. a Commit inside a Loop for every n records processed. First Like Ashish Said u will get ORA-01002 and apart from this u will hit by a bigger problem.. ORA-01555 on long running quries.. HTH Best Regards,Ganesh RDID : +65-6215-8413HP : +65-9067-8474 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of AshishSent: Saturday, April 05, 2003 12:19 AMTo: Multiple recipients of list ORACLE-LSubject: RE: PL/SQL- cursors and commits As the book says, it fails with following error(9.2.0.1 on Win2k). declare*ERROR at line 1:ORA-01002: fetch out of sequenceORA-06512: at line 12 -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Wiegand, KurtSent: Friday, April 04, 2003 9:39 AMTo: Multiple recipients of list ORACLE-LSubject: PL/SQL- cursors and commits I've been 'experimenting' with the following code in 8.1.5 and it seems to work fine. However, my "ORACLE PL/SQL" book from O'REILLY (Steven Feuerstein Bill Pribyl 1997) leads me to believe that it should not work. They state "As soon as a cursor with a FOR UPDATE is OPENed, all rows...are locked. When [a COMMIT]..occurs, the locks...are released. As a result, you cannot execute another FETCH against a FOR UPDATE cursor after you COMMIT.." They go further to suggest an ORA-01002 would be returned. Any comments? Thanks. Kurt Wiegand [EMAIL PROTECTED] declare local_f1 ctest.f1%TYPE := 0; local_f2 ctest.f2%TYPE := 0; batch_count number(6) := 0; cursor c_select is select f1,f2 from ctest for update; begin open c_select; loop fetch c_select into local_f1, local_f2; exit when c_select%NOTFOUND; update ctest set f2 = f2 + 1 where current of c_select; batch_count := batch_count + 1; if batch_count 99 then batch_count := 0; commit; end if; end loop; close c_select; commit; end;
RE: pl/sql engine doubt
Let me throw some shade onto the issue: a) PL/SQL engine does not execute SQL statements - ever. It passes them over to the SQL Executor engine. b) PL/SQL is just a procedural enclosure of the SQL language. There is another one: it's called Java. c) Procedural part can be parsed either by an application tool or oracle kernel. The thing to watch for is the version of the PL/SQL engine embedded in the application tool. The ideal situation is when these two versions are the same. If they are not, life can get interesting. -Original Message- Sent: Monday, March 24, 2003 4:29 AM To: Multiple recipients of list ORACLE-L Hi List, I was going thru Oracle PL/SQL User's Guide and Reference. http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920 /a96624/01_oview.htm#962 Paragraphs below (near the Figure 1-4 PL/SQL Engine in the doc) confused me little. para1 --- These two environments are independent. PL/SQL is bundled with the Oracle server but might be unavailable in some tools. In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 1-4 shows the PL/SQL engine processing an anonymous block. The engine executes procedural statements but sends SQL statements to the SQL Statement Executor in the Oracle server. para2-- In the Oracle Database Server: Application development tools that lack a local PL/SQL engine must rely on Oracle to process PL/SQL blocks and subprograms. When it contains the PL/SQL engine, an Oracle server can process PL/SQL blocks and subprograms as well as single SQL statements. The Oracle server passes the blocks and subprograms to its local PL/SQL engine. Now my doubt is: 1.In para1 - Does pl/sql engine only processes the procedural statements and always passes SQL statements to SQL Statement Executor ? 2.In para2 sentence When it contains ... says Oracle pl/sql engine can process SQL statements. Statements in both paragraphs seems ambiguous. Pls shade some light. Thanks Sam __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sam d INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Gogala, Mladen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: pl/sql engine doubt
PL/SQL can be seen as a stand-alone interpreted language and can as such exist in various environments. "Various envrionments" does in real life mean the PL/SQL engine is found in both the Oracle server and in the Oracle Forms tool. If an application needs to execute some PL/SQL it should (at least in principle) be able to execute this in any available environment where the PL/SQL engine is present, in practice, however, this is only the case in the database server and in Oracle Forms (plus probably Oracle Reports, I really don't know, but it's beside the point). In practical terms, if you are coding forms (plus probably reports), you have PL/SQL available in the tool and in the database server; in all other cases, it's only in the database server. Whenever PL/SQL needs to execute some SQL statements, it will have to go to the Oracle server; if your PL/SQL happens to already execute there, it is a simple internal context switch inside the server, if your PL/SQL happens to execute in Oracle Forms (or reports), it will have to go over your SQL*Net connection to execute the SQL code. To confuse things somewhat, PL/SQL actually has a SQL parser; hence, PL/SQL can verify SQL statements during parse without actually talking to the database. This has some interesting side effects in version 8, where the SQL parser inside PL/SQL tend to not have adopted all the latest SQL features, i.e. there is valid SQL (in e.g. 8.1.7), that you cannot use in PL/SQL without using dynamic SQL (either DBMS_SQL or native dynamic SQL). /Bjrn. sam d wrote: Hi List, I was going thru Oracle "PL/SQL User's Guide and Reference". http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/appdev.920/a96624/01_oview.htm#962 Paragraphs below (near the "Figure 1-4 PL/SQL Engine" in the doc) confused me little. para1 --- "These two environments are independent. PL/SQL is bundled with the Oracle server but might be unavailable in some tools. In either environment, the PL/SQL engine accepts as input any valid PL/SQL block or subprogram. Figure 1-4 shows the PL/SQL engine processing an anonymous block. The engine executes procedural statements but sends SQL statements to the SQL Statement Executor in the Oracle server." para2-- "In the Oracle Database Server: Application development tools that lack a local PL/SQL engine must rely on Oracle to process PL/SQL blocks and subprograms. When it contains the PL/SQL engine, an Oracle server can process PL/SQL blocks and subprograms as well as single SQL statements. The Oracle server passes the blocks and subprograms to its local PL/SQL engine." Now my doubt is: 1.In para1 - Does pl/sql engine only processes the procedural statements and always passes SQL statements to SQL Statement Executor ? 2.In para2 sentence "When it contains ..." says Oracle pl/sql engine can process SQL statements. Statements in both paragraphs seems ambiguous. Pls shade some light. Thanks Sam __ Do you Yahoo!? Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop! http://platinum.yahoo.com -- Bjrn Engsig, Miracle A/S Member of Oak Table Network [EMAIL PROTECTED] - http://MiracleAS.dk
Re: pl/sql and java script ???
Janet, Try the forums at http://java.sun.com or inquire at http://devtrends.oracle.com Darrell [EMAIL PROTECTED] 03/13/03 10:59AM Hi, Our app is strange. :-( We use pl/sql(9i) package to create all the html and java script. I have two drop down boxes on a form, the values for the second box changes dynamically depends on the value of the first box. The values for the boxes are from cursors written in pl/sql. We currently resubmit the form after the first box is clicked. How to handle this without resubmitting the form? How to let java script function read data from pl/sql cursors?? PS: If you know an email list or metalink like resource for J2EE and/or Java script, please let me know!!! Thank you in advance. Janet __ Do you Yahoo!? Yahoo! Web Hosting - establish your business online http://webhosting.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Janet Linsy INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL
You can enter them directly at the SQL prompt or you can store them in database or keep them in files and run them with the @ command. The choice is yours :O) K. -Original Message- Sent: 05 March 2003 08:50 To: Multiple recipients of list ORACLE-L Hello all, I wanted to know how to write scripts in PL/SQL. i mean how to start it ? directly write htem in sql SQL prompt itself ? and any good sites on PL/SQL to learn ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL
Use keyboard! :-) Use any text editor like vim or scite (synatax coloring is better) to write you script and save it to the file. Then run your script from sqlplus prompt (sqlplus:@/home/...path and name of your file). JP On Wednesday 05 March 2003 09:49, you wrote: Hello all, I wanted to know how to write scripts in PL/SQL. i mean how to start it ? directly write htem in sql SQL prompt itself ? and any good sites on PL/SQL to learn ? Thanks and Regards, Santosh -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL
Aside from docs at technet.oracle.com, a perfect book for you is Beginning Oracle Programming, authored by Sean Dillon, Christopher Beck, and Tom Kyte. (ISBN # 1-861006-90-X) This book sells for around $50.00 in the U.S., but it is one of the most useful books I ever purchased. Darrell Landrum [EMAIL PROTECTED] 03/05/03 02:49AM Hello all, I wanted to know how to write scripts in PL/SQL. i mean how to start it ? directly write htem in sql SQL prompt itself ? and any good sites on PL/SQL to learn ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Darrell Landrum INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL
Santosh - I went to http://www.google.com http://www.google.com and searched for the terms PL/SQL tutorial. I received several interesting-looking sites. Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, March 05, 2003 2:50 AM To: Multiple recipients of list ORACLE-L Hello all, I wanted to know how to write scripts in PL/SQL. i mean how to start it ? directly write htem in sql SQL prompt itself ? and any good sites on PL/SQL to learn ? Thanks and Regards, Santosh -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL output on Client machine
Title: RE: PL/SQL output on Client machine Options: 1. You could use utl_file and write it to server and then 1.1 manually or automagicall FTP it to client 1.2 use UTL_TCP to ftp it to client. 2. On the server side, create a [global] temp table and load all the data into it. 2.1 then use text_io from within form to select from the table and dump to a local file. i have few more suggestions ... but one of these should do the trick for you. raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: Jared Still [mailto:[EMAIL PROTECTED]] Sent: Tuesday, February 04, 2003 10:15 AM To: Multiple recipients of list ORACLE-L Subject: Re: PlSQL output on Client machine The output must be on drive mounted by the server. If you want local output, you have to use a client side program: Forms, Perl, C, VB, etc. Jared On Monday 03 February 2003 00:03, Foelz.Frank wrote: Hi folks, how can I tell a PLSQL script not to store it's UTL_FILE.Put_Line(fHandle,'TEXT') output to the filesystem of the server, but on the client ? Whenever I run this locally it's ok. But if the DB is on a network server, it's filesystem is used. I am using NT/SP6/Oracle 8x. Is this possible ??? TNX Frank -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). 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.*2
RE: PL/SQL Date Format
Dan,
Look
at the TO_DATE function. You can easily change your procedure to the
following:
PROCEDURE
set_expire_date ( p_user_group_id IN NUMBER DEFAULT
NULL,
p_product_id IN VARCHAR2 DEFAULT
NULL,
p_expire_date IN VARCHAR2 DEFAULT NULL)IS
local_date
date;
== added this
BEGIN dbms_output.enable(1);
dbms_output.put_line('This is set_expire_date');
local_date :=
to_Date(p_expire_date,'-MM-DD'); added
this
dbms_output.put_line('Expire date is '||to_char(local_date, '-MM-DD')); == changed this
EXCEPTION WHEN INVALID_NUMBER
THEN dbms_output.put_line('Invalid Date
format'); dbms_output.put_line('Format must be
-MM-DD ('||to_char(sysdate, '-MM-DD')||')'); WHEN
VALUE_ERROR THEN dbms_output.put_line('Invalid
Date format'); dbms_output.put_line('Format
must be -MM-DD ('||to_char(sysdate, '-MM-DD')||')');END
set_expire_date;
Hope this helps
Tom Mercadante Oracle Certified Professional
-Original Message-From: Fink, Dan
[mailto:[EMAIL PROTECTED]]Sent: Tuesday, January 07, 2003 6:20
PMTo: Multiple recipients of list ORACLE-LSubject:
PL/SQL Date Format
Okay, PL/SQL
programmers, a lowly dba is in need of your assistance. If you will show pity
on my poor self, who does not deserve even the mearest consideration, I will
be greatly indebted...
I have a proc that
needs to process a date field. The users want to enter it in a specific format
(-MM-DD)that is not the same as the system format (DD-MON-YY). If
they do not enter the correct format, I need to raise an exception. The
parameter was set as DATE, but it would not allow me to enter the requested
format, so I changed it to VARCHAR2. When it was date, it would not accept the
requested format. When it is varchar2, PL/SQL does an implict conversion of
the date. Unfortunately, it is an incomplete conversion and the date is not
correct (see example below).
SQL execute
qa_subs.set_expire_date(1,'TEST','01-JAN-01');This is
set_expire_dateExpire date is 0001-01-01
SQL execute
qa_subs.set_expire_date(1,'TEST','2001-01-01');This is
set_expire_dateExpire date is 2001-01-01
So I added a substr to extract the date
and try to convert it to numbers. Very
unelegant...
PROCEDURE
set_expire_date ( p_user_group_id IN NUMBER DEFAULT
NULL,
p_product_id IN VARCHAR2 DEFAULT
NULL,
p_expire_date IN VARCHAR2 DEFAULT NULL)ISBEGIN
dbms_output.enable(1); dbms_output.put_line('This is
set_expire_date'); v_expire_year := substr(p_expire_date, 1,
4); v_expire_month := substr(p_expire_date,
6,2); v_expire_day := substr(p_expire_date,
9,2); v_expire_date := to_date(p_expire_date,
'-MM-DD'); dbms_output.put_line('Expire date is
'||to_char(v_expire_date, '-MM-DD'));
EXCEPTION WHEN INVALID_NUMBER
THEN dbms_output.put_line('Invalid Date
format'); dbms_output.put_line('Format must
be -MM-DD ('||to_char(sysdate, '-MM-DD')||')'); WHEN
VALUE_ERROR THEN
dbms_output.put_line('Invalid Date format');
dbms_output.put_line('Format must be -MM-DD ('||to_char(sysdate,
'-MM-DD')||')');END set_expire_date;
Is there a method
(other than altering the session before calling the proc) to force an input
value to be in a certain format? I've checked my docs and online and I'm
drawing a blank.
With Humble
regards,
Dan
Fink
RE: PL/SQL Date Format
Ok, try this one. It's a little smarter. :)
create or replace function df1
( date_in varchar2 )
return date
is
x_date exception;
pragma exception_init(x_date, -1830);
v_source_date_format varchar2(20) := '-mm-dd';
begin
if owa_pattern.match(date_in,'^\d{4}-\d{2}-\d{2}')
then
null;
else
raise_application_error(-2,'Hey! Thats a bad date!');
end if;
return to_date(date_in, v_source_date_format);
end;
/
show errors function df1
Jared
Jeremy Pulcifer [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/07/2003 05:38 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: PL/SQL Date Format
Not good, Jared. Try this:
SQL select df1('01-JAN-03') from dual;
DF1('01-J
-
03-JAN-01
Oops! I don't know how you could do this other than to parse the string
like you did and look for invalid_num exceptions. Or force the app
software to handle the data entry validation and convert it to a data
format.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 4:53 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: PL/SQL Date Format
How about:
create or replace function df1
( date_in varchar2 )
return date
is
v_test_date date;
x_date exception;
pragma exception_init(x_date, -1830);
v_source_date_format varchar2(20) := '-mm-dd';
begin
begin
v_test_date := to_date(date_in, v_source_date_format);
exception
when x_date then
raise_application_error(-2,'Hey! Thats a bad date!');
end;
return v_test_date;
end;
/
show errors function df1
select df1('2003-01-07') from dual;
select df1('01-07-2003') from dual;
Jared
Fink, Dan [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/07/2003 03:20 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:PL/SQL Date Format
Okay, PL/SQL programmers, a lowly dba is in need of your
assistance. If
you will show pity on my poor self, who does not deserve even
the mearest
consideration, I will be greatly indebted...
I have a proc that needs to process a date field. The users
want to enter
it in a specific format (-MM-DD) that is not the same as
the system
format (DD-MON-YY). If they do not enter the correct format,
I need to
raise an exception. The parameter was set as DATE, but it
would not allow
me to enter the requested format, so I changed it to
VARCHAR2. When it was
date, it would not accept the requested format. When it is varchar2,
PL/SQL does an implict conversion of the date. Unfortunately,
it is an
incomplete conversion and the date is not correct (see example below).
SQL execute qa_subs.set_expire_date(1,'TEST','01-JAN-01');
This is set_expire_date
Expire date is 0001-01-01
SQL execute qa_subs.set_expire_date(1,'TEST','2001-01-01');
This is set_expire_date
Expire date is 2001-01-01
So I added a substr to extract the date and try to convert it
to numbers.
Very unelegant...
PROCEDURE set_expire_date ( p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2 DEFAULT NULL,
p_expire_date IN VARCHAR2 DEFAULT
NULL) IS BEGIN
dbms_output.enable(1);
dbms_output.put_line('This is set_expire_date');
v_expire_year := substr(p_expire_date, 1, 4);
v_expire_month := substr(p_expire_date, 6,2);
v_expire_day := substr(p_expire_date, 9,2);
v_expire_date := to_date(p_expire_date, '-MM-DD');
dbms_output.put_line('Expire date is '||to_char(v_expire_date,
'-MM-DD'));
EXCEPTION
WHEN INVALID_NUMBER THEN
dbms_output.put_line('Invalid Date format');
dbms_output.put_line('Format must be -MM-DD
('||to_char(sysdate,
'-MM-DD')||')');
WHEN VALUE_ERROR THEN
dbms_output.put_line('Invalid Date format');
dbms_output.put_line('Format must be -MM-DD
('||to_char(sysdate,
'-MM-DD')||')');
END set_expire_date;
Is there a method (other than altering the session before calling the
proc) to force an input value to be in a certain format? I've
checked my
docs and online and I'm drawing a blank.
With Humble regards,
Dan Fink
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
and in the message BODY, include
Re: PL/SQL Date Format
Jared,
SELECT df1('2002-13-01') FROM dual; -- :)
CREATE OR REPLACE PROCEDURE set_expire_date (
p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2 DEFAULT NULL,
p_expire_dateIN VARCHAR2 DEFAULT NULL
)
IS
ld_dummy DATE;
BEGIN
ld_dummy := TO_DATE(p_expire_date, '-MM-DD');
IF (TO_CHAR(ld_dummy, '-MM-DD') = p_expire_date)
THEN
dbms_output.put_line('Modified value: ' || TO_CHAR(ld_dummy,
'-MM-DD'));
ELSE
dbms_output.put('Err:');
dbms_output.put('p_expire_date=' || p_expire_date || '');
dbms_output.put_line(',ld_dummy=' || TO_CHAR(ld_dummy, '-MM-DD'));
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Something is wrong');
END set_expire_date;
/
SET SERVEROUTPUT ON
EXECUTE set_expire_date(1, 'TEST', '0001- 1-01');
EXECUTE set_expire_date(1, 'TEST', '01-01-01');
EXECUTE set_expire_date(1, 'TEST', '2001-13-01');
EXECUTE set_expire_date(1, 'TEST', '12-2002-01');
EXECUTE set_expire_date(1, 'TEST', '2002-12-01');
EXECUTE set_expire_date(1, 'TEST', ' 01-01-01');
EXECUTE set_expire_date(1, 'TEST', NULL);
EXECUTE set_expire_date(1, 'TEST', '01-JAN-03');
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
[EMAIL PROTECTED] wrote:
Ok, try this one. It's a little smarter. :)
create or replace function df1
( date_in varchar2 )
return date
is
x_date exception;
pragma exception_init(x_date, -1830);
v_source_date_format varchar2(20) := '-mm-dd';
begin
if owa_pattern.match(date_in,'^\d{4}-\d{2}-\d{2}')
then
null;
else
raise_application_error(-2,'Hey! Thats a bad date!');
end if;
return to_date(date_in, v_source_date_format);
end;
/
show errors function df1
Jared
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: PL/SQL Date Format
Hey, I'm a concepts guy!
He asked for valid formats, not valid dates.
Just combine the code from my 2 posts, and your done.
:)
Jared
Vladimir Begun [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/08/2003 02:29 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:Re: PL/SQL Date Format
Jared,
SELECT df1('2002-13-01') FROM dual; -- :)
CREATE OR REPLACE PROCEDURE set_expire_date (
p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2 DEFAULT NULL,
p_expire_dateIN VARCHAR2 DEFAULT NULL
)
IS
ld_dummy DATE;
BEGIN
ld_dummy := TO_DATE(p_expire_date, '-MM-DD');
IF (TO_CHAR(ld_dummy, '-MM-DD') = p_expire_date)
THEN
dbms_output.put_line('Modified value: ' || TO_CHAR(ld_dummy,
'-MM-DD'));
ELSE
dbms_output.put('Err:');
dbms_output.put('p_expire_date=' || p_expire_date || '');
dbms_output.put_line(',ld_dummy=' || TO_CHAR(ld_dummy,
'-MM-DD'));
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Something is wrong');
END set_expire_date;
/
SET SERVEROUTPUT ON
EXECUTE set_expire_date(1, 'TEST', '0001- 1-01');
EXECUTE set_expire_date(1, 'TEST', '01-01-01');
EXECUTE set_expire_date(1, 'TEST', '2001-13-01');
EXECUTE set_expire_date(1, 'TEST', '12-2002-01');
EXECUTE set_expire_date(1, 'TEST', '2002-12-01');
EXECUTE set_expire_date(1, 'TEST', ' 01-01-01');
EXECUTE set_expire_date(1, 'TEST', NULL);
EXECUTE set_expire_date(1, 'TEST', '01-JAN-03');
--
Vladimir Begun
The statements and opinions expressed here are my own and
do not necessarily represent those of Oracle Corporation.
[EMAIL PROTECTED] wrote:
Ok, try this one. It's a little smarter. :)
create or replace function df1
( date_in varchar2 )
return date
is
x_date exception;
pragma exception_init(x_date, -1830);
v_source_date_format varchar2(20) := '-mm-dd';
begin
if owa_pattern.match(date_in,'^\d{4}-\d{2}-\d{2}')
then
null;
else
raise_application_error(-2,'Hey! Thats a bad date!');
end if;
return to_date(date_in, v_source_date_format);
end;
/
show errors function df1
Jared
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Vladimir Begun
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Re: PL/SQL Date Format
How about:
create or replace function df1
( date_in varchar2 )
return date
is
v_test_date date;
x_date exception;
pragma exception_init(x_date, -1830);
v_source_date_format varchar2(20) := '-mm-dd';
begin
begin
v_test_date := to_date(date_in, v_source_date_format);
exception
when x_date then
raise_application_error(-2,'Hey! Thats a bad date!');
end;
return v_test_date;
end;
/
show errors function df1
select df1('2003-01-07') from dual;
select df1('01-07-2003') from dual;
Jared
Fink, Dan [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/07/2003 03:20 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:PL/SQL Date Format
Okay, PL/SQL programmers, a lowly dba is in need of your assistance. If
you will show pity on my poor self, who does not deserve even the mearest
consideration, I will be greatly indebted...
I have a proc that needs to process a date field. The users want to enter
it in a specific format (-MM-DD) that is not the same as the system
format (DD-MON-YY). If they do not enter the correct format, I need to
raise an exception. The parameter was set as DATE, but it would not allow
me to enter the requested format, so I changed it to VARCHAR2. When it was
date, it would not accept the requested format. When it is varchar2,
PL/SQL does an implict conversion of the date. Unfortunately, it is an
incomplete conversion and the date is not correct (see example below).
SQL execute qa_subs.set_expire_date(1,'TEST','01-JAN-01');
This is set_expire_date
Expire date is 0001-01-01
SQL execute qa_subs.set_expire_date(1,'TEST','2001-01-01');
This is set_expire_date
Expire date is 2001-01-01
So I added a substr to extract the date and try to convert it to numbers.
Very unelegant...
PROCEDURE set_expire_date ( p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2 DEFAULT NULL,
p_expire_date IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
dbms_output.enable(1);
dbms_output.put_line('This is set_expire_date');
v_expire_year := substr(p_expire_date, 1, 4);
v_expire_month := substr(p_expire_date, 6,2);
v_expire_day := substr(p_expire_date, 9,2);
v_expire_date := to_date(p_expire_date, '-MM-DD');
dbms_output.put_line('Expire date is '||to_char(v_expire_date,
'-MM-DD'));
EXCEPTION
WHEN INVALID_NUMBER THEN
dbms_output.put_line('Invalid Date format');
dbms_output.put_line('Format must be -MM-DD ('||to_char(sysdate,
'-MM-DD')||')');
WHEN VALUE_ERROR THEN
dbms_output.put_line('Invalid Date format');
dbms_output.put_line('Format must be -MM-DD ('||to_char(sysdate,
'-MM-DD')||')');
END set_expire_date;
Is there a method (other than altering the session before calling the
proc) to force an input value to be in a certain format? I've checked my
docs and online and I'm drawing a blank.
With Humble regards,
Dan Fink
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
RE: PL/SQL Date Format
Title: RE: PL/SQL Date Format
Not good, Jared. Try this:
SQL select df1('01-JAN-03') from dual;
DF1('01-J
-
03-JAN-01
Oops! I don't know how you could do this other than to parse the string like you did and look for invalid_num exceptions. Or force the app software to handle the data entry validation and convert it to a data format.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, January 07, 2003 4:53 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: PL/SQL Date Format
How about:
create or replace function df1
( date_in varchar2 )
return date
is
v_test_date date;
x_date exception;
pragma exception_init(x_date, -1830);
v_source_date_format varchar2(20) := '-mm-dd';
begin
begin
v_test_date := to_date(date_in, v_source_date_format);
exception
when x_date then
raise_application_error(-2,'Hey! Thats a bad date!');
end;
return v_test_date;
end;
/
show errors function df1
select df1('2003-01-07') from dual;
select df1('01-07-2003') from dual;
Jared
Fink, Dan [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
01/07/2003 03:20 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject: PL/SQL Date Format
Okay, PL/SQL programmers, a lowly dba is in need of your
assistance. If
you will show pity on my poor self, who does not deserve even
the mearest
consideration, I will be greatly indebted...
I have a proc that needs to process a date field. The users
want to enter
it in a specific format (-MM-DD) that is not the same as
the system
format (DD-MON-YY). If they do not enter the correct format,
I need to
raise an exception. The parameter was set as DATE, but it
would not allow
me to enter the requested format, so I changed it to
VARCHAR2. When it was
date, it would not accept the requested format. When it is varchar2,
PL/SQL does an implict conversion of the date. Unfortunately,
it is an
incomplete conversion and the date is not correct (see example below).
SQL execute qa_subs.set_expire_date(1,'TEST','01-JAN-01');
This is set_expire_date
Expire date is 0001-01-01
SQL execute qa_subs.set_expire_date(1,'TEST','2001-01-01');
This is set_expire_date
Expire date is 2001-01-01
So I added a substr to extract the date and try to convert it
to numbers.
Very unelegant...
PROCEDURE set_expire_date ( p_user_group_id IN NUMBER DEFAULT NULL,
p_product_id IN VARCHAR2 DEFAULT NULL,
p_expire_date IN VARCHAR2 DEFAULT
NULL) IS BEGIN
dbms_output.enable(1);
dbms_output.put_line('This is set_expire_date');
v_expire_year := substr(p_expire_date, 1, 4);
v_expire_month := substr(p_expire_date, 6,2);
v_expire_day := substr(p_expire_date, 9,2);
v_expire_date := to_date(p_expire_date, '-MM-DD');
dbms_output.put_line('Expire date is '||to_char(v_expire_date,
'-MM-DD'));
EXCEPTION
WHEN INVALID_NUMBER THEN
dbms_output.put_line('Invalid Date format');
dbms_output.put_line('Format must be -MM-DD
('||to_char(sysdate,
'-MM-DD')||')');
WHEN VALUE_ERROR THEN
dbms_output.put_line('Invalid Date format');
dbms_output.put_line('Format must be -MM-DD
('||to_char(sysdate,
'-MM-DD')||')');
END set_expire_date;
Is there a method (other than altering the session before calling the
proc) to force an input value to be in a certain format? I've
checked my
docs and online and I'm drawing a blank.
With Humble regards,
Dan Fink
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
-
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru')
and in the message BODY, include a line containing: UNSUB
ORACLE-L (or the name of mailing list you want to be removed
from). You may also send the HELP command for other
information (like subscribing).
RE: PL/SQL help
David- I've thought of both dbms_sql and dummy conditions too. Oracle Application reports use the dummy conditions all the time. I'm off to see if you can set cursor_sharing at the session level and not use bind params. I'm assuming no one else has figured this out either due to the lack of response. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/20/02 01:28 AMcc: Please respond toSubject: RE: PL/SQL help ORACLE-L Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 19:53 To: Multiple recipients of list ORACLE-L Subject: PL/SQL help I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name
RE: PL/SQL help
Title: RE: PL/SQL help Go to Tom Kyte's sight and look for contexts - he explains a way to build sql statements using native dynamic sql and refcursors and contexts where the select statement and predicate is variable and the output might also be variable. I get there by searching www.yahoo.com for asktom -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: Friday, September 20, 2002 2:06 PM To: Multiple recipients of list ORACLE-L Subject: RE: PL/SQL help David- I've thought of both dbms_sql and dummy conditions too. Oracle Application reports use the dummy conditions all the time. I'm off to see if you can set cursor_sharing at the session level and not use bind params. I'm assuming no one else has figured this out either due to the lack of response. Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan [EMAIL PROTECTED] To: [EMAIL PROTECTED] 09/20/02 01:28 AM cc: Please respond to Subject: RE: PL/SQL help ORACLE-L Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 19:53 To: Multiple recipients of list ORACLE-L Subject: PL/SQL help I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California
RE: PL/SQL help
Ron We've run into this problem and haven't really found a satisfactory answer. It sounds like a job for dbms_sql, but the docs say its slower than native dynamic sql. Has anyone tested this? One other alternative I can think of (but have never tried) is to use dummy conditions in the sql; something like: - c_sql := 'select col1 from atable where col2 = :1 and :2 is null and :3 is null'; -- David Lord -Original Message- From: Ron Thomas [mailto:[EMAIL PROTECTED]] Sent: 19 September 2002 19:53 To: Multiple recipients of list ORACLE-L Subject: PL/SQL help I am building a dynamic sql statement which will contain varying number of bind variables depending on user selection criteria. As an example, the sql statement may be: c_sql := 'select col1 from atable where col2 = :1' ; or it may be c_sql := 'select col1 from atable where col2 = :1 and col2 between :2 and :3' ; or it may be ... etc. I am trying to avoid ugly code such as: IF case1 THEN OPEN csr FOR c_sql USING var1 ; ELSIF case2 THEN OPEN csr FOR c_sql USING var1, var2, var3 ; ELSIF . END IF ; Once the sql statement is created, it will be opened/closed multiple times, so I want to use bind variables to avoid parsing. So I thought, hum, sounds like a job for EXECUTE IMMEDIATE, but in the various incarnations I tried, could not get it to work. The FM have not been much help (still looking tho). What am I missing? Thanks, Ron Thomas Hypercom, Inc [EMAIL PROTECTED] Each new user of a new system uncovers a new class of bugs. -- Kernighan -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Thomas INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ** This message (including any attachments) is confidential and may be legally privileged. If you are not the intended recipient, you should not disclose, copy or use any part of it - please delete all copies immediately and notify the Hays Group Email Helpdesk at [EMAIL PROTECTED] Any information, statements or opinions contained in this message (including any attachments) are given by the author. They are not given on behalf of Hays unless subsequently confirmed by an individual other than the author who is duly authorised to represent Hays. A member of the Hays plc group of companies. Hays plc is registered in England and Wales number 2150950. Registered Office Hays House Millmead Guildford Surrey GU2 4HJ. ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lord, David - CSG INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: PL/SQL Editor
We are using TOAD for the DBA team. For the developers we got PlSqlDeveloper and it works fine. You can check also www.quest.com for the new sql editor that they developed recently. Yechiel Adar Mehish - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 8:33 AM Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Yechiel Adar INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
Gotta love that. joe Mercadante, Thomas F wrote: Bill, You said One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. I got around this by replacing the v$session view with the following: Note the REPLACE statement in the select for the PROGRAM and MODULE columns: CREATE OR REPLACE VIEW V$SESSION ( SADDR, SID, SERIAL#, AUDSID, PADDR, USER#, USERNAME, COMMAND, OWNERID, TADDR, LOCKWAIT, STATUS, SERVER, SCHEMA#, SCHEMANAME, OSUSER, PROCESS, MACHINE, TERMINAL, PROGRAM, TYPE, SQL_ADDRESS, SQL_HASH_VALUE, PREV_SQL_ADDR, PREV_HASH_VALUE, MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO, FIXED_TABLE_SEQUENCE, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#, LOGON_TIME, LAST_CALL_ET, PDML_ENABLED, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS ) AS SELECT SADDR , SID , SERIAL# ,AUDSID ,PADDR ,USER# ,USERNAME ,COMMAND ,OWNERID ,TADDR ,LOCKWAIT ,STATUS ,SERVER ,SCHEMA# ,SCHEMANAME ,OSUSER ,PROCESS ,MACHINE ,TERMINAL ,REPLACE(UPPER(PROGRAM),'TOAD','FROG') PROGRAM ,TYPE ,SQL_ADDRESS ,SQL_HASH_VALUE ,PREV_SQL_ADDR ,PREV_HASH_VALUE ,REPLACE(UPPER(MODULE),'T·O·A·D·','F~R~O~G') MODULE ,MODULE_HASH ,ACTION ,ACTION_HASH ,CLIENT_INFO ,FIXED_TABLE_SEQUENCE ,ROW_WAIT_OBJ# ,ROW_WAIT_FILE# ,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW# ,LOGON_TIME ,LAST_CALL_ET ,PDML_ENABLED ,FAILOVER_TYPE ,FAILOVER_METHOD ,FAILED_OVER ,RESOURCE_CONSUMER_GROUP ,PDML_STATUS ,PDDL_STATUS ,PQ_STATUS FROM sys.V_$SESSION -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe Testa INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
Title: RE: PL/SQL Editor I hate to seem like I don't have a sense of humour, but wouldn't a more ethical way be to pay for the full version of the product? -Original Message- From: Mercadante, Thomas F [mailto:[EMAIL PROTECTED]] You said One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. I got around this by replacing the v$session view with the following: Note the REPLACE statement in the select for the PROGRAM and MODULE columns: ...
RE: PL/SQL Editor
PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
I really like Emace with PL/SQL+SQLplus mode. It is KISS. :=) mvh HEnrik --- [EMAIL PROTECTED] wrote: Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . = __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Editor
we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
Why didn't you tried TOra? http://www.globecom.se/tora I like it very much and I use it together with SciTE (text editor). JP On Wednesday 28 August 2002 16:18, you wrote: we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
Take a look to TORA, I prefer it over Toad and has a free version too. Ramon - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 9:18 AM we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Ramon E. Estevez INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
can you please post URL for this program? thx bill -Original Message- Sent: Wednesday, August 28, 2002 9:53 AM To: Multiple recipients of list ORACLE-L I really like Emace with PL/SQL+SQLplus mode. It is KISS. :=) mvh HEnrik --- [EMAIL PROTECTED] wrote: Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . = __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
cool - so the freeware version checks the v$session view and counts the existing number of TOAD programs/modules? -Original Message- Sent: Wednesday, August 28, 2002 10:31 AM To: '[EMAIL PROTECTED]' Cc: '[EMAIL PROTECTED]' Bill, You said One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. I got around this by replacing the v$session view with the following: Note the REPLACE statement in the select for the PROGRAM and MODULE columns: CREATE OR REPLACE VIEW V$SESSION ( SADDR, SID, SERIAL#, AUDSID, PADDR, USER#, USERNAME, COMMAND, OWNERID, TADDR, LOCKWAIT, STATUS, SERVER, SCHEMA#, SCHEMANAME, OSUSER, PROCESS, MACHINE, TERMINAL, PROGRAM, TYPE, SQL_ADDRESS, SQL_HASH_VALUE, PREV_SQL_ADDR, PREV_HASH_VALUE, MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO, FIXED_TABLE_SEQUENCE, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#, LOGON_TIME, LAST_CALL_ET, PDML_ENABLED, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS ) AS SELECT SADDR , SID , SERIAL# ,AUDSID ,PADDR ,USER# ,USERNAME ,COMMAND ,OWNERID ,TADDR ,LOCKWAIT ,STATUS ,SERVER ,SCHEMA# ,SCHEMANAME ,OSUSER ,PROCESS ,MACHINE ,TERMINAL ,REPLACE(UPPER(PROGRAM),'TOAD','FROG') PROGRAM ,TYPE ,SQL_ADDRESS ,SQL_HASH_VALUE ,PREV_SQL_ADDR ,PREV_HASH_VALUE ,REPLACE(UPPER(MODULE),'T·O·A·D·','F~R~O~G') MODULE ,MODULE_HASH ,ACTION ,ACTION_HASH ,CLIENT_INFO ,FIXED_TABLE_SEQUENCE ,ROW_WAIT_OBJ# ,ROW_WAIT_FILE# ,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW# ,LOGON_TIME ,LAST_CALL_ET ,PDML_ENABLED ,FAILOVER_TYPE ,FAILOVER_METHOD ,FAILED_OVER ,RESOURCE_CONSUMER_GROUP ,PDML_STATUS ,PDDL_STATUS ,PQ_STATUS FROM sys.V_$SESSION -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
Beware ... TORA is not free on Windows platform ... it is free however on Linux ... Funny no once has mentioned Oracle's built-in editor that comes with Forms ... My preferences are 1. Vi 2. Vim 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! *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.*2
RE: PL/SQL Editor
Benthicsoftware has a good series of editors. They are all about US$30 a piece or so. Not as powerfull as TOAD but if you are just doing PL/SQL Development they work great. From: Magaliff, Bill [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: PL/SQL Editor Date: Wed, 28 Aug 2002 06:18:24 -0800 we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). I no longer need to punish, deceive, or compromise myself, unless I want to stay employed. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
I think, he's talking about Emacs - huge and powerful OS without text editor :-))) JP On Wednesday 28 August 2002 17:43, you wrote: can you please post URL for this program? thx bill -Original Message- Sent: Wednesday, August 28, 2002 9:53 AM To: Multiple recipients of list ORACLE-L I really like Emace with PL/SQL+SQLplus mode. It is KISS. :=) mvh HEnrik --- [EMAIL PROTECTED] wrote: Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . = __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
I'll second that -- I haven't used Benthic's PL/SQL editor very much, but I use Golden almost religiously as a replacement for SQL*Plus because it is small and fast and has a tabbed window interface with spreadsheet style output that works well for me. -- Philip Douglass Internet Networking Group Database Administrator SIRS Publishing, Inc. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 12:52 PM Benthicsoftware has a good series of editors. They are all about US$30 a piece or so. Not as powerfull as TOAD but if you are just doing PL/SQL Development they work great. From: Magaliff, Bill [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: PL/SQL Editor Date: Wed, 28 Aug 2002 06:18:24 -0800 we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). I no longer need to punish, deceive, or compromise myself, unless I want to stay employed. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from
RE: RE: PL/SQL Editor
haven't looked at that in a while, but don't you have to install Developer to use that? (It's not available standalone, is it?) -Original Message- Sent: Wednesday, August 28, 2002 1:39 PM To: Multiple recipients of list ORACLE-L Well, since someone else cares to mention it, I do use Procedure Builder, Oracle's PL/SQL development tool from Developer and I like it a lot. The latest version even does syntax highlighting, indentation, and click on the error I'll show you where it is. Dick Goulet Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 8/28/2002 8:52 AM Beware ... TORA is not free on Windows platform ... it is free however on Linux ... Funny no once has mentioned Oracle's built-in editor that comes with Forms ... My preferences are 1. Vi 2. Vim Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor
I am getting this error: ORA-01731 Saying that I can't do it because of circular view, I can't create a view from another view; What's the workaround? Denis Vasconcelos DBA - Fábrica de Software Portais Brasil Tecnologia S.A. ( 0 XX 81 3465 - 3211 Ramal 37 * [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L Sent: Wednesday, August 28, 2002 12:33 PM Bill, You said One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. I got around this by replacing the v$session view with the following: Note the REPLACE statement in the select for the PROGRAM and MODULE columns: CREATE OR REPLACE VIEW V$SESSION ( SADDR, SID, SERIAL#, AUDSID, PADDR, USER#, USERNAME, COMMAND, OWNERID, TADDR, LOCKWAIT, STATUS, SERVER, SCHEMA#, SCHEMANAME, OSUSER, PROCESS, MACHINE, TERMINAL, PROGRAM, TYPE, SQL_ADDRESS, SQL_HASH_VALUE, PREV_SQL_ADDR, PREV_HASH_VALUE, MODULE, MODULE_HASH, ACTION, ACTION_HASH, CLIENT_INFO, FIXED_TABLE_SEQUENCE, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW#, LOGON_TIME, LAST_CALL_ET, PDML_ENABLED, FAILOVER_TYPE, FAILOVER_METHOD, FAILED_OVER, RESOURCE_CONSUMER_GROUP, PDML_STATUS, PDDL_STATUS, PQ_STATUS ) AS SELECT SADDR , SID , SERIAL# ,AUDSID ,PADDR ,USER# ,USERNAME ,COMMAND ,OWNERID ,TADDR ,LOCKWAIT ,STATUS ,SERVER ,SCHEMA# ,SCHEMANAME ,OSUSER ,PROCESS ,MACHINE ,TERMINAL ,REPLACE(UPPER(PROGRAM),'TOAD','FROG') PROGRAM ,TYPE ,SQL_ADDRESS ,SQL_HASH_VALUE ,PREV_SQL_ADDR ,PREV_HASH_VALUE ,REPLACE(UPPER(MODULE),'T·O·A·D·','F~R~O~G') MODULE ,MODULE_HASH ,ACTION ,ACTION_HASH ,CLIENT_INFO ,FIXED_TABLE_SEQUENCE ,ROW_WAIT_OBJ# ,ROW_WAIT_FILE# ,ROW_WAIT_BLOCK# ,ROW_WAIT_ROW# ,LOGON_TIME ,LAST_CALL_ET ,PDML_ENABLED ,FAILOVER_TYPE ,FAILOVER_METHOD ,FAILED_OVER ,RESOURCE_CONSUMER_GROUP ,PDML_STATUS ,PDDL_STATUS ,PQ_STATUS FROM sys.V_$SESSION -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denis Vasconcelos INET: [EMAIL PROTECTED] Fat
RE: RE: PL/SQL Editor
Nope ... actually Procedure builder is a deprecated (sp?) product. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, August 28, 2002 2:28 PM To: Multiple recipients of list ORACLE-L haven't looked at that in a while, but don't you have to install Developer to use that? (It's not available standalone, is it?) -Original Message- Sent: Wednesday, August 28, 2002 1:39 PM To: Multiple recipients of list ORACLE-L Well, since someone else cares to mention it, I do use Procedure Builder, Oracle's PL/SQL development tool from Developer and I like it a lot. The latest version even does syntax highlighting, indentation, and click on the error I'll show you where it is. Dick Goulet Reply Separator Author: Jamadagni; Rajendra [EMAIL PROTECTED] Date: 8/28/2002 8:52 AM Beware ... TORA is not free on Windows platform ... it is free however on Linux ... Funny no once has mentioned Oracle's built-in editor that comes with Forms ... My preferences are 1. Vi 2. Vim Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *2 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. *2
RE: PL/SQL Editor
can you please post URL's for these? thanks -Original Message- Sent: Wednesday, August 28, 2002 2:28 PM To: Multiple recipients of list ORACLE-L I'll second that -- I haven't used Benthic's PL/SQL editor very much, but I use Golden almost religiously as a replacement for SQL*Plus because it is small and fast and has a tabbed window interface with spreadsheet style output that works well for me. -- Philip Douglass Internet Networking Group Database Administrator SIRS Publishing, Inc. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 12:52 PM Benthicsoftware has a good series of editors. They are all about US$30 a piece or so. Not as powerfull as TOAD but if you are just doing PL/SQL Development they work great. From: Magaliff, Bill [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: PL/SQL Editor Date: Wed, 28 Aug 2002 06:18:24 -0800 we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). I no longer need to punish, deceive, or compromise myself, unless I want to stay employed. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED
RE: PL/SQL Editor
Well, I will throw our hat in. Compuware has a tool called DevPartner DB Tuner Debugger that can be used to create, optimize, tune and debug both SQL PL/SQL. Great tool (yes I do use it quite a bit) for PL/SQL profiling and debugging of code. www.compuware.com Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, August 28, 2002 12:52 PM To: Multiple recipients of list ORACLE-L Subject:RE: PL/SQL Editor Benthicsoftware has a good series of editors. They are all about US$30 a piece or so. Not as powerfull as TOAD but if you are just doing PL/SQL Development they work great. From: Magaliff, Bill [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: PL/SQL Editor Date: Wed, 28 Aug 2002 06:18:24 -0800 we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). I no longer need to punish, deceive, or compromise myself, unless I want to stay employed. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Wade INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: PL/SQL Editor
Hi, Mail me and I will send it you. Beest Regards Henrik --- Magaliff, Bill [EMAIL PROTECTED] wrote: can you please post URL for this program? thx bill -Original Message- Sent: Wednesday, August 28, 2002 9:53 AM To: Multiple recipients of list ORACLE-L I really like Emace with PL/SQL+SQLplus mode. It is KISS. :=) mvh HEnrik --- [EMAIL PROTECTED] wrote: Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . = __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Editor
Check www.benthicsoftware.com I've used Golden for a couple years now and love it for the same reasons as Philip... I also use PLEdit (their PL/SQL editor)... Similar to Golden in that it is simple and fast... Not as powerful as some of the others tools but not too bad either... Tim -Original Message- Sent: Wednesday, August 28, 2002 3:24 PM To: Multiple recipients of list ORACLE-L can you please post URL's for these? thanks -Original Message- Sent: Wednesday, August 28, 2002 2:28 PM To: Multiple recipients of list ORACLE-L I'll second that -- I haven't used Benthic's PL/SQL editor very much, but I use Golden almost religiously as a replacement for SQL*Plus because it is small and fast and has a tabbed window interface with spreadsheet style output that works well for me. -- Philip Douglass Internet Networking Group Database Administrator SIRS Publishing, Inc. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, August 28, 2002 12:52 PM Benthicsoftware has a good series of editors. They are all about US$30 a piece or so. Not as powerfull as TOAD but if you are just doing PL/SQL Development they work great. From: Magaliff, Bill [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: PL/SQL Editor Date: Wed, 28 Aug 2002 06:18:24 -0800 we're currently looking into this, too Our developers like TOAD, but the licensing cost is pretty steep - $700 a pop for the most pared down version. Quest was willing to offer us a one-time half-price offer, but for the 50 users we were considering it still comes out to over $15,000. PL/SQL Developer does basically everything TOAD does (that our developers do, at least) - explain plans, autotrace, stats, etc. - but with a price cap of $3,000 for unlimited user license it's very attractive. Their email support is pretty good, too - never wait more than a few hours for a response, usually by the same guy. All in all pretty good bang for the buck, and we'll probably end up purchasing it this week or next. I've also looked at Embarcadero's Rapid SQL, which I personally prefer over all the others, but the price is in the same league as TOAD. BMC has product that I haven't really looked into, but I think pricewise it's up there. One note about the Freeware version of TOAD - it has some internal thing that limits it to 5 concurrent connections to the same db, which is a problem for us in a dev shop. -bill -Original Message- Sent: Wednesday, August 28, 2002 8:18 AM To: Multiple recipients of list ORACLE-L PL/SQL Developer. http://www.allroundautomations.nl/plsqldev.html very very good. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]En nombre de Ofer Harel Enviado el: miércoles, 28 de agosto de 2002 9:13 Para: Multiple recipients of list ORACLE-L Asunto: RE: PL/SQL Editor Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Juan Miranda INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). I no longer need to punish, deceive, or compromise myself, unless I want to stay employed. _ Send and receive Hotmail on your mobile device: http://mobile.msn.com
RE: PL/SQL Editor
Try free Toad at http://www.toadsoft.com/downld.html regards Ofer Harel DBA team Barak ITC [EMAIL PROTECTED] -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, August 28, 2002 8:33 AM To: Multiple recipients of list ORACLE-L Hi All, which is the best PL/SQL editor available as a trial version . the editor should support Oracle sql,PL/SQL and may or may not support debugging. The tool should be support Win 9X OS and provide best performance . Freeware version.url Description: Binary data
RE: PL/Sql question
Dennis, I'd guess that the developer did not try it correctly. Ask to see the code. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 5:29 PM To: Multiple recipients of list ORACLE-L Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE /MM/DD NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB
RE: PL/Sql question
Tom - Thanks to you and everyone else for the great suggestions. He and I are sitting down tomorrow to straighten this out. I was concerned that there might be some PL/SQL oddity that I wasn't aware of (he is a pretty good PL/SQL programmer). I appreciate your ruling that out. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 22, 2002 7:53 AM To: Multiple recipients of list ORACLE-L Dennis, I'd guess that the developer did not try it correctly. Ask to see the code. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 5:29 PM To: Multiple recipients of list ORACLE-L Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159)
RE: PL/Sql question
Sounds like in the table the field c.marketcode is a char(3) instead of varchar2(3). Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). *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.*2
Re: PL/SQl question
Is the field in question in table C defined as CHAR or VARCHAR2? If CHAR that is why it is blank padded. Check datatype of variables in pl/sql Rick DENNIS WILLIAMS DWILLIAMS@life To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] touch.comcc: Sent by: Subject: PL/SQl question [EMAIL PROTECTED] m 08/21/2002 10:28 AM Please respond to ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like
RE: PL/SQl question
Dennis, In your PL/SQL program, did you try the RTRIM(date_field,' ') command? I know that TRIM is new, but I thought it needed additional parameters to tell it what to trim. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQl question
Check the definition of table C. It sounds like it is defined as CHAR(3) instead of VARCHAR2(3). I would also check the PL/SQL for using CHAR instead of VARCHAR2 for storing the value -- the trim should have eliminated this problem if it was put in the right place. Kevin Kennedy First Point Energy Corporation If you take RAC out of Oracle you get OLE! What can this mean? -Original Message- Sent: Wednesday, August 21, 2002 7:28 AM To: Multiple recipients of list ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kkennedy INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 question
I believe it is acting appropriately. You are trying to load a two-character byte filed into three-byte character field. Loader, if you don't terminate by whitespace or nulls, will add the blank into the field because it is character. Thus, you have two options: 1. Change the field to numeric. 2. Trim the data before it is loaded. Check the third position to see if it is a space or null; if so, only load n positions of data. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Wednesday, August 21, 2002 12:33 PM To: Multiple recipients of list ORACLE-L Subject:RE: PL/SQl question Dennis, In your PL/SQL program, did you try the RTRIM(date_field,' ') command? I know that TRIM is new, but I thought it needed additional parameters to tell it what to trim. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 10:28 AM To: Multiple recipients of list ORACLE-L I have a question for from one of my developers related to PL/SQL and how data is loaded. I have a field (marketcode) that is defined as VARCHAR2(3). I have a problem when I try to load the value of '20' into this field. All values with three characters work fine. The problem is when the value is less then 3 characters. When tables A and B have data loaded into this field using SQL/Loader the resulting value in the field appears to me as '20' with the third position =null. I have a separate PL/SQL process that loads this field into table C. When PL/SQL populates this same value into this field the field appears to me as '20' with the third position = space. I can't use SQL/Loader for this table as the data needs to be massaged before loading into Oracle. Thus when you try to link the tables together it does not find a match. select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=C.marketcode; (this returns 0 records) If I change the SQl statement to the following: select A.marketcode, C.marketcode from tableA A, tableC C where A.marketcode=trim(C.marketcode); (it correctly matches these up) Things I have tried to remedy this problem: 1) I have tried to modify my PL/SQL program to put a TRIM statement around the marketcode field when I populate table C. This did not work. 2) I have tried to check the 3rd position and if it is = space then I set the third position to null. But the field in Oracle is still a space when the program is finished. Does anyone have any thoughts on how I can properly output this field from Pl/SQl so it will match the data loaded via SQL/Loader? Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru
RE: PL/Sql question
Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE /MM/DD NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB POSITION(194:194) CHAR, CONNECTJOB POSITION(195:195) CHAR, STICKYALBUMJOB POSITION(196:196) CHAR, PAYSTATUS POSITION(197:197) CHAR, ORIGINALDATERECEIVED POSITION(198:207) DATE /MM/DD NULLIF ORIGINALDATERE, CMSNSTATUS POSITION(208:208) CHAR ) == All tables have the marketingcode field defined as varchar2(3) (none are char(3)) Bruce -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services--
RE: PL/Sql question
Tom - The developer reports that he tried this but it didn't work. The third position is still a space value. Thanks to everyone for the good replies. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 21, 2002 3:13 PM To: '[EMAIL PROTECTED]' Cc: DENNIS WILLIAMS Dennis, Try changing your insert statement to: insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, rtrim(MARKETINGCODE,' '), TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Tom Mercadante Oracle Certified Professional -Original Message- Sent: Wednesday, August 21, 2002 2:04 PM To: Multiple recipients of list ORACLE-L In response to the questions for more details, here are the PL/SQL code and SQL Loader control file. Everything is varchar2(2), explicitly defined as such in PL/SQL. Thanks for all the nice replies. PL/SQL snippets ...snip... marketingcodeVARCHAR2(3); ...snip... FILELOCATION := '/usr/users/madmload/text_files'; OPEN_MODE:= 'r'; FILENAME := 'prodload.txt'; FILENBR := UTL_FILE.FOPEN (FILELOCATION , FILENAME, OPEN_MODE ); ...snip... UTL_FILE.GET_LINE (FILENBR, OUTPUTSTRING); marketingcode := substr(outputstring, 21, 3); ...snip... insert into JOBOFFERFACT_LOAD (LIFETOUCHID, SOURCEFISCALYEAR, JOBNBR, PACKAGEID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PACKAGENAME, PACKAGEPRICE, PAIDPACKAGEQTY, UNPAIDPACKAGEQTY, SHIPPEDPACKAGEQTY, CMSNTYPE, PACKAGECMSNRATE, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PACKAGECODE, PACKAGECONFIG) VALUES (LIFETOUCHID, CURRENTFY, JOBNBR, PKGID, MARKETINGCODE, TERRITORYCODE, PLANTRECEIPTDATE, SEASON, PKGNAME, PACKAGEPRICE, PAIDPACKAGES, UNPAIDPACKAGES, SHIPPEDPACKAGES, CMSNTYPE, PACKAGECMSN, PACKAGETYPE, PACKAGECHARGEBACK, PACKAGEPOINTS, PKGCODE, PKGCONFIG ) ; Sql*Loader script LOAD DATA INFILE '/usr/users/madmload/joblid.txt' BADFILE '/usr/users/madmload/jobload.bad' APPEND INTO TABLE JOBFACT ( JOBNBR POSITION(1:10) CHAR, LIFETOUCHID POSITION(11:20) INTEGER EXTERNAL, MDRPRIMARYIDPOSITION(21:28) CHAR, MARKETINGCODE POSITION(29:31) CHAR, SUBPROGRAMCODE POSITION(32:32) CHAR, TERRITORYCODE POSITION(33:34) CHAR, SUBTERRITORYCODEPOSITION(33:36) CHAR, SELLINGMETHODCODE POSITION(37:37) CHAR, BIDIND POSITION(38:38) CHAR, PDKIND POSITION(39:39) CHAR, PDKPARTNBR POSITION(40:44) CHAR, RETAKEIND POSITION(45:45) CHAR, PLANTCODE POSITION(46:46) CHAR, PLANTRECEIPTDATEPOSITION(47:56) DATE /MM/DD NULLIF PLANTRECEIPTDA, PLANTRECEIPTYEARPOSITION(47:50) INTEGER EXTERNAL, PLANTRECEIPTMONTH POSITION(52:53) INTEGER EXTERNAL, PHOTOGRAPHYDATE POSITION(57:66) DATE /MM/DD NULLIF PHOTOGRAPHYDATE=BLANKS, SHIPDATEPOSITION(67:76) DATE /MM/DD NULLIF SHIPDATE=BLANKS, SHOTQTY POSITION(77:80) INTEGER EXTERNAL, SHIPPEDPACKAGEQTY POSITION(81:84) INTEGER EXTERNAL, PAIDPACKAGEQTY POSITION(85:88) INTEGER EXTERNAL, UNPAIDPACKAGEQTYPOSITION(89:92) INTEGER EXTERNAL, XNOPURCHASEQTY POSITION(93:96) INTEGER EXTERNAL, CASHRECEIVEDAMT POSITION(97:105)DECIMAL EXTERNAL, CASHRETAINEDAMT POSITION(106:114) DECIMAL EXTERNAL, ACCTCMSNPAIDAMT POSITION(115:123) DECIMAL EXTERNAL, ESTACCTCMSNAMT POSITION(124:132) DECIMAL EXTERNAL, CHARGEBACKAMT POSITION(133:141) DECIMAL EXTERNAL, SALESTAXAMT POSITION(142:150) DECIMAL EXTERNAL, TERRITORYCMSNAMTPOSITION(151:159) DECIMAL EXTERNAL, TERRITORYEARNINGSAMTPOSITION(160:168) DECIMAL EXTERNAL, EXPECTEDCASHAMT POSITION(169:177) DECIMAL EXTERNAL, SOURCEFISCALYEARCONSTANT '2003', PROOFPOSE POSITION(178:178) DECIMAL EXTERNAL, PROOFCOUNT POSITION(179:182)DECIMAL EXTERNAL, SEASONDESC POSITION(183:183)DECIMAL EXTERNAL, EXTRACTDATE POSITION(184:193) DATE /MM/DD NULLIF EXTRACTDATE=BLANKS, FUNPACKJOB POSITION(194:194) CHAR, CONNECTJOB POSITION(195:195) CHAR, STICKYALBUMJOB POSITION(196:196) CHAR, PAYSTATUS POSITION(197:197) CHAR, ORIGINALDATERECEIVED POSITION(198:207) DATE /MM/DD NULLIF ORIGINALDATERE, CMSNSTATUS
RE: pl/sql exception and whenever sqlerror
Title: RE: pl/sql exception and whenever sqlerror
(see answer below - What a difference, a raise makes!)
-Original Message-
From: Baker, Barbara [mailto:[EMAIL PROTECTED]]
I have a command procedure running a sql*plus script that
then runs a stored
procedure. (This is VMS, but I think it would work the same in unix.
maybe...) I have a whenever sqlerror exit failure rollback
in sql*plus.
This works great, and my command procedure can check the status and
determine whether the job ran successfully.
However, if the developer places an exception when others
code in the
procedure and an error occurs, the status back to the calling job is
SUCCESS. The exception does indeed catch the error. (It
will actually spit
it out if the developer remembers to set serverout on.) But
I really need
the calling procedure to know that there was an error.
Is there a call to raise or raise_application_error in the when others section of the exception clause?
e.g.
SQL set serveroutput on
SQL -- no raise - no error returned to calling program
SQL begin
2 execute immediate 'delete from non_existing_table' ;
3 commit ;
4 exception
5 when others then
6 rollback ;
7 dbms_output.put_line ('Error somewhere in my procedure') ;
8 end ;
9 /
Error somewhere in my procedure
Procédure PL/SQL terminée avec succès.
SQL -- with raise - error returned to calling program
SQL begin
2 execute immediate 'delete from non_existing_table' ;
3 commit ;
4 exception
5 when others then
6 rollback ;
7 dbms_output.put_line ('Error somewhere in my procedure') ;
8 -- you need a raise to have SQL*Plus realize an error occurred
9 raise ;
10 end ;
11 /
Error somewhere in my procedure
begin
*
ERREUR à la ligne 1 :
ORA-00942: Table ou vue inexistante
ORA-06512: à ligne 9
RE: PL/SQL Editor.
-Original Message- Sent: Thursday, August 15, 2002 11:09 AM I need a nice PL/SQL editor any advice ? I prefer vi myself but your probably looking for something like http://www.indus-soft.com/winsql/ -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farrell, Thomas M.Mr. NGB-ARNG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor.
Free OraEdit http://www.dkgadvancedsolutions.com/oraedit.htm JP On Thursday 15 August 2002 17:09, you wrote: Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ - Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor.
vi or vim. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Place for oracle [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 10:09 AM To: Multiple recipients of list ORACLE-L Subject: PL/SQL Editor. Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Editor.
I like RapidSQL from Embarcadero - but it's a bit pricy for a pretty good and less expensive alternative try PL/SQL Developer from allroundautomations -bill -Original Message- Sent: Thursday, August 15, 2002 11:09 AM To: Multiple recipients of list ORACLE-L Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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 Editor.
Title: RE: PL/SQL Editor. Hi, I use Emacs with PL/SQL module and Sqlplus.el Really nice and easy to use. Best Regards Henrik -Original Message- From: Place for oracle To: Multiple recipients of list ORACLE-L Sent: 8/15/2002 5:09 PM Subject: PL/SQL Editor. Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Editor.
TOAD is a very good one with a lot of useful options like commands configuration: you type: crbl ctrlspace you get: DECLARE CURSOR c1 IS SELECT FROM WHERE; c1rec c1%ROWTYPE; BEGIN OPEN c1; LOOP FETCH c1 INTO c1rec; EXIT WHEN c1%NOTFOUND; END LOOP; CLOSE c1; END; also you can use sqlnavigator and sql-programmer, there are a lot of very good tools.. Gabriel --- [EMAIL PROTECTED] wrote: vi or vim. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Place for oracle [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 10:09 AM To: Multiple recipients of list ORACLE-L Subject:PL/SQL Editor. Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). = Any dream worth having is a dream worth fighting for(Cualquier sueño que valga la pena tener, es un sueño por el que vale la pena luchar)Charles Xavier __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gabriel Aragon INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PL/SQL Editor.
You should see me trying to type a word document. vi command syntax doesn't go over to well... Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 11:43 AM To: [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L Subject: Re:RE: PL/SQL Editor. Scott, vi, your dinosaur side is showing!! *-) Dick Goulet Reply Separator Subject:RE: PL/SQL Editor. Author: [EMAIL PROTECTED] Date: 8/15/2002 8:38 AM vi or vim. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Place for oracle [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 10:09 AM To: Multiple recipients of list ORACLE-L Subject: PL/SQL Editor. Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: PL/SQL Editor.
-Original Message- Scott, vi, your dinosaur side is showing!! *-) Oh come on. He included vim. ; Cheers, Thom -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Farrell, Thomas M.Mr. NGB-ARNG INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (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: PL/SQL Editor.
:q! ...Doh! -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 11:49 AM To: Multiple recipients of list ORACLE-L You should see me trying to type a word document. vi command syntax doesn't go over to well... Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 11:43 AM To: [EMAIL PROTECTED]; Multiple recipients of list ORACLE-L Subject: Re:RE: PL/SQL Editor. Scott, vi, your dinosaur side is showing!! *-) Dick Goulet Reply Separator Subject:RE: PL/SQL Editor. Author: [EMAIL PROTECTED] Date: 8/15/2002 8:38 AM vi or vim. Scott Shafer San Antonio, TX 210-581-6217 -Original Message- From: Place for oracle [SMTP:[EMAIL PROTECTED]] Sent: Thursday, August 15, 2002 10:09 AM To: Multiple recipients of list ORACLE-L Subject: PL/SQL Editor. Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- 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: Orr, Steve INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: PL/SQL Editor.
DevPartner DB Tuner, Debugger Profiler for PL/SQL from Compuware. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, August 15, 2002 12:09 PM To: Multiple recipients of list ORACLE-L Subject:RE: PL/SQL Editor. I like RapidSQL from Embarcadero - but it's a bit pricy for a pretty good and less expensive alternative try PL/SQL Developer from allroundautomations -bill -Original Message- Sent: Thursday, August 15, 2002 11:09 AM To: Multiple recipients of list ORACLE-L Hello, I need a nice PL/SQL editor any advice ? Thx for any advice Regards W.B __ Do You Yahoo!? HotJobs - Search Thousands of New Jobs http://www.hotjobs.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Place for oracle INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Magaliff, Bill INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
