Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Simply-

how do I get table info with the DBI using a desc, or similar function? If I issue a desc TABLE_NAME through the DBI, oracle throws an error, which is odd because at a SQL prompt this command has no problems.....

Thoughts?
Cheers-
John

here is my non-funtional code:(which works with a regular query, 'select sysdate from dual') 

    my $select_x = "describe $table_name";
    $dbh = get_connection($me, $mepw);
    $sth = $dbh->prepare($select_x);
    my $rc  = $sth->execute;
    while (my (@rows) = $sth->fetchrow_array) {
      print "$rows[0] -- $rows[1]\n";
    }

[download]

2001-03-03 Edit by Corion : Added CODE tags.

Replies are listed 'Best First'.
Re: DBI + Oracle describe problem
by PsychoSpunk (Hermit) on Feb 22, 2001 at 05:02 UTC
    Quoth the Cheetah: 
    $sth = $dbh->table_info;

    [download]
    Returns an active statement handle that can be used to fetch information about tables and views that exist in the database.

    Also note that it says the method is experimental and may change.

    Also note that the full documentation that I am reading from the cheetah is also in 'perldoc DBI'.

    ALL HAIL BRAK!!!

[reply]
[d/l]
Re: DBI + Oracle describe problem
by aardvark (Pilgrim) on Feb 22, 2001 at 05:18 UTC
    You can also get a lot of good info from the Statement Handle Attributes. (p. 227 of Cheetah)

    $sth->{NAME}
    $sth->{TYPE}
    $sth->{NULLABLE}

    Also the 'Perl' chapter in the O'Reilly MySQL & mSQL book as a few cool scripts that will print out info about your database and tables. One script, tabledump.cgi (on page 159), will show you information about a specific table, much like a DESC. There is nothing MySQL specific about these scripts so you can give them a go with Oracle.

    Get Strong Together!!
[reply]
(dkubb) Re: (2) Database Independant DESC
by dkubb (Deacon) on Feb 22, 2001 at 11:40 UTC

    This is a solution I posted about a database independant way of getting all the columns in a table, by emulating DESC.

    Please let me know how it works for you, I am curious to know how it works with Oracle and other databases.

[reply]
Re: DBI + Oracle describe problem
by unixwzrd (Beadle) on Feb 22, 2001 at 14:08 UTC
    If what you want is lots of information about the columns in a particular table that you can view, try this: 
    my $sql_statement =
      "select distinct column_name, column_id
         from all_tab_columns
         where table_name = upper('$table_name')
         order by column_id";

    [download]
    You can adjust what information you want by selecting additional columns from the ALL_TAB_COLUMNS view. Lots of goodies there.

    I use this for dynamically creating persistent instances of objects and building the class attributes dynamically. Once I tighten up the code a bit more, I'll be posting my DB_Object class module here.

    The reason for the select distinct is that in Oracle, if there is a SYNONYM, it will be listed along with the table information and it will be identical. COLUMN_NAME and COLUMN_ID are the minimums to get away with for the distinct.

    Mike

    "The two most common elements in the universe are hydrogen... and stupidity."
    Harlan Ellison
[reply]
[d/l]

Back to Seekers of Perl Wisdom