Requisitos:
- SQLLOADER
- SQLPLUS
- Extensão DBASE habilitada no php.ini
/* Exemplo de utilização da Classe loader */
$loader = new Loader();
//CAMINHO DO DBF
$loader->arquivo_dbf = 'dbf/transcdd.dbf';
//ARQUIVOS GERADOS PELO PROCESSO
$loader->arquivo_ctl = 'tmp/transcdd.ctl';
$loader->arquivo_csv = 'tmp/transcdd.csv';
$loader->arquivo_sql = 'tmp/transcdd.sql';
$loader->arquivo_log = 'tmp/transcdd.log';
//DADOS DE CONEXAO COM O ORACLE
$loader->tabela_oracle = 'transcdd_teste';
$loader->user_db = 'usuario';
$loader->pass_db = 'senha123';
$loader->host_db = '192.168.0.25';
$loader->port_db = '1521';
$loader->service = 'SERVORACLE';
$loader->separador = ';';
$loader->terminador = '||';
$loader->executar();
?>
/* Implementação da Classe loader */
class Loader{
public $arquivo_dbf;
public $arquivo_csv;
public $arquivo_sql;
public $arquivo_log;
public $tabela_oracle;
public $user_db;
public $pass_db;
public $host_db;
public $port_db;
public $service;
public $separador;
public $terminador;
public function __construct(){
set_time_limit(0);
}
public function executar(){
$this->abrir_dbf();
$this->obter_campos();
$this->criar_sql();
$this->criar_ctl();
$this->criar_csv();
$this->criar_tabela();
$this->popular_tabela();
$this->fechar_dbf();
}
public function abrir_dbf() {
$this->dbf = dbase_open( $this->arquivo_dbf, 0 );
if( $this->dbf ) {
return true;
} else {
return false;
}
}
public function fechar_dbf() {
return dbase_close($this->dbf);
}
//OBTEM INFORMACOES DO DBF
public function obter_campos() {
if ($this->dbf) {
$nf = dbase_numfields($this->dbf);
$column_info = dbase_get_header_info($this->dbf);
$this->campos = array();
$cont=0;
for ($e = 0; $e < $nf; $e++) {
$name = $column_info[$e]['name'];
$length = $column_info[$e]['length'];
$precision = $column_info[$e]['precision'];
switch( $column_info[$e]['type'] ) {
case 'date':
$this->campos['SQL'][$e] = "\"$name\" DATE NULL \r\n";
$this->campos['CTL'][$e] = "$name DATE \"YYYYMMDD\" NULLIF ($name=BLANKS) \r\n";
$this->campos['NOME'][$e] = $column_info[$e]['name'];
$this->campos['POS'][$e] = $cont;
$cont++;
break;
case 'character':
$this->campos['SQL'][$e] = "\"$name\" VARCHAR2($length BYTE) \r\n";
$this->campos['CTL'][$e] = "$name \r\n";
$this->campos['NOME'][$e] = $column_info[$e]['name'];
$this->campos['POS'][$e] = $cont;
$cont++;
break;
case 'boolean':
$this->campos['SQL'][$e] = "\"$name\" CHAR(1 BYTE) \r\n";
$this->campos['CTL'][$e] = "$name \r\n";
$this->campos['NOME'][$e] = $column_info[$e]['name'];
$this->campos['POS'][$e] = $cont;
$cont++;
break;
case 'memo':
break;
case 'number':
$this->campos['SQL'][$e] = "\"$name\" NUMBER($length,$precision) \r\n";
$this->campos['CTL'][$e] = $name . ( ($precision) ? "\"REPLACE(:$name, '.', ',')\" \r\n" : " \r\n" );
$this->campos['NOME'][$e] = $column_info[$e]['name'];
$this->campos['POS'][$e] = $cont;
$cont++;
break;
default:
$this->campos['SQL'][$e] = "\"$name\" \r\n";
$this->campos['CTL'][$e] = "$name \r\n";
$this->campos['NOME'][$e] = $column_info[$e]['name'];
$this->campos['POS'][$e] = $cont;
$cont++;
break;
}
}
}
}
//CRIA O CTL DA TABELA
public function criar_ctl() {
$campos = implode( ',', $this->campos['CTL'] );
$fp = fopen( $this->arquivo_ctl, 'w+' );
fwrite( $fp, "LOAD DATA \r\n" );
fwrite( $fp, "INFILE '$this->arquivo_csv' \r\n" );
fwrite( $fp, "INTO TABLE {$this->tabela_oracle} \r\n" );
fwrite( $fp, "FIELDS TERMINATED BY \"$this->separador\" OPTIONALLY ENCLOSED BY '$this->terminador' \r\n" );
fwrite( $fp, "TRAILING NULLCOLS \r\n" );
fwrite( $fp, "( \r\n" );
fwrite( $fp, $campos );
fwrite( $fp, ") \r\n" );
fclose( $fp );
}
//CRIA O CSV DA TABELA
public function criar_csv() {
$fp = fopen( $this->arquivo_csv, 'w+' );
$record_numbers = dbase_numrecords($this->dbf);
for ($i = 1; $i <= $record_numbers; $i++) {
$campos = array();
$rec = dbase_get_record($this->dbf, $i);
foreach ( $this->campos['POS'] as $e ) {
$campos[] = $this->terminador.$rec[$e].$this->terminador;
}
$campos = implode( "$this->separador", $campos );
fwrite( $fp, $campos."\r\n" );
}
fclose( $fp );
}
//CRIA O DDL DA TABELA
public function criar_sql(){
$campos = implode( ',', $this->campos['SQL'] );
$fo = fopen( $this->arquivo_sql, 'w+' );
fwrite( $fo, "DROP TABLE {$this->tabela_oracle}; \r\n" );
fwrite( $fo, "CREATE TABLE {$this->tabela_oracle} ( \r\n " . $campos ."); \r\n" );
fwrite( $fo, "EXIT; \r\n" );
fclose( $fo );
}
//EXECUTA A DDL DA TABELA
public function criar_tabela(){
$comando = "sqlplus -S $this->user_db/$this->pass_db@$this->host_db:$this->port_db/$this->service @{$this->arquivo_sql}";
exec( $comando );
}
//EXECUTA A CARGA DE DADOS
public function popular_tabela(){
$comando = "sqlldr $this->user_db/$this->pass_db@$this->host_db:$this->port_db/$this->service control=$this->arquivo_ctl log=$this->arquivo_log";
exec( $comando );
}
}
?>
