is_ascii Oracle function

Here is an Oracle function that tells whether a column is all ASCII (from Mark Stafford):

create or replace function is_ascii (v varchar2) return varchar2 as
 r boolean := false;
begin
 r := length(v)
   - length(
       translate(v
         , chr(1)
           || translate(v, chr(1) || ' !"#$%&''()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~', chr(1) )
         , chr(1)
       )
   ) = 0;
 if v is null then r := true; end if;
 if r = true then return 'y'; else return 'n'; end if;
exception
 when others then
   return null;
end;
/

working with FrontBase

I'm accustomed to working with Oracle, but I'm using FrontBase for a project. So here's some interesting info I've had to learn:

• Here's a timestamp statement:

update client set enrollment_date = TIMESTAMP '2008-04-03 10:58:30' where client_id = 1000019

• This sets it to the current time:
  

update client set enrollment_date = cast(CURRENT_TIMESTAMP as timestamp) where client_id = 1000019

• Don't try to put numbers inside single-quotes. It will give error 217.

• To drop a table, add CASCADE at the end:

drop table CLIENT_ADMIN_AUDIT cascade

• Here are a bunch of error codes you can get back (found here and duplicated on many internet sites):

22 => DB_ERROR_SYNTAX,
85 => DB_ERROR_ALREADY_EXISTS,
108 => DB_ERROR_SYNTAX,
116 => DB_ERROR_NOSUCHTABLE,
124 => DB_ERROR_VALUE_COUNT_ON_ROW,
215 => DB_ERROR_NOSUCHFIELD,
217 => DB_ERROR_INVALID_NUMBER,
226 => DB_ERROR_NOSUCHFIELD,
231 => DB_ERROR_INVALID,
239 => DB_ERROR_TRUNCATED,
251 => DB_ERROR_SYNTAX,
266 => DB_ERROR_NOT_FOUND,
357 => DB_ERROR_CONSTRAINT_NOT_NULL,
358 => DB_ERROR_CONSTRAINT,
360 => DB_ERROR_CONSTRAINT,
361 = "Integrity constraint violation (FOREIGN_KEY...)" (from Java experiments)

• I keep hitting problems trying to use any native sequence functionality with Hibernate. You get the next sequence number with "SELECT UNIQUE" on the table name, thus:
  

  
  select unique from admin_user
  

  
  
  This is automatically available for any table that you create.
  
  Now, the generic Hibernate JDBC dialect acts badly, so I changed things so that I have to retrieve this unique value every time I try to insert into the DB. Sometimes it was difficult to figure out that this was the problem; even P6Spy wasn't showing me why I was getting an error 116 "DB_ERROR_NOSUCHTABLE". I finally found this statement in the FrontBase logs:
  
  

  
  select next_hi from hibernate_unique_key;