RE: Passing the "&" in PL/SQL

From: David Barbour <DBarbour_at_connectsouth.com>
Date: Tue, 25 Jul 2000 18:37:41 -0500
Message-Id: <10569.112952@fatcity.com>

Thanks to all who have replied. I've tried all your suggestions, and added a number of my own variations ..... all to no avail.

Her's the scenario - A user types in a few letters of a company name and is returned a list of those firms that match the input. The list hypertext links to the next page, passing the business name and other information as input variables. This list comes from a table where numerous firms are identified using the '&' symbol. I have no way of knowing what firm the user will ultimately select from the list they generate, so I don't know if there will be a & in the name. I've attempted to compensate for this by redefining the variable v_bus_name as

	IF INSTR(v_bus_name,'&') > 0 THEN
                v_bus_name:= substr(v_bus_name, 1, instr(v_bus_name,'&') -1)
                             ||'\&'||
                             substr(v_bus_name, instr(v_bus_name,'&')+1);
      

This works really well until the user clicks on the link to get to the next page. My "test case" - A-1 Appliance & Electronics, gets passed as (the parameters passing should be Customer Number:,Bus_name:,Bus_City,Bus_State:                 

        PARAMETERS:

---

  CUSTOMER_NUMBER:
   05005043419999
  BUS_NAME:
   A-1 Appliance
   ELECTRONICS&BUS_CITY:       BUS_STATE: I've tried using an ASCII conversion chr(38), Set Define off; Set Escape \&; Set Scan off; just can't get it to pass properly. Any more ideas?

David A. Barbour
Oracle DBA - ConnectSouth
512-681-9438
dbarbour_at_connectsouth.com

-----Original Message-----
From: Patrice Michel [mailto:Patrice.Michel_at_cetelem.fr] Sent: Monday, July 24, 2000 3:25 AM
To: Multiple recipients of list ORACLE-L Subject: Re: Passing the "&" in PL/SQL

Try : set def ^
(for example) in the beginning of your package.

David Barbour wrote:
>
> Hello good people (a little flattery never hurts!) -
>
> Has anybody found a way to pass a variable in a stored procedure that
> contains a " & " symbol without having to parse the data selected, search
> for the & , and actually build your variable?
>
> I'm selecting business names into a variable. If the name is something
like
> Father & Sons, I bomb out on calling the next screen. I've tried all
kinds
> of combinations of single quotes and double quotes along with some
> suggestions of using the ! and @ signs. The suggestions get weirder as
the
> day grows older. The day isn't the only thing growing older either.
>
> Any help or suggestions would be appreciated.
>
> David A. Barbour
> Oracle DBA - ConnectSouth
> 512-681-9438
> dbarbour_at_connectsouth.com
> --
> Author: David Barbour
> INET: DBarbour_at_connectsouth.com
>
> 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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include 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).

-- 
Author: Patrice Michel
  INET: Patrice.Michel_at_cetelem.fr

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: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Tue Jul 25 2000 - 18:37:41 CDT