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

Hi guys, I started to ask this question last week but I somehow was logged in as an anonymous monk - considering the mess I've made of understanding this I might have been better served staying anonymous! That said all I'm trying to do is extract the column names from any given table named in an Oracle database. The rough code that I've been using follows :- 
print "\n\t\t***** S T A R T S *****\n" ;

$dbh = DBI->connect("dbi:Oracle:$db","$uname","$password", {
               PrintError => 0,
               RaiseError => 0
               }   
) or die "\n\tCan't connect to $db :: $DBI::errstr\n" ;

$sth = $dbh->column_info(undef,undef,"$table","$column") or die "\n\tD
+idnae work min :: $!\n" ;

$sth->execute or die "\n\tCan't execute DBI command :: $DBI::errstr\n"
+ ;

#------------------------------------------#
#    Retrieve the returned rows of data    #
#------------------------------------------#

while ( @row = $sth->fetchrow_array() ) {
      $detail = $row[0] ;
      $row_count ++ ;

      print "\n\tValue\t$detail\n" ;

}

print "\n\tThere were $row_count rows returned\n" ;

[download]
The $table variable points to a table called USER$ - where our user names live & $column is set to COLUMN_NAME. This script runs but no rows are returned. I take it that I'm doing something particularily daft but can't see what it is can someone give me some pointers please?

Replies are listed 'Best First'.
Re: Finding column info from Oracle tables
by swampyankee (Parson) on Jul 03, 2007 at 15:10 UTC

    Information about tables -- including their column names -- can be accessed by using the DESCRIBE command. Your SQL would look like this: 

    DESCRIBE name_of_the_table;

    [download]

    When using Oracle, you can also find information about a table by querying the USER_TAB_COLUMNS.

    I hope that's vaguely in the ballpark of what you need.

    emc

    Any New York City or Connecticut area jobs? I'm currently unemployed.

    There are some enterprises in which a careful disorderliness is the true method.

    —Herman Melville
[reply]
[d/l]
      I've tried using "DESCRIBE USER\$" in a prepare statement and though it seems to "prepare" okay it fails on execution. Full error is "Can't execute SQL statement :: ORA-00900: invalid SQL statement (DBD ERROR: OCIStmtExecute)". I thought that would work but as it didn't I was hoping that I had the wrong syntax. Thanks anyway.
[reply]
        Try this: 
        select column_name,data_type from user_tab_columns where table_name='<your tablename>'
[reply]
Re: Finding column info from Oracle tables
by Mr. Muskrat (Canon) on Jul 03, 2007 at 15:27 UTC
    swampyankee is correct but column_info will work if you give it more information. You need to give it a schema! Also, I have never had much luck with using a column but if you leave it blank you can get at the COLUMN_NAME by accessing $row[3].
[reply]
[d/l]

Back to Seekers of Perl Wisdom