-
ORA-01403: no data found
Hello,
When I tried to execute the following PL/SQL code, I am facing the ORA-01403: no data found error:
------------------------------------------------------------
DECLARE
Tname varchar2(30) := 'CUSTOM';
Crows Integer(10);
Long varchar2(106);
BEGIN
Execute Immediate 'select data_type from DBA_TAB_COLUMNS where data_type=''LONG RAW'''||' and table_name='''||Tname||'''' into Long;
If Long = 'LONG RAW' Then
dbms_output.put_line( Tname );
End If;
END;
/
------------------------------------------------------------
Error Message:
ERROR at line 1:
ORA-01403: no data found
------------------------------------------------------------
The error is occuring when the Execute Immediate statement returns an empty value. The happens if the table do not contain LONG RAW data_type.
I do not know how to handle this?
ThanK You,
Seenu
Last edited by Nikee; 12-18-2002 at 11:39 PM.
-
You may want to check your query by using SQL*PLus to confirm there really is no data found (0 rows returned). Why? Because of your use of quotation marks in forming a string value.
Also, your query can be coded without the dynamic SQL. If you already know the table name, why bother with the dynamic SQL?
If it is the case where your query is structured properly AND there is no data to be returned, then you can wrap an IF-THEN-ELSE around your execute immediate. You can code it this way as well:
DECLARE
v_cnt NUMBER;
BEGIN
select count (*) into v_cnt
from dba_tab_columns
where data_type = 'LONG RAW'
and table_name = 'CUSTOM';
IF v_cnt > 0 THEN
dbms_output.put_line('There is long raw data in table CUSTOM');
ELSE
dbms_output.put_line('There is no long raw data in table CUSTOM');
END IF;
END;
You can also use exception handling, cursors, no data found, just to name a few other ways to do this.
-
Hi,
You can use EXCEPTIONS:
DECLARE
Tname varchar2(30) := 'CUSTOM';
Crows Integer(10);
Long varchar2(106);
BEGIN
Execute Immediate 'select data_type from DBA_TAB_COLUMNS where data_type=''LONG RAW'''||' and table_name='''||Tname||'''' into Long;
If Long = 'LONG RAW' Then
dbms_output.put_line( Tname );
End If;
EXCEPTIONS
WHEN NO_DATA_FOUND
DBMS_OUTPUT.PUT_LINE('No Data Found');
END;
/
Cheers.
It is better to ask and appear ignorant, than to remain silent and remain ignorant.
Oracle OCP DBA 9i,
C++, Java developer
-
hm what´s the point of that exception if you see the same message as there werent any exceptions defined
-
Hi Stecal,
Thanks for the solution.
Regards,
Seenu
-Nikee
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|