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

Oct 10, 2013

I have a table emp as mentioned below:

SELECT * FROM EMP_TEST;

EMP_TEST
-----------------------------------------------------
ENO EFIRSTNAME ESECONDNAME DEPTNO
1 JOHN PAI 10
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


ADVERTISEMENT

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 Insert In Index Table

Jul 8, 2013

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

View 9 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

Bulk Deletes Of Data From Huge Table

Jun 13, 2013

I am trying to delete 3 million records of data from huge table which already consists of 3 billion records.

This is hitting performance of DB and halting other activities of my users. Is there any easy way to delete such data fast. I have tried with forall delete but it is even taking lot of time.

View 5 Replies View Related

Convert Same Image To Blob - Store In A Dummy Table?

Jun 4, 2013

I have a table where user can store images in clob format.Need to convert the same image to blob and store in a dummy table.

I tried using this
--function creation
CREATE OR REPLACE FUNCTION FN_CLOB_TO_BLOB(CLOB_IN IN CLOB) RETURN BLOB IS
POS PLS_INTEGER := 1;
BUFFER RAW(32767);

[code]...

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
*Action:

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

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

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
or
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 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 View Related

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 :

INSERT INTO ABCTEMP SELECT * FROM DEFTEMP;

OR

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

View 6 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);
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

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

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

Forms :: Read Data From Table And Insert To Another Table With A Cursor?

Feb 20, 2013

I have a table with a BLOB column ;

I want read data from table and insert to another table with a cursor

My code is :

procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;

[code]....

when I run form , error FRM-40734 occurred

error in line " fetch .... "

View 1 Replies View Related

SQL & PL/SQL :: To Insert Data Into A Table

Mar 25, 2010

I'm trying to insert data into a table, but can't seem to get the actual code right, when I put it in, Here is the code I have that is giving me the error:

INSERT INTO Orders VALUES('00001', 'c001', 'ca01', '20.5', TO_DATE('12032009', 'DDMMYYYY');

View 4 Replies View Related

PL/SQL :: Insert Data Into One Table From Other?

Oct 7, 2012

i have one table name called TRXN with 43gb data with primary key...i have other table named TRXN_P with 15gb data...some of the records of TRXN_P are already there in TRXN table....i want to insert that remaining data in TRXN_P into TRXN table?

how can i insert ?

View 6 Replies View Related

Insert Data In Table

May 4, 2013

i created and inserted data to my database using SQL plus. all tables has been created sucsefully and i can see them in OUI. but all tables rows shown empety(it means under the rows, column is empety not 0). now how can see the data which i inserted to tables ? can i see them in OUI?(web interface)

View 12 Replies View Related

SQL & PL/SQL :: How To Insert Data From XML File To Table

Jul 2, 2010

use XML package & UTL_FILE package & i had install the software Oracle Database 10g Express Edition, Oracle Client 10g Express Edition & Oracle SQL Developer

I have tried different methods as mentioned below:

XML File : trailxml.xml stored in C:OracleProject

<?xml version = '1.0'?>
<metadata>
<Zipcodes>

[Code].....

View 21 Replies View Related

SQL & PL/SQL :: Insert CLOB Data To The Table?

May 28, 2010

I need to pass a large data into one of the tables where the column is declared as CLOB before which I was checking with the sample code as below which is throwing an error.

I was trying to insert the CLOB data into the table as below.

create table t1
( x number,
y clob
);
insert into t1(x) values (2)
declare
l_clob t1.y%type;

[code].....

The error I am getting is:

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 833
ORA-06512: at line 161

View 1 Replies View Related

SQL & PL/SQL :: Insert Data Into Lookup Table?

Jan 5, 2012

I have 2 tables, TABLE1 AND LOOKUP_TABLE. The requirement is to insert the distinct (AREA,CITY) from
TABLE1 into LOOKUP_TABLE, where LOOKUP_TABLE.LOOKUP_ID = LOOKUP_SEQ.NEXTVAL;

-- Test Case

DROP SEQUENCE lookup_seq;
CREATE SEQUENCE lookup_seq START WITH 1;
DROP TABLE table1;
CREATE TABLE table1

[code]...

Expected data in LOOKUP_TABLE
LOOKUP_ID AREA CITY
--------- --------- -------
1 area1 city1
2 area2 city2
3 area3 city3
4 area4 city4
5 area5 city5

Currently this is what I'm doing.

INSERT INTO lookup_table SELECT sl_no, area, city FROM table1;
DELETE FROM
lookup_table a
WHERE

[code]...

Is there a better way of doing this, using a single insert statement?

View 8 Replies View Related

Incorrect Data Value When Insert Into Table

Sep 25, 2013

Would like to ask expert here, how could I insert data into oracle table where the value is 03 ? The case is like that,  the column was defined as varchar2(50) data type, and I have a csv file where the value is 03 but when load into oracle table the value become 3 instead of 03.

View 8 Replies View Related

SQL & PL/SQL :: Insert Data Into Object Relational Table

Dec 26, 2010

I had my previous table model as below.

CREATE TABLE Rel_Module (
Id NUMBER (6) PRIMARY KEY,
Type CHAR (7) NOT NULL,

[Code]...

How I can easily load data into this new REL_MODULE table either using SQL or PL/SQL (e.g., Procedures, Functions, and Triggers). I don't need to write so many INSERT statement.

View 20 Replies View Related

SQL & PL/SQL :: Job Schedule - Insert Data Into Table Temporary

Nov 7, 2012

how made a success of insert data into table temporary in job_schedule ? because when i tried to change insert into table permanent always successfully.

CREATE GLOBAL TEMPORARY TABLE YG_PAYMENT_TMP
( SITE_CODE VARCHAR2(100 BYTE),
KBON VARCHAR2(100 BYTE),
FUTURE_DATE DATE

[code]...

why insert into table temporary not successfully in job_submit.

View 3 Replies View Related

Forms :: Insert Data And Then Update Table

Aug 5, 2010

i have a tabular form select * from emp and i want to create table and store there data in goup select empono,sal,com group by dept i want to insert in another table.

how i insert the data in table by forms front end and then update also when again click the button or any change occur in form insert into a select empono,sal,com group by dept

View 2 Replies View Related

Forms :: How To Insert Data In Two Table In Same Time

Feb 4, 2012

how to insert data in two tables in same time

table1 --master table
table2 --transaction table

View 13 Replies View Related

SQL & PL/SQL :: Select Data From 2 Tables And Insert Into Another Table

Apr 25, 2013

I want to select data from different tables and insert this into one table based on some conditions:

SELECT *
FROM welltest_msr
WHERE well_s = 3419740
AND check_ind = 1

[Code]....

So I tried doing this with selecting the data and looping through it to do the insert.

DECLARE
--
-- WELLS
--
CURSOR c_well

[code].....

View 6 Replies View Related

Forms :: Updating - Insert Some Data In Transaction Table

Feb 1, 2011

i want to insert some data in the transactions table on after insert trigger. Which trigger should i use on form level to accomplish this task.

View 4 Replies View Related







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