Tag Archives: Script

Shellscript: PL/SQL-Block in SQLPLUS-Here-Document with Parameter

#! /bin/bash

echo -n ORACLE_USER:
read ORACLE_USER
echo -n ORACLE_USER Password:
read -s ORACLE_PW

if [ -z “$1” ]; then
echo Usage: Please call this Script like this: “./SQL_HERE_DOCUMENT_WITH_PARAMETER.sh [DESCRIPTION]”
exit
fi

sqlplus $ORACLE_USER/$ORACLE_PW << EOF
DEFINE DESCRIPTION=$1
SET SERVEROUTPUT ON
DECLARE
v_description varchar2(255):= ‘&DESCRIPTION’;
v_output varchar2(255);
BEGIN

SELECT v_description
into v_output
from dual;
dbms_output.put_line(‘————————————————————–‘);
dbms_output.put_line(‘Given Description: ‘||v_output);
dbms_output.put_line(‘————————————————————–‘);
END;
/
EXIT;
EOF

exit;