dbms_output.put_line doesn't work inside function

Question

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;

The general answer to this is using DBMS_OUTPUT.PUT_LINE(). What business reason do you have for executing this? There's almost never a need for this in production code. — Ben
– Ben, Commented Jan 8, 2018 at 14:17
Which client are you using? How are you executing the query? If it's SQL*Plus or SQL Developer and you're running it as a script, it should just work. If you're running the query as a statement - so the function return value goes in a 'Query Result' window - have you got the 'Dbms Output' window open (from the View menu)? Do you see the abc message after you run a commit? — Alex Poole
– Alex Poole, Commented Jan 8, 2018 at 14:19

mikcutu · Accepted Answer · 2018-01-08 14:27:05Z

13

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.

answered Jan 8, 2018 at 14:27

mikcutu

1,1022 gold badges19 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steven Feuerstein · Accepted Answer · 2018-01-08 22:38:51Z

0

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;

answered Jan 8, 2018 at 22:38

Steven Feuerstein

2,0041 gold badge12 silver badges16 bronze badges

Collectives™ on Stack Overflow

dbms_output.put_line doesn't work inside function

2 Answers 2

TEST(30)

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

TEST(30)

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related