Home » SQL & PL/SQL » SQL & PL/SQL » drawing w/o replacement function
drawing w/o replacement function [message #254822] Sat, 28 July 2007 14:35 Go to next message
dpong
Messages: 73
Registered: January 2007
Member
Hi,

I'm trying to make use of dbms_random.value (1, N) where N is the maximum no allowed, inclusive.

What I'm trying to achieve here is to create a function that will take a number as the upper limit, then spit out the number that is supposedly to be random and not in the list of numbers that were already generated, for a particular set of rows in the table.

Any suggestions as to how to kick start ?

CREATE OR REPLACE
    FUNCTION draw_wo_replacement (Max IN number )
    RETURN NUMBER
    IS
    
BEGIN

    
    
END draw_wo_replacement;    
    


Re: drawing w/o replacement function [message #254833 is a reply to message #254822] Sun, 29 July 2007 01:00 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
I don't think it can be done without using a package to hold the values that have already been returned by the function. But if you can use packages, this will work.
CREATE OR REPLACE PACKAGE random_pkg IS

  TYPE int_tab_type IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
  
  g_random_table int_tab_type;

  PROCEDURE clear_set;
  
  FUNCTION next_val(p_max INTEGER)
   RETURN INTEGER;

END random_pkg;

CREATE OR REPLACE PACKAGE BODY random_pkg IS

  PROCEDURE clear_set
  IS
  BEGIN
      g_random_table.DELETE;
  END clear_set;
  
  FUNCTION next_val(p_max INTEGER)
   RETURN INTEGER
  IS
      v_int INTEGER;
  BEGIN
      IF g_random_table.COUNT >= p_max THEN
          raise_application_error(-20000, 'There are no more unique values under the limit');
      END IF;
      
      LOOP
          v_int := floor(dbms_random.value(1, p_max+1));
          IF NOT g_random_table.EXISTS(v_int) THEN
              g_random_table(v_int) := NULL;
              EXIT;
          END IF;
      END LOOP;
      RETURN v_int;
  END;
END random_pkg;

SQL> set serveroutput on
SQL> 
SQL> BEGIN
  2    random_pkg.clear_set;
  3  
  4    FOR i IN 1..11 LOOP
  5        dbms_output.put_line(random_pkg.next_val(10));
  6    END LOOP;
  7  END;
  8  /

8
2
7
9
10
4
1
5
3
6

BEGIN
  random_pkg.clear_set;

  FOR i IN 1..11 LOOP
      dbms_output.put_line(random_pkg.next_val(10));
  END LOOP;
END;

ORA-20000: There are no more unique values under the limit
ORA-06512: at "APPS.RANDOM_PKG", line 18
ORA-06512: at line 5

SQL> 
Re: drawing w/o replacement function [message #254836 is a reply to message #254833] Sun, 29 July 2007 01:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is actually not possible to do like this.
Choose 10, 100, 1000 as input values and tell how many times it takes to get the full set.

Regards
Michel
Re: drawing w/o replacement function [message #254935 is a reply to message #254836] Mon, 30 July 2007 01:57 Go to previous messageGo to next message
scottwmackey
Messages: 505
Registered: March 2005
Senior Member
Damn. There I go again. I never even thought to run something like the following before I posted to see if the solution was marginally viable.
Connected to Oracle8i Enterprise Edition Release 8.1.7.4.1 
Connected as APPS

SQL> 
SQL> 
SQL> DECLARE
  2    i INTEGER;
  3    j INTEGER;
  4  BEGIN
  5    random_pkg.clear_set;
  6  
  7    LOOP
  8        i := i+1;
  9        j := random_pkg.next_val(10);
 10    END LOOP;
 11  EXCEPTION
 12      WHEN OTHERS THEN NULL;
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 0.016 seconds

SQL> DECLARE
  2    i INTEGER;
  3    j INTEGER;
  4  BEGIN
  5    random_pkg.clear_set;
  6  
  7    LOOP
  8        i := i+1;
  9        j := random_pkg.next_val(100);
 10    END LOOP;
 11  EXCEPTION
 12      WHEN OTHERS THEN NULL;
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 0.016 seconds

SQL> DECLARE
  2    i INTEGER;
  3    j INTEGER;
  4  BEGIN
  5    random_pkg.clear_set;
  6  
  7    LOOP
  8        i := i+1;
  9        j := random_pkg.next_val(1000);
 10    END LOOP;
 11  EXCEPTION
 12      WHEN OTHERS THEN NULL;
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 0.172 seconds

SQL> DECLARE
  2    i INTEGER;
  3    j INTEGER;
  4  BEGIN
  5    random_pkg.clear_set;
  6  
  7    LOOP
  8        i := i+1;
  9        j := random_pkg.next_val(10000);
 10    END LOOP;
 11  EXCEPTION
 12      WHEN OTHERS THEN NULL;
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 1.937 seconds

SQL> DECLARE
  2    i INTEGER;
  3    j INTEGER;
  4  BEGIN
  5    random_pkg.clear_set;
  6  
  7    LOOP
  8        i := i+1;
  9        j := random_pkg.next_val(100000);
 10    END LOOP;
 11  EXCEPTION
 12      WHEN OTHERS THEN NULL;
 13  END;
 14  /

PL/SQL procedure successfully completed

Executed in 32.5 seconds

SQL> 
I should have kept in mind that most DBs are not running on anything nearly as fast as my Pentium 4, 2.53 GHz supercomputer, and that even if they were, 2 seconds for 10K numbers is completely unacceptable for most applications. And I should know after 10 years of doing this stuff that there is no way that any production DB can spare 32.5 seconds of CPU time for 100K numbers. But I am just a stupid programmer. There is certainly no way I could have figured out that the average number of loops to get the full set of numbers is roughly 10 times number of fully populated unique values (with the overwhelming majority of the work coming at the end), that the actual distribution of completion times for multiple runs of the same maximum number would fall into an asymmetrical bell curve. One would probably have had to have taken an introductory stats course for that kind of arcane knowledge. All along I was assuming that the function I provided could return millions of unique values in just seconds. Next time an OP asks for a "kick start", I will make sure I have a complete an infallible solution worked out before I post. I am so embarrassed with my shoddy work.
Re: drawing w/o replacement function [message #254941 is a reply to message #254935] Mon, 30 July 2007 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I should have kept in mind that most DBs are not running on anything nearly as fast as my Pentium 4, 2.53 GHz supercomputer, and that even if they were, 2 seconds for 10K numbers is completely unacceptable for most applications. And I should know after 10 years of doing this stuff that there is no way that any production DB can spare 32.5 seconds of CPU time for 100K numbers. But I am just a stupid programmer. There is certainly no way I could have figured out that the average number of loops to get the full set of numbers is roughly 10 times number of fully populated unique values (with the overwhelming majority of the work coming at the end), that the actual distribution of completion times for multiple runs of the same maximum number would fall into an asymmetrical bell curve. One would probably have had to have taken an introductory stats course for that kind of arcane knowledge. All along I was assuming that the function I provided could return millions of unique values in just seconds. Next time an OP asks for a "kick start", I will make sure I have a complete an infallible solution worked out before I post. I am so embarrassed with my shoddy work.

Wow! I'm really impressed by this paragraph. Really ./fa/1599/0/

Regards
Michel
Re: drawing w/o replacement function [message #254958 is a reply to message #254941] Mon, 30 July 2007 02:36 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Not shoddy at all. I have something worked out in a similar fashion. I'll post it up for comparison tomorrow morning when I'm back to the office.

It's totally understood here, regardless of the fact that I got a deg in Statistics. If you just picture it with the scenario of drawing a ball without replacement, one will surely understand.

-DP
Re: drawing w/o replacement function [message #255147 is a reply to message #254958] Mon, 30 July 2007 16:24 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
CREATE OR REPLACE
	PROCEDURE IRleadsApportioning AS
	
    --TYPE NumList IS TABLE OF NUMBER;		  
	leads number;
	N number;
	N2 number;			  			  
	duplicate integer := 1;
    nl NumList ; -- := NumList(1,3,5,7);
    nl_2 NumList;
	workload NUMBER;
    sales_agent_id IB_TECH_APPS_TEMPLATE.IRTEAM_MEMBER%TYPE;	
    inserted INTEGER := 0;
    random_no number := 0;
    index_I number;
    FSC IB_TECH_APPS.FOLLOWUP_SALES_CHANNEL%TYPE;
    IR_C number; 
    sumry_row IB_TECH_APPS_SUMMARY%ROWTYPE;
    
   -- FUNCTION EXISTED (RAND_NO number, n NumList)
   --    RETURN BOOLEAN;


    
    type curtype is ref cursor;
    cur_2 curtype;
    
    subtype sqltype is varchar2(1000);
    SQL_Statement sqltype; 
        
        			  
	CURSOR IB 
		 IS 
		   SELECT a.*,
		   		  b.IR_ID,
				  b.COUNTRY,
				  b.IRTEAM_MEMBER, 
                  b.IRTEAM_ALLOCATION 
		    FROM IB_TECH_APPS a INNER JOIN IR_TEAM_MEMBER b
		  	ON 	 a.FOLLOWUP_SALES_CHANNEL = b.IRTEAM;

			
 	CURSOR IB_2 (IR_TEAM_NAME IN CHAR)
		  IS
		  
            SELECT * FROM IB_TECH_APPS_SUMMARY
            WHERE IRTEAM = IR_TEAM_NAME;
            

    CURSOR IB_3 (sales_agent_id IN CHAR)
        IS
        
        SELECT COUNT(*) FROM IB_TECH_APPS_TEMPLATE 
        WHERE TRIM(IRTEAM_MEMBER) = sales_agent_id
        AND random_no != 0 ;


BEGIN

	 FOR IB_leads in IB LOOP
	 	 
         FSC := IB_leads.FOLLOWUP_SALES_CHANNEL;
        
        OPEN cur_2 for  SELECT * FROM IB_TECH_APPS_SUMMARY
                            WHERE IRTEAM = FSC;
        
        
--            FETCH IB_2 INTO FSC;
--            FSC:= IB_2.IRTEAM;

         FETCH cur_2 INTO  sumry_row;
          
         N := sumry_row.IB_COUNTS; 
         DBMS_OUTPUT.PUT_LINE ( 'N = ' || N );
         --N  := IB_2.IR_COUNTS;

         
        /* 2 scenarios: 
        leads <= IR_TEAM_MEMBERS 
        
        use draw_wo_replacement to randomize drawing  
        
        leads > IR_TEAM_MEMBERS (n) 
    
        #_of_leads mod n 
        */
        
        /*<<draw_wo_replacement>> */         
         WHILE (duplicate = 1) 
		 LOOP
               SELECT rand_gen (N) INTO N2 FROM dual; 
               
               IF EXISTED(N2, nl)   
			      THEN  Duplicate := 1;
				  	nl.extend;
					FOR i IN nl.FIRST .. nl.LAST
                    LOOP
                        IF nl(i) IS NULL THEN
                            nl(i) := N2;
                        END IF;
                        index_I := i;
                    END LOOP;    
    		   END IF;    
               
                        sales_agent_id := TRIM(IB_leads.IRTEAM_MEMBER) ;
                        OPEN IB_3 (sales_agent_id);
                            FETCH  IB_3 INTO INSERTED;     

                            DBMS_OUTPUT.PUT_LINE ( 'sumry_row.IR_COUNTS = ' || sumry_row.IR_COUNTS );
                                                
                            workload := ROUND( sumry_row.IR_COUNTS * IB_leads.IRTEAM_ALLOCATION / 100) ;         
        
                        CLOSE IB_3;
        
		                IF workload > inserted 
		                THEN SQL_statement := 'INSERT INTO IB_TECH_APPS_TEMPLATE SELECT a.*, :random_no FROM IB_TECH_APPS a';
                            execute immediate SQL_statement using N2;
                        
                         
				        dbms_output.put_line('random_no ' || random_no || ' inserted');
				            COMMIT;
				 
                       --inserted := inserted + 1;
				 
                        --ELSE GOTO <<draw_wo_replacement>>         
		                ELSE 
                             nl.delete(index_I);
                        
                             duplicate := 0;
         
		                END IF;

         END LOOP;
            
                 --nl := nl_2;
                 --dbms_output.put_line(nl.next(Index_I));
         
		-- nl.DELETE;
	 	 
	 END LOOP; 

END;
	



CREATE OR REPLACE
    FUNCTION rand_gen (Max IN number )
    RETURN NUMBER
    IS

	rand_gen NUMBER;    
BEGIN

	 SELECT floor(dbms_random.value(1, Max+1)) INTO rand_gen FROM DUAL;
	 
	 RETURN rand_gen; 
    
END rand_gen;    
    


CREATE OR REPLACE TYPE NumList IS TABLE OF NUMBER;


--BEGIN DROP_TABLE('IB_TECH_APPS_TEMPLATE'); END;
CREATE TABLE IB_TECH_APPS_TEMPLATE as
    SELECT a.*,
		   		  b.IR_ID,
                  b.COUNTRY,
                  b.IRTEAM_MEMBER, 
                  b.IRTEAM_ALLOCATION 
            FROM IB_TECH_APPS a INNER JOIN IR_TEAM_MEMBER b
          	ON 	 a.FOLLOWUP_SALES_CHANNEL = b.IRTEAM;
TRUNCATE TABLE IB_TECH_APPS_TEMPLATE;


CREATE OR REPLACE
FUNCTION EXISTED (RAND_NO IN number, n IN NumList )
    RETURN BOOLEAN 
    IS 
	
--	   duplicate BOOLEAN DEFAULT FALSE;

	   counter INTEGER;
	   i INTEGER ;
BEGIN

   counter := n.FIRST;
   FOR i IN n.FIRST .. n.LAST
   LOOP
      
	  
	  IF COUNTER = RAND_NO
	  THEN  return n.EXISTS(i) ;
	  ELSE	  
	  	  counter := n.NEXT(counter);	  
--		  	  	i := i + 1;	  
	  END IF;


   END LOOP;
END;
/


[Updated on: Mon, 30 July 2007 21:02]

Report message to a moderator

Re: drawing w/o replacement function [message #255151 is a reply to message #255147] Mon, 30 July 2007 19:03 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
don't understand why I'm getting the following error.

ORA-06531: Reference to uninitialized collection
ORA-06512: at "DM_METRICS.IRLEADSAPPORTIONING", line 109
ORA-06512: at line 3

That's around the line.
nl.DELETE;
Re: drawing w/o replacement function [message #255216 is a reply to message #255151] Tue, 31 July 2007 01:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because you don't initialize nl.
You just define it but don't initialize it.
You have to do something like "nl := numlist();".

Regards
Michel
Re: drawing w/o replacement function [message #256289 is a reply to message #254833] Fri, 03 August 2007 13:52 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
Is it true that I can't have the nl stop reinitializing without using a package?

Re: drawing w/o replacement function [message #256295 is a reply to message #256289] Fri, 03 August 2007 14:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand the question.

Regards
Michel
Re: drawing w/o replacement function [message #256331 is a reply to message #256295] Fri, 03 August 2007 17:20 Go to previous messageGo to next message
dpong
Messages: 73
Registered: January 2007
Member
The reason I am asking this I can't seem to have a numList that houses a unique list of numbers that were already drawn without have re-initialized by itself every time in the loop.

And Scott was saying I will have to use package to do that.
Re: drawing w/o replacement function [message #256364 is a reply to message #256331] Sat, 04 August 2007 01:10 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you want to use number by number each time you call your function, then you have to store somewhere the list and then pick up in this list.
Otherwise, each time you get a number you regenerate the list maybe in another order...
It could be real table (permanent or temporary) instead of a package variable depending on the scope you want for this list (session, multi-session same user, multi-user...) and the size of the list.

Regards
Michel
Previous Topic: Curious if this code is correct.
Next Topic: what is the command of object source
Goto Forum:
  


Current Time: Thu Dec 08 16:33:24 CST 2016

Total time taken to generate the page: 0.19942 seconds