package DBUtils;

# Author : Gavin Sherlock
# Date   : 14th December 2000

# This package exports functions that provide simple database utilities

use vars qw (@ISA @EXPORT_OK);
use strict;
use DBI;

require Exporter;
@ISA = qw(Exporter);
@EXPORT_OK = qw(GetColumnSizes GetColumns);

################################################################################################
sub GetColumnSizes{
################################################################################################
# This subroutine gets the sizes of the all the columns within a table, that is passed in as an
# argument.  It then populates a hash (passed in by reference), with the column names as the
# keys, and the column widths as the values.

# Usage : GetColumnSizes($dbh, \%columns, $table);

    my ($dbh, $columnSizesRef, $table) = @_;

    my ($column, $length, $type, $precision, $scale);

    my $sth = $dbh->prepare("SELECT column_name, Data_length, DATA_TYPE, DATA_PRECISION, DATA_SCALE
                                    FROM all_tab_columns
                                    where table_name = ?");

    $sth->execute(uc($table));

    $sth->bind_columns(undef, \$column, \$length, \$type, \$precision, \$scale);
    
    while($sth->fetch){

	if ($type ne "NUMBER"){
	    
	    $$columnSizesRef{$column} = $length;

	}else{

	    if ($scale){$precision++}; # take account of the decimal point

	    $$columnSizesRef{$column} = $precision;

	}
	
    }
    
    $sth->finish();

}

################################################################################################
sub GetColumns{
################################################################################################
# This subroutine simply gets the names of the all the columns within a table, that is passed in as an
# argument.  It then populates an array (passed in by reference), with the column names.

    my ($dbh, $columnsRef, $table) = @_;

    my ($column);

    my $sth = $dbh->prepare("SELECT column_name
                             FROM all_tab_columns
                             where table_name = ?");

    $sth->execute($table);

    $sth->bind_columns(\$column);
    
    while($sth->fetch){

	push (@{$columnsRef}, $column);
	
    }
    
    $sth->finish();

}