wouter13
asked on
Howto Q: Writing an Oracle Cursor in stead of select into
Hello *,
I am having an oracle table "Alfabet" with the columns A,B,D. The column D is a Long field. The table needs adjusted: the column C needs to be added. Because long fields needs to be at the end of the tabledefinition, a simple alter table won't do. I thought of creating a temp table, and Select into everything into the temptable, drop the original table, create the new table (with all A,B,C,D columns) and select everything back into it. NOT! The select into doesn't work with long fields (ora 997 error).
Questions:
1) i've heard that a cursor can be used to select into the temp table and back (and ora997 won't show up) - is this true?
2) being unexperienced with cursor writing, can someone send me the code to do this?
3) Or is there some other way i can do the trick?
TIA!
I am having an oracle table "Alfabet" with the columns A,B,D. The column D is a Long field. The table needs adjusted: the column C needs to be added. Because long fields needs to be at the end of the tabledefinition, a simple alter table won't do. I thought of creating a temp table, and Select into everything into the temptable, drop the original table, create the new table (with all A,B,C,D columns) and select everything back into it. NOT! The select into doesn't work with long fields (ora 997 error).
Questions:
1) i've heard that a cursor can be used to select into the temp table and back (and ora997 won't show up) - is this true?
2) being unexperienced with cursor writing, can someone send me the code to do this?
3) Or is there some other way i can do the trick?
TIA!
ASKER
Henka, isn't this the same idea as i posted? AFAIK, you cannot rename tables in oracle. So, there needs to be a temporal table, where the data can be stored, the old table can be deleted and recreated, and the data needs to be put back into place. This can only be done by a cursor if the fieldtype Long is used - AFAIK.
ASKER
Henka, isn't this the same idea as i posted? AFAIK, you cannot rename tables in oracle. So, there needs to be a temporal table, where the data can be stored, the old table can be deleted and recreated, and the data needs to be put back into place. This can only be done by a cursor if the fieldtype Long is used - AFAIK.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
create a new table alfabt1
create table alfabt1
(a char,
b number,
c number,
d long);
declare
cursor x is select a,b,d from alfabt;
begin
for xrec in x loop
insert into alfabt1
values(xrec.a,xrec.b,null,
end loop;
end;
/
update column c later using
update alfabt
set c= 'whatever';
it'll work!
create table alfabt1
(a char,
b number,
c number,
d long);
declare
cursor x is select a,b,d from alfabt;
begin
for xrec in x loop
insert into alfabt1
values(xrec.a,xrec.b,null,
end loop;
end;
/
update column c later using
update alfabt
set c= 'whatever';
it'll work!
ASKER
> update column c later using
update alfabt
set c= 'whatever';
it'll work!
... if you don't mind every row of alphabet having c set to the string 'whatever'!
:)
update alfabt
set c= 'whatever';
it'll work!
... if you don't mind every row of alphabet having c set to the string 'whatever'!
:)
... it is just a tip ...