Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: Need a point in the right direction

    I am trying to set up a column in a table in 8i that will check a constraint as follows:

    check_verification between 'XX001' and 'ZZ999'

    now i know that it isn't between i am looking for, but if you can picture the possible combinations i would have xx002...xx999, xy001....xz....zz999

    i tried using like but i can't figure out how to isolate the individual letters. maybe i am looking at this all wrong but if you have a suggestion let me know.

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    what do you exactly want to check ? that the first two are figures and the other three are digits ?
    You can try to split the value in two parts and try a to_number on the two parts.
    Also, you can use the ASCII code for all characters to check the values.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Sep 2003
    Posts
    6
    Originally posted by evanhattem
    hi,
    use of LIKE will help u better when cheking between values.
    declare a variable and range it
    then use LIKE
    bye

    what do you exactly want to check ? that the first two are figures and the other three are digits ?
    You can try to split the value in two parts and try a to_number on the two parts.
    Also, you can use the ASCII code for all characters to check the values.

    Hope this helps.

  4. #4
    Join Date
    Sep 2003
    Posts
    5
    But there is no way to check from 1 character to another like in Microsoft's sql right.

    [a-z][a-z][0-9][0-9][0-9]

  5. #5
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    yes there is a way.
    First, find out the ASCII value for the characters a through z and 0 through 9 (i think 0 - 9 are values 48 - 57, a - z are values 97-122 and A-Z is 65 - 90). Then start programming like this.

    if the string is always 6 characters, split the characters into 6 separate values storing them in 6 local variables. For an example i say the string must be in the format number number character character character character.
    Then, if the first character should be figure, check to see if the ASCII value of the first character is >=48 and <=57.
    Then repeat this for the second character.
    For the third check to see if the ascii value is between 97 and 122 or between 65 and 90.
    repeat this for the next three characters.

    This is the only way I know of.

    Hope this helpes.
    Edwin van Hattem
    OCP DBA / System analyst

  6. #6
    Join Date
    Sep 2003
    Posts
    5
    what if you wanted this to all be in a constraint in a table, is there a way to make local variable while doing a check.

  7. #7
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    there's no way to implement this is a single constraint like a check constraint. You''ll need to do this using a trigger like a before row update and/or a before row insert.
    Edwin van Hattem
    OCP DBA / System analyst

  8. #8
    Join Date
    Sep 2003
    Posts
    5
    Does anyone know what this error means or how to work around it,

    ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

    i am running a while loop that loops through a fairly large cursor and it doesn't get very far before it hits this error

  9. #9
    Join Date
    Sep 2003
    Posts
    5
    and thanks for the help with the last problem, i figured out another way to do it though in a constraint

    if you want to know for the hell of it....
    CHECK (
    (SUBSTR('VARIABLE',1,1) BETWEEN 'A' AND 'Z') AND
    (SUBSTR('VARIABLE',2,1) BETWEEN 'A' AND 'Z') AND
    (SUBSTR('VARIABLE',3,1) BETWEEN 'A' AND 'Z') AND
    (SUBSTR('VARIABLE',4,1) BETWEEN '0' AND '9') AND
    (SUBSTR('VARIABLE',5,1) BETWEEN '0' AND '9') AND
    )

  10. #10
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    Originally posted by confusious
    Does anyone know what this error means or how to work around it,

    ORA-20000: ORU-10027: buffer overflow, limit of 2000 bytes

    i am running a while loop that loops through a fairly large cursor and it doesn't get very far before it hits this error
    Loks like you are writing lines to screen using dbsm_output. But the buffersize is to small (2000 characters). Set it in sqlplus :
    set serveroutput on size 1000000 (max = 1 miljon)

    Hope that helps.
    Edwin van Hattem
    OCP DBA / System analyst

Posting Permissions

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