Stack Overflow
Stack Overflow

Questions tagged [bulk-collect]

84 questions
0
votes
1 answer

oracle bulk collect limit clause execution details using the sys refcursor

I am creating a stored procedure to bulk collect the data from one table (table 1) to another table (table 2) using limit clause . For example if I am loading 80000 records with the limit clause of 1000 how many times the select statement in the…
user2899615
  • 31
  • 1
  • 1
  • 10
0
votes
1 answer

PL/SQL: ORA-00947: not enough values

Im creating a procedure to display the n number of maximum and minimum salary for an employee. If i ll give 5 as input, the query will get me 5 maximum and minimum salary for an employee. For the above scenario, I have created an object with two…
Raja sekar
  • 79
  • 2
  • 11
0
votes
1 answer

ORA-00947 not enough values with function returning table of records

So I'm trying to build a function that returns the records of items that are included in some client subscription. So I've been building up the following: 2 types: CREATE OR REPLACE TYPE PGM_ROW AS OBJECT ( pID NUMBER(10), …
Marc
  • 352
  • 2
  • 6
  • 19
0
votes
1 answer

More efficient way to use cursor loop, insert into and commit for each row (Oracle)

I writed something like this below (it works), but in my case for 1,5 milions rows it is not so effective as I need (it will run maybe 2 days) I saw something like BULK COLLECT FETCH FORALL etc. but I am not managing to rewrite my code to this…
palo173
  • 13
  • 9
0
votes
1 answer

Bulk insertion of records using BULK COLLECT and FORALL

I am a beginner to PL/SQL and fetching huge amount of records from different tables and want to insert in to a one single table in PL/SQL using anonymous block with BULK COLLECT and FORALL. Could anybody help me out in deciding whether the following…
swetha reddy
  • 201
  • 5
  • 19
0
votes
1 answer

usage of LIMIT option in SELECT ... BULK COLLECT INTO

Starting from the answer of this question (Use Bulk Collect result in a select query without cursor), I am wondering if it is possible to use the LIMIT option in SELECT ... BULK COLLECT INTO ... I know the possibility to use an explicit cursor but I…
mikcutu
  • 1,013
  • 2
  • 17
  • 34
0
votes
2 answers

Using BULK COLLECT with rownum

Below query does Bulk select and then update the records in loop. I want to use BULK COLLECT INTO and FORALL constructs here to improve performance.But query uses rownum to update a column in loop. Is there anyway I can use BULK COLLECT INTO while…
Khushi
  • 325
  • 1
  • 11
  • 32
0
votes
1 answer

Bulk Collect with Sum function

I am trying to use Bulk all and Forall in Oracle database: Original code from Procedure is as below: IF NVL(v_mc,0) != 0 THEN FOR rec IN (SELECT a.testid, SUM(pct * NVL(cap,0))/v_mc lead1 BULK COLLECT INTO…
Khushi
  • 325
  • 1
  • 11
  • 32
0
votes
1 answer

PL Sql BULK COLLECT SQL command not property ended

I am trying to use BULK COLLECT INTO but I get an ORA-00933 SQL command not properly ended. I'm looked at my syntax but not sure what I am doing wrong. Am I doing something wrong? The issue seems to be with the RETURNING clause. If I remove the…
elesh.j
  • 192
  • 1
  • 3
  • 15
0
votes
2 answers

PL/SQL: ORA-00907 : Error in Bulk collect

Is there a way that we use the distinct & alias (as), to achieve the bulk collect query as below. SELECT distinct OBJ_TEST ( EMP_ID as E1, EMP_NAME) BULK COLLECT INTO LVOB_TEST FROM TMP_EMP ;
user3359124
  • 13
  • 2
0
votes
2 answers

How to put a part of a code as a string in table to use it in a procedure?

I'm trying to resolve below issue: I need to prepare table that consists 3 columns: user_id, month value. Each from over 200 users has got different values of parameters that determine expected value which are: LOB, CHANNEL, SUBSIDIARY. So I…
Maciej Kasprzak
  • 1
  • 2
0
votes
1 answer

Issue in Converting composite type of Oracle to Postgres and Bulk collect compatible equivalent in Postgres

In Oracle we have a function where we are using bulk collect to fetch data and store in a variable like. SELECT DISTINCT emp_id BULK COLLECT INTO v_emp_array FROM emp_details; where v_emp_array is a type: Oracle: CREATE OR…
Pooja
  • 327
  • 1
  • 5
  • 20
0
votes
1 answer

Oracle 11g: How to dynamically select multiple columns into BULK COLLECT

I have a query like the example below where I want to dynamically bulk collect 2 columns using a join into from a table that has 4 columns, but I keep getting the error that 'variable not in select list'. I believe this is because it is because the…
Jeremiah Reed
  • 83
  • 1
  • 2
  • 10
0
votes
1 answer

Individual Record Logging While Handling Large no of records

I am having a procedure that will assign Products against Existing List of Orders. For rec_ord in(Select Order_ID,Order_Prop1,Order_Prop2, from Order_Master Where ) Loop
Debabrata
  • 162
  • 9
0
votes
1 answer

oracle bulk collect and reading the data

I have created below proc to read all the data from one table and populate it in a grid in .net form. CREATE OR REPLACE PROCEDURE EVMPDADM.GETALLBATCHES_ARTICLE_57(p_batchstatus OUT XEVMPD_SUBMITTEDBATCH%ROWTYPE ) IS TYPE batch_status IS TABLE OF…
Jig232
  • 27
  • 1
  • 1
  • 9
1 2 3
4
5 6