source and target as the same table (using oracle stage)
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
source and target as the same table (using oracle stage)
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
Re: source and target as the same table (using oracle stage)
Can you be a little more... oh, I don't know... specific?kirankota79 wrote:I tried it, but it is giving errors.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
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.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?
Please post them.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
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
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
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...
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...
Assume everything I say or do is positive
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 pm
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 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
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
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?
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?
Assume everything I say or do is positive
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 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.
Reject link will also add a column sqlcode. Can you post this sqlcode.
Assume everything I say or do is positive
-
- Premium Member
- Posts: 315
- Joined: Tue Oct 31, 2006 3:38 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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.