This content has been marked as final. Show 2 replies
The situation looks quite unusual indeed. These days Oracle does quite a bit of internal transformations and optimizations, so a trace might give you a starting point for an investigation.
There is an easy work around though.
Create a function
and then simply issue
CREATE OR REPLACE FUNCTION EVERY_TIME ( p IN NUMBER ) RETURN VARCHAR2 AS r number; BEGIN with rnd as (select dbms_random.value (1,951) r from dual) select ln into r from ( select line ln , rownum rw from somepersons ) where rw = (select floor (r) from rnd);
update morevalues set mvspeid = every_time(mvspeid)
P.S. I have not tried this myself…
Edited by: Iordan Iotzov on Mar 22, 2013 4:00 PM
Edited by: Iordan Iotzov on Mar 22, 2013 4:01 PM
thanks for your help!
Its wierd thou that this seems not to be solvable simpler than with plsql.
Getting into a procedural solution, I also tried going with a for-loop with a select on the table and an update for each row. Worked, but was a bit too complicated. I like your solution with a function better. The query can be simplified a bit thou:
create or replace function getRndSPEID return NUMBER is a number; begin select speid into a from ( select rownum rw, speid from somepersons ) where rw = (select floor( dbms_random.value (1,951)) from dual); return a; end;
Its really strange thou, that also there, when you just put
you get the null or 2 result rows as I mentions in my first post.
where rw = floor( dbms_random.value( 1, 951 ) ) ;
PS. I leave this thread open, just in case there still IS another solution just with one DML statement. :)