若不存在用sys用户执行以下脚本: CREATE OR REPLACE PACKAGE SYS.dbms_random AS ------------ -- OVERVIEW -- -- This package should be installed as SYS. It generates a sequence of -- random 38-digit Oracle numbers. The expected length of the sequence -- is about power(10,28), which is hopefully long enough. -- -------- -- USAGE -- -- This is a random number generator. Do not use for cryptography. -- For more options the cryptographic toolkit should be used. -- -- By default, the package is initialized with the current user -- name, current time down to the second, and the current session. -- -- If this package is seeded twice with the same seed, then accessed -- in the same way, it will produce the same results in both cases. -- -------- -- EXAMPLES -- -- To initialize or reset the generator, call the seed procedure as in: -- execute dbms_random.seed(12345678); -- or -- execute dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS')); -- To get the random number, simply call the function, e.g. -- my_random_number BINARY_INTEGER; -- my_random_number := dbms_random.random; -- or -- my_random_real NUMBER; -- my_random_real := dbms_random.value; -- To use in SQL statements: -- select dbms_random.value from dual; -- insert into a values (dbms_random.value); -- variable x NUMBER; -- execute :x := dbms_random.value; -- update a set a2=a2+1 where a1 < :x; -- Seed with a binary integer PROCEDURE seed(val IN BINARY_INTEGER); PRAGMA restrict_references (seed, WNDS); -- Seed with a string (up to length 2000) PROCEDURE seed(val IN VARCHAR2); PRAGMA restrict_references (seed, WNDS); -- Get a random 38-digit precision number, 0.0 <= value < 1.0 FUNCTION value RETURN NUMBER; PRAGMA restrict_references (value, WNDS); -- get a random Oracle number x, low <= x < high FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER; PRAGMA restrict_references (value, WNDS); -- get a random number from a normal distribution FUNCTION normal RETURN NUMBER; PRAGMA restrict_references (normal, WNDS); -- get a random string FUNCTION string (opt char, len NUMBER) /* "opt" specifies that the returned string may contain: 'u','U' : upper case alpha characters only 'l','L' : lower case alpha characters only 'a','A' : alpha characters only (mixed case) 'x','X' : any alpha-numeric characters (upper) 'p','P' : any printable characters */ RETURN VARCHAR2; -- string of <len> characters (max 60) PRAGMA restrict_references (string, WNDS); -- Obsolete, just calls seed(val) PROCEDURE initialize(val IN BINARY_INTEGER); PRAGMA restrict_references (initialize, WNDS); -- Obsolete, get integer in ( -power(2,32) <= random < power(2,32) ) FUNCTION random RETURN BINARY_INTEGER; PRAGMA restrict_references (random, WNDS); -- Obsolete, does nothing PROCEDURE terminate; TYPE num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER; END dbms_random;
select * from dba_synonyms where table_name='DBMS_RANDOM';OWNER SYNONYM_NAME TABLE_OWNER ------------------------------ ------------------------------ ----------- TABLE_NAME ------------------------------ DB_LINK ------------------------------------------------------------------------- PUBLIC DBMS_RANDOM SYS DBMS_RANDOM是不是装了呢?谢谢。
包体; CREATE OR REPLACE PACKAGE BODY SYS.dbms_random AS mem num_array; -- big internal state hidden from the user counter BINARY_INTEGER := 55;-- counter through the results saved_norm NUMBER := NULL; -- unused random normally distributed value need_init BOOLEAN := TRUE; -- do we still need to initialize -- Seed the random number generator with a binary_integer PROCEDURE seed(val IN BINARY_INTEGER) IS BEGIN seed(TO_CHAR(val)); END seed; -- Seed the random number generator with a string. PROCEDURE seed(val IN VARCHAR2) IS junk VARCHAR2(2000); piece VARCHAR2(20); randval NUMBER; mytemp NUMBER; j BINARY_INTEGER; BEGIN need_init := FALSE; saved_norm := NULL; counter := 0; junk := TO_SINGLE_BYTE(val); FOR i IN 0..54 LOOP piece := SUBSTR(junk,1,19); randval := 0; j := 1; -- convert 19 characters to a 38-digit number FOR j IN 1..19 LOOP randval := 1e2*randval + NVL(ASCII(SUBSTR(piece,j,1)),0.0); END LOOP; -- try to avoid lots of zeros randval := randval*1e-38+i*.01020304050607080910111213141516171819; mem(i) := randval - TRUNC(randval); -- we've handled these first 19 characters already; move on junk := SUBSTR(junk,20); END LOOP; randval := mem(54); FOR j IN 0..10 LOOP FOR i IN 0..54 LOOP -- barrelshift mem(i-1) by 24 digits randval := randval * 1e24; mytemp := TRUNC(randval); randval := (randval - mytemp) + (mytemp * 1e-38); -- add it to mem(i) randval := mem(i)+randval; IF (randval >= 1.0) THEN randval := randval - 1.0; END IF; -- record the result mem(i) := randval; END LOOP; END LOOP; END seed; -- give values to the user -- Delayed Fibonacci, pilfered from Knuth volume 2 FUNCTION value RETURN NUMBER IS randval NUMBER; BEGIN counter := counter + 1; IF counter >= 55 THEN -- initialize if needed IF (need_init = TRUE) THEN seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') || USER || USERENV('SESSIONID')); ELSE -- need to generate 55 more results FOR i IN 0..30 LOOP randval := mem(i+24) + mem(i); IF (randval >= 1.0) THEN randval := randval - 1.0; END IF; mem(i) := randval; END LOOP; FOR i IN 31..54 LOOP randval := mem(i-31) + mem(i); IF (randval >= 1.0) THEN randval := randval - 1.0; END IF; mem(i) := randval; END LOOP; END IF; counter := 0; END IF; RETURN mem(counter); END value; -- Random 38-digit number between LOW and HIGH. FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is BEGIN RETURN (value*(high-low))+low; END value; -- Random numbers in a normal distribution. -- Pilfered from Knuth volume 2. FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1 v1 NUMBER; v2 NUMBER; r2 NUMBER; fac NUMBER; BEGIN IF saved_norm is not NULL THEN -- saved from last time v1 := saved_norm; -- to be returned this time saved_norm := NULL; ELSE r2 := 2; -- Find two independent uniform variables WHILE r2 > 1 OR r2 = 0 LOOP v1 := value(); v1 := v1 + v1 - 1; v2 := value(); v2 := v2 + v2 - 1; r2 := v1*v1 + v2*v2; -- r2 is radius END LOOP; -- 0 < r2 <= 1: in unit circle /* Now derive two independent normally-distributed variables */ fac := sqrt(-2*ln(r2)/r2); v1 := v1*fac; -- to be returned this time saved_norm := v2*fac; -- to be saved for next time END IF; RETURN v1; END normal; -- Random string. Pilfered from Chris Ellis. FUNCTION string (opt char, len NUMBER) RETURN VARCHAR2 is -- string of <len> characters optx char (1) := lower(opt); lo NUMBER; rng NUMBER; n NUMBER; xstr VARCHAR2 (60) := NULL; BEGIN IF optx = 'u' THEN -- upper case alpha characters only lo := 65; rng := 26; -- ASCII 41 to 5A (hex) ELSIF optx = 'l' THEN -- lower case alpha characters only lo := 97; rng := 26; -- ASCII 61 to 7A (hex) ELSIF optx = 'a' THEN -- alpha characters only (mixed case) lo := 65; rng := 52; -- ASCII 41 to 5A and 61 to 7A (see below) ELSIF optx = 'x' THEN -- any alpha-numeric characters (upper) lo := 48; rng := 36; -- ASCII 30 to 39 and 41 to 5A (see below) ELSIF optx = 'p' THEN -- any printable characters lo := 32; rng := 95; -- ASCII 20 to 7E (hex) ELSE lo := 65; rng := 26; -- default to upper case END IF; FOR i IN 1 .. least(len,60) LOOP /* Get random ASCII character value in specified range */ n := lo + TRUNC(rng * value); -- between lo and (lo + rng -1) /* Adjust FOR split range */ IF optx = 'A' AND n > 90 THEN n := n+6; -- exclude ASCII characters 5B to 60 ELSIF optx = 'X' AND n > 57 THEN n := n+7; -- exclude ASCII characters 3A to 40 END IF; xstr := xstr||chr(n); -- Append character to string END LOOP; RETURN xstr; END string; -- For compatibility with 8.1 PROCEDURE initialize(val IN BINARY_INTEGER) IS BEGIN seed(to_char(val)); END initialize; -- For compatibility with 8.1 -- Random binary_integer, -power(2,31) <= Random < power(2,31) -- Delayed Fibonacci, pilfered from Knuth volume 2 FUNCTION random RETURN BINARY_INTEGER IS BEGIN RETURN TRUNC(Value*4294967296)-2147483648; END random; -- For compatibility with 8.1 PROCEDURE terminate IS BEGIN NULL; END terminate;END dbms_random;
我是用sys登陆的,只是密码改了而已。会不会有影响呢?
装了, Public应该任何用户都能调用啊,
你先不用ceil,看好不好用, 是不是没有ceil函数啊?
谢谢各位 我试了试结果如下:运行 select dbms_random.value(1,4) from dual; 结果 select dbms_random.value(1,4) from dual * ERROR 位于第1行: ORA-00904: 无效列名 运行 select dbms_random.random from dual; 结果 select dbms_random.random from dual * ERROR 位于第1行: ORA-06510: PL/SQL:无法处理的用户自定义异常事件 ORA-06512: 在"SYS.DBMS_RANDOM", line 13 ORA-06512: 在"SYS.DBMS_RANDOM", line 33 ORA-06512: 在line 1
CREATE OR REPLACE PACKAGE SYS.dbms_random AS ------------
-- OVERVIEW
--
-- This package should be installed as SYS. It generates a sequence of
-- random 38-digit Oracle numbers. The expected length of the sequence
-- is about power(10,28), which is hopefully long enough.
--
--------
-- USAGE
--
-- This is a random number generator. Do not use for cryptography.
-- For more options the cryptographic toolkit should be used.
--
-- By default, the package is initialized with the current user
-- name, current time down to the second, and the current session.
--
-- If this package is seeded twice with the same seed, then accessed
-- in the same way, it will produce the same results in both cases.
--
--------
-- EXAMPLES
--
-- To initialize or reset the generator, call the seed procedure as in:
-- execute dbms_random.seed(12345678);
-- or
-- execute dbms_random.seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS'));
-- To get the random number, simply call the function, e.g.
-- my_random_number BINARY_INTEGER;
-- my_random_number := dbms_random.random;
-- or
-- my_random_real NUMBER;
-- my_random_real := dbms_random.value;
-- To use in SQL statements:
-- select dbms_random.value from dual;
-- insert into a values (dbms_random.value);
-- variable x NUMBER;
-- execute :x := dbms_random.value;
-- update a set a2=a2+1 where a1 < :x; -- Seed with a binary integer
PROCEDURE seed(val IN BINARY_INTEGER);
PRAGMA restrict_references (seed, WNDS); -- Seed with a string (up to length 2000)
PROCEDURE seed(val IN VARCHAR2);
PRAGMA restrict_references (seed, WNDS); -- Get a random 38-digit precision number, 0.0 <= value < 1.0
FUNCTION value RETURN NUMBER;
PRAGMA restrict_references (value, WNDS); -- get a random Oracle number x, low <= x < high
FUNCTION value (low IN NUMBER, high IN NUMBER) RETURN NUMBER;
PRAGMA restrict_references (value, WNDS); -- get a random number from a normal distribution
FUNCTION normal RETURN NUMBER;
PRAGMA restrict_references (normal, WNDS); -- get a random string
FUNCTION string (opt char, len NUMBER)
/* "opt" specifies that the returned string may contain:
'u','U' : upper case alpha characters only
'l','L' : lower case alpha characters only
'a','A' : alpha characters only (mixed case)
'x','X' : any alpha-numeric characters (upper)
'p','P' : any printable characters
*/
RETURN VARCHAR2; -- string of <len> characters (max 60)
PRAGMA restrict_references (string, WNDS); -- Obsolete, just calls seed(val)
PROCEDURE initialize(val IN BINARY_INTEGER);
PRAGMA restrict_references (initialize, WNDS); -- Obsolete, get integer in ( -power(2,32) <= random < power(2,32) )
FUNCTION random RETURN BINARY_INTEGER;
PRAGMA restrict_references (random, WNDS); -- Obsolete, does nothing
PROCEDURE terminate; TYPE num_array IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
END dbms_random;
------------------------------ ------------------------------ -----------
TABLE_NAME
------------------------------
DB_LINK
-------------------------------------------------------------------------
PUBLIC DBMS_RANDOM SYS
DBMS_RANDOM是不是装了呢?谢谢。
CREATE OR REPLACE PACKAGE BODY SYS.dbms_random AS
mem num_array; -- big internal state hidden from the user
counter BINARY_INTEGER := 55;-- counter through the results
saved_norm NUMBER := NULL; -- unused random normally distributed value
need_init BOOLEAN := TRUE; -- do we still need to initialize
-- Seed the random number generator with a binary_integer
PROCEDURE seed(val IN BINARY_INTEGER) IS
BEGIN
seed(TO_CHAR(val));
END seed;
-- Seed the random number generator with a string.
PROCEDURE seed(val IN VARCHAR2) IS
junk VARCHAR2(2000);
piece VARCHAR2(20);
randval NUMBER;
mytemp NUMBER;
j BINARY_INTEGER;
BEGIN
need_init := FALSE;
saved_norm := NULL;
counter := 0;
junk := TO_SINGLE_BYTE(val);
FOR i IN 0..54 LOOP
piece := SUBSTR(junk,1,19);
randval := 0;
j := 1; -- convert 19 characters to a 38-digit number
FOR j IN 1..19 LOOP
randval := 1e2*randval + NVL(ASCII(SUBSTR(piece,j,1)),0.0);
END LOOP; -- try to avoid lots of zeros
randval := randval*1e-38+i*.01020304050607080910111213141516171819;
mem(i) := randval - TRUNC(randval); -- we've handled these first 19 characters already; move on
junk := SUBSTR(junk,20);
END LOOP; randval := mem(54);
FOR j IN 0..10 LOOP
FOR i IN 0..54 LOOP -- barrelshift mem(i-1) by 24 digits
randval := randval * 1e24;
mytemp := TRUNC(randval);
randval := (randval - mytemp) + (mytemp * 1e-38); -- add it to mem(i)
randval := mem(i)+randval;
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF; -- record the result
mem(i) := randval;
END LOOP;
END LOOP;
END seed;
-- give values to the user
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION value RETURN NUMBER IS
randval NUMBER;
BEGIN
counter := counter + 1;
IF counter >= 55 THEN -- initialize if needed
IF (need_init = TRUE) THEN
seed(TO_CHAR(SYSDATE,'MM-DD-YYYY HH24:MI:SS') ||
USER || USERENV('SESSIONID'));
ELSE
-- need to generate 55 more results
FOR i IN 0..30 LOOP
randval := mem(i+24) + mem(i);
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
mem(i) := randval;
END LOOP;
FOR i IN 31..54 LOOP
randval := mem(i-31) + mem(i);
IF (randval >= 1.0) THEN
randval := randval - 1.0;
END IF;
mem(i) := randval;
END LOOP;
END IF;
counter := 0;
END IF;
RETURN mem(counter);
END value;
-- Random 38-digit number between LOW and HIGH.
FUNCTION value ( low in NUMBER, high in NUMBER) RETURN NUMBER is
BEGIN
RETURN (value*(high-low))+low;
END value;
-- Random numbers in a normal distribution.
-- Pilfered from Knuth volume 2.
FUNCTION normal RETURN NUMBER is -- 38 decimal places: Mean 0, Variance 1
v1 NUMBER;
v2 NUMBER;
r2 NUMBER;
fac NUMBER;
BEGIN
IF saved_norm is not NULL THEN -- saved from last time
v1 := saved_norm; -- to be returned this time
saved_norm := NULL;
ELSE
r2 := 2;
-- Find two independent uniform variables
WHILE r2 > 1 OR r2 = 0 LOOP
v1 := value();
v1 := v1 + v1 - 1;
v2 := value();
v2 := v2 + v2 - 1;
r2 := v1*v1 + v2*v2; -- r2 is radius
END LOOP; -- 0 < r2 <= 1: in unit circle
/* Now derive two independent normally-distributed variables */
fac := sqrt(-2*ln(r2)/r2);
v1 := v1*fac; -- to be returned this time
saved_norm := v2*fac; -- to be saved for next time
END IF;
RETURN v1;
END normal;
-- Random string. Pilfered from Chris Ellis.
FUNCTION string (opt char, len NUMBER)
RETURN VARCHAR2 is -- string of <len> characters
optx char (1) := lower(opt);
lo NUMBER;
rng NUMBER;
n NUMBER;
xstr VARCHAR2 (60) := NULL;
BEGIN
IF optx = 'u' THEN -- upper case alpha characters only
lo := 65; rng := 26; -- ASCII 41 to 5A (hex)
ELSIF optx = 'l' THEN -- lower case alpha characters only
lo := 97; rng := 26; -- ASCII 61 to 7A (hex)
ELSIF optx = 'a' THEN -- alpha characters only (mixed case)
lo := 65; rng := 52; -- ASCII 41 to 5A and 61 to 7A (see below)
ELSIF optx = 'x' THEN -- any alpha-numeric characters (upper)
lo := 48; rng := 36; -- ASCII 30 to 39 and 41 to 5A (see below)
ELSIF optx = 'p' THEN -- any printable characters
lo := 32; rng := 95; -- ASCII 20 to 7E (hex)
ELSE
lo := 65; rng := 26; -- default to upper case
END IF;
FOR i IN 1 .. least(len,60) LOOP
/* Get random ASCII character value in specified range */
n := lo + TRUNC(rng * value); -- between lo and (lo + rng -1)
/* Adjust FOR split range */
IF optx = 'A' AND n > 90 THEN
n := n+6; -- exclude ASCII characters 5B to 60
ELSIF optx = 'X' AND n > 57 THEN
n := n+7; -- exclude ASCII characters 3A to 40
END IF;
xstr := xstr||chr(n); -- Append character to string
END LOOP;
RETURN xstr;
END string;
-- For compatibility with 8.1
PROCEDURE initialize(val IN BINARY_INTEGER) IS
BEGIN
seed(to_char(val));
END initialize;
-- For compatibility with 8.1
-- Random binary_integer, -power(2,31) <= Random < power(2,31)
-- Delayed Fibonacci, pilfered from Knuth volume 2
FUNCTION random RETURN BINARY_INTEGER IS
BEGIN
RETURN TRUNC(Value*4294967296)-2147483648;
END random;
-- For compatibility with 8.1
PROCEDURE terminate IS
BEGIN
NULL;
END terminate;END dbms_random;
Public应该任何用户都能调用啊,
是不是没有ceil函数啊?
我试了试结果如下:运行
select dbms_random.value(1,4) from dual;
结果
select dbms_random.value(1,4) from dual
*
ERROR 位于第1行:
ORA-00904: 无效列名
运行
select dbms_random.random from dual;
结果
select dbms_random.random from dual
*
ERROR 位于第1行:
ORA-06510: PL/SQL:无法处理的用户自定义异常事件
ORA-06512: 在"SYS.DBMS_RANDOM", line 13
ORA-06512: 在"SYS.DBMS_RANDOM", line 33
ORA-06512: 在line 1
我觉得可能是你的包有问题,
或者你测试一下你的包SYS.DBMS_RANDOM,可能有问题