ORA-01403: no data found

[Nikee]

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

[stecal]

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.

[rotem_fo]

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.

[pando]

hm what´s the point of that exception if you see the same message as there werent any exceptions defined

[Nikee]

Hi Stecal,

Thanks for the solution.

Regards,
Seenu