How to show output of dbms_output.put_line used in procedure in sqlplus?

Question

I am trying to execute the procedure from my plsql block using SQLPLUS, unfortunately, I am not able to see the output from "dbms_output.put_line" from procedure just after the execution of SQL script, though the output is visible in the spool file, I wanted to show output in screen once the execution is completed.

For now, I am getting prompt but No output, though when I perform "SET SERVEROUTPUT ON;" in SQL developer it shows output, what shall I do to see the output in sqlplus once execution is completed.

Output in SQLPLUS

Output in SQL Developer

Table Structure and Data

create table cust_temp_temp
(name varchar2(20) , id varchar2(20));

insert into cust_temp_temp
select 'hasu t', '100' from dual
union all
select 'hasu r', '100' from dual
union all
select 'hasu e', '100' from dual
union all
select 'hasu  w', '100' from dual
union all
select 'hasu q', '100' from dual;

Package Structure :

CREATE OR REPLACE PACKAGE CUSTOM_PKG
AS
PROCEDURE get_transaction_details(
      id_ cust_temp_temp.id%TYPE);
END CUSTOM_PKG;
/

Package Body :

CREATE OR REPLACE PACKAGE BODY CUSTOM_PKG
AS
PROCEDURE get_transaction_details
  (
    id_ cust_temp_temp.id%TYPE
  )
AS
BEGIN
  dbms_output.put_line('SESSION DETAILS : ');
  FOR CUR_CUSTOM_LOG IN
  (SELECT name,
      id
    FROM cust_temp_temp
    WHERE id = id_
  )
  LOOP
    dbms_output.put_line('Name : '||CUR_CUSTOM_LOG.name);
    dbms_output.put_line('Id : '||CUR_CUSTOM_LOG.id);
  END LOOP;
  dbms_output.put_line('----------------');
END;
END CUSTOM_PKG;
/

Script :

SPOOL temp.lst;
SET TIME ON;
SET DEFINE OFF;
SET TERMOUT OFF;
SET PAGESIZE 20000;
SET LINESIZE 150;
SET ECHO ON;
SET SERVEROUTPUT ON;
  BEGIN
    CUSTOM_PKG.get_transaction_details(10);
  END;
/
SET ECHO OFF;
SET TIME OFF;
SET LINESIZE 100;
SET TERMOUT ON;
SET DEFINE ON;
SPOOL OFF;

Solution

The output is captured in your spoolfile, temp.lst. It's not displayed to the terminal due to

set termout off

In my test, temp.lst contained:

10:56:15 SQL> SET SERVEROUTPUT ON;
10:56:15 SQL>   BEGIN
10:56:15   2      CUSTOM_PKG.get_transaction_details(10);
10:56:15   3    END;
10:56:15   4  /
SESSION DETAILS :
----------------

PL/SQL procedure successfully completed.

10:56:15 SQL> SET ECHO OFF;

There are no actual results because all ids are 100 and you are passing 10.

Comments

[Barbaros Özhan]

I think you need to run @temp.lst instead of @temp.sql from the command prompt.

[Hargun Suri]

Hello William, It worked, But now it prints everything on the screen including PLSQL Block, due to the fact that "SET ECHO ON;", this will help to get complete script and execution inside spool file, so far I understood. But when I do SET ECHO OFF; and SET TERMOUT ON; I can get the desired output on Terminal but the spool file is now missing script that needs to be printed in Spool file, In short, I was looking for all the data to be present in SPOOL file and only part of CUSTOM_PKG.get_transaction_details(10); to be displayed on To the terminal.