Home » SQL & PL/SQL » SQL & PL/SQL » problem in dbms_random.
problem in dbms_random. [message #198039] Sat, 14 October 2006 01:10 Go to next message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

HI , ANY ONE CAN HELP ME .HOW I CAN GET RANDOMLY NUMBER FROM EMP TABLE... THAT IS PROBLUM

I M USING ORACLE 8.0.5 .

When i M running following command.

SQL> SELECT * FROM
2 ( SELECT empno FROM EMP
3 ORDER BY dbms_random.VALUE )
4 WHERE ROWNUM < 11;


following Error occur?

ERROR at line 3:
ORA-00907: missing right parenthesis

BEFORE DOING THIS .I GO TO THE WEB LINK .

http://asktom.oracle.com/~tkyte/Misc/Random.html

HERE I GOT SOME COMMANDS TO INSTALL 'DBMS_RANDOM' IN ORACLE 8.THESE ARE

SYSTEM connect MANAGER
Connected.

I RUN THESE COMMANDS BY USING "RUN".ONE BY ONE

SYSTEM @utlraw

SYSTEM @prvtrawb.plb

SYSTEM @dbmsoctk

SYSTEM @prvtoctk.plb

SYSTEM @dbmsrand

BUT I GOT " Nothing in SQL buffer to run."WHEN I RUN ABOVE ALL(1 BY 1)

THEN I PUT THE STATMENT .

SQL>select text from all_source
where name = 'DBMS_RANDOM'
and type = 'PACKAGE'
order by line;

SO I GOT

TEXT
------------------------------
----PACKAGE dbms_random AS

------
-------
120 rows selected.

BUT STILL I M RECEIVING THE ABOVE ERROR IN MY CODE (GIVEN AT THE TOP).

"ERROR at line 3:
ORA-00907: missing right parenthesis"


NOW What i can do ?

Best Regards and wishes.

LUNATE
Re: problem in dbms_random. [message #198084 is a reply to message #198039] Sat, 14 October 2006 10:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
Oracle 8.0 did not allow an order by clause in a subquery and that is what is causing your somewhat misleading error about a missing parenthesis. If you attempt to order by some column within that subquery, without using dbms_random, you will still get the same error. If you remove the order by clause, you will not get the error. You need to upgrade to at least 8i to use an order by clause in a subquery. However, even 8i is no longer supported, so you should really upgrade to at least 9i.


Re: problem in dbms_random. [message #198086 is a reply to message #198039] Sat, 14 October 2006 10:42 Go to previous message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
I don't have 8.0 to test with, but you might be able to do a workaround with something like this:

CREATE OR RELACE VIEW random_emp AS
SELECT e.*, DBMS_RANDOM.RANDOM AS rand FROM emp;
 
SELECT   *
FROM     random_emp outer
WHERE    10 >=
         (SELECT COUNT (*) 
          FROM   random_emp inner
          WHERE  inner.rand <= outer.rand);



Previous Topic: Which of the following is best- MERGE or update/insert trigger?
Next Topic: i have logged on but it still report" ORA-01012: not logged on"
Goto Forum:
  


Current Time: Sun Dec 04 10:47:59 CST 2016

Total time taken to generate the page: 0.06483 seconds