CODEWITHSUNDEEP
oracle-databaseplsqloracle11goracle-sqldevelopersqlplus
Hargun Suri
Hargun Suri

Reputation: 311

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

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

enter image description here

Output in SQL Developer

enter image description here

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;

Upvotes: 3

Views: 27297

Answers (1)

William Robertson
William Robertson

Reputation: 16001

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.

Upvotes: 7

Related Questions