Stack Overflow
Stack Overflow

Questions tagged [bulk-collect]

84 questions
0
votes
0 answers

What is the most efficient way of comparing 1 set of data with another involving data ranges

I am trying to come up with the most efficient way of comparing current production data with data where I have collapsed date ranges. So basically I have some production data where a user will be connected to a given plan and then possible switch to…
programmerNOOB
  • 121
  • 3
  • 19
0
votes
1 answer

PLSQL DML statements inside procedure

I am trying to execute below procedure but it says me to use BULKCOLLECT and FORALL to refactor my code. Is it just a warning or should i not use like below for my procedure. If so, how should i modify the code as i am iterating through an array and…
Karthik
  • 145
  • 3
  • 15
0
votes
1 answer

Oracle Bulk Collect with Limit and For All Not Processing All Records Correctly

I need to process close to 60k records of an Oracle table through a stored procedure. The processing is that for each such row, I need to delete and update a row in a second table and insert a row in a third table. Using cursor looping, the…
Soumyorup Dey
  • 26
  • 1
  • 5
0
votes
2 answers

Why does BULK COLLECT give a bad bind variable compile error in Oracle Package PROC?

I am trying to take in 3 strings to represent a set of last names, first names, and middle names. The incoming parameters would end up looking like this: lastNames IN VARCHAR2 := NULL 'Smith/Brown/Green' firstNames IN VARCHAR2 :=…
Sam
  • 4,766
  • 11
  • 50
  • 76
0
votes
1 answer

Using bulk collect with two rowtypes

I have a cursor query which returns me record of two tables like below. cursor c1 is select teb.*,te.* from table1 teb, table2 te where te.col1=teb.col2; How should I collect them in Collections using bulk collect? I tried: type tab_rec is table…
user2974954
  • 169
  • 1
  • 11
0
votes
1 answer

Issue with Bulk Collect

I have a procedure where I need to fetch data from a cursor using bulk collect. The issue with this approach is - sometimes the records are getting processed and sometimes they arent. I am unable to identify the root cause. When I try to debug the…
Amy2015
  • 25
  • 1
  • 6
0
votes
0 answers

Bulk collect and forall with save exception

I have the below code , emp1 table has data of 12 rows in which 2 empno's already present in emp table. i'm trying to save the exceptions for these 2 records and to insert remaining all to emp. But everything is getting error out and i couldn't able…
SVS
  • 1
  • 2
0
votes
1 answer

Insert data from pl/sql table type to another pl/sql table type

I am just wondering whether it is possible to insert data from one pl/sql table to another using bulkcollect? I am trying it out but looks like it is not possible conceptually and the only way is to loop through the Pl/sql table . Any insights would…
shirjai
  • 243
  • 2
  • 5
  • 20
0
votes
0 answers

Using Rownum in Cursor Bulk Collect Oracle

I'm trying to use the rownum to simulate a column autonumbered as I need to use it as an ID. Since it is an ID, I look at the final table if no record with MAX (ID). The problem I have is when I want to do arithmetic operations within the cursor or…
alejomarchan
  • 368
  • 1
  • 10
  • 20
0
votes
1 answer

Create PL/SQL script with 2 cursors, a parameter and give results from a table?

I need to create a script that puts a key number from table A (which will be used as a parameter later), then flow that parameter or key number into a query and then dump those results into a holding record or table for later manipulation and such.…
Asian Man
  • 25
  • 1
  • 2
  • 8
0
votes
1 answer

Oracle Bulk Collect - Limit number

I read about Bulk Collect and wrote some code using it (not deployed yet). The total amount of rows returned is in the vicinity of 80.000. I limited the amount of rows returned in one batch to 10.000, but there is no basis for using this number, I…
Robotronx
  • 1,728
  • 2
  • 21
  • 43
0
votes
1 answer

In PL SQL I want to refactor this for loop with inserts, by using BULK COLLECT and FORALL

For performance reasons, I want to rewrite the following to use BULK COLLECT and FORALL, rather than doing the inserts in a loop. The problem I'm running in to, is that empID must be generated on each iteration of the loop, or I need to do something…
Plant More Trees
  • 65
  • 1
  • 1
  • 9
0
votes
1 answer

Skip to next record in a pl sql Cursor with Bulk Collect

Please analyze the below code snippet and suggest a way to skip to the next record in the cursor. BEGIN OPEN cs_migrate_drop_ntd_object_status; LOOP FETCH cs_migrate_drop_ntd_object_status BULK COLLECT INTO r_current_loc_equip LIMIT 200; …
Wamglind Carmasaic
  • 163
  • 2
  • 8
0
votes
1 answer

bulk collect into inside for loop

I have declared a custom type create type finalrecord as object(aaa varchar2(10),bb varchar2(25)); create type mytable is table of finalrecord; and in my procedure i have written a for loop create or replace procedure myprocedure(mytab out…
Kannan Sundararaj
  • 84
  • 3
  • 13
0
votes
0 answers

Returning TABLE type from Procedure

I was testing this code to return a TABLE type from a procedure, but I am getting an error which I believe should not happen as I am taking table type of the entire row of employees table. CREATE OR REPLACE PACKAGE test_pkg IS TYPE my_rec IS…
user3446787
  • 27
  • 1
  • 4
1 2 3 4
5
6