Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Join Date
    Mar 2004
    Posts
    28

    Exclamation Unanswered: Urgent, please help

    hi, im using oracle9i and jdbc.
    i have 2 questions,

    would any one be able to tell me a query to retrieve the details of a named table, for example assuming a table name, i.e employee how would i be able to ascertain the names and types of fields (department varchar(20) idnum varchar(10) etc.

    and secondly i need to find out the names of tables that contain a primary key and (if possible) the primary key within that table assuming that there is only one primary key per table,
    thanks so much for your help and time
    regards.

  2. #2
    Join Date
    Oct 2003
    Location
    St.Louis,MO
    Posts
    120

    Re: Urgent, please help

    [QUOTE][SIZE=1]Originally posted by b737
    hi, im using oracle9i and jdbc.
    i have 2 questions,

    ----1. Desc table_name
    would any one be able to tell me a query to retrieve the details of a named table, for example assuming a table name, i.e employee how would i be able to ascertain the names and types of fields (department varchar(20) idnum varchar(10) etc.

  3. #3
    Join Date
    Mar 2004
    Location
    Tokyo
    Posts
    1

    java.sql

    hi,

    how abt using java.sql package?
    use ResultMetaData and DatabaseMetaData.

    regards,
    nise

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4

    Re: Urgent, please help

    Originally posted by b737
    I need to find out the names of tables that contain a primary key and (if possible) the primary key within that table assuming that there is only one primary key per table
    PHP Code:
    select table_nameconstraint_name
      from user_constraints  
      where constraint_type 
    'P'

  5. #5
    Join Date
    Mar 2004
    Posts
    28

    Re: Urgent, please help

    thanks for the reply guys, i know of the describe table_name query in sql but it wont work when i pass it through in jdbc, strange.

    regards.

  6. #6
    Join Date
    Mar 2004
    Posts
    28

    Re: Urgent, please help

    Originally posted by Littlefoot
    PHP Code:
    select table_nameconstraint_name
      from user_constraints  
      where constraint_type 
    'P'
    this works nicely, but for the constraint name it returns something similar
    to this:

    CONSTRAINT_NAME
    ---------------
    SYS_C0075456
    SYS_C0075457

    is there a way to convert the above two constraint names to a comprehensible format?
    thanks
    regards

  7. #7
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Constraint names like "SYS_C0075456" usually represent NOT NULL constraints (as people often declare them withount naming them, so they get such "unreadable" names).
    To see what they really mean, add a column "search condition" to the query.

  8. #8
    Join Date
    Mar 2004
    Posts
    28
    im a bit of a rookie at this sort of thing, so could you explain in more detail how i would do this?

    thanks for the reply

  9. #9
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure ...
    PHP Code:
    select table_nameconstraint_nameconstraint_typesearch_condition
      from user_constraints  
      where constraint_type 
    'P'
    Only, I'm afraid you won't get anything (as the search_condition) if the constraint type is 'P' (primary key).

    To see what the query returns in other cases, omit the WHERE clause.

  10. #10
    Join Date
    Mar 2004
    Posts
    1

    Arrow Re: Urgent, please help

    Originally posted by b737
    hi, im using oracle9i and jdbc.
    i have 2 questions,

    would any one be able to tell me a query to retrieve the details of a named table, for example assuming a table name, i.e employee how would i be able to ascertain the names and types of fields (department varchar(20) idnum varchar(10) etc.

    and secondly i need to find out the names of tables that contain a primary key and (if possible) the primary key within that table assuming that there is only one primary key per table,
    thanks so much for your help and time
    regards.
    FOR First one

    select column_name,data_type from user_tab_columns
    where table_name='T_RMD_TRAN_LOAN

  11. #11
    Join Date
    Mar 2004
    Posts
    28

    Re: Urgent, please help

    thanks for that, it works swell.
    cheers

  12. #12
    Join Date
    Mar 2004
    Posts
    28
    Originally posted by Littlefoot
    Sure ...
    PHP Code:
    select table_nameconstraint_nameconstraint_typesearch_condition
      from user_constraints  
      where constraint_type 
    'P'
    Only, I'm afraid you won't get anything (as the search_condition) if the constraint type is 'P' (primary key).

    To see what the query returns in other cases, omit the WHERE clause.
    i gave it a try but im still receiving that annoying SYS_C constraint name string.
    thanks anyway.
    regards

  13. #13
    Join Date
    Mar 2004
    Posts
    28
    sorry bout this again, but i think i meant to post that i need the name of the column that contains a primary key not the name of the constraint.

    thanks a mill,

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Try this one
    PHP Code:
    SELECT c.table_namec.constraint_namec.constraint_typeo.column_name
      FROM user_constraints c
    user_cons_columns o
     WHERE o
    .constraint_name c.constraint_name
       
    AND o.table_name c.table_name
       
    AND o.owner c.owner

  15. #15
    Join Date
    Mar 2004
    Posts
    28
    it seems to have spat out what i need, ill tweek it a little, and use it, thanks v.much for that
    cheers

    regards from ireland.

Posting Permissions

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