Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
insert records from one user to another from same schema!Urgent!
- Thread starter mkey
- Start date
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...
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
Programmer
as schema1:
grant select, insert, update, delete to schema2 on table_a;
grant select, insert, update, delete to schema2 on table_a;
- Thread starter
- #4
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;
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;
- Thread starter
- #6
JamesGordon
Programmer
When you ran the SQL what message came out at the end?
7 rows inserted?
ORA-????: ?
7 rows inserted?
ORA-????: ?
- Thread starter
- #8
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
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
- Status
Similar threads
