Home » SQL & PL/SQL » SQL & PL/SQL » how to insert data into a column
how to insert data into a column [message #22024] Wed, 18 September 2002 03:08 Go to next message
ksr
Messages: 112
Registered: January 2002
Senior Member
Hi,

I have a table with 2 columns..

empname varchar2(10)
eno number(5)

Now how do i generate a random 5 digit number for the eno (the number should not repeat).

Can anyone give me the code for this.

Presently i have only the empnames entered into the table, so i want to go through a loop and assign a random 5 digit number to the eno column.

Thanks in advance..
Re: how to insert data into a column [message #22033 is a reply to message #22024] Wed, 18 September 2002 09:50 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
declare
  v_random  pls_integer;
  v_count   pls_integer;
  cursor csrEmp is select eno from emp for update;
begin
  for r in csrEmp loop
 
    v_count := 1;
 
    while v_count = 1 loop 
 
      v_random := trunc(dbms_random.value(10000, 99999));
 
      select count(*)
        into v_count
        from emp
       where eno = v_random;
 
      if v_count = 0 then
        update emp
           set eno = v_random
         where current of csrEmp;
      end if;
 
    end loop;
  
 end loop;    
end;
/
Re: how to insert data into a column [message #203091 is a reply to message #22033] Mon, 13 November 2006 19:45 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
thanks Todd for your solution. Today i am searching to random update and writing query then found your's.
This saved my time.
Please keep it up
Regards
Rupi
Re: how to insert data into a column [message #203143 is a reply to message #203091] Tue, 14 November 2006 01:25 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If only it wasn't about random 5-digit numbers, you could have used a sequence:
CREATE SEQUENCE seq_test START WITH 10000;

UPDATE your_table SET eno = seq_test.NEXTVAL;
Re: how to insert data into a column [message #203362 is a reply to message #203143] Tue, 14 November 2006 21:28 Go to previous messageGo to next message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Thanks for this littlefoot this is also will be handy in future for me. Really appreciate your feedback.

Can we also generate random number with alphabet.
like
a12345
a23242
b23233
v23232
n23232

I want to generate random passwords for 5000 users.
any suggestion please

Regards
Rupinder
Re: how to insert data into a column [message #204099 is a reply to message #22024] Fri, 17 November 2006 16:11 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Below is a routine that I used for a similar process (random alpha checksums) and should do what your want with minor modifications.

declare
   CNT          NUMBER;
   CNT1         NUMBER;
   CNT2         NUMBER;
   CNT3         NUMBER;
   CNT4         NUMBER;
   check_str    varchar2(4);
   CHARS        VARCHAR2(36);

   cursor get_check is
      select store_cd,loc_cd
      from loc_check
      where check_string is null;


BEGIN

   CHARS := '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ';
   -- Startup the random number generator
   dbms_RANDOM.INITIALIZE((sysdate - to_date('01011970','mmddyyyy'))*24*3600); 

   -- Loop through the rows
   FOR pnt IN get_check LOOP

      -- Generate four random characters 
      cnt := 1;
      WHILE cnt = 1 LOOP
         CNT1 := MOD(ABS(DBMS_RANDOM.RANDOM),36)+1;
         CNT2 := MOD(ABS(DBMS_RANDOM.RANDOM),36)+1;
         CNT3 := MOD(ABS(DBMS_RANDOM.RANDOM),36)+1;
         CNT4 := MOD(ABS(DBMS_RANDOM.RANDOM),36)+1;
         check_str := SUBSTR(CHARS,CNT1,1) || SUBSTR(CHARS,CNT2,1) || SUBSTR(CHARS,CNT3,1) || SUBSTR(CHARS,CNT4,1);

         SELECT COUNT(*)
         INTO CNT
            FROM loc_check A
            WHERE A.CHECK_STRING = CHECK_STR;
      END LOOP;
      UPDATE LOC_CHECK A 
         SET A.CHECK_STRING = CHECK_STR
         WHERE A.STORE_CD = PNT.STORE_CD
         AND A.LOC_CD = PNT.LOC_CD;
   END LOOP;

   -- Terminate the random generator
   DBMS_RANDOM.TERMINATE;

   COMMIT;
END;


Re: how to insert data into a column [message #204246 is a reply to message #204099] Sun, 19 November 2006 18:25 Go to previous message
rmalhi
Messages: 42
Registered: May 2006
Location: Australia
Member
Thanks Bill Really appreciate your feedback.keep it up
Previous Topic: Explict Cursor
Next Topic: "&" in insert statements
Goto Forum:
  


Current Time: Thu Dec 08 10:39:40 CST 2016

Total time taken to generate the page: 0.07224 seconds