Re: pl/sql open cursor question

[Carel-Jan Engel]

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

[Jonathan Lewis]

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

[Jared . Still]

> 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

[Jonathan Lewis]

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

[Carel-Jan Engel]

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

Re: pl/sql open cursor question

[Jared Still]

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 ;  /* let the caller know that no data is 
> found */
>when others
>  then return 'ERROR: '||TO_CHAR(SQLCODE); /* return the error-code, 
> p

Re: pl/sql open cursor question

[Tanel Poder]

> 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

[Tanel Poder]

> > 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

[Carel-Jan Engel]

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 ;  /* 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

[Guang Mei]

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*/ 
>
> 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] 
>
>

-- 
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

[Ryan]

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 
  Miracle A/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

[Peter Gram]

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

[Ryan]

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 question and owa_pattern question

[Daniel Hanks]

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 manupil

Re: pl/sql question and owa_pattern question

[Mladen Gogala]

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 ht

RE: pl/sql question and owa_pattern question

[Jamadagni, Rajendra]

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 :

RE: pl/sql question and owa_pattern question

[Guang Mei]

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
-

RE: pl/sql question and owa_pattern question

[Guang Mei]

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

RE: pl/sql question and owa_pattern question

[Stephane Faroult]

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

[Mladen Gogala]

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

[Jonathan Gennick]

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-statement

[GKatteri]

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

[Thater, William]

[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

[Daniel Fink]

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 - can't accept user input - then how?

[Saira Somani]

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 - can't accept user input - then how?

[Jared . Still]

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 <
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?

[Stephen . Lee]

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 Question:Eliminate duplicate rows

[Johan Muller]

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]

RE: PL/SQL Question:Eliminate duplicate rows

[Mercadante, Thomas F]

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

RE: PL/SQL Question:Eliminate duplicate rows

[Ron Thomas]

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

RE: PL/SQL Question:Eliminate duplicate rows

[Igor Neyman]

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

[Mercadante, Thomas F]

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 yoursel

RE: PL/SQL Question:Eliminate duplicate rows

[Ron Thomas]

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 'ListGu

RE: PL/SQL Question:Eliminate duplicate rows

[Mladen Gogala]

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

[Mercadante, Thomas F]

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 statement help

[Shamita Singh]

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 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#    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 statement help

[Joe Testa]

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

[Nelson, Allan]

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 
  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#    
  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- cursors and commits

[Ganesh Raja]

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- cursors and commits

[Ashish]

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

[Reginald . W . Bailey]

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 engine doubt

[Bjørn Engsig]

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).

/Bjørn.

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
  


-- 
 Bjørn Engsig, Miracle A/S 
 Member of Oak Table Network 
 [EMAIL PROTECTED] - http://MiracleAS.dk 
 

RE: pl/sql engine doubt

[Gogala, Mladen]

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 and java script ???

[Darrell Landrum]

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

[DENNIS WILLIAMS]

Santosh - I went to 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

[Darrell Landrum]

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

[Jan Pruner]

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/...).

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

[Thomas, Kevin]

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 output on Client machine

[Jamadagni, Rajendra]

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

[Jared . Still]

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

[Vladimir Begun]

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

[Jared . Still]

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')||

RE: PL/SQL Date Format

[Mercadante, Thomas F]

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

[Jeremy Pulcifer]

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 Date Format

[Jared . Still]

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 package

[BigP]

Hi Hoyt ,
Let me know exactly what you need . I can give some ideas . May not be the
code , cuz  my company closed recently and I am looking out for another job
. :)Let me know you if you have something in mind . Is it really hard to
find job for senior oracle analyst /developer .


Thanks ,
Bp

- Original Message -
To: <[EMAIL PROTECTED]>
Sent: Thursday, October 31, 2002 8:50 AM


> Hi, BigP,
>
> I googled across a posting of yours about your PL/SQL error-handling
package. I'm looking for one, and yours sounds great!
>
> Is it the kind of thing you can share? I'd rather not re-invent.
>
> Thanks, Hoyt
> --
> __
> Sign-up for your own FREE Personalized E-mail at Mail.com
> http://www.mail.com/?sr=signup
>
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: BigP
  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

[Paula_Stankus]

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 confirm

RE: PL/SQL help

[Ron Thomas]

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 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-- Ma

RE: PL/SQL help

[Lord, David - CSG]

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

[Yechiel Adar]

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

[Jacques Kilchoer]

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

[Joe Testa]

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

[Johnston, Tim]

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

RE: PL/SQL Editor

[Place for oracle]

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

[Karniotis, Stephen]

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.


__

RE: PL/SQL Editor

[Magaliff, Bill]

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, o

RE: RE: PL/SQL Editor

[Jamadagni, Rajendra]

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

[Denis Vasconcelos]

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 

RE: RE: PL/SQL Editor

[Magaliff, Bill]

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

[Philip Douglass]

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 

Re: PL/SQL Editor

[Jan Pruner]

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

[Jay Wade]

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

[Jamadagni, Rajendra]

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

[Magaliff, Bill]

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

[Mercadante, Thomas F]

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).

RE: PL/SQL Editor

[Magaliff, Bill]

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

[Ramon E. Estevez]

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

[Jan Pruner]

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

[Magaliff, Bill]

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

[Place for oracle]

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

[Juan Miranda]

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

[Ofer Harel]

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 WILLIAMS]

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(1

RE: PL/Sql question

[Mercadante, Thomas F]

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,
FU

RE: PL/Sql question

[DENNIS WILLIAMS]

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,
CM

RE: PL/Sql question

[Mercadante, Thomas F]

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 S

RE: PL/SQl question

[Karniotis, Stephen]

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-50

RE: PL/SQl question

[kkennedy]

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

[Mercadante, Thomas F]

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

[Rick_Cale]

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
 
   
touch.com>cc:  
 
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 subscribing).

RE: PL/Sql question

[Jamadagni, Rajendra]

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 exception and whenever sqlerror

[Jacques Kilchoer]

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: RE: PL/SQL Editor.

[Robertson Lee - lerobe]

LOL Scott !!!

The amount of documents I have with ^F characters and 1G's etc 

Lee

-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: 15 August 2002 18:49
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).




The information contained in this communication is
confidential, is intended only for the use of the recipient
named above, and may be legally privileged.
If the reader of this message is not the intended
recipient, you are hereby notified that any dissemination, 
distribution, or copying of this communication is strictly
prohibited.
If you have received this communication in error,
please re-send this communication to the sender and
delete the original message or any copy of it from your
computer system. Thank You.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robertson Lee - lerobe
  INET: [EMAIL PROTECTED]

Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
San Diego, California-- Public Internet access / Mailing Lists

To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(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.

[Scott . Shafer]

Hehehe, this'll really get you - I take 1 part command-line SCCS for
versioning to 2 parts vi for editing.  Serve on the rocks.

Scott Shafer
San Antonio, TX
210-581-6217


> -Original Message-
> From: Ron Rogers [SMTP:[EMAIL PROTECTED]]
> Sent: Thursday, August 15, 2002 1:51 PM
> To:   Multiple recipients of list ORACLE-L
> Subject:  Re:RE: PL/SQL Editor.
> 
> Scott,
>  Dinosaur? When you have a good product it stays around for lng
> time.
> The complaints that I have with TOAD is that the pl/sql packages when
> converted to text have a lot of white space due to the formatting.
> Designer is not as bad but still has white space. I like to have the
> developers give me a text copy of the pl/sql package so I can keep it on
> the "source" disk incase there is a disaster and I have to start from
> scratch and also I use it for documentation purposes. So far the
> auditors have been happy when I show them the disk location for all of
> the creation scripts used on the server.
> Ron
> ROR mª¿ªm
> 
> >>> [EMAIL PROTECTED] 08/15/02 01:48PM >>>
> Scott,
> 
> vi, your dinosaur side is showing!! *-)
> 
> Dick Goulet
> 
> Reply Separator
> 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: Ron Rogers
>   INET: [EMAIL PROTECTED]
> 
> Fat City Network Services-- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California-- Public Internet access / Mailing Lists
> 
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be 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 

RE: PL/SQL Editor.

[Karniotis, Stephen]

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).

RE: RE: PL/SQL Editor.

[Orr, Steve]

: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: RE: PL/SQL Editor.

[Farrell, Thomas M.Mr. NGB-ARNG]

-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.

[Scott . Shafer]

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: PL/SQL Editor.

[Gabriel Aragon]

TOAD is a very good one with a lot of useful options
like commands configuration:

you type: crbl 
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: PL/SQL Editor.

[Ekenberg, Henrik]

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).