Trying to to Store Value of PL/SQL Block in UNIX Variable

Question

UNIX Code:

#!/bin/sh

if [ $# -gt 0 ]

    then

    ## Checking wheather or all the Server Objects are up and working

        if [[ $1 = 'PreCheck' ]];then

            COUNT_SERV=`${ORACLE_HOME}/bin/sqlplus ${dbConnect} <<GETCOUNT|tee -a ${logFile}

                SET HEADING ON
                SET SERVEROUTPUT ON SIZE 10000
                SET ECHO OFF
                SET FEEDBACK OFF

                @PLSQLBLCK1.sql

            GETCOUNT`

        fi
fi

exit 0;

SQL Code:

DECLARE

   COUNT_SERV INTEGER;

BEGIN

    DBMS_OUTPUT.ENABLE(1000000);

    SELECT COUNT (*) INTO COUNT_SERV FROM FT_LM_SERVERS WHERE STATE <> 'START';

        IF COUNT_SERV > 0 THEN

            DBMS_OUTPUT.PUT_LINE('******ERROR1: One or More BG Object is Down, please restart the BG to proceed ahead******');

        END IF;

END;

I am trying to get the value of DBMS_OUTPUT.PUT_LINE stored in COUNT_SERV (of UNIX), so that I can use it accordingly ahead. However, if I execute the shell script, I am getting output as below in Log file:

SQL> SQL> SQL> SQL> SQL> SQL> SQL>  18   19   20

Please ignore if some values are hidden, as I can not reveal the full code here.

Toru · Accepted Answer · 2020-06-16 22:09:20Z

1

Ther're some errors in your code:

Use the -Sparameter for SQL*Plus
Put an slash at end of PL/SQL block
Put the ending GETCOUNTin the first column

... and then the code will work e.g.:

test.sql:

DECLARE
    i PLS_INTEGER;
BEGIN
    SELECT COUNT(1)
    INTO i
    FROM dual;

    IF (i > 0)
    THEN
        DBMS_OUTPUT.PUT_LINE(TO_CHAR(i));
    END IF;
END;
/
-- ^^^ Slash!!!

test.sh:

foo=`sqlplus -S foo/bar@bla <<EOF | tee -a logfile
    SET HEADING ON;
    SET SERVEROUTPUT ON SIZE 10000;
    SET ECHO OFF;
    SET FEEDBACK OFF;
    @test.sql;
EOF` # <-- First column!!!

echo $foo; # --> Prints "1"

cat logfile # Prints "1"

edited Jun 16, 2020 at 22:09

answered Jun 16, 2020 at 21:50

Toru

9252 gold badges9 silver badges29 bronze badges

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

Collectives™ on Stack Overflow

Trying to to Store Value of PL/SQL Block in UNIX Variable

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related