DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: & Character

  1. #1
    Join Date
    Sep 2000
    Posts
    155

    Is there any way of inserting data into a table without using
    SET ESCAPE \ and without placing \ before "&" character ?

    The problem is; I am doing inserts into a table from a PERL program. There will be lots of data containing "&" characters.
    How do I handle this?

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    write a perl function to replace all & with \&.

    Jeff Hunter

  3. #3
    Join Date
    Sep 2000
    Posts
    155

    Thanks for your response!

    But, Do we still have to use SET ESCAPE ON apart from writing a PERL function????

    Is there any other method other than this???

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    use "set define off"

    <font face="courier">
    SQL> create table test (col1 varchar2(30));

    Table created.

    SQL> insert into test values ('&testing');
    Enter value for testing:
    old 1: insert into test values ('&testing')
    new 1: insert into test values ('')

    1 row created.

    SQL> set define off
    SQL> insert into test values ('&testing');

    1 row created.

    SQL> select * from test;

    COL1
    ------------------------------

    &testing

    SQL>
    </font>

  5. #5
    Join Date
    Jan 2001
    Posts
    318
    Is there any way to do this in PL/Sql ??
    eg:
    CREATE OR REPLACE PROCEDURE pr_test_sign
    (inRESID number, inLocation varchar2)
    IS

    Begin
    set define off;
    insert into a4branchtemp2 (TEMP_WORK_ID, TEMP_WORK_ITEM_CODE)
    values(inRESID, inLocation);

    set define on;
    COMMIT;
    End pr_test_sign;
    /

    I know I cannot have DDL inside PLSQL block.. this is just an eg of what I want to do.
    execute pr_test_sign(100, 'test@test.com');

    What I want to do is insert the @ sign from the procedure into a table.

    Note: Our application will use ODBC and I have their latest version for Oracle8.
    Sonali

  6. #6
    Join Date
    Jun 2000
    Posts
    417
    I don't think you have to worry about the @ sign. In any case the "set var value" commands are only for sqlplus, not for SQL or PL/SQL.

    If you're using the execute command, you're probably doing it from sqlplus, in which case if you had to worry about it like you did an &, you could just type set define off before you type execute, or put it in the script ahead of the execute commands.

    however the @ shouldn't cause any problems.

    this is without changing any sqlplus variables
    <font face="courier">
    SQL> insert into test values ('whatever@somewhere.org');

    1 row created.

    SQL> select * from test;

    COL1
    ------------------------------

    &testing
    whatever@somewhere.org

    SQL>
    </font>

  7. #7
    Join Date
    Oct 2000
    Posts
    123
    I don't know if this will help:

    execute pr_test_sign (100 , 'test'||'&'||'test.com');




  8. #8
    Join Date
    Jan 2001
    Posts
    318
    I should have been more clear on this ..
    Yes, it does work with worksheet or Sql* plus.
    But not through ODBC for some reason.
    If you use ODBC 32bit test, Oracle 8 and ODBC driver 8.00.57 or any other may be even 8.00.62.
    It doesn't work.
    Sonali

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width