source and target as the same table (using oracle stage)

kirankota79 · Post by **kirankota79** » Tue Apr 24, 2007 10:03 am

I have i/p oracle stage connected to transformer and then o/p oracle stage. Is it possible to use the same table as the source(i/p) and target(o/p)?. I tried it, but it is giving errors. I want to overwrite the records in the table after the transformation

chulett · Post by **chulett** » Tue Apr 24, 2007 10:06 am

kirankota79 wrote:I tried it, but it is giving errors.

Can you be a little more... oh, I don't know... specific?

kirankota79 · Post by **kirankota79** » Tue Apr 24, 2007 10:10 am

i have a table in the oracle database. i want to do a transformation on one column of that table and load back into the same table with the same job. that means i have to give the same table name in the oracle stage properties for the i/p and o/p. I want to know whether it works or not?

ivannavi · Post by **ivannavi** » Tue Apr 24, 2007 10:24 am

I would try to do it with a single Oracle update.
Or maybe, if you can't do without transformer, there should be two steps:
First prepare a table which holds the transformed data, and then use it in another job to update the desired table.

chulett · Post by **chulett** » Tue Apr 24, 2007 10:32 am

kirankota79 wrote:i have a table in the oracle database. i want to do a transformation on one column of that table and load back into the same table with the same job. that means i have to give the same table name in the oracle stage properties for the i/p and o/p. I want to know whether it works or not?

I understand all that. And of course it can be made to work. I quoted a part of your message because the specific errors you are getting will help diagnose your issue and help drive a solution. Just saying you are "getting errors" doesn't tell us much of anything.

Please post them.

kirankota79 · Post by **kirankota79** » Tue Apr 24, 2007 11:01 am

1.Oracle_Enterprise_2,0: SQL*Loader-500: Unable to open file (\\.\pipe\ora.924.267000d.fifo.0)

2.Oracle_Enterprise_2,0: SQL*Loader-553: file not found

3.Oracle_Enterprise_2,0: SQL*Loader-509: System error: The system cannot find the file specified.

4.Oracle_Enterprise_2,0: Call to sqlldr failed. Return code = 768Please see loader logfile: D:/Ascential/DataStage/Scratch/ora.924.267000d.0.log for details.

5. Oracle_Enterprise_2,0: SQL*Loader: Release 8.1.7.1.1 - Production on Tue Apr 24 11:56:55 2007
(c) Copyright 2000 Oracle Corporation. All rights reserved.
Control File: ora.924.267000d.0.ctl
Character Set WE8ISO8859P1 specified for all input.
Data File: \\.\pipe\ora.924.267000d.fifo.0
File processing option string: "FIX 96"
Bad File: ora.924.267000d.0.log.bad
Discard File: none specified
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65536 bytes
Continuation: none specified
Path used: Conventional
Table REQSEL.EMPLOYEETEST, loaded from every logical record.
Insert option in effect for this table: APPEND
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
USER_ID 1:19 19 CHARACTER
NULL if USER_ID = BLANKS
LAST_NAME 20:38 19 CHARACTER
NULL if LAST_NAME = BLANKS
FIRST_NAME 39:57 19 CHARACTER
NULL if FIRST_NAME = BLANKS
MIDDLE_INITIAL 58:76 19 CHARACTER
NULL if MIDDLE_INITIAL = BLANKS
WORK_PHONE 77:95 19 CHARACTER
NULL if WORK_PHONE = BLANKS
SQL*Loader-500: Unable to open file (\\.\pipe\ora.924.267000d.fifo.0)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.

6. Oracle_Enterprise_2,0: Operator's runLocally() failed.

The first three are warnings and the remaining are errors

csrazdan · Post by **csrazdan** » Tue Apr 24, 2007 5:14 pm

You want to update an existing record from the table but you are using Load option which is calling SQL Loader. Load is only for appending new records to the table.

If you use UPSERT option I don't see why you should not be able to update the table in the same job. To be on the safer side use a User-defined SQL in the input stage and select only key columns and the columns you want to update.

Hope it helps...

kirankota79 · Post by **kirankota79** » Wed Apr 25, 2007 12:45 pm

can u tell me how can we use the upsert mode on the o/p side (oracle stage) . iam not able to update and all the rows are getting rejected.

csrazdan · Post by **csrazdan** » Wed Apr 25, 2007 1:02 pm

Is job generating any warnings? Can you post warning message or something to guess waht problem you are facing....

kirankota79 · Post by **kirankota79** » Wed Apr 25, 2007 1:16 pm

no specific warnings relating to the o/p stage...just iam getting normal warnings

1. main_program: An NLS map <ISO-8859-1> is specified, but NLS_LANG is not set. The NLS map must be set to an ICU codepage which is equivalent to the character set specified by NLS_LANG
2.main_program: An NLS map <ISO-8859-1> is specified, but NLS_LANG is not set. The NLS map must be set to an ICU codepage which is equivalent to the character set specified by NLS_LANG
3. Employee: When checking operator: When binding output interface field "USER_ID" to field "USER_ID": Implicit conversion; from source type "string[max=20]" to result type "string[max=19]": Possible truncation of variable length string
4.WorkPhone_Lookup,0: Ignoring duplicate entry at table record 37; no further warnings will be issued for this table
5. Employee_Mask,0: Records Inserted: 0 Records Used for Update: 4417 Records Rejected: 0 Total Records: 4417

everything looks fine , but not getting updated

csrazdan · Post by **csrazdan** » Wed Apr 25, 2007 1:24 pm

4.WorkPhone_Lookup,0: Ignoring duplicate entry at table record 37; no further warnings will be issued for this table

5. Employee_Mask,0: Records Inserted: 0 Records Used for Update: 4417 Records Rejected: 0 Total Records: 4417

This log entry 5 suggests that records have been updated. What are you trying to update, is it phone number because the log entry 4 suggests you have duplicates.

Can it be a posibility that the records are being updated with the same values?

kirankota79 · Post by **kirankota79** » Wed Apr 25, 2007 1:40 pm

workphone is transformed to another value and should overwrite the original value with the transformed onw with the same job.

csrazdan · Post by **csrazdan** » Wed Apr 25, 2007 3:14 pm

Can you set the value for the column as constant say 1111111 (just before the database stage) and see if it gets updated. Also add a reject link to the database stage and write the records to a file.

Reject link will also add a column sqlcode. Can you post this sqlcode.

kirankota79 · Post by **kirankota79** » Wed Apr 25, 2007 3:41 pm

iam using the oracle stage. the sql code is -1400. i dont know what it is. the rejected values are exactly what i needed to update...but it is rejecting.

DSguru2B · Post by **DSguru2B** » Wed Apr 25, 2007 4:15 pm

WHat is the key? Is it USER_ID? IF yes then it is getting truncated as your error message indicates and no way it will be able to find the key to update if its getting truncated. Fix the length in the table and run your job again.

DSXchange

source and target as the same table (using oracle stage)

source and target as the same table (using oracle stage)

Re: source and target as the same table (using oracle stage)