Execute immediate 'insert into ' || targetUser || '.tableA select * from
' || sourceUser ||'.tableB'
If a dynamic sql is not used, your insert won't even pass parsing.
Regards,
Venmani KT
Direct: +91 44 6625 1369 | Mobile: +91 99401 64123 | VoIP: +6666 914
1369
Creating value that increases customer competitiveness
-----Original Message-----
From: Rajesh kalaria
[mailto:oracledba-ezmlmshield-x68547667.[Email address protected]
Sent: Wednesday, August 08, 2007 6:32 PM
To: LazyDBA Discussion
Subject: How to give user name as parameter in plsql procedure
Hi,
I have a requirement where i want to copy the data of some tables of
one user to tables of another user. So I want to create a procedure
having two parameter *sourceUser* and *targetUser.* But when I am trying
to compile that procedure it gives following error :
LINE/COL ERROR
--------
-----------------------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
3/59 PL/SQL: ORA-00942: table or view does not exist
* My procedure is like this :*
create or replace procedure copyTables (sourceUser in varchar2,
targetUser in varchar2) is
begin
insert into targetUser.tableA select * from sourceUser.tableB;
end;
/
Any idea about how to solve this ?
Thanks
Rajesh
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
Legal Disclaimer:
The information contained in this message may be privileged and confidential. It is intended to be read only by the individual or entity to whom it is addressed or by their designee. If the reader of this message is not the intended recipient, you are on notice that any distribution of this message, in any form, is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete or destroy any copy of this message
Oracle LazyDBA home page