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

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