RE: SQL Loader Question on 8.1.7
- From: "David Kurtz" <info@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 17 Jun 2004 00:50:26 +0100
See chapter 10 of the utilities manual, SQL*Loader Case Studies
Case Study 7: Extracting Data from a Formatted Report
It demonstrates using a variable in a packaged procedure referenced by an ON
INSERT trigger.
The catch is that the trigger will not fire when you run SQL*Loader in
direct path mode, you have to use the conventional path load mode which will
be much slower.
(BTW, The same restriction applies when specify SQL operators to fields.)
_________________________
David Kurtz
Go-Faster Consultancy Ltd.
tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
mailto:david.kurtz@xxxxxxxxxxxxxxx
web: www.go-faster.co.uk
PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Kevin Lange
Sent: 16 June 2004 22:25
To: 'oracle-l@xxxxxxxxxxxxx'
Subject: SQL Loader Question on 8.1.7
Evening;
I figured if anyone would know ... you all would. Here is a simple
example given in the Oracle SQLLDR manual:
You have conditional data in a singl file to be loaded into different
tables:
1 50 Manufacturing
2 1100 Smith
2 1200 Snyder
1 60 Shipping
2 1121 Stevens
Based on the Record ID, you want either the Department or the Employee
table to be loaded:
Into Table dept
When recid = 1
(recid Position(1:1) Integer External,
deptno Position(3:4) Integer External,
depname Position(8:21) Char)
Into Table emp
When recid = 2
(recid Position(1:1) Integer External,
empno Position(3:6) Integer External,
empname Position(8:17) Char
commission Position(19:25))
That is fairly simple.
It gets more difficult when you ask the question .... Can you carry the
data from one of the When sections over into another section ?? i.e. Is
there a way to make a presistant variable that is set in one section and
then used in another section ??
Using the sample above, we need to have the "Department Number" (depno)
inserted into the EMP table without having to add it to each individual
record where recid = 2 since it is already on the record where recid = 1.
Any help or direction to go for samples if possible would be greatly
appreciated.
Kevin
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts: