Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2004
    Posts
    35

    Unanswered: getting the max length

    i am using pl/sql and i need to do some validation regarding the max length of the string. but the problem is that i have to refer back to the database for the length so that if the database change, my code don't change.
    is there any way to get the max length in the field of the table?
    thanks

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    Do a query on the all_tab_columns table, specifically the char_length column. Just do a select * from that for your table, and see what you need!

    Hope that helps!
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Nov 2004
    Posts
    35
    i don't quite understand you.
    can give eg?
    lets say i got a table call Person with column Name(varchar2(66)) and Sex(char(1))
    so how i retrieve the max length of the field let say Name?

  4. #4
    Join Date
    Jan 2004
    Posts
    492
    If you have a varchar2(66), wouldn't you say the max length of the field is 66? If thats what you want, use something like this:
    Code:
    select data_length
    into v_max_length
    from all_tab_columns
    where table_name = 'YOUR_TABLE'
    and column_name = 'NAME'
    Or else do:
    Code:
    select max(length(name))
    into v_max_length
    from your_table
    Hope that helps more!
    Oracle OCPI (Certified Practicing Idiot)

  5. #5
    Join Date
    Nov 2004
    Posts
    35
    power la.. althought i have not tried that yet but i think should be able to..

  6. #6
    Join Date
    Nov 2004
    Posts
    35
    Quote Originally Posted by ss659
    If you have a varchar2(66), wouldn't you say the max length of the field is 66? If thats what you want, use something like this:
    Code:
    select data_length
    into v_max_length
    from all_tab_columns
    where table_name = 'YOUR_TABLE'
    and column_name = 'NAME'
    Or else do:
    Code:
    select max(length(name))
    into v_max_length
    from your_table
    Hope that helps more!
    it failed. the first code return nothing n the 2nd code only return the max length of the data in the field.

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    I can assure you it works - try this. Outside of your PL/SQL code, launch SQL*PLUS or TOAD or whatever strikes your fancy.

    Just issue this query:
    Code:
    select * from 
    all_tab_columns
    This is the data dictionary for all of the tables. You would eventually add the table_name to your where clause as I did above. The data length column is the column that stores....well the length of the column.

    I am assuming that you replaced 'MY_TABLE' with the actual name of the table and 'NAME' with the actual column name above. Try it again, b/c I am 100% positive this works.
    Oracle OCPI (Certified Practicing Idiot)

  8. #8
    Join Date
    Nov 2004
    Posts
    35
    ok..
    but i am currently at home. all my work are at my office..
    it will be the first thing i do when i reach my office tomorrow..
    stay tune for my ans..
    lolx
    cheers

  9. #9
    Join Date
    Nov 2004
    Posts
    35
    what the hell..
    y are u guys so pro?
    it really works..
    can exchange your brain with mine?
    hehe

  10. #10
    Join Date
    Jan 2004
    Posts
    492
    lol - well after the extended weekend (from last thursday to sunday) i doubt you would want mine anymore!

    But I'm glad you got something that works! Enjoy!
    Oracle OCPI (Certified Practicing Idiot)

Posting Permissions

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