#! /usr/local/bin/oraperl 
# @(#) sqlx.pl : subs for 1-line SQL statements & col/table name validation
# @(#) SunOS deep sun4m (jstander)  
# @(#) loc: /home/jstander/bin
# @(#) $Revision 1.0 $ (jstander 12.07.93): new 
###############################################################################
# author
#	Jeff.Stander@ml.csiro.au CSIRO Division Of Fisheries, Hobart,
#	Tasmania 7001, Australia
###############################################################################

#-----------------------------------------------------------------------------
# Name 
#	ok_tab
# Description:
#	Check if table is in database
# Synopsis:
#	&ok_tab(table_name)
# Returns: name of table (in caps) if valid, else 0;
#-----------------------------------------------------------------------------
sub ok_tab {
        local($tabn) = @_;
        $tabn || die "&ok_tab requires argument: &ok_tab(\$table_name)\n";
        $tabn =~ tr/a-z/A-Z/;
        $qh="SELECT table_name from tabs where table_name='$tabn'";
        &sqlx( $qh );
}

#-----------------------------------------------------------------------------
# Name 
#	ok_col
# Description:
# 	Check if column is in table 
# Synopsis:
#	&ok_col(table_name,column_name)
# Returns: length of column in bytes if valid column, else 0
#-----------------------------------------------------------------------------
sub ok_col {
        local($tabn,$coln) = @_;
	die "&ok_col requires arguments: &ok_tab(\$table_name,\$column_name)\n" 
		if !$tabn && !$coln;
        $tabn =~ tr/a-z/A-Z/;
        $coln =~ tr/a-z/A-Z/;
	$qh="SELECT data_length from cols where table_name='$tabn' and column_name='$coln'"; 
	(&sqlx ( $qh ))[0];
}

#-----------------------------------------------------------------------------
# Name 
#	sqlx	
# Description:
# 	Extract first line of output from SQL query
# Synopsis:
#	sqlx(query_handle , [ $lda ] )
#	sqlx(query_handle , [ user/passwd ], [ database ] )
# Description:
#	Process the SQL statement and assign the first line returned
#	by oracle to the subroutine return value.  If arg#2 is an open
#	database pointer, don't open or close the database, else open and 
#	close database using optional user/passwd and database arguments.
# Returns list of results of query, 0 on error;
#------------------------------------------------------------------------------
sub sqlx {
	local($sqlstmt,$passwd,$db) = @_;
	local($sqlx_lda,$nologon);
	($user,$passwd)=split('/',$passwd);
	$nologon = sprintf("%s",$user) =~ /^0x/ || 0;
	if ( $nologon ) { 
		$sqlx_lda=$user;
	}
	else {
		$sqlx_lda = &ora_login($db,$user,$passwd) || return 0; 
	}
	$csr = &ora_open($sqlx_lda,$sqlstmt) || return 0; 
	@result = &ora_fetch($csr);
	&ora_close($csr);
	&ora_logoff($sqlx_lda) if !$nologon; 
	@result;
}
1;