OraFAQ Forum: SQL & PL/SQL » dbms

Home » SQL & PL/SQL » SQL & PL/SQL » dbms_random package

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

dbms_random package [message #15436]

Thu, 07 October 2004 21:25

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

hi,

i m trying to populate a table using dbms_random,but i m getting an error 'invalid column'.i have gone thru the doc ,but couldn't figure out what it meant.Do i need to run some script ?can someone help me .i m using 8.1.5.

�

regards

�

Report message to a moderator

Re: dbms_random package [message #15438 is a reply to message #15436]

Thu, 07 October 2004 21:31

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

An example of how you are trying to call the package would be helpful.

Report message to a moderator

Re: dbms_random package [message #15439 is a reply to message #15438]

Thu, 07 October 2004 21:45

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

hi,
i have a test table.

insert into test
select dbms_random.string('U',5) from emp;

regards

Report message to a moderator

Re: dbms_random package [message #15447 is a reply to message #15439]

Fri, 08 October 2004 04:24

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

Your code works in Oracle 9i, as demonstrated below. I think, perhaps the string function was not introduced until Oracle 9i. Do you see the function when you describe dbms_random, as also demonstrated below?

 
scott@ORA92> create table test (col1 varchar2(5))
  2  /

Table created.

scott@ORA92> insert into test select <b>dbms_random.string</b> ('U', 5) from emp
  2  /

14 rows created.

scott@ORA92> select * from test
  2  /

COL1
-----
TCYTV
HWEUZ
TOWCP
CDBET
LKYUE
MSHZD
YKJHW
BWGGM
WPMFU
UTKZD
XAGRB
YPRAU
KVODH
QPTJW

14 rows selected.

scott@ORA92> <b>desc dbms_random</b>
PROCEDURE INITIALIZE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            BINARY_INTEGER          IN
FUNCTION NORMAL RETURNS NUMBER
FUNCTION RANDOM RETURNS BINARY_INTEGER
PROCEDURE SEED
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            BINARY_INTEGER          IN
PROCEDURE SEED
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAL                            VARCHAR2                IN
<b>FUNCTION STRING RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 OPT                            CHAR                    IN
 LEN                            NUMBER                  IN</b>
PROCEDURE TERMINATE
FUNCTION VALUE RETURNS NUMBER
FUNCTION VALUE RETURNS NUMBER
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 LOW                            NUMBER                  IN
 HIGH                           NUMBER                  IN

scott@ORA92>

Report message to a moderator

Re: dbms_random package [message #15631 is a reply to message #15447]

Sun, 17 October 2004 21:36

dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai

Senior Member

hi,

sorry to revert back late.

but, when i try to describe dbms_random, i get the following err.

object dbms_random does not exist
ORA-04043:
do, i need to run some script ?

plz explain.

regards,

Report message to a moderator

Re: dbms_random package [message #15662 is a reply to message #15631]

Mon, 18 October 2004 14:49

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

On newer versions of Oracle, there is no need to run a script to install the dbms_random package, but on Oracle 8.1.5 you have to install the dbms_random package by either finding and running your catoctk.sql file or by running both the prvtoctk.plb and dbmsoctk.sql files. You must be connected as sys when you run the script(s). However, you still will not have the string function, since that was introduced in 9i. You could write something to convert a number of randomly generated numbers to a string of characters, using chr() or some such thing to convert the numbers to characters, being careful to use only those numeric values, where the chr() of the value is a letter. Please see the example below.

scott@ORA92> CREATE OR REPLACE FUNCTION my_random_string
  2    (p_length IN NUMBER)
  3    RETURN	    VARCHAR2
  4  AS
  5    v_string     VARCHAR2(32767);
  6    v_num	    NUMBER;
  7  BEGIN
  8    FOR i IN 1 .. p_length LOOP
  9  	 SELECT MOD (ABS (DBMS_RANDOM.RANDOM), 25) INTO v_num FROM DUAL;
 10  	 v_num := v_num + 65;
 11  	 v_string := v_string || CHR (v_num);
 12    END LOOP;
 13    RETURN v_string;
 14  END my_random_string;
 15  /

Function created.

scott@ORA92> SHOW ERRORS
No errors.
scott@ORA92> SELECT my_random_string (5) FROM DUAL
  2  /

MY_RANDOM_STRING(5)
----------------------------------------------------------------------------------------------------
LCNPF

scott@ORA92> SELECT my_random_string (5) FROM DUAL
  2  /

MY_RANDOM_STRING(5)
----------------------------------------------------------------------------------------------------
TMTCI

scott@ORA92> SELECT my_random_string (5) FROM DUAL
  2  /

MY_RANDOM_STRING(5)
----------------------------------------------------------------------------------------------------
ASYSX

Report message to a moderator

Previous Topic:	BLOB performance
Next Topic:	Stored Procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Aug 28 20:29:32 CDT 2025