[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql loader

Posted on 2006-05-31
20
Medium Priority
?
2,254 Views
Last Modified: 2008-01-09
This is subsequent to

http://www.experts-exchange.com/Databases/Oracle/Q_21865533.html

Now I'm able to concatenate more than one record using logical operator continueif

But the pbm is, I need to write more than one continueif as the current line's last char should not be '|' and the next lines first letter should be '|' then I want to concatenate else not.

I wrote conditions like,

CONTINUEIF LAST PRESERVE != '|'

CONTINUEIF FIRST PRESERVE != '|'

but how do i write these two in a same loader script? if i write like this,
CONTINUEIF (LAST PRESERVE != '|' and  FIRST PRESERVE != '|')

it throws error. please help.




0
Comment
Question by:ram_0218
  • 11
  • 5
  • 4
20 Comments
 
LVL 23

Expert Comment

by:paquicuba
ID: 16801733
I don't get it:

<< as the current line's last char should not be '|' >> CONTINUEIF LAST PRESERVE != '|'   -- OK

<< and the next lines first letter should be '|' >> CONTINUEIF FIRST PRESERVE != '|'  -- ?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16801946
CONTINUEIF syntax is as following:

cont_if ::=

  CONTINUEIF { [THIS | NEXT] [PRESERVE]  [(]  pos_spec | LAST [PRESERVE] [(] }  
             operator { str | X'hex_str' } [)]


only one condition can be defined.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16801961
have you tried my other suggestion in last post - using str (record stream format)?

I gave you a detailed sample for you to chew on.

acton
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 23

Expert Comment

by:paquicuba
ID: 16801989
'Next' would be an option if you never have '|' as the last and first byte.

CONTINUEIF NEXT PRESERVE = '|'
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16802023
if you use continueif, the \r \n would be lost. If you want to preserve them, you'd have to use str.

let me know how your inputting data look like (especially for comment data). I could help you out for that.


Acton
0
 
LVL 17

Author Comment

by:ram_0218
ID: 16802058
guys! i'm sorry, i'm very weak in english i apologize for my language. Heres what i'm trying to say,

every record and field gets terminated with a '|'

so my first record looks like,

|a|,|b|,|c|,|de
fg|

Here after de, theres a new line and thats good and can be caught by
CONTINUEIF LAST PRESERVE != '|'

so my record becomes a,b,c,defg

but when i've,
|a|,|b|,|c|,|
defg|

please note here, defg the string has new line character at its first place so this has to be handled differently.

Now i'm tring to write a loader script which can handle these two situations. Hope my question is clear now.
0
 
LVL 17

Author Comment

by:ram_0218
ID: 16802067
>>only one condition can be defined.

This is where I'm stuck. I need to write one more continueif statement, Somehow I'll have to handle this situation.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16802108
I c. I can help you out for this.

Are you working on unix or windows?
0
 
LVL 17

Author Comment

by:ram_0218
ID: 16802136
UNIX, but as directed, I cant do this neither in UNIX nor in DB2.

If I'm allowed to so, I would have done it myself more easily !
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16802217
did you try str?

Also a quick question: does each record end by "\r\n" or "\n"?
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16802241
more specificly:

in your case, does it look like:

|a|,|b|,|c|,|   <- \n in the end?
defg|              <- \r\n in the end or \n in the end?


acton
0
 
LVL 17

Author Comment

by:ram_0218
ID: 16802277
both lines do have '\n'

but here the first record is the catch, the second line does not start with a '|' so it should be attached to the previous line.

In my earlier example,
The first line was not terminated by the '|' so it should be attached to the next line.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16802356
is this not enough?

CONTINUEIF NEXT PRESERVE (1) != '|'
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16802367
this means: if the next rows doesn't start with '|', then merge the next row. I think that is what you want.
0
 
LVL 17

Author Comment

by:ram_0218
ID: 16802529
what about the record like this?

|aa|bb|cc
|def|

Please note here the first line does not get ended with a | so we should concatenate the next line also. But the next line starts with a |

practically speaking we encounter newlines before and after or in middle of the comments field which is causing this jimmick.
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16803091
those lines will be in the bad file and you can continue to process those data.

acton
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16807731
>>|aa|bb|cc
>>|def|

i though your record would be as:

|aa|,|bb|,|cc
|,|def|

if you use
fields terminated by "," enclosed by "|"

if you use CONTINUEIF NEXT PRESERVE (1) != '|' then sqlloader would reject those lines into bad file.

you could process those lines in bad file to do another APPEND load.

Acton
0
 
LVL 19

Expert Comment

by:actonwang
ID: 16807735
if you guys can define a special end characters for each record, STR should do this pretty easily.

acton
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16819229
Can you create an external table to manipulate the strings?
0
 
LVL 23

Accepted Solution

by:
paquicuba earned 2000 total points
ID: 16819700
Ram,

This is just to give you an idea (External Tables are very flexible and faster than SQL*Loader when used with APPEND hint.



FIELDS.TXT
----------

|1234567|,|
Y|
|89|,
|Y|
|123456789|,|N
|
|12345678|
,|N|
|555
|,|N|
|
88888|,|N|


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DROP TABLE TEMPTBL;

CREATE TABLE TEMPTBL(
  TEMPCOL1 VARCHAR2(100),
  TEMPCOL2 VARCHAR2(100)
  )
  ORGANIZATION EXTERNAL
  (TYPE ORACLE_LOADER
  DEFAULT DIRECTORY CDIR
  ACCESS PARAMETERS
  (
  RECORDS DELIMITED BY NEWLINE
  NOBADFILE
  NODISCARDFILE
  NOLOGFILE
  FIELDS TERMINATED BY ','
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
  (
  TEMPCOL1 CHAR(100),
  TEMPCOL2 CHAR(100)
  )
  )
  LOCATION ('FIELDS.TXT'))
  REJECT LIMIT UNLIMITED;


ALEX@PROD > SELECT * FROM TEMPTBL;

TEMPCOL1             TEMPCOL2
-------------------- --------------------
|1234567|            |
Y|
|89|
|Y|
|123456789|          |N
|
|12345678|
                     |N|
|555
|                    |N|
|
88888|               |N|

12 rows selected.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


SELECT TEMPCOL1, TEMPCOL2 FROM(
SELECT REPLACE(TEMPCOL1,'|') TEMPCOL1, REPLACE(TEMPCOL2,'|') TEMPCOL2
FROM(
SELECT
CASE WHEN LENGTH(TEMPCOL1) = 1 AND SUBSTR(TEMPCOL2,1,1) <> '|' THEN
TEMPCOL1||LEAD(TEMPCOL1) OVER ( ORDER BY ROWNUM)
WHEN SUBSTR(TEMPCOL1,1,1) = '|' AND SUBSTR(TEMPCOL2,-1,1) = '|' THEN
TEMPCOL1
END
TEMPCOL1,
CASE WHEN LENGTH(TEMPCOL1) = 1 AND SUBSTR(TEMPCOL2,1,1) <> '|' THEN
LEAD(TEMPCOL2) OVER ( ORDER BY ROWNUM )
ELSE
TEMPCOL2
END
TEMPCOL2
FROM
(SELECT TEMPCOL1,
CASE
WHEN TEMPCOL2 IS NULL THEN
LEAD(TEMPCOL1) OVER (ORDER BY ROWNUM)
WHEN LENGTH(TEMPCOL2) = 1 THEN
TEMPCOL2||LEAD(TEMPCOL1) OVER (ORDER BY ROWNUM)
WHEN SUBSTR(TEMPCOL2,-1,1) <> '|' THEN
TEMPCOL2||LEAD(TEMPCOL1) OVER (ORDER BY ROWNUM)
ELSE
TEMPCOL2
END||LEAD(TEMPCOL2) OVER (ORDER BY ROWNUM) TEMPCOL2
FROM TEMPTBL)))
WHERE TEMPCOL1 IS NOT NULL
/

TEMPCOL1             TEMPCOL2
-------------------- --------------------
1234567              Y
89                   Y
123456789            N
12345678             N
555                  N
88888                N

6 rows selected.



0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to recover a database from a user managed backup
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question