OraFAQ Forum: SQL & PL/SQL » problem in dbms

Home » SQL & PL/SQL » SQL & PL/SQL » problem in dbms_random.

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

problem in dbms_random. [message #198039]

Sat, 14 October 2006 01:10

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

Report message to a moderator

Re: problem in dbms_random. [message #198084 is a reply to message #198039]

Sat, 14 October 2006 10:24

Barbara Boehmer
Messages: 9100
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.

Report message to a moderator

Re: problem in dbms_random. [message #198086 is a reply to message #198039]

Sat, 14 October 2006 10:42

Barbara Boehmer
Messages: 9100
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);

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 09:41:31 CST 2024