Stack Overflow
Stack Overflow

Questions tagged [bulk-collect]

84 questions
7
votes
1 answer

How do I select from Bulk Collected Table of Records Type

I've got a procedure where I need to cache some data, for performance reasons, for downstream operations. The TYPE definitions work The BULK COLLECT INTO works The SELECT does not work PROCEDURE MYPROC((PARAMS))AS TYPE REC_TYPE IS RECORD ( …
Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
6
votes
2 answers

Bulk Collect Twice over same nested table

Is there any way that after the second bulk collect, data does not get override of the first bulk collect. I don't want to iterate in loop. DECLARE TYPE abc IS RECORD (p_id part.p_id%TYPE); TYPE abc_nt IS TABLE OF…
Gaurav Soni
  • 6,278
  • 9
  • 52
  • 72
3
votes
1 answer

Strange behaviour of BULK COLLECT

I tinkered together the following PL/SQL BULK-COLLECT which works astonishingly fast for updates on huge tables (>50.000.000). The only problem is, that it does not perform the updates of the remaining < 5000 rows per table. 5000 is the given limit…
royskatt
  • 1,190
  • 2
  • 15
  • 35
3
votes
2 answers

bulk collect using "for update"

I run into an interesting and unexpected issue when processing records in Oracle (11g) using BULK COLLECT. The following code was running great, processing through all million plus records with out an issue: -- Define cursor cursor My_Data_Cur…
Grant
  • 33
  • 1
  • 1
  • 3
3
votes
1 answer

Using Oracle PL/SQL table of record with multiple %rowtype fields

How can I populate a table of records which has more than one field of %rowtype using bulk collect? my code: drop table child_table; drop table parent_table; / create table parent_table(pk number primary key); create…
Jakob
  • 778
  • 3
  • 9
  • 25
3
votes
3 answers

Two (or more) DMLs inside one bulk collect operation loop

I have problem with BULK COLLECT logic on Oracle 11g. The original logic in stored procedure is: PROCEDURE FOO(IN_FOO IN VARCHAR2) IS BEGIN FOR CUR IN (SELECT COL1,COL2,COL3 FROM SOME_TABLE) LOOP INSERT INTO OTHER_TABLE (C1,C2,C3) VALUES…
WBAR
  • 4,924
  • 7
  • 47
  • 81
3
votes
1 answer

oracle - multiple insert into type table collection

I have created the following object in oracle 11g. CREATE OR REPLACE TYPE myObject as object( fieldOne number, fieldTwo number ); And created a new table type of myObject; CREATE OR REPLACE TYPE myTable IS TABLE OF myObject; I would now like to…
Timbob
  • 35
  • 1
  • 4
2
votes
2 answers

How to do conditional processing in a bulk collect loop?

we have Oracle 11G and i'm trying to move data from one table to another using bulk collect. Problem is when I tried to evaluate if one field from origin is empty my package got invalidated. What I have: Declaration: CREATE OR REPLACE PACKAGE…
downtheroad
  • 409
  • 4
  • 11
2
votes
1 answer

Insert bulk records in to remote database (dblink) using Bulk Collect

I want to insert huge records from different tables in to a destination remote table 'Audition_Detail' using DBLINK - @FMATLINK. I have used Bulk collect, but its throwing errors. I have gone through some links too: Overcoming the restriction on…
swetha reddy
  • 201
  • 5
  • 19
2
votes
2 answers

PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list

I have created a procure to display the data in two table using BULK COLLECT, but i keep getting this error. PLS-00497: cannot mix between single row and multi-row (BULK) in INTO list However it works if i remove the BULK COLLECT and include a…
jack
  • 21
  • 1
  • 1
  • 3
2
votes
2 answers

Oracle PLSQL BULK Collect and For Loop

I have written the following oracle procedure to fetch data in bulk and process it in blocks. I am using the bulk collect option with limit to fetch the data. But inside for loop i am not able to retrieve the ORD_ID. I am trying to output the ORD_ID…
Andromeda
  • 12,659
  • 20
  • 77
  • 103
2
votes
2 answers

bulk collect into table type of objects

I got en error when attempting to use a BULK COLLECT statement ORA-00947: not enough values for table of objects. Error occurs at the line from (select jta.nobject_id, CREATE OR REPLACE TYPE "T_PPW_WORK" as object …
adelak
  • 647
  • 4
  • 11
  • 25
1
vote
2 answers

oracle FOR LOOP does not iterate in SYS_REFCURSOR

Here is the Procedure: Opening a cursor and then fetching the output of select query through bulk collect. Issue is all the ID's are getting stored in bulk collect but I am unable to loop through the second select query by using the bulk collect…
Linnea
  • 65
  • 1
  • 6
1
vote
4 answers

How to fetch the Rowid with all the columns of a table using bulk collect for a cursor in oracle

declare cursor c_1 is select a.*, a.rowid from an_test a; type t_1 is table of an_test%rowtype; type l_row_id is table of UROWID; tab t_1; row l_row_id; begin open c_1; loop fetch c_1 bulk collect into tab,…
Node98
  • 23
  • 6
1
vote
1 answer

How to set a Limit on Bulk Collect with a Nested Table PL/SQL Collection?

I'm trying to set a limit on a database query with the limit keyword. This is my current working query without the limit keyword. DECLARE TYPE NESTED_TABLE_DECLARATION IS TABLE OF SCHEMA.TABLE_NAME%ROWTYPE; NESTED_TABLE…
Matt
  • 135
  • 3
  • 17
1
2 3 4 5 6