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]...
View 11 Replies
ADVERTISEMENT
Sep 23, 2012
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:-
SQL> create table sub_Nemp(empid number(6),name varchar2(20));
I'm unable to find this error...
View 7 Replies
View Related
Sep 29, 2011
Using the Bulk collect for insert into table,it's raising the below error.
ORA-00600: internal error code, arguments: [25027], [130], [1], [], [], [], [], [], [], [], [], []
View 5 Replies
View Related
Feb 4, 2013
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?
View 5 Replies
View Related
Aug 21, 2011
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.
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
Jul 7, 2010
how can i insert and update to a table in oracle database 10g through a select statement. not using merge.
View 2 Replies
View Related
Jul 1, 2010
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.
View 3 Replies
View Related
Jan 30, 2011
What is Bulk COllect and How it can be use
View 2 Replies
View Related
Oct 11, 2012
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 ?
View 8 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
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
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
Aug 17, 2012
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;
/
View 2 Replies
View Related
Mar 8, 2012
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.
My collection is of a sql object type.
View 5 Replies
View Related
Jul 21, 2011
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 ?
Is there something I have to ask the DBAS ?
View 4 Replies
View Related
May 20, 2013
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,
[code]....
View 7 Replies
View Related
Mar 25, 2013
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]........
View 2 Replies
View Related
Jun 2, 2010
1. how can i impose a restriction on a table so that the data gets updated only specific period of time say 9 a.m. to 10 p.m.
2. Can i use bulk collect in dynamic sql? If yes how?
View 5 Replies
View Related
Dec 14, 2012
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.
View 4 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
CURSOR GET_ORD_DET IS
(
SELECT ORD.ROW_WID ORDER_ID
FROM SRMW.W_ORDER_D@ARCHIVAL_TO_CRMSPA2 ORD
WHERE
(
[code]....
View 4 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
Oct 19, 2011
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]...
View 5 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
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
Oct 4, 2008
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.
View 1 Replies
View Related
