This is part of The Pile, a partial archive of some open source mailing lists and newsgroups.
Subject: Column names for table_info
From: Ronald Warden <ronald.warden@ag.gov.bc.ca>
Date: Wed, 30 Aug 2000 08:36:31 -0700
I am attempting to create a perl script that read the table definitions
in one Oracle database then uses that information to create the
identical table in a second database instance. This is for management
information application where summary data is being created by stored
procedures in the operation database, then my script moves the data to
an MIS data mart. Since this is the first iteration I want to make the
extract script general - read the table definition in database 1,
,create the table in database 2, populate table in database 2 with data
from table in database 1.
The Very Fine Manual talks about experimental method table_info. I can
make the method works fine, so that I can get the table name, the column
data_type, column size and whether the column is null able.
What I can't get is the column name!
The type_info doesn't appear to help either. Has anyone every got the
column names for a table using any of these methods? The question to Tim
is whether the table_info or type_info does return the column names. If
they don't I would suggest that could be an enhancement.
===
Subject: Re: Column names for table_info
From: Chris Winters <cwinters@intes.net>
Date: Wed, 30 Aug 2000 12:16:21 -0400
Ronald Warden (ronald.warden@ag.gov.bc.ca) [000830 12:08]:
>
> I am attempting to create a perl script that read the table definitions
> in one Oracle database then uses that information to create the
> identical table in a second database instance. This is for management
> information application where summary data is being created by stored
> procedures in the operation database, then my script moves the data to
> an MIS data mart. Since this is the first iteration I want to make the
> extract script general - read the table definition in database 1,
> ,create the table in database 2, populate table in database 2 with data
> from table in database 1.
>
> The Very Fine Manual talks about experimental method table_info. I can
> make the method works fine, so that I can get the table name, the column
> data_type, column size and whether the column is null able.
>
> What I can't get is the column name!
>
> The type_info doesn't appear to help either. Has anyone every got the
> column names for a table using any of these methods? The question to Tim
> is whether the table_info or type_info does return the column names. If
> they don't I would suggest that could be an enhancement.
>
> Cheers
If you do a simple failed statement and check the {NAME} 'property' of
the statement handle, you'll get what you need. Here's a chunk that
saves the type information into a hash for later perusal ( slightly
modified to take out any confusing stuff :)
my ( $sth );
eval {
$sth = $dbh->prepare( "SELECT * FROM $table WHERE 1 = 0" );
$sth->execute;
};
my $fields = $sth->{NAME};
my $types = $sth->{TYPE};
warn " (DB): List of fields: ", join( ", ", @{ $fields } ), "\n" if ( $DBG );
for ( my $i = 0; $i < scalar @{ $fields }; $i++ ) {
$TYPE_INFO{ $type_idx }->{ $fields->[ $i ] } = $types->[ $i ];
}
HTH
Chris
--
Chris Winters
Senior Internet Developer intes.net
cwinters@intes.net http://www.intes.net/
Integrated hardware/software solutions to make the Internet work for you.
===
Subject: Re: Column names for table_info
From: Hardy Merrill <merrill@missioncriticallinux.com>
Date: Wed, 30 Aug 2000 12:14:31 -0400
Ronald Warden [ronald.warden@ag.gov.bc.ca] wrote:
>
> I am attempting to create a perl script that read the table definitions
> in one Oracle database then uses that information to create the
> identical table in a second database instance. This is for management
> information application where summary data is being created by stored
> procedures in the operation database, then my script moves the data to
> an MIS data mart. Since this is the first iteration I want to make the
> extract script general - read the table definition in database 1,
> ,create the table in database 2, populate table in database 2 with data
> from table in database 1.
>
> The Very Fine Manual talks about experimental method table_info. I can
> make the method works fine, so that I can get the table name, the column
> data_type, column size and whether the column is null able.
>
> What I can't get is the column name!
Look at the attributes available for the *statement* handle -
you'll see an attribute called "NAME" that will give you the
column names of all the selected columns. "table_info()" is
a method you can run against a *database* handle, and doesn't
include the names of the columns. You may have to do a
"select * from my_table" to get all(*) the columns and a
statement handle you can use(from prepare), and then
execute the "NAME" attibute on that statment handle to
get the names of the columns - you get the jist of it.
Pages 145-151 in the "Programming the Perl DBI" gives a nice
little dbi application you can run to print out the tables in
a database, and the columns and attributes of each column in
each table.
HTH.
--
Hardy Merrill
Mission Critical Linux, Inc.
http://www.missioncriticallinux.com
===
Subject: RE: Column names for table_info
From: "Ihnen, David" <David_Ihnen@mycio.com>
Date: Wed, 30 Aug 2000 08:55:12 -0700
Its a workaround, but I believe I remember seeing that fetchall_hashref or
something like that returns a hash of the column names from a select. Maybe
that would be handy.
David
===
Subject: Re: Column names for table_info
From: "Michael A. Chase" <mchase@ix.netcom.com>
Date: Wed, 30 Aug 2000 15:47:09 -0700
In this case you'd probably be better off reading the Oracle data dictionary
instead. Some of the storage parameters may be critical to making the table
work and are not available in table_info. As long as you are Oracle
specific, you might as well get everything directly.
all_tab_columns
===