SQL & PL/SQL :: Insert Into Select Or Cursor For 100k Rows
Jan 22, 2012
I am trying to insert 100K rows, I have written this proc using cursor. But it is saying anonymous block completed, and no rows are inserted. If I just run the select it returns the rows.Could if just insert into select would be fine or should I use cursor.
CREATE OR replace PROCEDURE Insert_data (l_from_date IN VARCHAR2,
l_to_date IN VARCHAR2)
IS
lc_err_msg VARCHAR2 (2000);
ln_count NUMBER := 0;
CURSOR ins_d IS
SELECT a.col1 AS url,
b.col1 AS ref_url,
COUNT (*) AS total_views
[code]....
View 5 Replies
ADVERTISEMENT
Aug 12, 2010
I have written code as below. I want to insert all rows values fetched by cursor INTO table emp2 (blank as column as employees) . How Can I insert it.
what parameter or code I should used ?
I have marked with color row as below.
DECLARE
CURSOR C1 IS select * from employees;
C2 C1%ROWTYPE;
[Code]....
View 6 Replies
View Related
Jan 12, 2011
If i inserted the values in table it gets inserting very few rows only.I dont know y it is?
View 15 Replies
View Related
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
May 7, 2013
In 11g, When I am trying to insert the records with select insert option it is failing.
Below is my Query:
insert into table_1 select * from table_2
The above query is not inserting any records into table_1. But when i query select * from table it is returning records.
The same select Insert query is inserting records properly in 10g.
View 5 Replies
View Related
Jan 2, 2013
I was wondering if I could use a to_date in an insert statement in a cursor.Would the following piece of code be valid?
create table main_table
( eff_date date,
version varchar2 (5),
user1 varchar2(10));
[code]....
View 11 Replies
View Related
Oct 6, 2010
I have an audit table FEE_RULE_AUD with the following structure.
FEE_RULEVARCHAR2(10 BYTE)
AUDIT_DATETIMESTAMP(6)
AUDIT_ACTIONVARCHAR2(30 BYTE)
AUDIT_USERVARCHAR2(8 BYTE)
NAMEVARCHAR2(30 BYTE)
FEE_BASISCHAR(1 BYTE)
FEES_ONCHAR(1 BYTE)
SECURITYCHAR(10 BYTE)
I have to generate a report for the audit with in the dates specified(range). I got the set of record for the specified date range by using the following query:
select * from fee_rule_aud where TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))>20090629
and TO_NUMBER(TO_CHAR(TRUNC(audit_date),'YYYYMMDD'))<=20100710 order by fee_rule,audit_date
this query returned some five records, now I have to iterate through this and compare 1st and 2nd row in first iteration (1st row will have the new value and 2nd row will have old vale). If there is any difference then insert into audit_log table which has the following structure:
Fee_rule , column_name, old_value,new_value
This process has to repeat for all the 5 rows like comparing 1st,2nd rows and 2nd,3rd rows and 3rd,4th rows and so on if it has more rows.
get the two rows and compare ?
View 16 Replies
View Related
Sep 27, 2013
The following code is indicative of what I'd like to do (as in not correct at all ). Would there be a more immediate way to accomplish this other than executing a SELECT statement after the UPDATE?
-- Incorrect indicative example 1.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
RETURNING <updated_rows> INTO v_cur
END;
-- Incorrect indicative example 2.
DECLARE
v_cur SYS_REFCURSOR;
BEGIN
OPEN v_cur FOR
UPDATE table1(f1, f2)
SET ('v1', 'v2')
WHERE f3 = 'v3'
END;
View 4 Replies
View Related
Apr 28, 2010
I have a cursor in a procedure that selects columns from a table on a remote Oracle database using a database link. It all works fine when the table name and database link name is 'hard coded' into the procedure but what I would like to achieve is have the select statement lookup the table name and database link name from a column in another table. See example below
CURSOR c_total_bookings IS
SELECT personnel_id,
surname,
forenames,
birth_date,
[code]...
What I would like to do is replacecompany_bookings@remote.linkwith a variable that takes its value from another table.
View 1 Replies
View Related
Nov 19, 2010
I am not an Oracle programmer but I have been given a task to produce a number of Text files from an Oracle table based on a selection from the table itself.The table consists of many records with a currency code and I need to extract the data into named files based on the currency code.My first idea was to use Cursors and try a select statement matching on the returned value of the cursor but no Joy I can't make it work - perhaps I need to take a new approach.
Current / latest attempt below
DECLARE
CURSOR tmp_splitter_cur IS
SELECT DISTINCT end_consumer_country
FROM tmp_splitter
WHERE 1 = 1;
currency_rec tmp_splitter_cur%ROWTYPE;
[code]....
View 6 Replies
View Related
Jul 9, 2012
How to make cursor to take a select statement
then check the rowcount
in certain case: assign another selete statement to the cursor
View 3 Replies
View Related
Aug 19, 2010
I've create two types:
create or replace type VAL_REC as object
(WAARDE number(20,5) ) ;
create or replace type VAL_TAB is VARRAY(200) of VALREC ;
create or replace type WSN_REC as object
( START_DATE date
, END_DATE date
, DATA VAL_TAB) ;
create or replace type WSN_TAB as table of WSN_REC ;
In a PL/SQL table collecton WSN_TAB is filled. Now I want to create a query (ref cursor) that gives the result:
START_DATE END_DATE WAARDE(1) WAARDE(2) WAARDE(3) etc.
2010-08-15 2010-08-16 20 20 40
2010-08-16 2010-08-17 23 45 40
View 16 Replies
View Related
Sep 30, 2013
I need to copy some text value in to a multi-line text item on the current cursor position.
View 8 Replies
View Related
Feb 8, 2011
SELECT
contgrp_num,
cpgrp_desc,
strategy_id,
perftyp_cd,
stiertyp_id_calcbase,
stiertyp_id,
cpgrp_flg_aggr,
pgrptyp_cd
[Code]..
The above query is not returning any value. But, when I put this within a begin-end block, its fetching data.
why its not returning any data without the begin-end block.
The entire code has an outer begin-end block within which the cursor is also defined.
View 1 Replies
View Related
Jul 21, 2010
I've been examining som old queries in an existing db due to more and more problems regarding performance. The sql is used as backend for a java/jboss web application with the possibility for users to enter data. With more and more data, there starting to come complaints about the performance.
I stumbled upon a select query with an embedded cursor similar to this :
select id, name ...,
cursor(select id, sequence.... from table2),
cursor(select id, name.... from table3)
from table1
join table4 on (table1.id = table4.id)
where .....
The javacode is a prepared statement with the actual sql as a string and the content of the cursors saved in conjunction with each row.
when i use sqldeveloper to show the explain plan without the cursors, the cost is 2428
when i use sqldeveloper to show the explain plan with just 1 of the cursors, the cost is ~165000
Is there a better way to do this instead of cursors ?
View 2 Replies
View Related
Jul 29, 2008
How to fetch all the rows in a cursor in local variables(host variables) in PRO C . /*For x in cur is not working....*/
View 2 Replies
View Related
Apr 8, 2010
i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE
v_mydept1 TEST1.DEPT_CD%TYPE;
v_mydept2 TEST2.DEPT_CD%TYPE;
CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR;
[code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF"
I am not sure what is the exact problem when we want to update multiple columns of different tables.
View 5 Replies
View Related
Oct 14, 2010
I have to insert many computed rows (not coming from a select...where...) into a target table.
I wrote a code like the following example; it works but it seems to me not efficient:
WHILE l_data < l_dt_end
LOOP
l_value := MY_FUNCTION( l_data );
l_color := MY_COLOR( l_data );
INSERT INTO MY_TARGET
( value, color )
VALUES( l_value, l_color );
l_data := l_data + 1;
END LOOP;
COMMIT;
EXCEPTION
...
ROLLBACK;Which is the best way to write this?
View 9 Replies
View Related
Aug 4, 2010
In oracle 11i, unable to insert rows using insert statement. unable to retrieve data using select statement.
View 5 Replies
View Related
May 2, 2013
I must to build triggers that insert other two rows when the user insert the first record, but the First record must to change Seq to 2 (was 1) then in trigger to insert other record with seq equal 1 and more other record with seq equal 800, I tried some ways , but return error
First insert the user from application
INSERT INTO ARCTB_ACAO_IMEDIATA ( CD_ARC, CD_TIPO_ACAO, CD_ACAO_IMEDIATA, DS_ACAO, CD_RESPONSAVEL, DT_ORIGINAL, DT_ABERTURA, NR_PRAZO,DT_PREVISTA, DS_HISTORICO ) VALUES (9732, 0,1, 'TESTE','ucrwilma', To_date('02/05/2013','DD/MM/YYYY'), To_date('02/05/2013','DD/MM/YYYY'), 1, To_date('02/05/2013','DD/MM/YYYY'), '');I create triggers
CREATE OR REPLACE TRIGGER ARCTR_ACAO_IMEDIATA_I
BEFORE INSERT
[Code]....
END ARCTR_ACAO_IMEDIATA_IUreturn me error
ORA-06519: active autonomous transaction detected and rolled back
ORA-06512: at "CLIBGF.ARCTR_ACAO_IMEDIATA_IU", line 221
ORA-04088: error during execution of trigger 'CLIBGF.ARCTR_ACAO_IMEDIATA_IU'
ORA-06512: at line 7using 9.2.08
View 2 Replies
View Related
Jun 10, 2011
I am unable to insert the rows into table after creation of trigger because Mutating error was getting.
SQL> desc test;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMP_NAME VARCHAR2(10)
EMP_NO VARCHAR2(10)
SQL> select * from test;
EMP_NAME EMP_NO
---------- ----------
ORACLE PC729
JAVA PC047
C PC0729
SQL> create or replace trigger trig_test
2 after insert on test
3 for each row
4 declare
5 ename varchar2(10);
6 eno varchar2(10);
7 begin
8 select emp_name,emp_no into ename,eno from test
9 where emp_no ='1';
10 insert into test2(emp_name,emp_no) values(
11 ename,eno);
12 end;
13 /
Trigger created.
SQL> insert into test values ('PRO','1');
insert into test values ('PRO','1')
*
ERROR at line 1:
ORA-04091: table APPS.TEST is mutating, trigger/function may not see it
ORA-06512: at "APPS.TRIG_TEST", line 5
ORA-04088: error during execution of trigger 'APPS.TRIG_TEST'
SQL> spool off;
View 5 Replies
View Related
Oct 3, 2011
I have a table named SOURCE with about 1.000.000 rows looking like this
date varchar2 varchar2 varchar2
DATE ID1 ID2 AMOUNT
---------- ----------- --------- --------
2010-07-03 1403 1403 1500
2010-07-13 2015438 etc 188608 6074
2010-08-28 1151927 410,4222 1750
2010-08-28 13622012 41026 178.99
2010-08-28 1600246 John 65
I want to insert the rows into a table TARGET
date integer integer number
DATE ID1 ID2 AMOUNT
There are about 10.000 rows where ID1, ID2 and/or AMOUNT contains characters. These rows I don't want to insert as the columns in the target table are INTEGER. I simply want to discard these.
View 3 Replies
View Related
May 27, 2013
in my sql we can use limit to select first 4 rows in the table then next 4 rows ,can oracle do that ?
I have fifty rows inside the table
select * from student where rownum between 0 and 4 order by stuid ->it work
select * from student where rownum between 5 and 9 order by stuid -> did not work
what is the correct way to do it ?
View 10 Replies
View Related
Jul 24, 2007
I have here my SQL
-> INSERT into myTable (col1, col2, col3) values(SELECT table_seq.nextval from dual, 'value2', 'value3');
That's not working...
View 8 Replies
View Related
Jan 11, 2011
I have a schema northwind which has tables and then i have another schema northwind_staging which has its own tables.
CREATE TABLE "NORTHWIND"."CUSTOMERS"
(
"CUSTOMERID" VARCHAR2(5 CHAR),
"COMPANYNAME" VARCHAR2(40 CHAR) NOT NULL ENABLE,
"CONTACTNAME" VARCHAR2(30 CHAR),
"CONTACTTITLE" VARCHAR2(30 CHAR),
"ADDRESS" VARCHAR2(60 CHAR),
[code]...
I created a procedure
CONNECT NORTHWIND_STAGING/NENAGH1;
create or replace procedure INSERTCUSTOMERS_STAGING
as
begin
INSERT INTO NORTHWIND_STAGING.customers_staging
SELECT DISTINCT c.customerid, c.companyname,c.contactname,c.region, c.country
FROM NORTHWIND.CUSTOMERS c;
end;
/
show errors This works fine when i ran my test scripts in SQL developer. I created a role Select on Northwind
View 10 Replies
View Related
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
Apr 11, 2013
I am trying to find records in table A that don't have any corresponding records in table B
select a.ID,a.templatenames from cbe a where not exists (select 1 from cbe_child b where a.ID=b.cbe_id)
Both tables have about 100 mil rows. And there is no Parent/Child relationship here, A.ID is a PK column.how can i write this select to perform better.
View 2 Replies
View Related
Oct 25, 2013
I am getting [Error] PLS-00402 (182: 1): PLS-00402: alias required in SELECT list of cursor to avoid duplicate column names error in my SP.I have created alias for each column and still i am getting the error.
for my_rec_lot in
(SELECT LLP.BOOK_VALUE LLP_BOOK_VALUE,LLP.COMMISSION LLP_COMMISSION,LLP.CURRENCY LLP_CURRENCY,LLP.EXCHANGE_RATE LLP_EXCHANGE_RATE,LLP.EXPENSES LLP_EXPENSES,
[Code].....
View 6 Replies
View Related
Mar 22, 2007
I am an Oracle beginner and I am having some trouble with the following insert query.
I am inputting values into text boxes and then this is carried out as a trigger upon clicking a button.
INSERT INTO client VALUES(':student.txtclientid', ':student.txtclientname', ':student.clientaddress', 13564338);
INSERT INTO enrolment VALUES(':student.txtclientid', ':student.lstoccurrence', null, null);
The above text boxes are all working fine as I have viewed the values using the message command. My proplem is that if i leave the fields blank it inserts ':student.txtclientname' into the row, otherwise it returns "Could not insert record"
View 1 Replies
View Related
Sep 3, 2008
I have a table with three columns X, Y and Z.The data in Column z is of the type 20/1425SE, 13/1235NW.Is there a way to split the data entries where Z LIKE '%/% and insert them as two separate rows.
I don't want to have any entries with '/'. Can these be deleted along with splitting the data entries?
View 3 Replies
View Related
