# Thread: Need a point in the right direction

1. Registered User
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. Registered User
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.

3. Registered User
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. Registered User
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. Registered User
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.

6. Registered User
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. Registered User
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.

8. Registered User
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. Registered User
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. Registered User
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.

#### Posting Permissions

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