急。。。。关于oracle的错误

[小MAY]

请大家看一下，到底是哪里错误，我的头都晕了
create table test
(
name varchar2(20),
address varchar2(20)
);

select * from test;

declare
str varchar2(500);
c_1 varchar2(20);
r_1 test%rowtype;
begin
c_1:='测试人员';
str:='select * from test where name=:C WHERE ROWNUM=1';
execute immediate str into r_1 using c_1;
DBMS_OUTPUT.PUT_LINE(r_1.name||r_1.address);
end ;

[hebo2005]

'select * from test where name=:C WHERE ROWNUM=1'
两个WHERE啊
应该是这样的吧
'select * from test where name=:C and ROWNUM=1'

[ronnyma_mawei]

嗷嗷

[小MAY]

create table test
(
name varchar2(20),
address varchar2(20)
);

select * from test;

declare
str varchar2(500);
c_1 varchar2(20);
r_1 test%rowtype;
begin
c_1:='测试人员';
str:='select * from test where name=:C and ROWNUM=1';
execute immediate str into r_1 using c_1;
DBMS_OUTPUT.PUT_LINE(r_1.name||r_1.address);
end ;

改过来，报未找到数据的错误！！！！晕了！！！

[iihero]

因为表是空表，没有数据，所以出错。
添加一条记录就可以了。

insert into test values('测试人员', 'abcdef');
SQL> declare
2 str varchar2(500);
3 c_1 varchar2(20);
4 r_1 test%rowtype;
5 begin
6 c_1:='测试人员';
7 str:='select * from test where name=:C and ROWNUM=1';
8 execute immediate str into r_1 using c_1;
9 DBMS_OUTPUT.PUT_LINE(r_1.name ||r_1.address);
10 end;
11 /

PL/SQL 过程已成功完成。

PL/SQL 过程已成功完成。

SQL> set serverout on
SQL> /
测试人员abcdef