SQLLDR into multiple tables 1

[harebrain]

I'm receiving data in Excel, which is easy enough to save as CSV for SQLLDR. (Score!)

The spreadsheet is w-i-i-i-de: several tables are laid out side-by-side. I'm trying to use SQLLDR to get the data into several tables. An extract of my code looks like this:

LOAD DATA
APPEND
INTO TABLE BATCH1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
  C_CON	  CHAR,
  C_DATE	"TO_DATE(:C_DATE,'DD-MON-YYYY')",
  C_WIT	  CHAR,
  C_ID	  CHAR,
  C_KEY		"SUBSTR(:C_ID,1,8)"
)

INTO TABLE BATCH2 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
  C_TYPE	CHAR,
  C_NUM	  CHAR,
  C_PREVDT	"TO_DATE(:C_PREVDT,'DD-MON-YYYY')",
  C_KEY		"SUBSTR(:C_ID,1,8)"
)

A substring of Excel's Column D is going to be the primary key in each table. For the first table, Batch1, SQLLDR lets me create the C_Key field on-the-fly from field C_ID. But when I subsequently try to refer to :C_ID again, it gives me an error: SQL*Loader-291: Invalid bind variable :C_ID in SQL string for column C_KEY.

How can I grab the C_ID value for the second (and following) tables?

 

[LKBrwnDBA]

Create an "organization external" table on the file and use SQL to do your thing.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[harebrain]

That's a good answer, but really not an option. I'm working with an exposed interface of a validated system. I must be able to accomplish this within SQLLDR.

The brute-force method would be to duplicate that column for every table. Ugh.

Anybody know where I can access the examples from the Utilities book? They were not installed on my system, unfortunately. Darn little info in the book itself on working with bind variables. And, oy, before this month, my most recent Oracle experience was with version 7.

 

[karluk]

When you are loading table BATCH1, you refer to Excel's column D as "C_ID", but when you are loading BATCH2, you refer to column D as "C_KEY". It certainly looks as if SQL*Loader has discarded the column definitions from the BATCH1 load and is only aware of the names you are using for the BATCH2 load. That suggests you could fix the SQL*Loader-291 errors with the following

LOAD DATA
APPEND
INTO TABLE BATCH1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
  C_CON      CHAR,
  C_DATE    "TO_DATE(:C_DATE,'DD-MON-YYYY')",
  C_WIT      CHAR,
  C_ID      CHAR,
  C_KEY        "SUBSTR(:C_ID,1,8)"
)

INTO TABLE BATCH2 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
  C_TYPE    CHAR,
  C_NUM      CHAR,
  C_PREVDT    "TO_DATE(:C_PREVDT,'DD-MON-YYYY')",
  C_KEY        "SUBSTR(:C_KEY,1,8)"
)

 

[harebrain]

The mapping I want goes like this:

TABLE1
C_CON     <- Col. A
C_DATE    <- Col. B
C_WIT     <- Col. C
C_ID      <- Col. D
C_KEY     <- substring of Col. D

TABLE2
C_TYPE    <- Col. E
C_NUM     <- Col. F
C_PREVDT  <- Col. G
C_KEY     <- substring of Col. D

It seems to me that if I follow karluk's advice, TABLE2.C_KEY will be taken from Col. H, which isn't what I want.

Surely this is a rather common need: loading flat files into a normalized structure.

 

[SantaMufasa]

That's a good answer, but really not an option. I'm working with an exposed interface of a validated system. I must be able to accomplish this within SQLLDR.

IMHO, the best solution to your need is LKBrwnDBA's suggestion. Using organization external tables uses the SQLLDR engine...It uses SQL syntax via any SQL*Plus interface. Once the table exists (via the "CREATE TABLE...organization external" syntax), you can do anything you want to the data to normalize it into multiple tables.


When you say your access is "with an exposed interface of a validated system", how does that prevent you from using "organization external" tables?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[karluk]

According to the SQL*Loader documentation, you have to use the "position" parameter in the first column of the second table in order to get the loader to rescan fields that it has already processed for the first table. Since you aren't actually interested in loading most of the rescanned fields, you would use filler fields until you get to Column D. The following code is untested, but most likely is close to what you need:

LOAD DATA
APPEND
INTO TABLE BATCH1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
  C_CON      CHAR,
  C_DATE    "TO_DATE(:C_DATE,'DD-MON-YYYY')",
  C_WIT      CHAR,
  C_ID      CHAR,
  C_KEY        "SUBSTR(:C_ID,1,8)"
)

INTO TABLE BATCH2 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(
  C_CON     FILLER    POSITION(1)    CHAR,
  C_DATE    FILLER    "TO_DATE(:C_DATE,'DD-MON-YYYY')",
  C_WIT     FILLER    CHAR,
  C_ID      FILLER    CHAR,
  C_TYPE    CHAR,
  C_NUM     CHAR,
  C_PREVDT  "TO_DATE(:C_PREVDT,'DD-MON-YYYY')",
  C_KEY     "SUBSTR(:C_ID,1,8)"
)

 

[karluk]

By the way, I don't agree that external tables are necessarily superior to traditional SQL*loader syntax in this situation. No doubt an external table would work, but it seems to me that the main goal is to avoid having to read through the input file twice in order to get the two tables loaded. That can be important for large data files and the multiple "into table" clauses of SQL*loader is a clear way to accomplish this, if only we can figure out the exact syntax to make it work.

 

[Thargy]

karl,

I agree with your point about double reading but disagree with your conclusion. When loading from external tables on e of the major advantages is that one has the full power of sql at one's disposal. This is significantly more powerful and capable than anything sql loader can do.

Also, running two pieces of sql to load two tables from the one external will be fine, the system will only fetch that which it needs. Also, since external tables are significantly faster than loader, the speed should improve too.

Regards

T

 

[karluk]

I would be very willing to conduct a performance test to resolve the disagreement, but I certainly don't think it's obvious that the performance of external tables would be as good as loader in the absence of such a test - unless, of course, you have a technique to force the external table to update two separate Oracle tables with only a single pass through the input file.

 

[harebrain]

>IMHO, the best solution to your need is...

... one I can actually use. I'm not the master of this universe. I have one hook into the system, and that is a call to SQLLDR. I may not create database objects, so defining an external table is out. No, I can't do anything I want.

Having continued to search for answers, I've come to pretty much the same conclusion as karluk proposes: reposition at 1 for each subsequent table and declare the no-longer-needed Excel columns as filler.

Thank you all for your input. Happy Holidays!