#!/bin/bash

sqlplus () {
   echo 'This is the input which sqlplus receives'
   nl
   echo 'End of sqlplus'
   echo result
}

echo '** Not working'

sqlplus "${DBAdminUser}/${DBAdminPassword}@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ${DBServerName})(PORT = ${DBServerPort}))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ${ServiceName})))"<<EOF  
-- =======================================================  

spool File_`date +%d_%m_%y`.csv REPLACE     
result=$(echo 'select SP_CREATION_TIME from shared_space where SP_CREATION_TIME like '%20-AUG-20' ');  
spool off  
EOF
echo ==============================================================  
echo Printing the results  
echo ==============================================================  
echo "$result"
echo =============================================================  

echo 
echo '** ... ^^ notice also how "result" is printed when sql runs, not when you echo "$result"'
echo

echo
echo '** Working'

result=$(sqlplus "${DBAdminUser}/${DBAdminPassword}@(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ${DBServerName})(PORT = ${DBServerPort}))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ${ServiceName})))"<<EOF  
-- =======================================================  

spool File_`date +%d_%m_%y`.csv REPLACE     
select SP_CREATION_TIME from shared_space where SP_CREATION_TIME like '%20-AUG-20;
spool off  
EOF
)
echo ==============================================================  
echo Printing the results  
echo ==============================================================  
echo "$result"
echo =============================================================