Re: PL/SQL cursor status without fetching???

Tom

The reply was never intended to advocate writing code the way it was written. Just looking at the code suggest a slew of issues ("or we have another java
program going down the tubes real fast "), most of which have been dealt with on the replies. I just added EE as a measure to highlight the fact that although Oracle provides a solution, the solution may not be as cheap as they expected. I just hope for Giovanni's sake they are using EE.

You've posted a couple of excellent tips;

fbi - which is in EE

shadow fields maintained by a trigger - choice in standard, and EE

I've also seen a third scenario where people just decided to store everything in a fixed case (lower or upper) and then search with the variable (search parameter) moved to either lower or upper case.

As a side note, your input has been higly valued and respected.

"Thomas Kyte" <tkyte_at_oracle.com> wrote in message news:ald6lm01vq8_at_drn.newsguy.com...
> In article <xGde9.12119$YR2.1855486_at_news20.bellglobal.com>, "M says...
> >
> >Correct me if I'm wrong. Isn't the Function based index a feature of EE.
> >That also begs another question, can you afford it?
> >
>
>
> If you are going to write code they way they were -- can you afford not
 to?
>
> Yes, fbi's are a feature of EE. If they are constantly going to search a
 large
> table with a query like:
>
> select *
> from t
> where upper(trim(c1)) = upper(trim(bind_variable1))
> and upper(trim(c2)) = upper(trim(bind_variable2))
> and upper(trim(c3)) = upper(trim(bind_variable3))
>
> they will either need function based indexes (as the table grows....) or
 they
> will need shadow fields maintained by a trigger:
>
> alter table t add c1_shadow ...;
> (and c2/c3_shadow)
>
>
> create trigger t_trigger before insert or update on t for each row
> begin
> :new.c1_shadow := upper(trim(:new.c1));
> like wise for c2, c3
> end;
>
> so they can code:
>
> where c1_shadow = upper(trim(bind_variable1))
> ........
>
> and having an index on c1_shadow,c2_shadow,c3_shadow would solve the issue
 as
> well.
>
>
>
> >"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
> >news:albho502e0m_at_drn.newsguy.com...
> >> In article <3D7947F1.F254D04D_at_oracle.com>, Martin says...
> >> >
> >> >This is a multi-part message in MIME format.
> >> >--------------139C138F58951CA6FE7B6C06
> >> >Content-Type: text/plain; charset=UTF-8
> >> >Content-Transfer-Encoding: 7bit
> >> >
> >> >Thomas Kyte wrote:
> >> >
> >> >> In article <alagg9$1oqfm3$1_at_ID-114658.news.dfncis.de>, "Giovanni
 says...
> >> >> [snip]
> >> >>I sure hope you know ALL ABOUT function based indexes, or we have
 another java
> >> >>program going down the tubes real fast (user VARCHAR2, skip the trim,
 whats up
> >> >> with upper(zip_code)?).....
> >> >> [snip]
> >> >
> >> >Upper(zip_code) would only make sense if there may be addresses
 outside
 USA e.g.
> >> >Canada, UK which have alphanumeric post codes. Which makes me wonder
 why
 the
> >> >WHERE
> >> >clause makes no reference to country code
> >>
> >> You know, I'm always forgetting about those other places... (only
 kidding)..
> >>
> >> btw, if they do this upper(trim()) thing, read about FUNCTION BASED
 INDEXES
> >> (please)
> >>
> >>
> >>
> >> >
> >> >--------------139C138F58951CA6FE7B6C06
> >> >Content-Type: text/x-vcard; charset=UTF-8;
> >> > name="martin.doherty.vcf"
> >> >Content-Transfer-Encoding: 7bit
> >> >Content-Description: Card for Martin Doherty
> >> >Content-Disposition: attachment;
> >> > filename="martin.doherty.vcf"
> >> >
> >> >begin:vcard
> >> >n:Doherty;Martin
> >> >tel;work:+1 650 506-8398
> >> >x-mozilla-html:FALSE
> >> >url:http://industries.oraclecorp.com
> >> >org:Oracle Corporation;Industry Solutions
> >> >version:2.1
> >> >email;internet:martin.doherty_at_oracle.com
> >> >title:Senior Software Specialist
> >> >adr;quoted-printable:;;Mail Stop C3038=0D=0A10 Twin Dolphin
 Dr=0D=0A;Redwood
> >> >Shores;CA;94065;USA
> >> >fn:Martin Doherty
> >> >end:vcard
> >> >
> >> >--------------139C138F58951CA6FE7B6C06--
> >> >
> >>
> >> --
> >> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> >> Expert one on one Oracle, programming techniques and solutions for
 Oracle.
> >> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> >> Opinions are mine and do not necessarily reflect those of Oracle Corp
> >>
> >
> >
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
Received on Sun Sep 08 2002 - 17:23:09 CDT