jump to navigation

A user defined function for Sybase ASE to emulate Oracle’s dbms_random.string() function May 31, 2012

Posted by Mich Talebzadeh in Oracle and Sybase.
trackback

In Oracle, dbms_random package allows one to generate random data. A very widely used function within this package is dbms_random.string(opt, len) that returns a random string whose length is determined by the len argument.

The returned string’s characteristics are determined by the opt argument. opt must be a char. The following five options are supported:
‘u’ or ‘U’: Upper case alpha characters
‘l’ or ‘L’: Lower case alpha characters
‘a’ or ‘A’: Upper and lower case alpha characters
‘x’ or ‘X’: Upper alpha and numeric characters
‘p’ or ‘P’: Any printable character

An example calling the above function with different options would be

col u format a10
col l format a10
col a format a10
col x format a10
col p format a10

select
2    dbms_random.string('u', 10) as u,
3    dbms_random.string('l', 10) as l,
4    dbms_random.string('a', 10) as a,
5    dbms_random.string('x', 10) as x,
6    dbms_random.string('p', 10) as p
7  from
8    dual;
U          L          A          X          P
---------- ---------- ---------- ---------- ----------
PSXPJUOMMT byebvyevlb JPRjgoAxOb QM4XU14IPX [`i06!e8m0

In Sybase ASE this random string function does not exist. The problem is that even if you write your own user defined function (UDF) it won’t work. There is currently an issue with calling non deterministic functions (getdate(),rand, rand2, newid(), etc) directly from within an ASE UDF. Basically if I try to do the following:

create function sf_test(@dummy int)
returns int
as
begin
  declare @v int
  select @v = rand2()  -- calling rand function here
  return @v
end

I will get the following error:

Msg 11798, Level 15, State 65:
Illegal BUILTIN FUNCTION statement within a scalar SQL function.

The work around is to define these functions in a view and call them in UDF referencing the view (thus avoiding direct references to these functions in UDF).

For example create the view in sybsystemprocs first

create view vudf
as
select
  CHAR(ASCII('A')+ABS(CAST(NEWID() AS VARBINARY) %26)) as valphaU
, CHAR(ASCII('a')+ABS(CAST(NEWID() AS VARBINARY) %26)) as valphaL
, CONVERT(char(1),ABS(CAST(NEWID() AS VARBINARY) %9)) as vrandnum
, CHAR(ASCII('!')+ABS(CAST(NEWID() AS VARBINARY) %95))as vprintable
, FLOOR(RAND2()*100) as vrand
, GETDATE() as vgetdate

This is not ideal but as a work around it can be expanded to provide all the non deterministic functions required. The overhead is maintaining this view in sybsystemprocs. An example calling getdate in udf:

create function sf_getdate(@datein datetime)
returns int
as
begin
  declare @diff int
  select @diff = datediff(day,@datein,vgetdate)from sybsystemprocs.dbo.vudf
  return @diff
end
go
-- run this
select dbo.sf_getdate('Apr 15 2012')
go
-----------
46

Similar to above and using the view generated we can write a random string function in ASE:

use sybsystemprocs
go
if exists(select 1 from sysobjects where type = 'V' and name = 'vudf')
drop view vudf
go
create view vudf
as
select
  CHAR(ASCII('A')+ABS(CAST(NEWID() AS VARBINARY) %26)) as valphaU
, CHAR(ASCII('a')+ABS(CAST(NEWID() AS VARBINARY) %26)) as valphaL
, CONVERT(char(1),ABS(CAST(NEWID() AS VARBINARY) %9)) as vrandnum
, CHAR(ASCII('!')+ABS(CAST(NEWID() AS VARBINARY) %95))as vprintable
, FLOOR(RAND2()*100) as vrand
, GETDATE() as vgetdate
go
if exists (select 1 from sysobjects where type = 'SF' and name = 'sf__randalpha')
drop function sf__randalpha
go
create function sf__randalpha (@opt char(1),@len int)
returns varchar(255)
as
/******************************************************************************
**
** Name        : sf__randalpha
**
** Created By  : Mich Talebzadeh
** Date        : 30/05/2012
** Purpose     : The function string returns a string whose length is determined by the len argument.
**               The returned string's characteristics are determined by the opt
**               argument. opt must be a char. The following five options are possible:
**               'u' or 'U': Upper case alpha characters
**               'l' or 'L': Lower case alpha characters
**               'a' or 'A': Upper and lower case alpha characters
**               'x' or 'X': Upper alpha and numeric characters
**               'p' or 'P': Any printable character
**
**               Examples.
**               declare @c varchar(30)
**               select @c = sybsystemprocs.dbo.sf__randalpha('a',30)
**
******************************************************************************/
begin
  if UPPER(@opt) NOT IN ('U','L','A','X','P') return null
  if (@len <= 0) return null
  declare @randalpha varchar(255)
  declare @counter int
  declare @valpha char(1)
  declare @i int
  set @randalpha = null
  set @counter = 1
  while (@counter <= @len)
  begin
    if UPPER(@opt)='U' select @valpha = valphaU from sybsystemprocs.dbo.vudf
    if UPPER(@opt)='L' select @valpha = valphaL from sybsystemprocs.dbo.vudf
    if UPPER(@opt)='P' select @valpha = vprintable from sybsystemprocs.dbo.vudf
    if UPPER(@opt) = 'A'
    begin
      select @i = vrand from sybsystemprocs.dbo.vudf
      if @i < 50 begin select @valpha = valphaU from sybsystemprocs.dbo.vudf end else begin select @valpha = valphaL from sybsystemprocs.dbo.vudf end
    end
    if UPPER(@opt) = 'X'
    begin
     select @i = vrand from sybsystemprocs.dbo.vudf
     if @i < 50 begin select @valpha = valphaU from sybsystemprocs.dbo.vudf end else begin select @valpha = vrandnum from sybsystemprocs.dbo.vudf end
    end
    select @randalpha = @randalpha || @valpha
    select @counter = @counter + 1
  end
  return substring(@randalpha,1,@len)
end
go
grant exec on dbo.sf__randalpha to public
go
sp_help sf__randalpha
go

Now if I use the above function I can create all the random strings as per Oracle

1> select
2>   substring(sybsystemprocs.dbo.sf__randalpha('u',10),1,10) as u,
3>   substring(sybsystemprocs.dbo.sf__randalpha('l',10),1,10) as l,
4>   substring(sybsystemprocs.dbo.sf__randalpha('a',10),1,10) as a,
5>   substring(sybsystemprocs.dbo.sf__randalpha('x',10),1,10) as x,
6>   substring(sybsystemprocs.dbo.sf__randalpha('p',10),1,10) as p
7> go
u          l          a          x          p
---------- ---------- ---------- ---------- ----------
GEMIGRGZHV sxttmcwujw nNxJBYefqa W5K2NTHG67 oi'8e&8{GV

Comments»

No comments yet — be the first.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: