| oracle cursors not working well [message #21747] |
Tue, 27 August 2002 11:35  |
Krijn Michiels
Messages: 4
Registered: August 2002
|
Junior Member |
|
|
Hello,
Whenever I use cursors on Oracle it is very slow.
I have tried the fetch and the 'for i in ..loop' for inserting and updating rows.
Now I have reduced the PL/SQL prog to
insert into ..
select 0,name,..
Problem here is that I have to fill up the zero with a unique identifier (I don't use automatic sequences).
I've tried rownum but is this the correct and/or only way for doing this? (It is possible that I have 10000 rows but the max. Id value is 987898)
PS:without succes I experimented with the FORALL clause but the oracle documention is rather dark about it.
Please help,
Thanks
|
|
|
|
| Re: oracle cursors not working well [message #21749 is a reply to message #21747] |
Tue, 27 August 2002 12:25  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I'm not sure what cursors have to do with your situation.
How are you keeping the identifier unique without using sequences? Especially in a multi-user environment?
Please provide more detail (and maybe some pseudo-code) that shows what you are trying to do.
|
|
|
|
| Re: oracle cursors not working well [message #21756 is a reply to message #21747] |
Wed, 28 August 2002 11:44 |
Krijn Michiels
Messages: 4
Registered: August 2002
|
Junior Member |
|
|
Hello,
We have our own sequences table (se_filename,se+_sequence). Please don't ask why? (it was already there).
But I think I've found the solution. It is something like this:
declare
TYPE IDList TABLE OF TEST1.ID%TYPE;
TYPE NAMEList TABLE OF TEST1.NAME%TYPE;
v_ID IDlist;
v_name NAMElist;
COUNTER NUMBER;
begin
SELECT ID,NAME BULK COLLECT INTO v_IDlist,v_Name FROM TEST1;
SELECT NVL(MAX(ID)+1,0) INTO COUNTER FROM TEST2;
FORALL I IN v_ID.FIRST..V_ID.LAST
INSERT INTO TEST1
VALUES(v_ID(i),v_NAME(i));
END;
Note:This is a look a like
Thanks for you replay
|
|
|
|
| Re: oracle cursors not working well [message #21761 is a reply to message #21747] |
Wed, 28 August 2002 13:01 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Your pseudo-code doesn't make much sense to me. You are:
1) loading rows from TEST1 into an array
2) finding the max + 1 id value from TEST2
3) inserting the array contents back into TEST1
I'm assuming that the insert is really into TEST2, not TEST1. If so, what are you doing with the COUNTER variable value? Nothing in your example.
This sure looks like it should be an INSERT INTO/SELECT statement. The BULK COLLECT/FOR ALL processes make this far more complex than it should be.
Just remember that 'select max(id) + 1' logic is not multi-user compatible at all.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
