| Updates [message #319497] |
Sun, 11 May 2008 22:51  |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
I am applying updates to a table based on partition key. I need to apply 50,000 updates. I need help with the most efficient way to apply the 50,000 updates to the partitioned table. The column to update has a constant value and two other columns values changes.
Lucky
|
|
|
|
|
|
| Re: Updates [message #319543 is a reply to message #319501] |
Mon, 12 May 2008 02:30  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
UPDATE table
SET col1 = 'A'
, col2 = col2 + 10
WHERE partcol = 'X'
Ross Leishman
|
|
|
|
| Re: Updates [message #319671 is a reply to message #319501] |
Mon, 12 May 2008 09:48 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
I have to applied 50,000 updates to a single row based on information that is supplied in a text format.
Example is listed below:
CODE_ID is constant.
COUNTY_ID and MUNICI_CODE are not constant.
The update is applied to a partition using the PARTITION_KEY. Based on the additional information, how can I transfer the information from the notepad text to update the table?
UPDATE STATE_T
SET CODE_ID = '400'
WHERE
PARTITION_KEY = 400
AND COUNTY_ID = 0003012
AND MUNICI_CODE = 4508160402
AND CITY_PRIN = 5;
Lucky
|
|
|
|
|
|
|
|
| Re: Updates [message #319689 is a reply to message #319679] |
Mon, 12 May 2008 11:57 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
I don't want to cut and paste 50,000 times to apply an updates. I am asking for a more efficient way to apply the updates. I could use any suggestions.
Thanks.
|
|
|
|
| Re: Updates [message #319690 is a reply to message #319689] |
Mon, 12 May 2008 11:59 |
 |
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | I am asking for a more efficient way to apply the updates
|
Like what?
Put all the values in a file and use an external table to join and make the update.
Regards
Michel
[Updated on: Mon, 12 May 2008 12:00] Report message to a moderator |
|
|
|
|
|
|
|
|
|
| Re: Updates [message #319757 is a reply to message #319702] |
Mon, 12 May 2008 23:38 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
This is the create table:
CREATE TABLE LOAD_STAGE_T
(
PARTITION_KEY NUMBER(4) NOT NULL,
PROCESS_NAME VARCHAR2(20) NOT NULL,
TENANT_ID VARCHAR2(20),
DEPT_ID VARCHAR2(3),
CLIENT_NUM NUMBER(5),
BUSINESS_UNIT NUMBER(10),
SERVICE_DATE DATE
)
TABLESPACE SKY_BLUE_1
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
NOLOGGING
PARTITION BY RANGE (PARTITION_KEY, SERVICE_DATE)
SUBPARTITION BY HASH (TENANT_ID)
UPDATE LOAD_STAGE_T
SET DEPT_ID = '021'
WHERE
PARTITION_KEY = 300
AND BUSINESS_UNIT= 077787
AND PROCESS_NAME = 012345600765432
AND CLIENT_NUM = 14;
In the where clause, the value of the BUSINESS_UNIT and PROCESS_NAME is not constant. The above UPDATE is 50,000, provided in notepad text. How can I apply the 50,000 UPDATE?
Thanks,
Lucky
|
|
|
|
|
|
| Re: Updates [message #319989 is a reply to message #319760] |
Tue, 13 May 2008 11:20 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
Raj, I like your suggestion and went along to implement it in testing before going to production. This is the error I am getting:
ORA-30657: operation not supported on external organized table
Thanks,
Lucky
|
|
|
|
|
|
|
|
| Re: Updates [message #319994 is a reply to message #319993] |
Tue, 13 May 2008 11:58 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
I got the above error while trying to create the external table.
However, any suggestions on how to get the data from my C:\ drive to a table in the database?
Thanks,
Lucky
|
|
|
|
|
|
|
|
| Re: Updates [message #320010 is a reply to message #319999] |
Tue, 13 May 2008 12:45 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
Michel,
I did comply with your request to post an example of the table and data. The data is in the form of UPDATE query in notepad as posted below under heading: "DATA to apply for the update:"; 50,000 updates query which is saved on my C:\ drive. Please note that the BUSINESS_UNIT and PROCESS_NAME values are not constant. Here is the create table posting again:
This is the create table:
CREATE TABLE LOAD_STAGE_T
(
PARTITION_KEY NUMBER(4) NOT NULL,
PROCESS_NAME VARCHAR2(20) NOT NULL,
TENANT_ID VARCHAR2(20),
DEPT_ID VARCHAR2(3),
CLIENT_NUM NUMBER(5),
BUSINESS_UNIT NUMBER(10),
SERVICE_DATE DATE
)
TABLESPACE SKY_BLUE_1
PCTUSED 40
PCTFREE 10
INITRANS 1
MAXTRANS 255
NOLOGGING
PARTITION BY RANGE (PARTITION_KEY, SERVICE_DATE)
SUBPARTITION BY HASH (TENANT_ID)
DATA to apply for the update:
UPDATE LOAD_STAGE_T
SET DEPT_ID = '021'
WHERE
PARTITION_KEY = 300
AND BUSINESS_UNIT= 077787
AND PROCESS_NAME = 012345600765432
AND CLIENT_NUM = 14;
UPDATE LOAD_STAGE_T
SET DEPT_ID = '021'
WHERE
PARTITION_KEY = 300
AND BUSINESS_UNIT= 077792
AND PROCESS_NAME = 012345600850264
AND CLIENT_NUM = 14;
Thanks Again,
Lucky
|
|
|
|
|
|
| Re: Updates [message #320017 is a reply to message #320013] |
Tue, 13 May 2008 13:05 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
To answer your question Michel:
BUSINESS_UNIT and PROCESS_NAME are the only variable parts of the updates. The others are constant.
The version of Oracle is 10.2.0.2 on Solaris. I can login using sqlplus from Windows as I have Oracle Client installed.
Thanks,
Lucky
|
|
|
|
|
|
|
|
| Re: Updates [message #320024 is a reply to message #320020] |
Tue, 13 May 2008 13:57 |
|
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
In this case it is much easier, something like:
create table ext_table (
<DEPT_ID,PARTITION_KEY,BUSINESS_UNIT,PROCESS_NAME,CLIENT_NUM fields with their type>
)
organization external (
type oracle_loader
default directory <an Oracle direcory created vie CREATE DIRECTORY>
access parameters (
records delimited by X'0A'
nobadfile
nologfile
nodiscardfile
fields terminated by ',' optionally enclosed by '"'
missing field values are null
(DEPT_ID,PARTITION_KEY,BUSINESS_UNIT,PROCESS_NAME,CLIENT_NUM)
)
location ('<your file.csv>)
)
reject limit unlimited
/
Then
merge into mytable a
using ext_table b
on (b.partition_key = a.partition_key
and <... for all other fields>)
when matched then update set dept_id = b.dept_id
/
Regards
Michel
|
|
|
|
| Re: Updates [message #320278 is a reply to message #320024] |
Wed, 14 May 2008 08:57 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
How do I solve the below error? I have created a directory and granted read and write on the directory to the user. I am getting this error message:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file C:\Loadfiles\PRODUPDATED0.csv in EXT_TAB_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19
Thanks,
Lucky
|
|
|
|
|
|
| Re: Updates [message #320292 is a reply to message #320280] |
Wed, 14 May 2008 10:01 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
The user, with DBA privilege who would be querying both the External Table and the Base Table.
Thanks,
Lucky
|
|
|
|
| Re: Updates [message #320307 is a reply to message #320278] |
Wed, 14 May 2008 11:38 |
|
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | How do I solve the below error?
|
Read
| Quote: | file C:\Loadfiles\PRODUPDATED0.csv in EXT_TAB_DIR not found
|
This is the only information you gave us.
This is the only answer we can give you.
Regards
Michel
[Updated on: Wed, 14 May 2008 11:40] Report message to a moderator |
|
|
|
| Re: Updates [message #320335 is a reply to message #320307] |
Wed, 14 May 2008 13:50 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
I created the directory ext_tab_dir AS '\C:\Loadfiles';(as SYS)
and granted read and write to ext_dir_tab to user LMADBA;
The data file PRODUPDATED0.csv is located in:
C:\Loadfiles.
This is the query I ran:
merge into UPDATE_STAGE_T a
using UPDATE_LOAD_T b
on (b.partitioning_ky = a.partitioning_ky
and b.B_SYS_ID = a.B_SYS_ID
and b.CLM_TCN = a.CLM_TCN and b.C_LI_NUM = A.C_LI_NUM)
when matched then update set PLAN_ID = b.PLAN_ID
/
I get the following error:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file C:\Sqlloader\PRODUPDATED0.csv in EXT_TAB_DIR not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19
With the information provided, how best to solve this?
Thanks,
Lucky
|
|
|
|
|
|
|
|
|
|
| Re: Updates [message #320343 is a reply to message #320340] |
Wed, 14 May 2008 14:34 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
I created and replaced the Directory with both C:\Sqlloader and C:\Loadfiles and I am still getting the same error.
Be patient anacedent. If I didn't need help, I wouldn't be posting on the forum. I just need some guidance as I've gotten in the past from the forum in many ways of which I commend you all and I am appreciative.
Thanks,
Lucky.
|
|
|
|
| Re: Updates [message #320345 is a reply to message #320343] |
Wed, 14 May 2008 14:40 |
|
Michel Cadot
Messages: 68664
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
| Quote: | Be patient anacedent.
|
I don't know for Ana but myself I gave up.
You have been asked to copy and paste your session and not to badly and wrongly explain what you did.
Use SQL*Plus and DO IT!
Forgot: AND FORMAT IT!
Regards
Michel
[Updated on: Wed, 14 May 2008 14:41] Report message to a moderator |
|
|
|
| Re: Updates [message #320348 is a reply to message #320343] |
Wed, 14 May 2008 14:49 |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Is your C: drive on your server or your client? In order to use external tables, the directory must be on your server, not your client. If on your client, then you either need to move/copy the files to your server first or use SQL*Loader instead. When you create an Oracle directory object, it accepts any string for the path and does not attempt to validate it until you attempt to use it.
[Updated on: Wed, 14 May 2008 14:49] Report message to a moderator |
|
|
|
| Re: Updates [message #320355 is a reply to message #320348] |
Wed, 14 May 2008 16:12 |
Lucky A
Messages: 68
Registered: October 2007
|
Member |
|
|
Barbara,
Thanks for pointing out my unintended error. Michel, Anacedent and the rest, thanks also. Apparently the C:\ drive is on my client. I have copied the data to the server and recreated the directory to point to the directory on the server. I am no longer getting any error message. But the merge command on one of my tests comes with no rows merged. Is my query in order?
SQL> merge into LOAD_STAGE_T a
2 using UPDATE_LOAD_T b
3 on (b.partition_key = a.partition_key
4 and b.BUSINESS_UNIT = a.BUSINESS_UNIT
5 and b.PROCESS_NAME = a.PROCESS_NAME and b.CLIENT_NUM = A.CLIENT_NUM)
6 when matched then update set DEPT_ID = b.DEPT_ID
7 /
0 rows merged.
Thanks,
Lucky
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
