CODEWITHSUNDEEP
oracle-databaseplsqlsqlplus
Felipe Vidal
Felipe Vidal

Reputation: 505

How can I skip printing an SQL script but still show the DBMS values?

I'm developing a code and would like to see in the results only the actual values, instead of the script I executed.

So I'm trying to develop a "table rows counter" and I've been quite successful, but I still get the full executed script, when I only need its results.

Let's say, my code is:

DECLARE
VAR_1 NUMBER := 0;

BEGIN
    SELECT IMITM INTO VAR_1 FROM PRODDTA.F4101 WHERE ROWNUM <= 1;
    DBMS_OUTPUT.PUT_LINE('DUMMY TEST 1' || VAR_1);
    DBMS_OUTPUT.PUT_LINE('DUMMY TEST 2');

END;
/

Well, by clicking F5 I would see:

DUMMY TEST 150041087
DUMMY TEST 2
 PL/SQL procedure successfully completed.

In the complete code it reads about 200 lines of only code, so...

How can I get only the output lines, and in this case, remove the "PL/SQL procedure..." legend?

I've tried unsuccessfully yo use "SET FEED" and "SET TERM" off.

Upvotes: 1

Views: 1187

Answers (2)

Felipe Vidal
Felipe Vidal

Reputation: 505

I actually managed to do it by exploring SET commands.

For the SQL script "acknolwedgement", we can skip it with the following commands:

To disable the SQL & variables verification before the actual execution, use:

SET VERIFY OFF

To disable the post execution notes, as Tejash indicated already, use:

SET FEEDBACK OFF

So my prologue ends up being:

SET SERVERTOUTPUT ON;
SET VERIFY OFF;
SET FEEDBACK OFF;

Thanks a lot for the answers & comments.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

According to Oracle documentation:

SET FEEDBACK OFF also turns off the statement confirmation messages such as 'Table created' and 'PL/SQL procedure successfully completed' that is displayed after successful SQL or PL/SQL statements.

You need to use SET FEEDBACK OFF as following:

SQL> SET SERVEROUT ON
SQL> SET FEEDBACK OFF
SQL> DECLARE
  2      VAR_1   NUMBER := 0;
  3  BEGIN
  4      SELECT
  5          1
  6      INTO VAR_1
  7      FROM
  8          DUAL
  9      WHERE
 10          ROWNUM <= 1;
 11
 12      DBMS_OUTPUT.PUT_LINE('DUMMY TEST 1' || VAR_1);
 13      DBMS_OUTPUT.PUT_LINE('DUMMY TEST 2');
 14  END;
 15  /
DUMMY TEST 11
DUMMY TEST 2
SQL>
SQL>

Cheers!!

Upvotes: 2

Related Questions