| PLS-00201 error ... [message #36902] |
Wed, 02 January 2002 19:36  |
buchie
Messages: 5
Registered: March 2001
|
Junior Member |
|
|
Happy New Year!!
1. I made a database link, aaa to another Oracle DBServer.
link name: aaa
table name: S50TBL
2. And when I use this select command on the SQL*Plus prompt, I success like this.
SQL> select S50NM from S50TBL@aaa where S50CD='32817';
S50NM
--------------------
rmathrrhddPrhk
SQL>
3.But When I create new procedure that includes this select command, I fail like this.
DROP PROCEDURE user1.newproc_test
/
CREATE OR REPLACE Procedure user1.newproc_test
( param1 IN varchar2, param2 OUT varchar2 )
IS
BEGIN
select S50NM into param2 from param1 where S50CD='32817';
EXCEPTION
WHEN others THEN
raise;
END;
/
-- When I compile this procedure, I meet these errors.
(1):PLS-00201: identifier 'PARAM1' must be declared
(2):PL/SQL: SQL Statement ignored
4. when I call this procedure(user1.newproc_test),
I want to pass table name and database link name to IN parameter param1.
ex) newproc_test('S50TBL@aaa',param2);
5. How could I solve this problem.. Please answer me.
----------------------------------------------------------------------
|
|
|
|
| Re: PLS-00201 error ... [message #36908 is a reply to message #36902] |
Thu, 03 January 2002 07:24  |
vk
Messages: 13
Registered: December 2001
|
Junior Member |
|
|
use dynamic sql or 'execute immediate'
execute immediate 'select S50NM into param2 from '||param1 ||'where S50CD='''32817'''';
----------------------------------------------------------------------
|
|
|
|
| Re: PLS-00201 error ... [message #36914 is a reply to message #36902] |
Thu, 03 January 2002 14:59 |
buchie
Messages: 5
Registered: March 2001
|
Junior Member |
|
|
Thank you ...
use this statement,
execute immediate 'select S50NM from '||param1||' where S50CD=''32817''' into param2 ;
instead of
execute immediate 'select S50NM into param2 from '||param1 ||'where S50CD='''32817'''';
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
