Home » SQL & PL/SQL » SQL & PL/SQL » PLS-00201 error ...
PLS-00201 error ... [message #36902] Wed, 02 January 2002 19:36 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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'''';

----------------------------------------------------------------------
Previous Topic: Spooling Results into Variable File Name.????
Next Topic: report output to an excel file
Goto Forum:
  


Current Time: Thu Mar 28 05:45:28 CDT 2024