| Converting table into control file [message #38442] |
Wed, 17 April 2002 19:51  |
krishna
Messages: 141
Registered: October 1998
|
Senior Member |
|
|
Hi!
Can anyone suggest me to convert my oracle table into control file so that i can convert back to oracle table using sql loader. B'coz i need to copy my table contents to remote system.
Regards
Krishna
|
|
|
|
| Re: Converting table into control file [message #38549 is a reply to message #38442] |
Fri, 26 April 2002 04:26  |
Saga
Messages: 51
Registered: April 2002
|
Member |
|
|
I'm sending some file hope it might help. Copy the contents to generate the appropriate files
File 1: sqlldr_exp.cmd
@echo off
if "%1"=="" goto :usage
if "%2"=="" goto :usage
sqlplus -s %1 @sqlldr_exp.sql %2
goto :done
:Usage
echo "usage sqlldr_exp un/pw [[tables|views]]"
echo "example sqlldr_exp scott/tiger emp dept"
echo "description Select over standard out all rows of table or view with "
echo " columns delimited by tabs."
:done
File 2: sqlldr_exp.sql
set wrap off
set linesize 500
set feedback off
set pagesize 0
set verify off
set termout off
spool saga.sql
prompt prompt LOAD DATA
prompt prompt INFILE *
prompt prompt INTO TABLE &1
prompt prompt REPLACE
prompt prompt FIELDS TERMINATED BY ','
prompt prompt (
select 'prompt ' || decode(column_id,1,'',',') || lower(column_name)
from user_tab_columns
where table_name = upper('&1')
/
prompt prompt )
prompt prompt BEGINDATA
prompt select
select lower(column_name)||'||chr(44)||'
from user_tab_columns
where table_name = upper('&1') and
column_id != (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
/
select lower(column_name)
from user_tab_columns
where table_name = upper('&1') and
column_id = (select max(column_id) from user_tab_columns where
table_name = upper('&1'))
/
prompt from &1
prompt /
spool off
set termout on
spool sg.sql
@saga.sql
spool off
exit
Hope it will help and do let me know.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
