CREATE OR REPLACE
PROCEDURE CopyData
(
p_from_user IN VARCHAR2,
p_to_user IN VARCHAR2,
p_from_table IN VARCHAR2,
p_to_table IN VARCHAR2,
p_column_list IN VARCHAR2 := NULL,
p_condition IN VARCHAR2 := NULL
)
IS
v_SQL VARCHAR2(2000);
BEGIN
-- create the INSERT statement ...
v_SQL := 'INSERT INTO '||p_to_user||'.'||p_to_table||' '||
'SELECT '||NVL(p_column_list,'*')||' FROM '||
p_from_user||'.'||p_from_table||' WHERE
'||NVL(p_condition,' 1=1 ');

-- INSERT the required rows ...
EXECUTE IMMEDIATE v_SQL;

-- Add the required exception handling ...
END;
/

-----Original Message-----
From: Rajesh kalaria
[mailto:oracledba-ezmlmshield-x68547667.[Email address protected]
Sent: Wednesday, August 08, 2007 9:02 AM
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



Oracle LazyDBA home page