CODEWITHSUNDEEP
sqloracle-databaseplsql
Jeroen Vannevel
Jeroen Vannevel

Reputation: 44439

Script doesn't generate any output

I'm using the Oracle Developer Days image to get started with PL/SQL in SQL Developer.

Right now I have the following PL/SQL anonymous block:

DECLARE
  v_country_name VARCHAR2(40);
  v_region_id NUMBER;

BEGIN
  SELECT country_name, region_id
  INTO v_country_name, v_region_id
  FROM countries 
  WHERE country_id = 'CA';

  DBMS_OUTPUT.PUT_LINE('The country name is: ' || v_country_name ||
                        ' and is located in ' || v_region_id || '.');

EXCEPTION
  WHEN TOO_MANY_ROWS THEN
  DBMS_OUTPUT.PUT_LINE('Your SELECT statement retrieved multiple rows. ' ||
                        ' Consider using a cursor.');

END;

This executes, but it doesn't do what I want it to do. The Script Output only contains 'anonymous block completed'.

I have tried to explicitly enable the output with DBMS_OUTPUT.enable; but this didn't make a difference.

What am I overlooking?

Upvotes: 0

Views: 209

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

If you're running it as a script you can add set serveroutput on before your declare, which will show the output in the 'Script Output' window. This will enable output for the remainder of your session, or until you turn it off again.

If you're running it as a statement rather than a script, and don't already have (or don't want) serveroutput on, you can attach SQL Developer to the output. From the View menu choose 'DBMS Output', which opens a new panel with the same name. Running your code again will still produce nothing at this point.

Click the green plus sign (+) and choose your connection from the list. The next time you run it you will see the output. The first time it will probably show multiple versions as the previous runs will have stored the output in a buffer on the server, and they will all be retrieved. Subsequent runs will just show the new output generated by each run.

There's more in the SQL Developer documentation; and there is background in the PL/SQL packages and types reference, which you've probably already seen. Personally I only ever use the script output, partly because it's quicker, partly because it puts mixed output from SQL and PL/SQL in one place (if out of step), but mostly from habit and to maintain script compatibility with SQL*Plus.

Upvotes: 1

Related Questions