SQL & PL/SQL :: Bulk Collect And For All Statements To Select And Insert
Feb 19, 2013
I used bulk collect and for all statements to select and insert the data in temp table.The select SQl is returning one row. But its not inserting this row into temp table.Its not throwing any exceptions. Used ref cursor because the select statement is going for every cursor.
here modified the code and provided only one cursor.
Create Or Replace Procedure Sales_Hist_Update_Bkp Is
Type Type_Name Is Record(
Sku_Item_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Sku_Item_Key%Type,
Locationno Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Locationno%Type,
Bsns_Unit_Key Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Bsns_Unit_Key%Type,
Act_Item_Cost_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Item_Cost_Amt%Type,
Act_Rglr_Unit_Price_Amt Ordm_Int.Dwi_Rtl_Sls_Retrn_Line_Bkp.Rglr_Unit_Price_Amt%Type,
[code]...
SQL> declare 2 TYPE id_collection is TABLE of number(6); 3 TYPE ename_collection is TABLE of varchar2(20); 4 id ID_COLLECTION; 5 ename ENAME_COLLECTION; 6 cursor c is select empid,name from Nemp; 7 begin 8 open c; 9 loop
[code]....
Here sub_Nemp is my new table in which i have to insert the values from Nemp old table.Both tables are same like below:-
I am working on oracle 11g...I have one normal insert proc
CREATE OR REPLACE PROCEDURE test2 AS BEGIN INSERT INTO first_table (citiversion, financialcollectionid, dataitemid, dataitemvalue, [code]....
I am processing 1 lakh rows.tell me the reason why bulk collect is taking more time. ? According to my knowledge it should take less time. do i need to check any parameter?
Table contains 10k records,we are going to insert data into another table with FORALL bulk collect limit 1000. if i use 10000 ,it's completed fast compared to 1000 limit.Can u tell me which one is better Limit.
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.
I am facing a problem in bulk insert using SELECT statement.My sql statement is like below.
strQuery :='INSERT INTO TAB3 (SELECT t1.c1,t2.c2 FROM TAB1 t1, TAB2 t2 WHERE t1.c1 = t2.c1 AND t1.c3 between 10 and 15 AND)' ....... some other conditions.
EXECUTE IMMEDIATE strQuery...These SQL statements are inside a procedure. And this procedure is called from C#.The number of rows returned by the "SELECT" query is 70.
On the very first time call of this procedure, the number rows inserted using strQuery is 70. But in the next time call (in the same transaction) of the procedure, the number rows inserted is only 50.And further if we are repeating calling this procedure, it will insert sometimes 70 or 50 etc. It is showing some inconsistency.On my initial analysis it is found that, the default optimizer is "ALL_ROWS". When i changed the optimizer mode to "rule", this issue is not coming.I am using Oracle 10g R2 version.
Can we execute more than one insert statements at a time (eg 10) in database and givecommit at the end of insert statements or else give a commit one by one after each insert statements ?
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?
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.
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]..........
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
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;
For the following procedure if I send the existed employee number of emp table as input. The procedure is executing successfully. But if I send the employee number as input which does not exist in the emp table . The execution block does not handling the exception.
I am getting the following error.
ORA-06502: PL/SQL: numeric or value error ORA-06512: at "RAKULA.SP_TEST_EXCEPTION_BULK", line 8 ORA-06512: at line 7
If I use WHEN OTHERS exception then I am able to handle that exception. Why it's happening like this.
CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception_bulk(i_empno NUMBER) IS t type_test1; BEGIN SELECT deptno BULK COLLECT INTO t FROM emp WHERE empno=i_empno; [code].......
how to handle that exception.
If I create the procedure without using
BULK COLLECT then I am able to handle that exception using WHEN NO_DATA_FOUND
In the following procedure I am able to handle the exception.
CREATE OR REPLACE PROCEDURE RAKULA.sp_test_exception(i_empno NUMBER,v_dept_no OUT NUMBER) IS BEGIN SELECT deptno INTO v_dept_no FROM emp WHERE empno=i_empno; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line('employee number' ||i_empno|| 'does not exist'); END sp_test_exception; /
I have a select..bulk collect into clause which is inside a for loop as the query gets a parameter from the loop, Then, how to extend the collection rows for each iteration.
The procedure uses bulk collect to fetch from a normal cursor, Then I am using for all to insert into target table, The number of rows are 234965470
Question: What should ideally be the limit for my bulk collect ?
According to below, it should be in hundreds[URL]...
I put a bulk collect limit of 50000 - took close to 2 hours then i tried 10000 - just 3 mins shorter than the above time But if you commit every 500 rows, Then is there not another theory that frequent commits is not good ?
In my below code the procedures "total_score_proc" and "CopyInternalScores" are calling "score_proc" procedure 50 times for different variable values.
Instead of calling the "score_proc" procedure 50 times.I want to hold the values in to collection , defining it in package and call that procedure only once.
how to implement "score_proc" using bulk collect.
CREATE OR REPLACE PACKAGE total_score_pkg IS PROCEDURE total_score_proc(pBUID IN STAGING_ORDER_DATA.BUID%TYPE, OrderNum IN STAGING_ORDER_DATA.ORDER_NUM%TYPE,
The following is the Record type and table type created. It is called in the below procedure. The procedure will be called in another procedure to return the records. But the count is showing as 0. How I get an OUT param with records which I can use in the calling procedure.
-- Record type type t_ein_cmpl_rec IS RECORD( IN_req_param_id t_IN_req_param_id, IN_call_request_id t_IN_call_request_id, IN_action t_IN_action, IN_event_id t_event_id, IN_ported_nbr t_IN_ported_nbr, [code]........
if it is possible to assign a varchar2(14) index to a pl/sql table while fetching bulk data with bulk collect.my requirement is to assign varchar2(14) index to a pl/sql table so that i can directly reach to my record by index and process the record further.
further i need to run a loo on this ( for processing each record)my database version is 11.2.01.0.
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 CURSOR GET_ORD_DET IS ( SELECT ORD.ROW_WID ORDER_ID FROM SRMW.W_ORDER_D@ARCHIVAL_TO_CRMSPA2 ORD WHERE (
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;
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.
I have an app that reads records from a driver table in order to update another (account) table. The idea is that it runs quickly but does not impact other processes on the system. In total I have around 1M records to update.
Originally I wanted to BULK COLLECT and employ a LIMIT of 1000 with a FORALL. The problem with this approach though is that I make two updates. One to the target table but I also need to update the driver table showing that I have processed the record. Therefore I cannot use this option to commit every time I reach the LIMIT by having a COMMIT inside the LOOP.
So instead I I have a FOR LOOP and test the count, if it is 1000 then I want to commit. I thought this syntax would be fine but I get the 'Fetch Out of Sequence' error. Below is a copy of the code.. Is this being caused by the double update... as the format of the code looks correct to me (though it is late!!)?
PROCEDURE update_set IS CURSOR cur_get_recs IS SELECT account_num, ttw_active_flag, acct_rowid, rowid driver_rowid FROM driver_table FOR UPDATE OF processed; [code]...
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);
Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.
