no data found error in an "execute immediate" statement

[stmfc]

i dont know why pl/sql block below returns no data found error.
do you have any idea about it?

declare
c1 varchar2(80);
c2 varchar2(80);
begin
execute immediate 'select col2 from table1 where col1=:c1'
into c2 using 'AAA';
end;
/

here is the definition of table:

CREATE TABLE TABLE1
(
  COL1  VARCHAR2(80 BYTE),
  COL2  VARCHAR2(80 BYTE),
  VAL   VARCHAR2(80 BYTE)
)

and the data contained by the table:

Insert into TABLE1
   (COL1, COL2)
 Values
   ('AAA', '1');
Insert into TABLE1
   (COL1, COL2)
 Values
   ('BB', '2');
Insert into TABLE1
   (COL1, COL2)
 Values
   ('C', '3');
COMMIT;

error message:
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

[amitpatel66]

Try This:

[code=oracle]

declare
c1 varchar2(80):= 'AAA';
c2 varchar2(80);
begin
execute immediate 'select col2 from table1 where col1='||CHR(39) ||c1||CHR(39)
into c2;
end;
/
[/code]

[stmfc]

Try This:

[code=oracle]

declare
c1 varchar2(80):= 'AAA';
c2 varchar2(80);
begin
execute immediate 'select col2 from table1 where col1='||CHR(39) ||c1||CHR(39)
into c2;
end;
/
[/code]

amitpatel66, thanks a lot for your reply.
i tried the way that you told, but it gives the same error (no data found)

[amitpatel66]

just a silly verification, I hope you had already checked, if not then Could you please check your table ones if it contains data?

[stmfc]

just a silly verification, I hope you had already checked, if not then Could you please check your table ones if it contains data?

i have checked it again, and for sure it contains data.

[Dave44]

it must be something like your data isnt committed into the table. your code seems as though it should work.

[97]asok@DEV01> create table t (col varchar2(1));

Table created.

Elapsed: 00:00:00.20
[97]asok@DEV01> insert into t values ('A');

1 row created.

Elapsed: 00:00:00.03
[97]asok@DEV01> commit;

Commit complete.

Elapsed: 00:00:00.01
[97]asok@DEV01> select * From t;

C
-
A

Elapsed: 00:00:00.07
[97]asok@DEV01> DECLARE
  2       v1     VARCHAR2(30);
  3       v2     VARCHAR2(30);
  4  BEGIN
  5       EXECUTE IMMEDIATE 'select col from t where col=:v1'
  6       INTO              v2
  7       USING             'A';
  8  END;  
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
[97]asok@DEV01> DECLARE
  2       v1     VARCHAR2(30);
  3       v2     VARCHAR2(30);
  4  BEGIN
  5       EXECUTE IMMEDIATE 'select col from t where col=:v1'
  6       INTO              v2
  7       USING             'C';
  8  END; 
  9  /
DECLARE
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5


Elapsed: 00:00:00.06
[97]asok@DEV01> DECLARE
  2       v1     VARCHAR2(30) := 'A';
  3       v2     VARCHAR2(30);
  4  BEGIN
  5       EXECUTE IMMEDIATE 'select col from t where col=:v1'
  6       INTO              v2
  7       USING             v1;
  8  END;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04