- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- Re: sql*loader question
Operating System - HP-UX
1753720
Members
4753
Online
108799
Solutions
Forums
Categories
Company
Local Language
юдл
back
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
юдл
back
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Blogs
Information
Community
Resources
Community Language
Language
Forums
Blogs
Topic Options
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-26-2003 01:46 AM
тАО06-26-2003 01:46 AM
sql*loader question
Hi,
We're using sql*loader to load a file with 13 fileds delimited whit '|'.
We want to load all record that have the last field equal NULL, non caracter at all.
Problem is, there is no such keyword when using a WHEN CLAUSE.
LOAD DATA
REPLACE
INTO TABLE INTERFACE_FRONT_ECO_PREV
WHEN (1:7) != 'DEFFILE'
AND (1:7) != 'FTRFILE'
AND (1:6) != 'DEFLOT'
AND (1:6) != 'FTRLOT'
****** HERE ************
AND (13) = BLANKS
AND (13) = NULL
************************
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
IDENTIFIANT CHAR,
PERIOD DATE(8) "DDMMRRRR",
TYPE_PRESTATION CHAR,
DEVISE CHAR,
CODE_SEG CHAR,
CODE_STAT CHAR,
CODE_DEPARTEMENT CHAR,
SECT_GEO CHAR,
TYPE_CLIENT CHAR,
CANAL_RESA CHAR,
SOURCE_RESA CHAR,
VALEUR DECIMAL EXTERNAL,
SOURCE CONSTANT BOCOSAEXPBFRIJ09030625162920.TXT
)
Does anybody can provide some help,
thanks in advance,
We're using sql*loader to load a file with 13 fileds delimited whit '|'.
We want to load all record that have the last field equal NULL, non caracter at all.
Problem is, there is no such keyword when using a WHEN CLAUSE.
LOAD DATA
REPLACE
INTO TABLE INTERFACE_FRONT_ECO_PREV
WHEN (1:7) != 'DEFFILE'
AND (1:7) != 'FTRFILE'
AND (1:6) != 'DEFLOT'
AND (1:6) != 'FTRLOT'
****** HERE ************
AND (13) = BLANKS
AND (13) = NULL
************************
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
IDENTIFIANT CHAR,
PERIOD DATE(8) "DDMMRRRR",
TYPE_PRESTATION CHAR,
DEVISE CHAR,
CODE_SEG CHAR,
CODE_STAT CHAR,
CODE_DEPARTEMENT CHAR,
SECT_GEO CHAR,
TYPE_CLIENT CHAR,
CANAL_RESA CHAR,
SOURCE_RESA CHAR,
VALEUR DECIMAL EXTERNAL,
SOURCE CONSTANT BOCOSAEXPBFRIJ09030625162920.TXT
)
Does anybody can provide some help,
thanks in advance,
All different, all Unix
3 REPLIES 3
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-26-2003 02:58 AM
тАО06-26-2003 02:58 AM
Re: sql*loader question
According to this:
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x6fb8506d69a7d711abdc0090277a778c,00.html
the problem is solved.
Pete
http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x6fb8506d69a7d711abdc0090277a778c,00.html
the problem is solved.
Pete
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-26-2003 03:18 AM
тАО06-26-2003 03:18 AM
Re: sql*loader question
hi,
Are you using Oracle 8i or 9i?
In 9iR1 and up with external tables you have the ability to query a flat file using SQL. I find the flexibility of SQL over SQLLDR control files to be so useful that I would opt for an external table over sqlldr in most all cases. Use SQLLDR when SQL cannot be used to accomplish the job.
e.g.
create or replace directory data_dir as '/tmp/'
create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by '|' )
location ('emp.dat')
)
/
In Oracle8i and SQLLoader:
Consider the following example:
My control file:
=====================================
LOAD DATA
INFILE *
INTO TABLE dept
append
FIELDS TERMINATED BY '|'
(
deptno position(001:002),
dname position(004:013),
loc position(014:023) "nvl(:loc,'XX')",
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH |DALLAS
30|SALES |RESTON
40|OPERATIONS|
=====================================
Just load the data, the last column having null value will be substituted by 'XX'.
$ sqlldr userid=yd/yd@dev control=yd.ctl log=mlog
SQL> select * from dept;
DEPTNO DNAME LOC BUILDING
---------- ------------------------------ ------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES RESTON
40 OPERATIONS XX
After that, i can just do a delete where loc = 'XX'
delete dept where loc=''XX';
This is the easiest way (brute force).
Will need to investigate for other possibilities.
Hope this helps
regards
Yogeeraj
Are you using Oracle 8i or 9i?
In 9iR1 and up with external tables you have the ability to query a flat file using SQL. I find the flexibility of SQL over SQLLDR control files to be so useful that I would opt for an external table over sqlldr in most all cases. Use SQLLDR when SQL cannot be used to accomplish the job.
e.g.
create or replace directory data_dir as '/tmp/'
create table external_table
(EMPNO NUMBER(4) ,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7, 2),
COMM NUMBER(7, 2),
DEPTNO NUMBER(2)
)
ORGANIZATION EXTERNAL
( type oracle_loader
default directory data_dir
access parameters
( fields terminated by '|' )
location ('emp.dat')
)
/
In Oracle8i and SQLLoader:
Consider the following example:
My control file:
=====================================
LOAD DATA
INFILE *
INTO TABLE dept
append
FIELDS TERMINATED BY '|'
(
deptno position(001:002),
dname position(004:013),
loc position(014:023) "nvl(:loc,'XX')",
)
BEGINDATA
10|ACCOUNTING|NEW YORK
20|RESEARCH |DALLAS
30|SALES |RESTON
40|OPERATIONS|
=====================================
Just load the data, the last column having null value will be substituted by 'XX'.
$ sqlldr userid=yd/yd@dev control=yd.ctl log=mlog
SQL> select * from dept;
DEPTNO DNAME LOC BUILDING
---------- ------------------------------ ------------- --------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES RESTON
40 OPERATIONS XX
After that, i can just do a delete where loc = 'XX'
delete dept where loc=''XX';
This is the easiest way (brute force).
Will need to investigate for other possibilities.
Hope this helps
regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО06-26-2003 03:22 AM
тАО06-26-2003 03:22 AM
Re: sql*loader question
sorry! did not notice that this problem was already solved ;)
cheers
Yogeeraj
cheers
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
The opinions expressed above are the personal opinions of the authors, not of Hewlett Packard Enterprise. By using this site, you accept the Terms of Use and Rules of Participation.
News and Events
Support
© Copyright 2024 Hewlett Packard Enterprise Development LP