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

SQL & PL/SQL :: Getting Numeric Or Value Error In Bulk Collect Insert?

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

SQL & PL/SQL :: Using Bulk Collect For Insert Into Table - Internal Error Code

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

PL/SQL :: Normal Insert Proc - Bulk Collect Taking More Time

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

SQL & PL/SQL :: Insert Data Into Another Table With FORALL Bulk Collect Limit 1000

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

SQL & PL/SQL :: Bulk Select And Insert

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

SQL & PL/SQL :: Insert And Update Through A Select Statements

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

Number Of Rows Inserted Is Different In Bulk Insert Using Select Statement

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

SQL & PL/SQL :: What Is Bulk COllect

Jan 30, 2011

What is Bulk COllect and How it can be use

View 2 Replies View Related

SQL & PL/SQL :: Insert Statements / Give A Commit One By One After Each Insert Statements?

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

SQL & PL/SQL :: Bulk Collect Of Procedure

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

SQL & PL/SQL :: Bulk Collect Into Varray

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

SQL & PL/SQL :: Query On Bulk Collect

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

SQL & PL/SQL :: Bulk Collect In Load

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

Bulk Collect With DBLink?

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

SQL & PL/SQL :: Procedure Bulk Collect

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

SQL & PL/SQL :: Handling Exceptions In Bulk Collect

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

SQL & PL/SQL :: Bulk Collect Inside A Loop?

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

SQL & PL/SQL :: Good Bulk Collect Limit?

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

SQL & PL/SQL :: How To Implement (score-proc) Using Bulk Collect

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

SQL & PL/SQL :: Bulk Collect And Return Record As Out Param

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

SQL & PL/SQL :: Table Level Constraints And Bulk Collect

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

PL/SQL :: Assigning Varchar2 Index On Bulk Collect

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

Performance Tuning :: Bulk Collect Over DBLink?

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

PL/SQL :: Bulk Collect Update Data Just Hangs?

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

SQL & PL/SQL :: Bulk Collect - Update Two Columns Without Any Filtration

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

SQL & PL/SQL :: BULK COLLECT And Employ LIMIT Of 1000 With FORALL

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

Forms :: Compiling Error When Using Bulk Collect In Oracle 10g?

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

SQL & PL/SQL :: Forall Update With Bulk Collect For Multiple Columns

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

Bulk Collect Cursor That Can Reduce Number Of Context Switches

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







Copyrights 2005-15 www.BigResource.com, All rights reserved