insert records from one user to another from same schema!Urgent!

[mkey]

hi all,
can i write a query like this?
insert into schema1.table_a values (select * from schema2.table_a where id= 100);

Right now this script doesn't work?
mkey!
Thank you!

 

[THoey]

You don't mention what happens when you run it, what is the error? You need to make sure that from the schema that the query being run from (the user logged in at the time), that that user has INSERT permissions.

If that doesn't do it, please provide more information. Terry
**************************
* General Disclaimor - Please read *
**************************
Please make sure your post is in the CORRECT forum, has a descriptive title, gives as much detail to the problem as possible, and has examples of expected results. This will enable me and others to help you faster...

 

[JamesGordon]

as schema1:
grant select, insert, update, delete to schema2 on table_a;

 

[mkey]

When I run this script it tell me 7 records selected. But when i check if the records were inserted into user1.table_a there is not recrods for pipeid = 1000. Can you tell what I'm doing wrong hear? I also checked the trigger and I don't have any triggers from these columns.

insert into user1.table_a
(PIPEID,DWGNUM,DWGNUM2,ZOOMXP,PREVNUM,PREVDESC,ORGPAGE,DWGSC
ALE,STASTART,STAEND,STARTEQ,
ENDEQ,ABSTATION1,ABSTATION2,TOWNSHIP,COUNTY,STATE,TOWNUM,RAN
GENUM,SECTIONNUM,SHEETDIR,DWGSTATUS,STATUS,
USERID,EFFECTIVE_DATE,EDIT_TRACKID,SHAREID,CROSSOVER,LINKID,
RELOCATION,EDITDWG,MPSTART,MPEND,UTMZONE)
SELECT
PIPEID,DWGNUM,DWGNUM2,ZOOMXP,PREVNUM,PREVDESC,ORGPAGE,DWGSCA
LE,STASTART,STAEND,STARTEQ,
ENDEQ,ABSTATION1,ABSTATION2,TOWNSHIP,COUNTY,STATE,TOWNUM,RAN
GENUM,SECTIONNUM,SHEETDIR,DWGSTATUS,STATUS,
USERID,EFFECTIVE_DATE,EDIT_TRACKID,SHAREID,CROSSOVER,LINKID,
RELOCATION,EDITDWG,MPSTART,MPEND,UTMZONE
FROM user2.table_a WHERE PIPEID = 1000;

 

[jee]

Perhaps this is too simple - Did you remember to commit?

 

[mkey]

yap i did

 

[JamesGordon]

When you ran the SQL what message came out at the end?

7 rows inserted?
ORA-????: ?

 

[mkey]

hi all,
I am getting an ORA-04091: table name is mutating, trigger/function may not see it error.
Columns linkid and other two column that is not part of my script have trigger on them. So when ever we insert new records we automate the linkid process so that it will be old linkid plus one and another two columns we do the same process as linkid. So I took off the linkid from my script. Still I was getting the same error. So I'm thinking maybe the trigger is causing the problem.

I have did this process in a long way by selecting all the records from one user and wrote an insert script for each by using a procedure. So technically I have done my job but I'm still quries on how I should solve this issue? There should be another way to this(One would hope).
Thanks,
mkey