Re: How To Trap No Rows Returned From Select Statement

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1089084180.553222_at_yasure...
| Chris Val wrote:
| > Hi all.
| >
| > I am having trouble working out how to trap whether
| > my select statement returned any rows - This seems
| > to be more difficult with implicit cursors, and I
| > been able to figure it out :-).
| >
| > I would like to trap for the user entering the wrong name,
| > and displaying an appropriate message as shown, hopefully:

[snip]

Hi Daniel - Sorry for the delay, I just got home, and google takes for ever to update, so I did not see your reply.

| > Why does SQL%NOTFOUND not return true ?, and how do I fix it ?
| >
| > Any help appreciated.
| >
| > Thanks.
| > Chris Val
|
| Because at the point where it executes it is meaningless. How could
| anything not be found when nothing yet has happened?

Yes, you're right - I was just experimenting and left it there.

However, I did try it within the implicit cursor loop as well, but it did not do what I was expecting.

| CREATE OR REPLACE PROCEDURE PrintMethod ( MethodName VARCHAR2 ) IS
|
| CURSOR MyCursor IS
| SELECT *
| FROM USER_SOURCE
| WHERE NAME = UPPER(TRIM(MethodName));
|
| i PLS_INTEGER;
|
| BEGIN
| SELECT COUNT(*)
| INTO i
| FROM user_source
| WHERE name = UPPER(TRIM(MethodName));

Ah! - I see the light now :-), thanks.

| IF i = 0 THEN
| DBMS_OUTPUT.PUT_LINE('Oop''s - ....');
| ELSE
| FOR MyRow IN MyCursor LOOP
| DBMS_OUTPUT.PUT_LINE( 'Line ....');
| END LOOP;
| END IF;
|
| EXCEPTION
| WHEN OTHERS THEN
| DBMS_OUTPUT.PUT_LINE( 'Oop''s - Unknown Error Raised ... ' );
| END PrintMethod;
| /

Excellent, it works fine - I just have a further question.

When the above prints, I get an additional blank line printed that I don't want. For example, I get the following:

Line 1: FUNCTION GetNumber( N IN number ) RETURN number

Line 2: IS

Line 3: MyNumber number;

Line 4: BEGIN

Line 5: MyNumber := N;

Line 6: RETURN N;

Line 7: END GetNumber;

Is this the behaviour of the 'DBMS_OUTPUT.PUT_LINE' method ? Do I have to parse each line to strip a new line character ?

| But why reinvent the wheel? Take a look at the DBMS_METADATA
| built-in package.

Thanks - I just had a go at this, and it works great. You could have forewarned me about the setting the long value :-).

In any case, I would still like to play around with the above to familiarise myself further with PL/SQL in general. I was going so well, but had to let it go for a while, but hopefully I can pick it up to a reasonable level soon :-).

Much appreciated.
Chris Val Received on Tue Jul 06 2004 - 03:26:57 CDT