Re: INSERTing string containing character '&'

From: Arlette BROSSARD <abray_at_club-internet.fr>
Date: Mon, 17 May 1999 03:29:10 +0200
Message-ID: <7hnrdu$of5$1@front1.grolier.fr>

'&' is the default character for DEFINE (to use parameters in SQL+) You can change it for a while in SQL+, so you can use another character that you're sure is not
in your file (µ for example).
SET DEFINE µ
INSERT INTO names VALUES ( '00001', 'Bartles&James' ); SET DEFINE & (if necessary)

If the names are in a file, you can read this file in a PL/SQL block and insert all the names in a table
with no trouble. For that you can use the build-in package UTL_FILE.

An example for Windows :
DECLARE

sname        VARCHAR2(45);
file         UTL_FILE.FILE_TYPE;

BEGIN
   file := UTL_FILE.FOPEN ('C:','NAMES.TXT','R');    UTL_FILE.GET_LINE(file,sname);
   LOOP

      INSERT INTO names VALUES (SUBSTR(sname,1,5),SUBSTR(sname,6,40));
      UTL_FILE.GET_LINE(file,sname);

   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND THEN

      UTL_FILE.FCLOSE(file);
      COMMIT;
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      UTL_FILE.FCLOSE_ALL;

END; With this 3 records in C:\NAMES.TXT
00001TRUC&MUCHE
00002MACHIN&BIDULE
00003TOTO&TITI SQL> select * from names;

MFGCO MFGNAME

----- ----------------------------------------

00001 TRUC&MUCHE
00002 MACHIN&BIDULE
00003 TOTO&TITI 3 ligne(s) sélectionnée(s).

Don't forget UTL_FILE_DIR in the INIT.ORA file For example : UTL_FILE_DIR = * (all directories)

abray_at_club-internet.fr

Howard Lee Harkness a écrit dans le message <7hkq9f$kfv$1_at_nnrp1.deja.com>...
>I have a table defined as:
>CREATE TABLE NAMES
>(
>MFGCODE VARCHAR2(5),
>MFGNAME VARCHAR2(40)
>);
>
>I tried to do an insert similar to the following:
>
>INSERT INTO NAMES
>( '00001', 'Bartles&James' );
>
>and SQL*Plus responded with:
>
>Enter value for James:
>
>Which is a good indicator that the '&' is some sort of special
>character, presumably used for passing parameters interactively. So
>how do I defeat the special nature of this character? Are there other
>characters I need to defeat? I have about 7 Mb of names in a file to
>import, many of which contain '&' in the name.
>
>--
>Replies send to this email address are automatically discarded. To
>reply, please post in this newsgroup, or send to harkness at skeptics
>dot org. Thank-you.
>
>
>--== Sent via Deja.com http://www.deja.com/ ==--
>---Share what you know. Learn what you don't.---
Received on Sun May 16 1999 - 20:29:10 CDT