DBAsupport.com Forums - Powered by vBulletin
Results 1 to 10 of 10

Thread: Random Varchar

  1. #1
    Join Date
    Sep 2002
    Posts
    376

    Random Varchar

    Hi,

    I want to generate random String (Alphanumeric). I need this to Set the password for database users.

    I tried dbms_random.string, but i am getting Special Characters.

    How to get only Alphanumeric string ?
    Last edited by bang_dba; 03-09-2004 at 10:41 AM.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's not very elegant, but you could generate a very long random string including special characters, then use Translate() to strip the special chanracters, then take the leftmost 8 or however many of the the resulting string
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434

    Re: Random Varchar

    Originally posted by bang_dba
    I want to generate random String (Alphanumeric). I need this to Set the password for database users.
    Wass the need for it? ( Coz users will any ways be given thier passwords right ? )
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439

    Re: Random Varchar

    Originally posted by bang_dba
    I tried dbms_random.string, but i am getting Special Characters.
    What kind of special characters? Can you provide some ascii codes for those characters? Which option have you used for "opt" parameter to DBMS_RANDOM.STRING() function? If you look at the source code of the DBMS_RANDOM package you'll se that it can't possibly return anything with special (nonprintable) characters (ascii codes less than 32). So which caharacters in the return string are actualy causing you troubles?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Sep 2002
    Posts
    376
    hi,

    jmodic,

    i am using x to get the alphanumeric value....

    I think x is supposed to give me aphanumeric value

    [

    ('x','X' any alpha-numeric characters (upper) )

    ]


    But i am getting some special characters as well

    Here is the query and o/p

    select dbms_random.string('X',10) from dual

    -------------
    HOK2F:?@?P

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    I'm not seeing this behaviour on 9.2.0.3.0 ...

    Code:
    select dbms_random.string('X',10) from dba_objects
    where rownum < 100
    /
    
    DBMS_RANDOM.STRING('X',10)
    ----------------------------
    
    MQXBBO699Y
    4CLPWV6DPV
    B7QGHN11LL
    WIPN8U5GRS
    7K6K1LEKJ9
    0LZB93EZDX
    A3SYLD7R5O
    ODYWGCT411
    X2NLW6M3MM
    RGJKQTXEC6
    A2YH5GYTFB
    5C10I7AE28
    ZZ7P8F7VAS
    J37ZJ3JLEC
    HRGONANTZS
    1XEJVJH5DG
    9HXNAOFLLX
    KR6IWM9VI7
    79TOCFMO5X
    JS3BZSFV99
    77L5BYKOFE
    QPF75LXKCG
    LZDO8KKMD6
    VH33DJ7DCR
    TDEK4PE0WC
    91VNYGS6RP
    4G5II48MKI
    DRYQ1PORP4
    WUN9UUXVA5
    8CUIACIN7P
    7RRU2ARJFN
    LO0FYZA2ES
    9GZE0N3GD8
    FVJGYZ5FT9
    HIJBK6B4KO
    4R4647EUAG
    J6FY3JN5UR
    8RLY3UNAJK
    C87IEJ3XB9
    FOL98L27GF
    XKW6M8MRHN
    SR2HYYQJWE
    S3UG0HCN5K
    AKVFRZGT9W
    7VOEQEMCS4
    0VABWX19TS
    TWCC3JQJB8
    UXEKSJHGTS
    LDJFV6IXOL
    R9PM9AFFX2
    NGA7PTFRSP
    QNMD7P2VAR
    4HT1U38UZQ
    M4X3ZB6Y7O
    W9LAYYYSHJ
    2JWQ4HKI1P
    J2WQTQZSBE
    DZ4V23GSUB
    MADWHRMIM1
    0P04EQZK2A
    DCSLE4MDJY
    T7TFM0TJ8G
    RZICJRP4F5
    DU4MX1F0A0
    QFPGGNQUNM
    6QKKMW4IPU
    AZ8NLQ89FW
    1JYXW7N866
    EVUZQZR85N
    VP0IOB6YPV
    JSDLK2Y7TT
    3IDKT0ZZWQ
    E4ZGEPV4VM
    261KSZYS6K
    TVRI8PQ0IK
    2K2PBYI8QG
    O0DVDVICD0
    KTUO9MTPGW
    AYPYBE5JSJ
    GMTKF6QAVL
    PATC9C6VES
    VS6FEZFJE6
    FYZ7LZ0LY5
    8J0A9JBVTQ
    60CZZUM83T
    D68USAAUY7
    HEFE25CHB8
    PLKQ5ETLGO
    TDR7Z1UA47
    RSA9496OHB
    3CUNKIK2F9
    Z37QEGF9O7
    D4AMHUVQFM
    PXYACDWC9F
    AUDCX69QJT
    GJTRXL7PEZ
    5AZEKCG42O
    WP32JABKC1
    OB99ENI50Q
    
    99 rows selected.
    I expect that you are hitting bug 1541060. Metalink has a fix.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Jan 2004
    Posts
    162
    Good bug spot. One wonders why noone actually called that function to see whether it returned what it was supposed to before releasing it.

    In general however I find the flag style input of DBMS_RANDOM.STRING to be rather restrictive and was tempted to write my own version which allows you to pass the character list, something like...
    Code:
    Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
    With the Partitioning, OLAP and Oracle Data Mining options
    JServer Release 9.2.0.4.0 - Production
    
    SQL> CREATE OR REPLACE FUNCTION random_string ( 
      2    p_len IN NUMBER, 
      3    p_chr IN VARCHAR2) 
      4    RETURN VARCHAR2 
      5  IS 
      6    v_chr_len NUMBER := LENGTH (p_chr); 
      7    v_rnd_str VARCHAR2 (32767); 
      8  BEGIN 
      9    FOR i IN 1..p_len LOOP 
     10      v_rnd_str := v_rnd_str || SUBSTR (p_chr, 
     11        TRUNC (v_chr_len * DBMS_RANDOM.VALUE) + 1, 1); 
     12    END LOOP; 
     13    RETURN v_rnd_str; 
     14  END;
     15  /
    
    Function created.
    
    SQL> SET SERVEROUTPUT ON;
    SQL> DECLARE 
      2    v_abc VARCHAR2 (26) := 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'; 
      3    v_num VARCHAR2 (10) := '0123456789'; 
      4  BEGIN 
      5    FOR i IN 1..10 LOOP 
      6      DBMS_OUTPUT.PUT_LINE ( 
      7        random_string (10, v_num || v_abc || LOWER (v_abc))); 
      8    END LOOP; 
      9  END;
     10  /
    m4GeyeYamj
    sMcYXMpohm
    No0M6dWR4J
    o4Q3rjsrlr
    XEPcb9bp6X
    j5Hrytin52
    xNfDmj5VPI
    EM37PzycBD
    BGWMAmy8rx
    lqEQ4JxU81
    
    PL/SQL procedure successfully completed.
    
    SQL>
    Note that Oracle does not recommend DBMS_RANDOM for use in cryptography. The enhanced DBMS_OBFUSCATION_TOOLKIT (in 9i) and the new DBMS_CRYPTO (in 10g I believe - at least the name is shorter) have routines specifically for the generation of cryptographically strong keys.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by padders
    Good bug spot. One wonders why noone actually called that function to see whether it returned what it was supposed to before releasing it.
    Maybe they missed it because they read the version 9.2 documentation, where the function isn't even listed.

    * glurk *

    Seems adequate for assigning new passwords for users -- if i was given a password like that I'd be pretty quick to change it for my dog's name, or whatever.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Jun 2002
    Location
    Longmont, Colorado
    Posts
    174

    Padders...Ur brilliant

    Loved your random_string function.
    I'm using it to generate password from db users who have no way to do it them selfs and FDA requires new password every 90 days.

    You that man and I just wanted to give you "props"....that slang for "credit".

    Thanks man.

  10. #10
    Join Date
    Jan 2004
    Posts
    162
    You'll be pleased to know the licensing options are very reasonable ;-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width