| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Help Me With a Simple PL/SQL Request Please !!
In article <tce21ejl9p43bd_at_beta-news.demon.nl>, Sybrand Bakker
<postbus_at_sybrandb.demon.nl> writes:
>This is a TFAQ (Too Frequently Asked Question).
>Please read up on REF CURSOR in your pl/sql documentation, or search google
>for the numerous replies to the same issue.
>
>Regards,
>
>Sybrand Bakker, Oracle DBA
>
>
>"Charlotte Hurley" <firas3_16_at_yahoo.com> wrote in message
>news:2fsdct02ofko6t45ehbrtuu1o0s2ollh6c_at_4ax.com...
>> Hello Everyone,
>>
>> Has anyone developed a PL/SQL procedure/function used to
>> return a search result from a table ?. Consider the following table :
>>
>> Table : MEMBER_CONTACT
>>
>> Fields: CONTACT_ID
>> ORGANIZATION_ID
>> FIRST_NAME
>> LAST_NAME
>> TITLE
>> EMAIL_ADDRESS
>> CITY
>> STATE
>> COUNTRY
>>
>>
>> I would like to make a procedure/function that would take in
>> the last_name as an input parameter, and return all the users in this
>> table matching the surname. Does anyone know what is the most
>> efficient way to do this ? I can only return one record as a result so
>> far, but does anyone know how to make the procedure return multiple
>> records for possible matches based on the surname ? (in an efficient
>> way). Any help would be greatly appreciated, thank you in advance.
>>
>>
>> Kind Regards,
>> Charlotte Hurley,
>> firas3_16_at_yahoo.com
>
Sybrand is right in that the reference cursor is the best way to do this;
however, if your front-end product does not support the use of reference
cursors you can look into passing multiple rows back using a plsql table
(single dimension array, not a database table). If forced to use this method
you will probably need to use a seperate array for each column passed back.
See the plsql manual for examples of using plsql tables. Your front-end tool
documentation should have examples of retruning sets of data.
I posted a reference cursor example, taken from the Oracle readme, within the last week that you should be able to find.
 |
 |