Question: I have a pl/sql script name test

and a table name testtable

```
//testtable
a#
-----------
1
2
3
4

```

Inside my test script:

```
CREATE OR REPLACE FUNCTION test(a_num IN NUMBER )
        RETURN NUMBER;
IS
        check NUMBER := 0;
        match NUMBER := 0;
BEGIN
        SELECT COUNT(*) into check FROM TESTTABLE WHERE a_num = a#;

        IF check > 0 THEN
             match := match + 1;
        END IF;

        IF match = 0 THEN
             DBMS_OUTPUT.PUT_LINE( 'not found' );
             RETURN(-1);
        ELSE
             DBMS_OUTPUT.PUT_LINE( 'found' );
             RETURN(0);
        END IF;
END test;
/

```

for this script, when i enter following query:

```
select test(5) from testtable;

```

the result return will be 4 times : 

```
not found
not found
not found
not found

```

The following query:

```
select (test2) from testtable;

```

returns:

```
found
found
found
found

```

how to make it show only 1 message?

Answer: 
If you want a single result, don't select from `testtable`, select from a single-row table. Oracle provides the `dual` table for just that purpose

```
SELECT test(5)
  FROM dual

```

and 

```
SELECT test(2)
  FROM dual

```

will each return a single row of results.

Of course, this assumes that your `test` function is actually valid and that it actually returns results. The code you posted won't compile because you have a function that does not return anything. There is no `return` clause in the specification of the function that tells the compiler what data type the function returns. And there is nothing in the function that returns a result. So the function cannot possibly compile. My assumption is that your actual function does compile and that it does return something rather than just writing to `dbms_output`.
Try to use this code:-

```
CREATE OR REPLACE FUNCTION test(a_num IN NUMBER )
     RETURN NUMBER;
IS
    check NUMBER := 0;
    match NUMBER := 0;
BEGIN
    SELECT COUNT(*) into check FROM TESTTABLE WHERE a# =a_num;

    IF check > 0 THEN
         match := match + 1;
    END IF;

    IF match = 0 THEN
         DBMS_OUTPUT.PUT_LINE( 'not found' );
         RETURN(-1);
    ELSE
         DBMS_OUTPUT.PUT_LINE( 'found' );
         RETURN(0);
    END IF;
END test;
/

```

Hope this will solve your problem.