Home » SQL & PL/SQL » SQL & PL/SQL » select random numbers /
icon4.gif  select random numbers / [message #206311] Wed, 29 November 2006 09:56 Go to next message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

hi all.
can any one help me, i want to take first 5 number which are randomlly genrated by using cursor.

remmeber each and every time random function generates new random numbers.

so how can i pic only first 5 number .

suppose.
the randomlly genrated numbers are.

1022
1003
1002
1220
1220
1210

and then on next time,(again function generates)/

1022
1000
1230
1237
1210
1502

how i can pic only first 5 from it using cursor.

Re: select random numbers / [message #206321 is a reply to message #206311] Wed, 29 November 2006 10:13 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What cursor? How does it look like? Why cursor? Why not something like
SELECT DBMS_RANDOM.VALUE
FROM all_objects
WHERE rownum < 6
Re: select random numbers / [message #206468 is a reply to message #206321] Thu, 30 November 2006 03:50 Go to previous messageGo to next message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

Quote:
oye 1965 ki jang main ulltay mashwaray dainay wallay.



dear,thanx for reply,

dear problem is that the dbms_Random (package)is not working with form 6i.okiez
and then i use view to generate my list of random numbers and attach a simple query with when button pressed trigger of a button and simple got the desire result;

now i wanna to select only first 5 number from the all ready generated list of random number through that view.

then wannna to insert these into anothere table.

so problem is that the each and every time when i pressed the button it generated a new list of numbers randomlly.so i want ur sweet help to get sequentionally only first 5 numbers each and every time from these randomlly generated list of numbers.

i think so this could be possible by using cursor and then inert these into an othere table.

how do u think?
can u help me.
Re: select random numbers / [message #206476 is a reply to message #206311] Thu, 30 November 2006 04:10 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

"dear problem is that the dbms_Random (package)is not working with form 6i.okiez"

Why ?

So i guess this doesnt work for you then

message(dbms_random.value(1,10));

What error do you get in that case ?
Re: select random numbers / [message #206497 is a reply to message #206476] Thu, 30 November 2006 05:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What version of Oracle are you using?

Also, if DBMS_RANDOM isn't working, then what are you using to generate the random numbers?
Re: select random numbers / [message #206542 is a reply to message #206468] Thu, 30 November 2006 08:32 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
[quote title=lunate wrote on Thu, 30 November 2006 04:50]
Quote:
oye 1965 ki jang main ulltay mashwaray dainay wallay.


And what is this?
Re: select random numbers / [message #206558 is a reply to message #206542] Thu, 30 November 2006 09:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
itsay otnay igpay atinlay, hattat orfay uresay.

Cool
Re: select random numbers / [message #206585 is a reply to message #206558] Thu, 30 November 2006 11:06 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
./fa/1597/0/
icon10.gif  Re: select random numbers / [message #206660 is a reply to message #206585] Fri, 01 December 2006 00:46 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

Yorkshire dialect ?
Re: select random numbers / [message #206662 is a reply to message #206660] Fri, 01 December 2006 00:48 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
LOL! Laughing
Re: select random numbers / [message #206690 is a reply to message #206660] Fri, 01 December 2006 02:22 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Pig Latin
icon14.gif  Re: select random numbers / [message #206702 is a reply to message #206690] Fri, 01 December 2006 03:08 Go to previous messageGo to next message
tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden
Senior Member

OMG ! Laughing

One can tell it's friday today Razz
icon13.gif  Re: select random numbers / [message #206801 is a reply to message #206497] Fri, 01 December 2006 10:11 Go to previous messageGo to next message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

Quote:
OYE KOHTI DAI BACHOO !! ORACLE ANDI HAI K NAHI,MAINU LAGDA HAY TUSI

SARAY ITHAY AMM LAIN AY HOO (AANI DAI) .


my dear seNiors ,

i m using oracle 9i with form 6i.but my form 6i does not working with

the dbms_random. i got errors each and every time at :

ORDER BY DBMS_RANDOM.NORMAL .

i damn think so that this package is not included in the built-in package list of FORM 6i. Razz
SO then i have created a view ,(bcause view is always working inside the database ).so then i call this view simple by using a trigger(when_button_pressed) on my front-end.,

now the problem is that ,i first generate the random numbers through the

view (using trigger),each and every time when i pressed the button i

generates a new sequence of numbers(randomlly).

now i wanna to get the first five numbers of the list (which is

already being generated by trigger each and every time when i press the

button).

and then i wanna to insert them into anothere table.


OR some-one please help me that :

HOW CAN I INCLUDE THE 'DBMS_RANDOM' PACKAGE INTO THE 'BUILT-IN PACKAGE

' LIST OF FORM 6I.

because this is the most smartest way to solve my problem .

so please reply me .

regards.

[Updated on: Fri, 01 December 2006 10:16]

Report message to a moderator

Re: select random numbers / [message #206810 is a reply to message #206311] Fri, 01 December 2006 11:01 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I tried this on 9i/Forms 10 and it compiled fine. I couldn't find dbms_random in the 'built in packages' list but it still compiled OK.

Can you let us know what error you get when you try to use it?

ah-ha found it, in forms developer its listed under Database Objects -> SYS -> PL/SQL Stored Program Units. Theres a Spec and a Body. Try looking there, see if you have it?

[Updated on: Fri, 01 December 2006 11:10]

Report message to a moderator

Re: select random numbers / [message #206834 is a reply to message #206801] Fri, 01 December 2006 12:43 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
lunate wrote on Fri, 01 December 2006 17:11
the dbms_random. i got errors each and every time at :

ORDER BY DBMS_RANDOM.NORMAL .

i damn think so that this package is not included in the built-in package list of FORM 6i. Razz
SO then i have created a view ,(bcause view is always working inside the database ).so then i call this view simple by using a trigger(when_button_pressed) on my front-end.,


I must say JRowbottoms reply made more sense to me than this one.
Re: select random numbers / [message #206840 is a reply to message #206834] Fri, 01 December 2006 13:25 Go to previous messageGo to next message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

PLEASE JUST FOCUS ON ERROR IN THE SNAP.OKIEZ




  • Attachment: untitled.JPG
    (Size: 31.35KB, Downloaded 184 times)
Re: select random numbers / [message #206849 is a reply to message #206840] Fri, 01 December 2006 14:33 Go to previous messageGo to next message
Littlefoot
Messages: 20891
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
SELECT statement in PL/SQL block requires an INTO clause (which you don't have). Furthermore, as you select multiple values, it isn't possible to fetch them into scalar local variables, but you'll need something else - ref cursor, possibly. Whatever it is, you didn't declare it.

I'm sorry, but I don't have Developer 6i installed so I can't try and check your statement that DBMS_RANDOM isn't recognized there; I *think* that it is more database than Forms Builder version dependant, but OK - I can't check it so I'd better keep my mouth shut.

This is the code which, eventually, might return something useful. I have no idea what your plans about it are, but ... that's your problem.
DECLARE
  l_empno EMP.empno%TYPE;
  l_ename EMP.ename%TYPE;
  par_rc  sys_REFCURSOR;
BEGIN
  OPEN par_rc FOR 
    SELECT * 
    FROM (SELECT empno, ename FROM EMP
          ORDER BY dbms_random.value) 
    WHERE ROWNUM < 10;
	
  FETCH par_rc INTO l_empno, l_ename;	
  WHILE par_rc%FOUND LOOP
    FETCH par_rc INTO l_empno, l_ename;
    -- your code should go here; I'm simply writing it to the screen
    dbms_output.put_line(l_empno ||' - ' || l_ename);
  END LOOP;
END;


Re: select random numbers / [message #206859 is a reply to message #206849] Fri, 01 December 2006 15:20 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I think Designer 6i uses a SQL-engine that does not allow an order by in an inner view, that would mean that this has nothing to do with dbms_random!

[Updated on: Fri, 01 December 2006 15:21]

Report message to a moderator

icon14.gif  Re: select random numbers / [message #206956 is a reply to message #206849] Sat, 02 December 2006 12:22 Go to previous messageGo to next message
lunate
Messages: 74
Registered: October 2006
Location: Pakistan
Member

thanx for reply ,dear littlefoot the code is produceing the same problem when i compile it.

my dear i write only a sample query just to show /caught error in developer .and then attached it ,i know we must use into clause.

any ways i got my output by using some othere tricks.

so thanx to all othere persons.
if some one othere body need are wanna to know my tricks or code through which i tickle my problem then pls tell me ,then i will post my code in response .ok

bbye/
Cool
Re: select random numbers / [message #207072 is a reply to message #206956] Mon, 04 December 2006 01:32 Go to previous message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
yeah please do show the tricks you used
Previous Topic: Minus to Not Exists
Next Topic: create table
Goto Forum:
  


Current Time: Sun Dec 04 06:41:56 CST 2016

Total time taken to generate the page: 0.15052 seconds