RE: How to give user name as parameter in plsql procedure

RE: How to give user name as parameter in plsql procedure

 

  

You need to use dynamic sql.

-----Original Message-----
From: ashok
[mailto:oracledba-ezmlmshield-x82881839.[Email address protected]
Sent: Wednesday, August 08, 2007 8:57 AM
To: LazyDBA Discussion
Subject: RE: How to give user name as parameter in plsql procedure


You need to have the Grant Privileges for the other users table where
you are trying to insert or select



With Thanks and Regards,
Ashok
9840884644

"True Friendship is seen through the heart not through the eyes"

-----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




The information contained in this electronic message and any attachments
to this message are intended for the exclusive use of the addressee(s)
and may contain proprietary, confidential or privileged information. If
you are not the intended recipient, you should not disseminate,
distribute or copy this e-mail. Please notify the sender immediately and
destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient
should check this email and any attachments for the presence of viruses.
The company accepts no liability for any damage caused by any virus
transmitted by this email.

www.wipro.com


---------------------------------------------------------------------
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



Oracle LazyDBA home page