| create a procedure [message #7151] |
Fri, 23 May 2003 21:46  |
freewill
Messages: 16
Registered: October 2002
|
Junior Member |
|
|
hi,I want to create a procedure ,but it always show errors,the code is as following:
create or replace procedure dataupdate (table_name in varchar2,month in varchar2) as
begin
INSERT INTO BHXWZZK0
(select * from table_name
where zkhpid0
not in
(select zkhpid0 from bhxwzzk0
where substr(zkHPID0,3,2)= month));
end
SQL> show error
LINE/COL ERROR
-------- ------------------------------------------
3/1 PL/SQL: SQL Statement ignored
4/16 PLS-00201: identifier 'TABLE_NAME' must be defined
urgent!any help will be appreciated.
|
|
|
|
|
|
| Re: create a procedure [message #7154 is a reply to message #7152] |
Sat, 24 May 2003 10:21  |
freewill
Messages: 16
Registered: October 2002
|
Junior Member |
|
|
tks.
in fact ,I want to insert records from different tables into a certain table ,so i hope create a procedure which receive the table name as a parameter ,but it seems that passed parameter works well in other place other than behind "from " clause.
can i pass a table name as a parameter?
|
|
|
|
| Re: create a procedure [message #7155 is a reply to message #7151] |
Sat, 24 May 2003 16:06 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
You need to use dynamic SQL whenever you are substituting object names (tables, columns, etc.):
create or replace procedure dataupdate
(p_table_name in user_tables.table_name%type,
p_month in varchar2)
as
begin
execute immediate 'INSERT INTO BHXWZZK0' ||
'(select * from ' || p_table_name ||
' where zkhpid0 not in ' ||
'(select zkhpid0 from bhxwzzk0' ||
' where substr(zkHPID0,3,2) = :month))' using p_month;
end;
/ |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
