Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    56

    Unanswered: Trouble inserting Certificate value

    I have a column where the value represents a certificate. I think its encrypted. The problem is there is an &s in the certificate value and Oracle is interrupting it as a prompt for a string value. How do I insert the &s as just part of the string? And since the character sting can contain any values, are there any other character values that need to be reformatted for the insert to work?

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    There are several ways to do that. One is to use SET DEFINE OFF:
    Code:
    SQL> create table burke (col varchar2(20));
    
    Table created.
    
    SQL> set define off
    SQL> insert into burke values ('This is &certificate');
    
    1 row created.
    
    SQL> select * From burke;
    
    COL
    --------------------
    This is &certificate
    
    SQL>
    Another one is to use ESCAPE character (backslash):
    Code:
    SQL> set escape on
    SQL> insert into burke values ('This is \&certificate');
    
    1 row created.
    
    SQL> select * from burke;
    
    COL
    --------------------
    This is &certificate
    
    SQL>
    And another one (which you probably won't use) is to concatenate & with the rest of the string:
    Code:
    SQL> insert into burke values ('This is ' || chr(38) || 'certificate');
    
    1 row created.
    
    SQL> select * From burke;
    
    COL
    --------------------
    This is &certificate
    
    SQL>
    Last edited by Littlefoot; 08-06-07 at 13:26.

  3. #3
    Join Date
    Aug 2006
    Posts
    56
    Thanks, that will work

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I hope so.

    By the way, what do (most of) your questions have to do with being a DBA? Rarely something, mostly nothing. So perhaps you should remove that silly signature.

Posting Permissions

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