Impotar dados por shell

[orehon 0]

E ai galera tudo bom? Espero que sim!Estou com um problema que seria o seguinte... tenho uma tabela no postgres e uma replica dela no Oracle!O que eu preciso fazer é exportar o que esta nessa tabela do postgres para a tabela do Oracle! ( eu já consigo exportar e importar... sendo que so pelo htmldb)E isso é muito ruim porque eu preciso automatizar esse processo! Não quero ninguem pegando um arquivo em algum lugar e indo lá e mechendo naquele wizard chato para importar os dados!Teria alguma forma de importar um arquvo de texto ( a formatacao do arquivo de texto pode ser qualquer uma eu consigo manupular isso bem facil usando o postgres ) o problema todo gira em torno do Oracle!Entao resumindo, COMO POSSO FAZER UMA IMPORTACAO REMOTA NO ORACLE A PARTIR DE UM COMANDO SHELL!Abraços!lucascarvalho (at) gmail (dot) com

[ska_ska 0]

o sql loader não atende sua necessidade?não me lembro da sintaxe, mas acho q eh assim:sqlldr usuario@servidor/senha control=arquivo_ctl.CTL log=log.LOG direct=y

[alphamek 2]

tenho um exemplo aqui, muito bom que pode lhe ajudar, está em inglês e é para EXPORT, mais é fácil converter para IMPORT.

 

#!/bin/ksh# +----------------------------------------------------------------------------+# |						  Jeffrey M. Hunter								 |# |					  jhunter@idevelopment.info							 |# |						 www.idevelopment.info							  |# |----------------------------------------------------------------------------|# |	  Copyright (c) 1998-2006 Jeffrey M. Hunter. All rights reserved.	   |# |----------------------------------------------------------------------------|# | DATABASE : Oracle														  |# | FILE	 : export_backup_online_full_9i									|# | CLASS	: UNIX Shell Scripts											  |# | PURPOSE  : Used to perform a logical backup of an Oracle database using	|# |			EXPORT. By default, this script performs a full (consistent)	|# |			logical backup of the database using a dynamically created	  |# |			parameter file that gets written to a temporary directory and   |# |			run through EXP.												|# |																			|# | PARAMETERS : DBA_USERNAME	   Database username EXP will use to login to |# |								 the database. This user must have the DBA  |# |								 role.									  |# |			  DBA_PASSWORD	   Database password EXP will use to login to |# |								 the database.							  |# |			  TNS_ALIAS		  TNS connect string to the target database. |# | USAGE	:																 |# |																			|# |export_backup_online_full_9i.ksh "DBA_USERNAME" "DBA_PASSWORD" "TNS_ALIAS"  |# |																			|# | NOTE	 : As with any code, ensure to test this script in a development   |# |			environment before attempting to run it in production.		  |# +----------------------------------------------------------------------------+# +----------------------------------------------------------------------------+# | ************************************************************************** |# | *					 DEFINE ALL GLOBAL VARIABLES						* |# | ************************************************************************** |# +----------------------------------------------------------------------------+VERSION="1.0"SCRIPT_NAME_FULL=$0SCRIPT_NAME=${SCRIPT_NAME_FULL##*/}START_DATE=`date`DATE_LOG=`date +%y%m%d:%H%M`HOSTNAME=`hostname`HOSTNAME_UPPER=`hostname | tr -s '[:lower:]' '[:upper:]'`# +----------------------------------------------------------------------------+# | ************************************************************************** |# | *					 DEFINE ALL GLOBAL FUNCTIONS						* |# | ************************************************************************** |# +----------------------------------------------------------------------------+showUsage() {	echo "USAGE:"	echo "export_backup_online_full_9i.ksh \"DBA_USERNAME\" \"DBA_PASSWORD\" \"TNS_ALIAS\""	echo " "}showSignonBanner() {	echo " "	echo "$SCRIPT_NAME - Version $VERSION"	echo "Copyright (c) 1998-2005 Jeffrey M. Hunter. All rights reserved."	echo " "}switchOracleEnv() {	DB_ENTRY_HOME="$1"	# +---------------------------------------------------------+	# | Ensure that "OLDHOME" is non-null. The following is a   |	# | portable way of saying, if oracle_home is not set, then |	# | return a zero. this will then set OLDHOME to the $PATH  |	# | variable. If ORACLE_HOME is set, then set OLDHOME to	|	# | that of the old $ORACLE_HOME. Another way to perform	|	# | this check is using a less portable statement:		  |	# |	   if [ ${ORACLE_HOME:-0} = 0 ]; then				|	# +---------------------------------------------------------+	if [ ${ORACLE_HOME=0} = 0 ]; then	  OLDHOME=$PATH	else	  OLDHOME=$ORACLE_HOME	fi	# +--------------------------------------------------------+	# | Now that we backed up the old $ORACLE_HOME, lets set   |	# | the environment with the new $ORACLE_HOME.			 |	# +--------------------------------------------------------+	ORACLE_HOME=$DB_ENTRY_HOME	export ORACLE_HOME	# +------------------------------------------+	# | Set $PATH								|	# +------------------------------------------+	case "$PATH" in	  *$OLDHOME/bin*)  PATH=`echo $PATH | sed "s;$OLDHOME/bin;$DB_ENTRY_HOME/bin;g"`;;	  *$DB_ENTRY_HOME/bin*) ;;	  *:)			  PATH=${PATH}$DB_ENTRY_HOME/bin:;;	  "")			  PATH=$DB_ENTRY_HOME/bin;;	  *)			   PATH=$PATH:$DB_ENTRY_HOME/bin;;	esac	export PATH 	# +------------------------------------------+	# | Set $LD_LIBRARY_PATH					 |	# +------------------------------------------+	case "$LD_LIBRARY_PATH" in	  *$OLDHOME/lib*)	LD_LIBRARY_PATH=`echo $LD_LIBRARY_PATH | sed "s;$OLDHOME/lib;$DB_ENTRY_HOME/lib;g"`;;	  *$DB_ENTRY_HOME/lib*);;	  *:)				LD_LIBRARY_PATH=${LD_LIBRARY_PATH}$DB_ENTRY_HOME/lib:;;	  "")				LD_LIBRARY_PATH=$DB_ENTRY_HOME/lib;;	  *)				 LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$DB_ENTRY_HOME/lib;;	esac	export LD_LIBRARY_PATH	ORACLE_DOC=$DB_ENTRY_HOME/doc	export ORACLE_DOC 	ORACLE_PATH=$DB_ENTRY_HOME/rdbms/admin:$DB_ENTRY_HOME/sqlplus/admin:$HOME/common/bin	export ORACLE_PATH	ORA_NLS33=$DB_ENTRY_HOME/ocommon/nls/admin/data	export ORA_NLS33	TNS_ADMIN=$DB_ENTRY_HOME/network/admin	export TNS_ADMIN}getOSName() {	case `uname -s` in		*BSD)			UNIX_TYPE="bsd";;		SunOS)			case `uname -r` in				5.*) UNIX_TYPE="solaris";;				  *) UNIX_TYPE"sunos";;			esac		   ;;		Linux)			UNIX_TYPE="linux";;		HP-UX)			UNIX_TYPE="hpux";;		AIX)			UNIX_TYPE="aix";;		*) UNIX_TYPE="unknown";;	esac}# +----------------------------------------------------------------------------+# | ************************************************************************** |# | *						  SCRIPT STARTS HERE							* |# | ************************************************************************** |# +----------------------------------------------------------------------------+showSignonBannerif [[ $# -eq 3 ]]; then	export DBA_USERNAME=$1	export DBA_PASSWORD=$2	export TNS_ALIAS=$3else	showUsage	exit 2figetOSNameif [[ $UNIX_TYPE = "linux" ]]; then	ORATAB_FILE="/etc/oratab"elif [[ $UNIX_TYPE = "solaris" ]];then	ORATAB_FILE="/var/opt/oracle/oratab"else	ORATAB_FILE="/etc/oratab"fiif [[ $ORABACKUP = "" ]]; then	ORABACKUP=/u03/oraexp/$TNS_ALIASfiif [[ $ORALOG = "" ]]; then	ORALOG=/u01/app/oracle/custom/oracle/logfiif [[ $ORATMP = "" ]]; then	ORATMP=/u01/app/oracle/custom/oracle/tempfiPARFILE=${ORATMP}/${SCRIPT_NAME}_${TNS_ALIAS}.parfileLOGFILE=${ORALOG}/${SCRIPT_NAME}_${TNS_ALIAS}.logDUMPFILE=${ORABACKUP}/exp_full_${TNS_ALIAS}.dmpecho "======================================================"echo "SCRIPT	   : $SCRIPT_NAME"echo "VERSION	  : $VERSION"echo "START TIME   : $START_DATE"echo "HOST		 : $HOSTNAME"echo "UNIX TYPE	: $UNIX_TYPE"echo "TARGET DB	: $TNS_ALIAS"echo "EXP USER	 : $DBA_USERNAME"echo "ORABACKUP	: $ORABACKUP"echo "ORALOG	   : $ORALOG"echo "ORATMP	   : $ORATMP"echo "PARFILE	  : $PARFILE"echo "LOGFILE	  : $LOGFILE"echo "DUMPFILE	 : $DUMPFILE"echo "======================================================"echo " "for DB_ENTRY in `cat ${ORATAB_FILE} | grep -v '^\#' | grep -v '^\*' | cut -d":" -f1,2`do	ORACLE_SID=`echo $DB_ENTRY | cut -d":" -f1`	export ORACLE_SID	if [[ $ORACLE_SID = $TNS_ALIAS ]]; then		NEW_ORACLE_HOME=`echo $DB_ENTRY | cut -d":" -f2`		export NEW_ORACLE_HOME		switchOracleEnv $NEW_ORACLE_HOME		echo "=============================================================="		echo "  - Working on : ${ORACLE_SID}  (${NEW_ORACLE_HOME})"		echo "=============================================================="		echo " "		echo "=============================================================="		echo "  - REMOVE OLD LOG AND PARAMETER FILE(S)."		echo "=============================================================="		rm -f $PARFILE		rm -f $LOGFILE		echo "=============================================================="		echo "  - WRITE EXPORT PARAMETER FILE."		echo "=============================================================="		echo "userid=${DBA_USERNAME}/${DBA_PASSWORD}@${TNS_ALIAS}buffer=50000000file=${DUMPFILE}compress=ngrants=yindexes=ydirect=nolog=${LOGFILE}rows=yconsistent=yfull=ytriggers=ystatistics=noneconstraints=y" > $PARFILE		echo "=============================================================="		echo "  - MOVE OLD EXPORT (DUMP) FILE."		echo "=============================================================="		rm -f ${DUMPFILE}.backup		mv $DUMPFILE ${DUMPFILE}.backup		echo "=============================================================="		echo "  -  PERFORM EXPORT."		echo "=============================================================="		exp parfile=$PARFILE	fidoneEND_DATE=`date`echo "======================================================"echo "FINISH TIME : $END_DATE"echo "======================================================"exit

Abraços, B)