CODEWITHSUNDEEP
sqloracle-databaseplsql
Toma Radu-Petrescu
Toma Radu-Petrescu

Reputation: 2262

dbms_output.put_line doesn't work inside function

I expect the following code to print 'abc' before returning 1.

Even though I ran set serveroutput on, it still doesn't print anything.

If, instead of a function, it would be a procedure, it would work.

Can somebody explain to me what I am doing wrong?

Thanks.

CREATE OR REPLACE FUNCTION test (
    code NUMBER
) RETURN NUMBER
    IS
BEGIN
    dbms_output.put_line('abc');
    RETURN 1;
END;

SELECT
    test(30)
FROM
    dual;

Upvotes: 0

Views: 9895

Answers (2)

Steven Feuerstein
Steven Feuerstein

Reputation: 1974

Executing a SQL statement doesn't always flush the DBMS_OUTPUT buffer; that'll depend in part on the execution environment/tool.

You can be pretty darn sure to see the output if you put the query inside a block as in:

DECLARE
   i NUMBER;
BEGIN
   SELECT test(30) INTO i
     FROM dual;
END;

Upvotes: 0

mikcutu
mikcutu

Reputation: 1092

If you are using SQL Developer, you should add the

SET SERVEROUTPUT ON

before calling the function.

and then, execute the function using F5 (Run Script) instead of F9(Run Statement). The difference betwen these 2 modes to execute a sentence is the mode of displayng the result. F5 displays the result as like a PLSQL code F9 displays the result as like a SQL code. On this way, you will see only the result of the SQL.

Here is the output using the F5 mode:

TEST(30)

     1

abc

other wise, you should include the select statement into a PL_SQL anonymous block.

Upvotes: 13

Related Questions