-
Does anyone have a script that will spool out a ctl file by giving a table name ? I'm looking for something that is easy and straight forward. I would appreciate any help.
My e-mail is misticawi@aol.com
-
I don't think a table name is enough. You also need the datafile and the strucure of the data in the datafile.
-
ofcourse u need the structure of the table. I'm looking for a shell script that will just ask you to enter the table name then it will do the rest.
-
-- =========
--
-- Prepares a SQL*Loader Control FILE FOR a TABLE already existing IN the
-- DATABASE. The script accepts the TABLE name AND automatically creates a FILE
-- WITH the TABLE name AND extension 'ctl'.
-- DEFAULT choices FOR the FILE are AS follows (ALTER TO your needs):
-- Delimiter: comma (',')
-- INFILE FILE extension: .dat
-- DATE format: 'MM/DD/YY'
--
-- You may define the Loader Data Types OF the other Data Types BY revising the
-- DECODE FUNCTION pertaining TO them.
--
--
-- =============
-- Requirements:
-- =============
--
-- SELECT PRIVILEGES ON the TABLE.
REM ---------------------------------------------------------------------------
REM EXAMPLE:
REM SQL> start control.sql emp
REM
REM LOAD DATA
REM INFILE 'EMP.dat'
REM INTO TABLE EMP
REM FIELDS TERMINATED BY ','
REM (
REM
REM EMPNO
REM , ENAME
REM , JOB
REM , MGR
REM , HIREDATE DATE "MM/DD/YY"
REM , SAL
REM , COMM
REM , DEPTNO
REM
REM )
REM
REM ---------------------------------------------------------------------------
REM DISCLAIMER:
REM This script is provided for educational purposes only. It is NOT
REM supported by Oracle World Wide Technical Support.
REM The script has been tested and appears to work as intended.
REM You should always run new scripts on a test instance initially.
REM --------------------------------------------------------------------------
REM Main text of script follows:
SET heading OFF
SET verify OFF
SET feedback OFF
SET show OFF
SET trim OFF
SET pages 0
SET CONCAT ON
spool &&table_name..ctl
SELECT
'LOAD DATA'||CHR(10)
||'INFILE '''||LOWER(table_name)||'.dat'' '||CHR(10)
||'INTO TABLE '||table_name||CHR(10)
||'FIELDS TERMINATED BY ''&field_termitated_by'' '||CHR(10)
||'TRAILING NULLCOLS'||CHR(10)
||'('
FROM user_tables
WHERE TABLE_NAME = UPPER('&table_name');
SELECT DECODE(ROWNUM,1,' ',' , ')||RPAD(column_name,33,' ')
|| DECODE(data_type,
'VARCHAR2','CHAR NULLIF('|| column_name ||'=BLANKS)',
'FLOAT', 'DECIMAL EXTERNAL NULLIF('||column_name||'=BLANKS)',
'NUMBER',
DECODE(data_precision,
0, 'INTEGER EXTERNAL NULLIF ('||column_name
||'=BLANKS)',
DECODE(data_scale,0,
'INTEGER EXTERNAL NULLIF
('||column_name ||'=BLANKS)',
'DECIMAL EXTERNAL NULLIF
('||column_name ||'=BLANKS)'
)
),
'DATE', 'DATE "MM/DD/YY" NULLIF ('||column_name
||'=BLANKS)',NULL)
FROM user_tab_columns
WHERE TABLE_NAME = UPPER('&table_name')
ORDER BY COLUMN_ID;
SELECT ')'
FROM sys.dual;
spool OFF
-
Highlander thanks for ur help
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|