Bulk Update In Oracle
Mar 28, 2012
I have performance issue with the bulk update. I have 2 tables one with 21 millions of data and the other table with 2 millions of data.here the requirement is to update the table which is having 21 millions with the other tables data(which is having 2 million records) based on some matching criteria.
The procedure is taking 9 hours to update the table(which is having 21 millions data). I have created required indexes on table also.But the performance is not good.
Here my procedure:
CREATE OR REPLACE PROCEDURE AHM_GKPR.CLAIMS_WITHOUT_PARTITIONS
IS
TYPE T_PL_CO IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
L_PL_CO T_PL_CO;
TYPE T_PL_CD IS TABLE OF VARCHAR2(9) INDEX BY BINARY_INTEGER;
L_PL_CD T_PL_CD;
TYPE T_PL_NB IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
L_PL_NB T_PL_NB;
TYPE T_SE_CD IS TABLE OF VARCHAR2(1) INDEX BY BINARY_INTEGER;
L_SE_CD T_SE_CD;
TYPE T_BIR_DT IS TABLE OF DATE INDEX BY BINARY_INTEGER;
[code].........
View 5 Replies
ADVERTISEMENT
Apr 18, 2013
My oracle version is 10g.I want to update a table which has 2 million rows and 20 columns. I want to update 3 columns in my table based on a query output.
Say:-
select decode(col1,'a',col2,col3) up_col1, decode(col5,'Y',20,30)* col6 up_col2, col7 up_col3 from base_tab a, update_tab b where a.key = b.key
I tried the simple update SQL, it hangs and never comeback even after 2 hrs.So I resort to PLSQL to complete my job using bulk collect with limits. Now I am stumbled upon with the Bulk update step.I referred to the BULK Update example given in [URL]...
I gone by 9i approach, I got invalid rowid error. Since the example doesn't uses a join.
I am little bit worried to go by 10g approach given in the example. I have to fetch entire row from my update table. My update table has 5 varchar(1000) fields. Anyway I am not going to affect them in my update. Whether it will occupy memory and again leads to hanging of my procedure.
View 2 Replies
View Related
Dec 5, 2011
I am trying to write an update using two tables, one table contains the list of updates to be applied, and the second contains the old data. I have a working solution but I was wondering if there might be some other better way to accomplish this task.
CREATE TABLE foo
(
foo_id NUMBER,
foo_val NUMBER
);
CREATE TABLE foo_change
(
foo_id NUMBER,
foo_val NUMBER
[code]...
The update works, but I thought there may be a better way I just don't know about.
View 2 Replies
View Related
Dec 15, 2008
Has 2 tables with the following columns
1.abc_y (notes,frmt)
sample data:
notes:
i live in texas and work in AIG.
2.abc_z(tags_name)
sample data:
tags_name:
live in work.We shoud look for tags_name in notes field of abc_y. If we encounter any tags_name in notes field they should be removed and inserted in frmt field Now the column format should be updated to 'i texas and AIG'.abc_y has 2 million rows and abc_z has 120 rows.wrote the code sucessfuly but cannot bulk update it, which is really needed for me.
I am havin some problm in FOR LOOP after FORALL in the folowing.
DECLARE
l_sql_strng VARCHAR2 (20000);
TYPE var_tab IS TABLE OF VARCHAR2 (20000)
INDEX BY BINARY_INTEGER;
l_text_arry var_tab;
CURSOR c1
[code]...
View 1 Replies
View Related
Aug 3, 2012
optimize this code. Scenario have to update about 40 million rows to static value, I'm committing 1Million rows in one loop. The first 1 millions rows are getting updated very fast probably in a 2 minute, after that the code just hungs and i don't see increase in committed rows.
Declare
cursor c1 is
select rowid from t1
where c1 is not null;
[Code]....
View 2 Replies
View Related
Jun 19, 2013
I have more than 10 lakhs records in the table for which i am going to update two columns without any filtration. i have pasted my query in it..it's taking more time to update..is there any way to fine tune this block.
DECLARE
l_fallback_page Au_Case_Parallel_19062013.page_num%TYPE;
l_fallback_kwd Au_Case_Parallel_19062013.Fallback_keyword%TYPE;
lv_type varchar2(1000);
[Code]....
View 9 Replies
View Related
Jan 25, 2013
I am trying to update a table column values if any change occurs using bulk collect and for all update not able to get idea. below is the proc working out.it is for insert and update using the cursors.
CREATE OR REPLACE PROCEDURE PRC_INS(P_ID IN NUMBER,P_STAT OUT NUMBER) IS
TYPE T_TEST_TAB IS TABLE OF T_DTLS%ROWTYPE;
V_PARAM T_TEST_TAB;
V_STATUS NUMBER;
V_BUS VARCHAR2(20);
V_UP VARCHAR2(1);
V_Q VARCHAR2(50);
[Code]....
View 2 Replies
View Related
Aug 13, 2010
I am trying to bulk update records in oracle using XML , front end is vb.net.Now the problem when i updating for 1000 - 5000 records on my development server. Its getting updated.
But when we are updating on the production server for 100000-200000 records , we receive error
"ORA-01460: unimplemented or unreasonable conversion requested "
View 1 Replies
View Related
Mar 15, 2010
i am getting compiling error when using bulk collect in oracle form 10g
Quote:this feture is not supported on client-side programs
View 3 Replies
View Related
Jun 27, 2013
I would like to update an XML element without using the function APPENDCHILDXML or INSERTCHILDXML because they are not available in Oracle 10GR1.
In my database, Oracle XDB is not installed.
The following query fail with the following error : ORA-00904: "INSERTCHILDXML" : identificateur non valide
update scl_profile
set profile_data =
insertChildXML(profile_data,'/exportImportMarcheCriteria','colonnesExport',
XMLType('<colonnesExport>ENTETE_GESTIONNAIRES_AUTORISES</colonnesExport>'))
where profile_xmltype =
'fr.mipih.marches.marche.criteres.ExportImportMarcheCriteria'
and profile_type =
'eMagh2.MRGS.AccesMarche.ListeMarche.Export.OptionsExportImport';
[code]........
If i try to use the package DBMS_XMLDOM, i have the following error :
ORA-06550: Ligne 3, colonne 11 :
PLS-00201: l'identificateur 'DBMS_XMLDOM.DOMDOCUMENT' doit etre declare
ORA-06550: Ligne 3, colonne 11 :
PL/SQL: Item ignored
I think it's because ORACLE XDB component is not installed in my database.
View 1 Replies
View Related
May 16, 2011
what is the error in this procedure.I am getting error where s.msg_id = Tbl_Repo_Salo (i.msg_id));
Create or replace Procedure Proc_Stg_Clear(P_Err_Code OUT NOCOPY number,
P_err_msg OUT NOCOPY varchar2) as
v_count number;
Type Repo_Salo is table of Tbl_Tml_msg_Salo_Stg%ROWTYPE;
Type Repo_Smb is table of Tbl_Tml_msg_SMB_Stg%ROWTYPE;
[code]...
View 9 Replies
View Related
Jan 30, 2011
What is Bulk COllect and How it can be use
View 2 Replies
View Related
Sep 30, 2011
I need to add a new column to a very large table, and update it with 'N'. (this is similar as specifying default 'N'). I'm using Oracle 9i. Which is the best method regarding to speed, to update this column on entire table? The table contains ~30 millions of records. I've read that parallel DML (here UPDATE) does not work on unpartitioned tables. My table is not partitioned. If i specify:
update /*+ full(p) parallel(p,10) */ my_table p set p.my_column = 'N';
This, i think will not speed up the operation on 9i. Our business does not accept to use CREATE TABLE AS SELECT, then renaming table and recreating all indexes and so on.
View 21 Replies
View Related
Sep 28, 2011
I made two runs for bulk insertion
In first run, 16,36,897 were inserted successfully in around 38 seconds.But in second run, 54,62,952 records had to be inserted, but process failed after 708 seconds with following error :
Error report:
ORA-04030: out of process memory when trying to allocate 980248 bytes (PLS non-lib hp,DARWIN)
ORA-06512: at line 21
04030. 00000 - "out of process memory when trying to allocate %s bytes (%s,%s)"
*Cause: Operating system process private memory has been exhausted
*Action:
Here is my code snippet :
.......
FORALL i in products_tab.first .. products_tab.last
INSERT INTO tab1 VALUES products_tab(i);
COMMIT;
.........
I think that there should not have been any problem in getting it completed successfully.
View 4 Replies
View Related
Sep 2, 2011
I have a driver table from which I need to update another table while, at the same time, record the fact that I have processed each record on the driver table.
The driver table will contain around 3.5 million records, therefore I intended to handle this using some bulk collections, with a LIMIT option so that I don't hit any memory problems.
I would also prefer to commit in batches, or at least handle exceptions using the SAVE EXCEPTION clause. The problem is I seem to be running into an error when trying to make the update to the driver table (the commented out code). With this in, I get the error:
ORA-01410: invalid ROWID
ORA-06512: at "CUST_MAIL_UPDATE", line 217
ORA-06512: at line 38
Can the CURRENT OF not work with the FORALL? What is my best approach here? If I use a FOR LOOP I lose my SAVE EXCEPTIONS exception handling.
The Procedure is as follows:
-- declare some object structures to hold the retrieved data
TYPE driver_rec IS RECORD (
account_no ext_driver.account_no%TYPE,
update_action ext_driver.update_action%TYPE,
customers_rowid ext_driver.customers_rowid%TYPE);
TYPE driver_recs_tt IS TABLE OF driver_rec;
-- cursor to get the records from the driver table
[code].......
View 10 Replies
View Related
May 11, 2011
Is it possible to Bulk Upload file into Oracle table's BLOB column using Pl/Sql code.
The process I know of dose 1 file @ a time as of now.
I have more than 10000+ files to upload and this will be one off process.
View 3 Replies
View Related
Dec 14, 2012
We have requirement to create INSERT SCRIPT from the table having thousands of records and load that into flat file. if there are any better option other than using UTL_FILE package which process record by record.
View 5 Replies
View Related
Apr 21, 2011
I am finishing using procedure migration from HP-ux server to Oracle linux server. I am currently testing migration by procedure, and there's time limit, so I like to use bulk collect and other faster way to do that, however I could convert normal procedure to bulk procedure.
here's my script of old table, new table, normal procedure, and my new procedure.which parts can be corrected to use bulk collect or bulk insert?
CREATE TABLE ORAASFS.NATELIST
(
MINNO VARCHAR2(12 BYTE),
PHONENO VARCHAR2(12 BYTE)
)
CREATE TABLE ORAASFS.TM_SFS_USR_NATE_LST_01
(
[code]......
View 1 Replies
View Related
Dec 24, 2010
I simulated a sample procedure for my requirement.When i try to compile procedure it throws error 'cannot mix single and multiple rows ( bulk) into'...I have to pass a table as dynamic in a cursor ,collect the data and process it using and forall.
create table dynamic (emp_name varchar2(20),emp_id varchar2(20), tel_no varchar2(20);
create table dynamic_1 (emp_name_1 varchar2(20),emp_id_1 varchar2(20), tel_no_1 varchar2(20);
insert into dynamic values ('Mike','1','123456');
insert into dynamic values ('Nike','2','1234567');
create or replace PROCEDURE proc_1(t_name varchar2) IS
TYPE parent_rec IS RECORD (part_num dynamic.emp_name%type,part_name dynamic.emp_id%type,part_id dynamic.tel_no%type) ;
p_rec parent_rec;
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR 'select EMP_NAME, EMP_ID,TEL_NO FROM '||t_name ||' WHERE EMP_ID = ''1''' ;
[code]....
View 21 Replies
View Related
Mar 7, 2011
I'm getting error message
PLS-00386: type mismatch found at 'RECORD_VARRAY' between FETCH cursor and INTO variables
while executing the below code.
PROCEDURE MAIN_BULK_COLLECT(P_STARTDATE IN TIMESTAMP DEFAULT NULL,
P_ENDDATE IN TIMESTAMP DEFAULT NULL,
P_ROW_COUNT IN NUMBER DEFAULT 1000,
O_RECORD_VARRAY OUT NOCOPY SSAM_VARRAY_TYPE,
P_ERROR OUT VARCHAR2) AS
[code]....
I'm able to run the program successfully using FOR LOOP instead of BULK COLLECT but wish to run using bulk collect.
View 6 Replies
View Related
Feb 24, 2011
Okay I am not asking for detailed solution here but I have quick query.
One of the procedure have this cursor query returning say 10 columns and have declared an collection of that cursor type.
Now the cursor is bulk fetched in the collection.
By any means it is possible to happen that bulk collection will scramble column values ?
The cursor seem to return expected output. But when table insert happens using the collection there I see values are mismatched.
I was wondering if bulk collect can be an issue ?
View 4 Replies
View Related
Jan 7, 2011
We are doing a bulk select and insert (10,000 rows processed in each transaction). If one record fails, the entire transaction is rolled out. We need to fix this and re-run. the process is repeated unless all errors are fixed.
How to capture all errors in a single run ?
View 3 Replies
View Related
Mar 25, 2011
I have the following questions you can create a bulk collect in which he has 3 fields and one of them is a type collect failing index or other bulk collect?.If so, how would the procedure for when to insert the first record sub fill the bulk collect. being for example something like this:
-----------------------------------------------------------------
index | codigo | nombre | telefono
| | |-----------------------
| index | telefono
-----------------------------------------------------------------
| | | |
[code]...
View 2 Replies
View Related
Mar 5, 2012
How to resolve this issue?
CREATE OR REPLACE PROCEDURE fast_proc
IS
TYPE ARRAY IS TABLE OF mkt_total_lvl_indx_dly_stg%ROWTYPE;
l_data ARRAY;
cursor C IS
SELECT *
[code]..........
show error
Error code
PROCEDURE fast_proc compiled
Warning: execution completed with warning
17/47 PL/SQL: ORA-03001: unimplemented feature
17/5 PL/SQL: SQL Statement ignored
View 13 Replies
View Related
Feb 23, 2012
create or replace PROCEDURE CDR_PROC_ARCHIVE_ORDER_EXTRACT
IS
/*
Criteria to be followed to Order Archival
* Order Status should be 'Cancelled' or 'Complete'
* Order Closed date should be 6 months before
*
-- main Cursor to spool the Orders to be archived based on criteria
[code]...
View 1 Replies
View Related
Nov 26, 2010
identify what type of error is present in below procedure?
create or replace procedure test_bulk_load_type
as
type c1_owner is table of bulk_load_all_object.owner%type
index by binary_interger;
v_owner c1_owner;
[Code]....
View 7 Replies
View Related
May 7, 2008
have one XL sheet Doc that has 2 tabs. One has the list of keywords for deleting the records from Oracle that has the keywords in it. The second one needs the update query. Read the first column in the XL sheet and replace it with the second column value for the records that has the first column value in it.
I never worked in the combination of XL and Oracle.
View 3 Replies
View Related
Aug 16, 2010
how can i update CLOB values in Oracle? I am passing string values to Clob datatype from .Net Eg '13223311','12122112','122125552'
View 6 Replies
View Related
Mar 30, 2010
I am trying to create some PL/SQL that will create a trigger for UPDATES and INSERTS which will update a column on the same row to the system date.
So far I have:
DECLARE
tablename VARCHAR(30) := 'table';
triggername VARCHAR(30) := 'mytrigger_' || tablename;
[Code]....
I have declared the tablename and triggername outside the trigger creation as I need this to be parameterised for a list of tables.
The procedure IsCDCUser just checks it should be updating for this user.
The problem I get is the following error:
Bind Variable "NEW" is NOT DECLARED
View 3 Replies
View Related
Apr 13, 2011
This is my working query in ms access...
UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));
This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..translate this query to work on oracle?
View 4 Replies
View Related
