443,715 Members | 1,815 Online
Need help? Post your question and get tips & solutions from a community of 443,715 IT Pros & Developers. It's quick & easy.

# no data found error in an "execute immediate" statement

 P: 65 i dont know why pl/sql block below returns no data found error. do you have any idea about it? Expand|Select|Wrap|Line Numbers 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: Expand|Select|Wrap|Line Numbers CREATE TABLE TABLE1 (   COL1  VARCHAR2(80 BYTE),   COL2  VARCHAR2(80 BYTE),   VAL   VARCHAR2(80 BYTE) ) and the data contained by the table: Expand|Select|Wrap|Line Numbers 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 Apr 4 '08 #1
5 Replies

 Expert 100+ P: 2,367 Try This: Expand|Select|Wrap|Line Numbers   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; /   Apr 4 '08 #2

 P: 65 Try This: Expand|Select|Wrap|Line Numbers   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; /   amitpatel66, thanks a lot for your reply. i tried the way that you told, but it gives the same error (no data found) Apr 4 '08 #3

 Expert 100+ P: 2,367 just a silly verification, I hope you had already checked, if not then Could you please check your table ones if it contains data? Apr 4 '08 #4

 P: 65 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. Apr 4 '08 #5

 100+ P: 153 it must be something like your data isnt committed into the table. your code seems as though it should work. Expand|Select|Wrap|Line Numbers [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   Apr 4 '08 #6