How to print only the output of dbms_out from a sqlplus script launched from powershell

Question

myscript.sql:

set serveroutput on
set feedback off
set heading off
set echo off


DECLARE
    CURSOR c1 IS
        select 2 from dual
BEGIN
    FOR i IN c1
    LOOP
        DBMS_OUTPUT.put_line (i.object_text );
    END LOOP;
END;
/
exit;

I call my script with that:

sqlplus  user/pass@database  @Dpath\myscript.sql  -S | Out-String | echo

SQL*Plus Version..........is always printed in the beginning. I don't want it.
the lines of my query are printed. that is OK.
Disconnected .... from Oracle Database is always printeed in the end. I don't want it

This code is just a reproductible example.I know that for the particular case, there is other way to do that. But what I want is to print just the dbms_out

The -s (or -silent) flag will suppress the banner, but also the statement being executed; are you sure you want to see that? But that flag has to be before the @script part (and the credentials), otherwise it's just seen as another positional parameter. — Alex Poole
– Alex Poole, Commented Jun 8, 2022 at 7:32
Ok it's working now. the banner isn't printed. But the output from prompt or dbms_output is printed. Thanks — Pierre-olivier Gendraud
– Pierre-olivier Gendraud, Commented Jun 8, 2022 at 7:59

Gnqz · Accepted Answer · 2022-06-08 06:57:51Z

1

You can just use SPOOL to create a separate file, where you will have full control of what is printed. You can also use a combination of the options SET FEEDBACK OFF, SET HEAD OFF and SET VERIFY OFF to remove the number of rows received, column names etc. so that your file looks more like an actual report instead of redirected output.

answered Jun 8, 2022 at 6:57

Gnqz

3,3823 gold badges28 silver badges38 bronze badges

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

Comments

Dmitry Demin · Accepted Answer · 2022-06-08 09:40:47Z

0

    set serveroutput on
    set feedback off
    set heading off
    set echo off

    DECLARE
        CURSOR c1 IS
            select 2 as object_text  from dual;
    BEGIN
        FOR i IN c1
        LOOP
            DBMS_OUTPUT.put_line (i.object_text );
        END LOOP;
    END;
    /
    EXIT;


    PS C:\otr> sqlplus -s user/password@dev19  @c:\otr\file1.sql  -S | Out-String | echo
    2

answered Jun 8, 2022 at 9:40

Dmitry Demin

2,1332 gold badges19 silver badges21 bronze badges

Collectives™ on Stack Overflow

How to print only the output of dbms_out from a sqlplus script launched from powershell

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related