Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2009
    Posts
    16

    Unanswered: REGEXP_LIKE invalid relational operator

    Hey i just downloaded Oracle SQL Developer 1.5.5

    CVS Version Internal to Oracle SQL Developer (client-only)
    Java(TM) Platform 1.5.0_06
    Oracle IDE 1.5.5.59.69
    Versioning Support 1.5.5.59.69

    The problem is i can't seem to get REGEXP_LIKE to work, does
    anyone know how i can fix this problem? I'm pretty sure Oracle
    recognises it because it gives it a blue colour as it does with special
    keywords as SELECT, FROM, WHERE etc. but it keeps saying it's an
    "invalid relational operator". Is it the way i'm using it? if so then
    how does oracle want me to use this so that i can do a check that
    the correct format for a Pet_ID is entered?


    Code:
    CREATE TABLE Pet
    (
    PET_ID char(8) NOT NULL,
    Name char(50) NOT NULL,
    PRIMARY KEY(PET_ID),
    CONSTRAINT PET_ID_FORMAT CHECK(PET_ID REGEXP_LIKE   
    '[A-Z][{4}[1,2,3,4,6,7][0-9]{3}')
    )
    Error at Command Line:6 Column:38
    Error report:
    SQL Error: ORA-00920: invalid relational operator
    00920. 00000 - "invalid relational operator"


    Thanx heaps,
    regards,
    noob

  2. #2
    Join Date
    May 2006
    Posts
    132
    Not sure what requirement the regex is trying to accomplish, but I really don't think it is correct.

    Could have just been a typo? Should it have been '[A-Z]{4}[123467][0-9]{3}' ?

    Code:
    SQL> CREATE TABLE Pet
      2  (
      3  PET_ID char(8) NOT NULL,
      4  Name char(50) NOT NULL,
      5  PRIMARY KEY(PET_ID),
      6  CONSTRAINT PET_ID_FORMAT CHECK(REGEXP_LIKE
      7  (pet_id,'[A-Z]{4}[123467][0-9]{3}'))
      8  );
    
    Table created.
    
    SQL> insert into pet values ('ABCD2123', 'TEST');
    
    1 row created.
    
    SQL> insert into pet values ('ABCD8123', 'TEST');
    insert into pet values ('ABCD8123', 'TEST')
    *
    ERROR at line 1:
    ORA-02290: check constraint (TEST.PET_ID_FORMAT) violated

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    what version is the database. Run the following select and post the result.

    select * from v$version;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    May 2006
    Posts
    132
    Version 10.2.0.3

    Code:
    SQL> CREATE TABLE Pet
      2  (
      3  PET_ID char(8) NOT NULL,
      4  Name char(50) NOT NULL,
      5  PRIMARY KEY(PET_ID),
      6  CONSTRAINT PET_ID_FORMAT CHECK(PET_ID REGEXP_LIKE
      7  '[A-Z][{4}[1,2,3,4,6,7][0-9]{3}')
      8  );
    CONSTRAINT PET_ID_FORMAT CHECK(PET_ID REGEXP_LIKE
                                          *
    ERROR at line 6:
    ORA-00920: invalid relational operator
    Prior version would most likely see ORA-00904.

  5. #5
    Join Date
    Sep 2009
    Posts
    16

    REGEXP_LIKE is still giving error "invalid relational operator"

    I fixed up that stupid typo of mine, thanx 4 that, but that's not the problem, it still
    complaining abt the invalid relational operator, the column number is right before "REGEXP_LIKE".


    Code:
    CREATE TABLE Pet
    (
    PET_ID char(8) NOT NULL,
    Name char(50) NOT NULL,
    PRIMARY KEY(PET_ID),
    CONSTRAINT PET_ID_FORMAT CHECK(PET_ID REGEXP_LIKE   
    '[A-Z]{4}[1,2,3,4,6,7][0-9]{3}')
    );

    Error at Command Line:6 Column:38
    Error report:
    SQL Error: ORA-00920: invalid relational operator
    00920. 00000 - "invalid relational operator"


    After running:

    select * from v$version;

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
    PL/SQL Release 10.2.0.1.0 - Production
    "CORE 10.2.0.1.0 Production"
    TNS for Linux: Version 10.2.0.1.0 - Production
    NLSRTL Version 10.2.0.1.0 - Production



  6. #6
    Join Date
    May 2006
    Posts
    132
    Look closely at my syntax.

  7. #7
    Join Date
    Sep 2009
    Posts
    16

    Syntax changed to what ebrian suggested but still same issue

    Sorry abt that, i thought you it was jus that stupid square bracket that
    i had to the left of the {4}. But yeh i ran it as follows and got that result


    Code:
    CREATE TABLE Pet
    (
    PET_ID char(8) NOT NULL,
    Name char(50) NOT NULL,
    PRIMARY KEY(PET_ID),
    CONSTRAINT PET_ID_FORMAT CHECK(PET_ID REGEXP_LIKE   
    '[A-Z]{4}[123467][0-9]{3}'?)
    )
    Error at Command Line:26 Column:38
    Error report:
    SQL Error: ORA-00920: invalid relational operator
    00920. 00000 -  "invalid relational operator"
    *Cause:    
    *Action:

  8. #8
    Join Date
    May 2006
    Posts
    132
    Check again....very...very closely...

    Code:
    SQL> CREATE TABLE Pet
      2  (
      3  PET_ID char(8) NOT NULL,
      4  Name char(50) NOT NULL,
      5  PRIMARY KEY(PET_ID),
      6  CONSTRAINT PET_ID_FORMAT CHECK(REGEXP_LIKE
      7  (pet_id,'[A-Z]{4}[123467][0-9]{3}'))
      8  );

  9. #9
    Join Date
    Sep 2009
    Posts
    16

    Thank Heaps!!

    ebrian you are a LEGEND!!!!, Thanx a lot, i really appreciate your help

    cheers

  10. #10
    Join Date
    May 2006
    Posts
    132
    You are welcome...glad I could help!

Posting Permissions

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