Stack Overflow
Stack Overflow

Questions tagged [bulk-collect]

84 questions
1
vote
1 answer

How to bulk collect into a Type where UDT in subquery

I'm trying to create a collection of values for which i plan on running a FORALL DELETE FROM [table] on. When i have a UDT in the subquery nothing seems to get bulk collected into the collection. This does not seem to be working. SELECT ATTR1 BULK…
XmalevolentX
  • 121
  • 1
  • 2
  • 9
1
vote
0 answers

BULK COLLECT and FORALL with parameters

I'm trying to use BULK COLLECT and FORALL to speed up the changes I need to make. I am using 2 cursors, the first one returns +6000 records and i need a parameter of this one to execute the second cursor, which must execute 300 updates for each…
Montefusco
  • 11
  • 1
1
vote
3 answers

Oracle - .CSV file to multiple tables at once

I have to load .CSV file in to my Oracle Tables. but the thing is that the single CSV file will have data for multiple Tables. The trick is we need to identify the first column data to insert into specific table. i.e. if first column has value '16'…
124
  • 2,757
  • 26
  • 37
1
vote
3 answers

FORALL Bulk Collect - Need to add a sequence to insert statement without using a loop?

So I have a FORALL bulk collection that I am using to insert values into a table. Usually I would use a loop for the sequence field that has to be populated. Example would be like this........ seqno = seqno +1 .....and then I would loop through…
jck7282008
  • 61
  • 1
  • 8
1
vote
1 answer

What is maximum limit of bulk collect In nested table(i am using it for 10 million records)

I have used nested table to collect data of a table.And used this data to insert in another table.Below is code I am using.I am curious about capacity of bulk collect. begin type bandtvarray is table of BANDWISETVCOVERAGE%rowtype; …
Vineesh Choudhary
  • 23
  • 8
1
vote
4 answers

Oracle PL/SQL 6504 with BULK COLLECT

I have this simple query: SELECT MEASURE_ID, MEASURE_VALUE FROM MY_TABLE; At the moment returning just a couple of records (in the future there will plenty of them): 8 265.7 7 559.6 A DESC on such table provides: Name Null Type …
serkelion
  • 73
  • 2
  • 8
1
vote
1 answer

Oracle Bulk Collect into Collection using LOOP

http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html In this page they have mentioned that: When you are using BULK COLLECT and collections to fetch data from your cursor, you should never rely on the cursor attributes…
Natty
  • 141
  • 1
  • 2
  • 14
1
vote
1 answer

FORALL insert destination table then a FORALL delete from source table

Cam the below be done without issues? Where as soon as you insert a record into the destination table you can then delete that record from the source table. Obviously this record is in memory within the loop, can you see any problems with this or…
Shaun Kinnair
  • 495
  • 2
  • 10
  • 27
1
vote
3 answers

PL/SQL: Getting Error ORA-006550 PLS-00306 in stored procedure

I developed a stored procedure that send notification emails once so that I do not get 100,000 separate emails from my explicit cursors, "crs". The stored procedure name is, "a_test_email". I created an explicit cursors named, "crs". The cursor…
caa35
  • 71
  • 2
  • 10
1
vote
2 answers

How to store the result of an execute immediate in PLSQL?

I have a table which has select queries and insert queries stored as varchars. I have to execute the select query and insert the result of the select query using the insert query using a procedure. Right now I'm executing immediate and bulk…
cumberdame
  • 41
  • 2
  • 9
1
vote
0 answers

Global temporary table and bulk collect

i'm gonna to explain you my problem. I have to convert a t-sql script into a pl/sql script. There is my code in t-sql : CREATE TABLE #temp_tb ( temp_row nvarchar(max)) if @@error <> 0 goto lbl_end DECLARE @bulk_cmd varchar(1000) set @bulk_cmd =…
S. Guillaume
  • 67
  • 1
  • 1
  • 12
1
vote
0 answers

Does bulk collect affect multiple sesssions?

I have a piece of code that uses tables as well as PL/SQL tables and collections. This piece of code runs for multiple sessions (multiple companies in our business terms) create or replace TYPE TY_REC FORCE IS OBJECT ( : : ); create or…
Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72
1
vote
2 answers

SELECT from a bulk collection

Is it possible to select from a bulk collection? Something along these lines: DECLARE CURSOR customer_cur IS SELECT CustomerId, CustomerName FROM Customers WHERE CustomerAreaCode = '576'; TYPE customer_table IS TABLE OF…
Wonko the Sane
  • 10,623
  • 8
  • 67
  • 92
1
vote
2 answers

Return data rows from a pl/sql block

I want to write pl/sql code which utilizes a Cursor and Bulk Collect to retrieve my data. My database has rows in the order of millions, and sometimes I have to query it to fetch nearly all records on client's request. I do the querying and…
shasan
  • 178
  • 2
  • 13
1
vote
2 answers

How to use BULK COLLECT and FORALL to replace CURSOR FOR LOOP in PL/SQL?

How to use BULK COLLECT and FORALL to replace CURSOR FOR LOOP in PL/SQL? I'd like to have a more efficient way to update records in a single table. Suppose I have the following PL/SQL code: DECLARE var_buy_more_shoes …
Hank
  • 189
  • 2
  • 7
  • 20
1
2
3 4 5 6