By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,715 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
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
  1. declare
  2. c1 varchar2(80);
  3. c2 varchar2(80);
  4. begin
  5. execute immediate 'select col2 from table1 where col1=:c1'
  6. into c2 using 'AAA';
  7. end;
  8. /

here is the definition of table:

Expand|Select|Wrap|Line Numbers
  1. CREATE TABLE TABLE1
  2. (
  3.   COL1  VARCHAR2(80 BYTE),
  4.   COL2  VARCHAR2(80 BYTE),
  5.   VAL   VARCHAR2(80 BYTE)
  6. )

and the data contained by the table:


Expand|Select|Wrap|Line Numbers
  1. Insert into TABLE1
  2.    (COL1, COL2)
  3.  Values
  4.    ('AAA', '1');
  5. Insert into TABLE1
  6.    (COL1, COL2)
  7.  Values
  8.    ('BB', '2');
  9. Insert into TABLE1
  10.    (COL1, COL2)
  11.  Values
  12.    ('C', '3');
  13. COMMIT;
error message:
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5
Apr 4 '08 #1
Share this Question
Share on Google+
5 Replies


amitpatel66
Expert 100+
P: 2,367
Try This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare
  3. c1 varchar2(80):= 'AAA';
  4. c2 varchar2(80);
  5. begin
  6. execute immediate 'select col2 from table1 where col1='||CHR(39)||c1||CHR(39)
  7. into c2;
  8. end;
  9. /
  10.  
Apr 4 '08 #2

P: 65
Try This:

Expand|Select|Wrap|Line Numbers
  1.  
  2. declare
  3. c1 varchar2(80):= 'AAA';
  4. c2 varchar2(80);
  5. begin
  6. execute immediate 'select col2 from table1 where col1='||CHR(39)||c1||CHR(39)
  7. into c2;
  8. end;
  9. /
  10.  

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

amitpatel66
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
  1. [97]asok@DEV01> create table t (col varchar2(1));
  2.  
  3. Table created.
  4.  
  5. Elapsed: 00:00:00.20
  6. [97]asok@DEV01> insert into t values ('A');
  7.  
  8. 1 row created.
  9.  
  10. Elapsed: 00:00:00.03
  11. [97]asok@DEV01> commit;
  12.  
  13. Commit complete.
  14.  
  15. Elapsed: 00:00:00.01
  16. [97]asok@DEV01> select * From t;
  17.  
  18. C
  19. -
  20. A
  21.  
  22. Elapsed: 00:00:00.07
  23. [97]asok@DEV01> DECLARE
  24.   2       v1     VARCHAR2(30);
  25.   3       v2     VARCHAR2(30);
  26.   4  BEGIN
  27.   5       EXECUTE IMMEDIATE 'select col from t where col=:v1'
  28.   6       INTO              v2
  29.   7       USING             'A';
  30.   8  END;  
  31.   9  /
  32.  
  33. PL/SQL procedure successfully completed.
  34.  
  35. Elapsed: 00:00:00.06
  36. [97]asok@DEV01> DECLARE
  37.   2       v1     VARCHAR2(30);
  38.   3       v2     VARCHAR2(30);
  39.   4  BEGIN
  40.   5       EXECUTE IMMEDIATE 'select col from t where col=:v1'
  41.   6       INTO              v2
  42.   7       USING             'C';
  43.   8  END; 
  44.   9  /
  45. DECLARE
  46. *
  47. ERROR at line 1:
  48. ORA-01403: no data found
  49. ORA-06512: at line 5
  50.  
  51.  
  52. Elapsed: 00:00:00.06
  53. [97]asok@DEV01> DECLARE
  54.   2       v1     VARCHAR2(30) := 'A';
  55.   3       v2     VARCHAR2(30);
  56.   4  BEGIN
  57.   5       EXECUTE IMMEDIATE 'select col from t where col=:v1'
  58.   6       INTO              v2
  59.   7       USING             v1;
  60.   8  END;
  61.   9  /
  62.  
  63. PL/SQL procedure successfully completed.
  64.  
  65. Elapsed: 00:00:00.04
  66.  
Apr 4 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.