Re: Question Regarding Select Into

From: Frank van Bortel <frank.van.bortel_at_gmail.com>
Date: Fri, 23 Dec 2005 21:30:21 +0100
Message-ID: <dohm4p$1n7$1_at_news5.zwoll1.ov.home.nl>


OracleNewbie wrote:
> Oracle Gurus,
>
> DECLARE
> A VARCHAR2 (30 BYTE);
> BEGIN
> A := '';
>
> SELECT FIELDNAME INTO A
> FROM EXCELTABLEDEF
> WHERE ROWNUM = 1 AND CST = 'RP_DAT' ';
> DBMS_OUTPUT.PUT_LINE (A);
> END;
>
> The above works fine if it finds a record. If there is no record then
> it throws up the following error
> ORA-01403: no data found
> ORA-06512: at line 6
>
> I can have the exception to handle it. But, is there a way where i can
> have some value if it doesn't return anything like ISNULL(A, 'TBLS')
>
> Again, ppl i'm new and my question might too simple or ultimate
> stupidity. If you know and can guide me do post else dont post some
> crap like what one of the guy "Frank" in this group do. He thinks he is
> an Big **le in Oracle.
>

Oracle treats empty strings a nulls, so: begin
  a := null; might be a better idea. It at least does not have the danger of confusion on empty strings and nulls. Some platforms do make a distinction, Oracle not - better get used to Oracle ways.

The where clause has a typo - three quotes:   WHERE ROWNUM = 1 AND CST = 'RP_DAT' '; Exception handling:
begin
  ....
exception
....
end;
in your case:
  WHERE ROWNUM = 1 AND CST = 'RP_DAT';
  DBMS_OUTPUT.PUT_LINE (A);
exception
  when no_data_found

      then dbms_output.put_line('No data!');   when others

      then raise;
end;

A simple nvl(A,'No data!') yields the same, as already stated.

And seasons greetings to you , too!

-- 
Regards,
Frank van Bortel

Top-posting is one way to shut me up...
Received on Fri Dec 23 2005 - 21:30:21 CET

Original text of this message