0

hope you're having a nice day. I'm learning how to use functions on SQL-LOADER and i have a question about it, lets say i have this table

table a
--------------
code
name
dept
birthdate
secret

the data.csv file contains this data

name
dept
birthdate

and i'm using this code to load data to it with SQLLOADER

LOAD DATA
INFILE "data.csv"
APPEND INTO TABLE a;
FIELDS TERMINATED BY ',' optionally enclosed by '"'
TRAILING NULLCOLS
(code "getCode(:name,:dept)",name,dept,birthdate,secret "getSecret(getCode(:name,:dept),birthdate)")

so this works like a charm it gets the values from my getCode and getSecret functions, however, i would like to reference the previously calculated value (by getCode) so i don't have to nest functions on getSecret, like this:

getSecret(**getCode(:name,:dept)**,birthdate)

i've tried to do it like this:

getSecret(**:code**,birthdate)

but it gets the original value from the file (meaning null) and not the calculated by the function (guess because it does it on the fly), so my question is if there is a way to avoid these nest calls for previously calculated values, so i don't have to loose performance recalculating the same values over and over again (the real table i'm using it's like 10 times bigger and nests a lot of functions for these previously calculated values, so i guess that's reducing performance)

any help would be appreciated, Thanks!!

complement

Sorry, but i haven't used external tables before (kinda new here), how could i implement this using this tables? (considering all the calculated values i need to get from functions i developed, tried trigger (SQL Loader, Trigger saturation?), killed database...)

Community
  • 1
  • 1
E. Diaz
  • 145
  • 2
  • 14

1 Answers1

1

I'm not aware of a way of doing this.

If you switched to using external tables you'd have a lot more freedom for this sort of thing -- common table expressions, leveraging subquery caching, that sort of stuff.

David Aldridge
  • 51,479
  • 8
  • 68
  • 96
  • +1 for external tables. Unless we're loading meeelions of rows their flexibility always trumps the marginally faster load times for SQL Loader. – APC Jul 05 '13 at 15:43
  • Well even for meeelions of rows I think external tables have a distinct usability edge. A parallel load from a single file with SQL*Loader requires that it be split, and multiple sessions run -- it's a nightmare. I wouldn't touch it unless I had to. – David Aldridge Jul 05 '13 at 16:04
  • I totally agree. I haven't touched SQL Loader since my last 9i system, and even then I would have preferred using external tables (but I was outvoted). – APC Jul 05 '13 at 16:06
  • Sorry, but i haven't used external tables before (kinda new here), how could i implement this using this tables? (considering all the calculated values i need to get from functions i developed, tried trigger (http://stackoverflow.com/questions/17436972/sql-loader-trigger-saturation), killed database...) – E. Diaz Jul 05 '13 at 16:29