1 2 Previous Next 23 Replies Latest reply on Dec 8, 2017 8:46 AM by Jarkko Turpeinen Go to original post
      • 15. Re: Create a Function with Random Social Security Number With Dashes
        DSteele41

        I've Got This to work; outside of implementing to Function

         

        select (To_Char(dbms_random.value(100000000,999999999), '000g00g0000','nls_numeric_characters=.-'))

        From sys.dual;

        1 person found this helpful
        • 16. Re: Create a Function with Random Social Security Number With Dashes
          mathguy

          Just one person's opinion: awarding yourself marks for "helpful answers" makes you look foolish.

           

          The way you are using DBMS_RANDOM.VALUE, it will generate fractional numbers. Your instinct to use ROUND wasn't wrong, you were just using it in the wrong place; you could use it around the DBMS_RANDOM.VALUE expression. However, when you use TO_CHAR with that format mask, you are simply instructing Oracle to use the integer part and to ignore the decimal part; so you don't really need to round.

           

          Are you clearly aware, though, that there is a chance that this function will NOT produce UNIQUE numbers? Did you understand what Frank and the others told you - repeatedly? That you can't have truly "random" and "unique" at the same time? If you roll two dice, it IS possible (actually the probability is 1/6, or a little more than 16%) that the two numbers you will get WILL be duplicates, right? (1/6 is the probability of rolling a "pair" - same number on both dice.)

           

          You also have not explained why you need "random" for MADE-UP data. What is wrong with giving the first person the number 100-00-0000, the next one 100-00-0001, etc.? What will you be able to test, regarding your overall system, with random numbers, that you will NOT be able to test with sequential numbers (which can be "guaranteed" to be unique much more easily than for random numbers)?

           

          If "using random numbers" is part of your school project - if your teacher requires you to practice generating random numbers - you may use a "poor man's random number generator" - since you are not actually working with important data, you can use "weak" (or "flawed") randomizing algorithm.

           

          For example: Take any collection of "social security" numbers, even in three parts: 100-00-0000, 100-00-0001, etc. (very easy to generate). Then replace the three parts A, B, C (A is the three-digit number that is the first part of the SSN, B is the two-digit number in the middle and C is the four-digit at the end), and replace A, B, C with

           

          mod(427*A + 302, 1000),    mod(31*B +84, 100)    and    mod(7229*C + 5063, 10000).

           

          This way you just need to generate numbers IN SEQUENCE from 100000000 to wherever you need them; separate into three parts, apply those arithmetic transformations and then concatenate the numbers back into a single SSN. They will look pretty random, and they are guaranteed to be unique.

           

          You also mentioned encryption... what for? For a made-up system? Is that another part of the project, and the teacher wants you to use encryption? (If you are building a real-life system, not a school project, and you want to test encryption for the "real" database, that is fine; but why does it matter that you would be "testing" encryption, in your toy system, on SSN's that are generated in sequence? I guess that's what John meant with his question.)

           

          Good luck!      mathguy

          1 person found this helpful
          • 17. Re: Create a Function with Random Social Security Number With Dashes
            Jarkko Turpeinen

            Hi,

             

            random is nice, but! Here's sequence approach done the way where you don't get gaps (i guess it is important for an ssn).

             

            create sequence ssn start with 10000000
            ;
            -- table with 10 generated ssn's
            create table ssns as
            select to_char( ssn.nextval, '0000g00g000', 'nls_numeric_characters='',-''' ) ssn, cast (null as date ) as assigned 
            from dual connect  by level <= 10
            ;
            alter table ssns add primary key (ssn)
            ;
            create or replace 
            function AssignedSsn return varchar2 is
            begin
              for r in (
                select ssn from ssns where assigned is null for update of assigned skip locked
              )loop
                update ssns set assigned = sysdate where ssn = r.ssn;
                return r.ssn;
              end loop;
            end;
            /
            
            
            Sequence SSN created.
            Table SSNS created.
            Table SSNS altered.
            Function ASSIGNEDSSN compiled
            

             

            testing...

             

            PL/SQL procedure successfully completed.
            
            sn: 0100-00-000
            
            PL/SQL procedure successfully completed.
            
            ssn: 0100-00-001
            
            PL/SQL procedure successfully completed.
            
            ssn: 0100-00-002
            
            Assigned ssns by this session
            USER is "JARKKO"
            
            SSN          ASSIGNED 
            ------------ ----------
             0100-00-000 04.12.2017
             0100-00-001 04.12.2017
             0100-00-002 04.12.2017
             0100-00-003           
             0100-00-004           
             0100-00-005           
             0100-00-006           
             0100-00-007           
             0100-00-008           
             0100-00-009           
            
            
             10 rows selected 
            
            

             

            and another session

             

            PL/SQL procedure successfully completed.
            ssn: 0100-00-003
            
            PL/SQL procedure successfully completed.
            
            ssn: 0100-00-004
            
            PL/SQL procedure successfully completed.
            
            ssn: 0100-00-005
            
            Assigned ssns by this session
            USER is "SYS"
            
            SSN          ASSIGNED 
            ------------ ----------
             0100-00-000           
             0100-00-001           
             0100-00-002           
             0100-00-003 04.12.2017
             0100-00-004 04.12.2017
             0100-00-005 04.12.2017
             0100-00-006           
             0100-00-007           
             0100-00-008           
             0100-00-009           
            
            
             10 rows selected 
            
            Rollback complete.
            
            drop function AssignedSsn
            ;
            drop sequence ssn
            ;
            drop table ssns purge
            ;
            
            
            Function ASSIGNEDSSN dropped.
            Sequence SSN dropped.
            Table SSNS dropped.
            
            1 person found this helpful
            • 18. Re: Create a Function with Random Social Security Number With Dashes
              Manik

              Something like this (for non-repeated random ssn)  .. Suggestions for improvement welcome!

              ----------------

               

              set timing on
              drop table ssns purge;
              create table ssns
              as
                    select LTRIM ( TO_CHAR ( ROWNUM + 1 - 1, '000g00g0000', 'nls_numeric_characters=.-' ) ) ssn, 0 used
                      from (with dataset
                         as (  select ROWNUM r
                             from DUAL
                           connect by ROWNUM <= 1000)
                        select a.r
                        from dataset a, dataset b, dataset c);
              --commit;
              create index idx_used
                    on ssns ( used )
                    parallel;
              alter table ssns add constraint pk_ssns primary key
              (
                ssn
              )
              parallel;
              create or replace function non_repeated_random_ssn
                    return varchar2
              as
                    pragma autonomous_transaction;
                    v_ssn   varchar2 ( 11 );
              begin
                    --       update ssns
                    --        set used   = 1
                    --        where ssn = (select ssn
                    --             from ssns
                    --            where used = 0
                    --              and ROWNUM = 1)
                    --      returning ssn
                    --         into v_ssn;
                    for rec in (   select ssn
                           from ssns
                          where used = 0
                            and ROWNUM = 1
                         for update of used )
                    loop
                          v_ssn   := rec.ssn;
                          update ssns
                           set used   = 1
                           where ssn = rec.ssn;
                    end loop;
                    commit;
                    return v_ssn;
              end;
              

               

              select non_repeated_random_ssn from dual connect by rownum<=8;

               

              select * from ssns where used =1;

               

              SSN                USED

              ----------------------------

              196-70-5601   1

              326-85-5537   1

              675-95-2393   1

              423-45-7441   1

              582-17-1377   1

              477-75-3745   1

              546-45-0937   1

              900-80-3569   1

               

              Cheers,

              Manik.

              1 person found this helpful
              • 19. Re: Create a Function with Random Social Security Number With Dashes
                DSteele41

                mathguy I will heed your advice on "Awarding" my self Helful Answers;  I'd like to iterate that my Goal is not racking up "Points" it is to heed to everyones superior advice and knowledge on here.

                 

                In regards to T0_Char with Round;  Clear on To_char utilizing whole number and not decimals;

                Also I do see now that Random (could be repeated) and Unique would not repeat ;  I am more unless taking this as a Test Project for my learning and Can use Sequence to generate Mock SSNs  but was just thinking it would be good to learn to Create a Unique SSN with my Mock dummy data (strictly for learning)

                 

                could you elaborate a little on using Mod with A,B,C I follow the Mod but would I combine that with sequence to increment by 1 or 2 or by however many to combine with Mod function and then taking those Transformed numbers and Concatenate them to a SSN??

                 

                In regards to Encryption, I was not looking to do that I was Honestly just replying to that gentleman who was answering semi-rashly I thought
                I get you and his point of it being Mock / dummy/ test data but again it was More for Learning

                 

                 

                Hey again very redundant but Thank YOU

                • 20. Re: Create a Function with Random Social Security Number With Dashes
                  DSteele41

                  Jarkko Turpeinen

                  First thank You Sir

                  The above is a little above my knowledge level thus far; I will be going through a PL/SQL Course here in January;  I had been through a very beginners course but was a few years ago so I'm taking time to review an 11g pl/sql book binder

                  1 question that will show this is ; "How do you Call the Function" to utilize

                  and again currently I'm using Oracle APEX to create a Transactions Application and was looking to utilize some Functions, Triggers, and Stored Procedures; ?

                  an also  what is "r"  For r IN (    is that just an Alias or ?  sorry if that sounds like a very dumb question but I'm Pretty New

                  Thanks again for Reply Sir

                  • 21. Re: Create a Function with Random Social Security Number With Dashes
                    mathguy

                    Hi,

                     

                    Never mind trying to generate each portion of the SSN separately (as I was suggesting); the first two parts may end up being constant for a small number of generated SSN's, unless you do even more work.

                     

                    Instead, you can generate SSN's all at once, in one pass - use TO_CHAR with a format mask using -  (dash) as the "thousands" separator to get the desired format. Here is the code and the output; the explanation is shown at the bottom of this Reply.

                     

                    select level, to_char( mod(132038297 * level + 1340482, 1000000000), 'FM000G00G0000', q'{nls_numeric_characters = '.-'}') as ssn
                    from   dual
                    connect by level <= 12
                    ;

                     

                     

                    LEVEL  SSN       
                    -----  ------------
                        1  133-37-8779
                        2  265-41-7076
                        3  397-45-5373
                        4  529-49-3670
                        5  661-53-1967
                        6  793-57-0264
                        7  925-60-8561
                        8  057-64-6858
                        9  189-68-5155
                       10  321-72-3452
                       11  453-76-1749
                       12  585-80-0046

                     

                    OK, so why does this work? Consider the function f(x) = mod ( ax + b , 1000000000 ).  As long as a is relatively prime to 10 (which means a is odd and not divisible by 5),  f is one-to-one (injective) on the range of values x = 1, 2, 3, ... , 999999999. This is an easy result in number theory. So, if LEVEL (in my hierarchical query) runs over that range, the nine-digit values returned by that MOD function are DISTINCT, which is what you wanted. Are they RANDOM? Absolutely not! Even so, though: Are you, by just looking at the output and not knowing ANYTHING about the way I created them, able to recognize they are not RANDOM? I strongly doubt it.

                     

                    Are the specific values ( a = 132038297 , b = 1340482 ) important in any way? Absolutely not. The only condition is that a be an odd number and not divisible by 5.

                     

                    Would I use something like this to generate meaningful numbers in real life, which SHOULD be random? ABSOLUTELY NOT! If there is enough money to be made form it, it is quite easy to "break the code" and figure out the non-randomness of my so-called "random" numbers. Yet, to generate random numbers for real-life applications, the process starts with something like this, and then it goes much, much further to prevent "easy" breaking of the code.

                    1 person found this helpful
                    • 22. Re: Create a Function with Random Social Security Number With Dashes
                      DSteele41

                      I will have to do more reading up on the Mod function and how it arrives at a remaider, I do understand the Function f(x) = ax+ b

                      but the 100000000, is part I dont understand.

                       

                      also if I generated say 1000 rows how would I call a row (a Not perfectly unique SSN) to my table to a specific Table

                      Not sure if that is a Question to be answered in this Thread

                      Thanks sir

                      mathguy

                      • 23. Re: Create a Function with Random Social Security Number With Dashes
                        Jarkko Turpeinen

                        Have a good time while learning!

                         

                        what is "r" For r IN ( is that just an Alias

                        That is arbitrary variable name for a collection of column values for a single row of result set.

                         

                        So that you can reference columns by name in your code. For example r.hire_date if the query contains such column.

                         

                        Official explanation says it is a record. https://docs.oracle.com/cloud/latest/db112/LNPLS/cursor_for_loop_statement.htm#LNPLS1155

                        record

                        Name for the loop index that the cursor FOR LOOP statement implicitly declares as a %ROWTYPE record variable of the type that cursor or select_statement returns.

                        record is local to the cursor FOR LOOP statement. Statements inside the loop can reference record and its fields. They can reference virtual columns only by aliases. Statements outside the loop cannot reference record. After the cursor FOR LOOP statement runs, record is undefined.

                         

                        edit: to avoid confusions, collection was a wrong choice of word. Record is correct.

                        See: https://docs.oracle.com/cloud/latest/db112/LNPLS/composites.htm#LNPLS005 

                        In a collection, the internal components always have the same data type, and are called elements. You can access each element of a collection variable by its unique index, with this syntax: variable_name(index). To create a collection variable, you either define a collection type and then create a variable of that type or use %TYPE.

                        In a record, the internal components can have different data types, and are called fields. You can access each field of a record variable by its name, with this syntax: variable_name.field_name. To create a record variable, you either define a RECORD type and then create a variable of that type or use %ROWTYPE or %TYPE.

                        You can create a collection of records, and a record that contains collections.

                        1 2 Previous Next