SQL & PL/SQL :: Bulk Insert Based On Record Count

Jun 19, 2012

Is there any defined record count range for the following ways of bulk insert :



through a cursor, bulk fetch and bulk insert under a loop.

View 6 Replies


SQL & PL/SQL :: Count Number Of Record Based On Some Condition

Jun 28, 2013

i have two tables one as test_master and other one limit master.

test master table structure

ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure

limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --

ct_id and limit_id is match column used for mapping.

now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.

how to achieve the same using sql/oracle query.

View 7 Replies View Related

Record Count Mismatch Between Select And Insert

Mar 20, 2013

We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?

It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.

Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem
Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.

View 3 Replies View Related

SQL & PL/SQL :: Bulk Delete With Total Count

Feb 24, 2009

I have a table A which has million records, and one of the column is a date. All records prior to 01-FEB-2009 should be deleted.

I cannot run the below code considering the amount of records or rows in the table A.

Delete from A where trunc(date_column) < '01-FEB-2009';

where we can delete the rows prior to 01-FEB-2009 and also have a total count of how many were deleted?

View 14 Replies View Related

SQL & PL/SQL :: Delete After Count Oldest Row Query BULK

Mar 24, 2011

I like to increase speed to delete our table. Is it possible to use BULK COLLECT or FORALL this query? This is not single delete or select, maybe I got the error? Is it possible to use BULK method to this query?

delete from
rowid in (

View 1 Replies View Related

PL/SQL :: Selecting Single Record From Multiple Record Based On Date?

Aug 26, 2013

I have a table which contains the multiple records for single ID No. Now i have to select single record which contains the latest date. here is the structure Name  

Null Type  ------ ---- ------------ ID_P        NUMBER       NAME_P      VARCHAR2(12) DATE_P      TIMESTAMP(6) Records---------------------1 loosi     22-AUG-13 PM1 nammi  26-AUG-13 PM2 kk        22-AUG-13 PM2 thej      26-AUG-13 PM 

now i have to select below 2 rows how can write select qurie for this?

1 loosi 26-AUG-13 PM2 thej  26-AUG-13 PM

View 4 Replies View Related

Error During BULK Insert?

Sep 28, 2011

I made two runs for bulk insertion

In first run, 16,36,897 were inserted successfully in around 38 seconds.But in second run, 54,62,952 records had to be inserted, but process failed after 708 seconds with following error :

Error report:
ORA-04030: out of process memory when trying to allocate 980248 bytes (PLS non-lib hp,DARWIN)
ORA-06512: at line 21
04030. 00000 - "out of process memory when trying to allocate %s bytes (%s,%s)"
*Cause: Operating system process private memory has been exhausted

Here is my code snippet :
FORALL i in products_tab.first .. products_tab.last
INSERT INTO tab1 VALUES products_tab(i);

I think that there should not have been any problem in getting it completed successfully.

View 4 Replies View Related

SQL & PL/SQL :: Bulk Insert Into File

Dec 14, 2012

We have requirement to create INSERT SCRIPT from the table having thousands of records and load that into flat file. if there are any better option other than using UTL_FILE package which process record by record.

View 5 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 :: 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,

View 2 Replies View Related

SQL & PL/SQL :: Bulk Insert In Index Table

Jul 8, 2013

why bulk insert is not possible in a table which has index?

View 9 Replies View Related

Insert Using Append / Bulk Correct?

Aug 3, 2011

I want to know which is the most efficient insert method among the followiing

1)using the hint append
2)bulk collect. Preferably I'd like to know which method of inserting for say 2-5 millions rows is the best.

View 6 Replies View Related

SQL & PL/SQL :: Bulk Insert Dummy Data Into Table

Oct 10, 2013

I have a table emp as mentioned below:


2 ABC DEF 20
3 EFG GHI 30

Now the primary key in this above table is pk_emp_test_eno on eno.

I have a requirement where i need to dump some dummy data (600000000 numbers of data ) into the emp_test based on these existing data without disabling the constraints (maintaining unique constraint for each record). And while inserting i want to commit after every 1000 insertion.

in bulk inserting dummy datas into the table as it is taking much more time to insert into the same.

View 5 Replies View Related

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,

View 11 Replies View Related

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);
6 cursor c is select empid,name from Nemp;
7 begin
8 open c;
9 loop


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

Precompilers, OCI & OCCI :: Bulk Insert To Stored Procedure?

May 12, 2009

I have an array of C structs say

struct S
int a, long b, double c;
S s[100];

Further suppose I have an Oracle table T like this:

create table T
a number(10),
b number(10),
c float

I want to bulk insert all 100 instances of S from a client application into T. I've seen code that does this for *one* field or column. The code defines a stored procedure which accepts a single argument which is a TABLE and then does a FORALL ... insert. The client application passes in the array of data.

What I need is N columns. In my example above struct S has N=3 fields which conform to the N=3 columns in T. In reality my N will be 50+. I am trying to avoid creating stored procedures which will take the 50 or so arguments it will eventually need.

So does my stored procedure need to accept N TABLE arguments? Or can I cajole OCI/OTL/ODBC and PL/SQL so that the stored procedure can take an array of rows which the type of row conforms to T by defining a record or something? That is, do I need:

Option 1: // declares one type and one argument each for N cols
create or replace procedure insert_S(
a_array IN A_TABLE, -- type A_TABLE is TABLE of number;
b_array IN B_TABLE, -- type B_TABLE is TABLE of number;
c_array IN C_TABLE) -- type C_TABLE is ...
begin ... end

Option 2: // this somehow accepts an array compatible with T
// if I could get a OCI/OCCI/OTL/ODBC application
// to send this data, this procedure would have
// only one argument
create or replace procedure insert_S(
row_array IN ?????????? type -- some sort of array of rows
begin ... end

Or should I pass the whole memory chunk of data in as an image or varchar array -- basically an opaque block of data -- and then internally decypher/decode the memory block inside the stored procedure as discussed on [URL].

best way to pass an array of N C-structs of M fields to a stored procedure for insertion into a table with M compatible columns? One TABLE per column? with an array of a custom type compatible with a row in T? As glob of data? Another option is to populate some host variables ... but, again, I'd need N host variables.

View 1 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 :: 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

     INSERT INTO first_table
     (citiversion, financialcollectionid,
     dataitemid, dataitemvalue,

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

Client Tools :: INSERT INTO With GUI - Insert Record Greyed Out?

Oct 28, 2013

I wish to make this simple statement with Toad GUI

VALUES ('xxx',

Insert record is greyed out. How to insert new rows with Toad (click click)?

View 7 Replies View Related

SQL & PL/SQL :: Retrieving The Data Based On Count?

Apr 2, 2010

I want to retrieve the data based on the count.

Sample data

What i want is I want the data which account is having more than 5 subaccounts within the last one month. Also need data with other date criteria like last 100 days if more than10 sub accounts. need single query.

Output is :


Having more than 8lacks in my database. i wrote the query but it is taking much time and didnt give the data even after 14 hours.

My query is :
select * from table a where account in
(select account from table b where b.subaccount=a.subaccount
and b.date>=sysdate-35
group by b.account having count(b.subaccount)>5)
select * from table a where account in
(select account from table b where b.subaccount=a.subaccount
and b.date>=sysdate-100
group by b.account having count(b.subaccount)>10)

how to retrieve the Subaccounts which satisfies my requirements.

View 33 Replies View Related

SQL & PL/SQL :: Export Based On Count Distinct

Jun 7, 2013

Is there a way to export records based on a select distinct into different xls or csv files in oracle? I could be using SQL Developer.

The following will generate 74 unique values which each contain approx 3000 records.

select distinct(telespornr from vingelen2012

View 25 Replies View Related

PL/SQL :: How To Implement Count Case Based On 2 Criteria

Feb 14, 2013

i am looking to write a query that will count all NOT NULL queue values in a table that belongs to a certain group, but not sure how to write it. The following is what I have wrote, but just wanted to clarify how to implement count case based on 2 criterias.

select *
count(case when queue is not null then 1 end) over(partition by group) as queue_orders
from table_a;

For example if I have 10 jobs that belong to group 1, but I would like to count all group 1 jobs that have a queue value.

I have no data at the mo, just something that I am trying to start off.

View 10 Replies View Related

SQL & PL/SQL :: Analytic Function To Get Count Based On Special Criteria?

Nov 4, 2010

I have the following query with analytic function but wrong results on the last column COUNT.

1)I am getting the output order by b.sequence_no column . This is a must.
2)COUNT Column :

I don't want the total count based on thor column hence there is no point in grouping by that column. The actual requirement to achieve COUNT is:

2a -If in the next row, if either the THOR and LOC combination changes to a new value, then COUNT=1
(In other words, if it is different from the following row)

2b-If the values of THOR and LOC repeats in the following row, then the count should be the total of all those same value rows until the rows become different.
(In this case 2b-WHERE THE ROWS ARE SAME- also I only want to show these same rows only once. This is shown in the "MY REQUIRED OUTPUT) .

My present query:
select r.name REGION ,
p.name PT,
do.name DELOFF,
ro.name ROUTE,


My incorrect output[PART OF DATA]:Quote:


My required output[PART OF DATA]-:Quote:


NOTE :Count as 1 is correctly coming.But where there is same rows and I want to take the total count on them, I am not getting.

View 9 Replies View Related

PL/SQL :: Updating Count Data Based On Records In Another Table

Nov 6, 2013

I have 2 tables

Table 1Name Item   DateJon  Apples  06/11/2013 00:30:00 hrsSam  OrangesNish Apples  
Table 2 - Net countName Item CountNish Apples 10Nish Oranges 17Nish BananaSam Apples 10Sam Oranges 1Sam Bananas 1Jon  Apples 8 

I need to create a job that checks Table 1 for new records added after last run and then add the count in Table 2 accordingly.how to achieve this using PL/SQl or something similar

View 2 Replies View Related

SQL & PL/SQL :: Inner Join Record Count?

Dec 24, 2012

What is the maximum number of rows that is possible to be produced by inner joining two tables having N and M (M < N) number of records on a non-prime column?

View 3 Replies View Related

Record Count Difference

Mar 19, 2013

I am trying to get a record count of this table and i get 2 different output , select count(*) shows 68560 and after updating the stats the NUM_ROWS shows 68323, why the difference .

SQL> select count(*) from dcepcd;


SQL> select num_rows from dba_tables where table_name='DCEPCD';


View 4 Replies View Related

Multiple Record Insert Using INSERT ALL

Jan 2, 2009

I'm trying to insert only a few columns (not all of them) from temp_ioi_010209 into mtl_system_items_interface. Both of these tables have more columns than just the 7 I'm specifying but everything I found under INSERT ALL here makes me think I'm doing it right. According to the DESCRIBE of mtl_system_items_interface the only non-nullable column is set_process_id and I'm specifying that one...

Why I'm getting the error "ORA-00947: not enough values"?

INSERT ALL INTO mtl_system_items_interface

View 3 Replies View Related

Forms :: Record Count - Applied And Saved?

Jan 27, 2010

Why showing at the status bar "3 records applied and saved" instated of "2 records applied and saved". 2 means 1 for Header and 1 for Detail. How I change status bar.

View 7 Replies View Related

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