sql query with shell, trying the following and it is not working

Question

I am writing a shell script that connects to sql DB and run select query, need to have the result in a parameter and print it

This is my code: - please provide also explanation. I tried the following code but receiving the following output:

> SQL> SQL> SQL> SQL> SQL> SQL> SQL> SP2-0734: unknown command beginning "result=SP2..." - rest of line ignored.

Here is my code:

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 =============================================================  

Answer 1

The here document (the stuff between <<EOF and EOF) is not executed by the shell. The result=$(echo ...) is being subjected to command substitution but the result= part is simply being passed through verbatim to SQL, which of course doesn't know what to do with it.

The proper solution is to do the command substitution around the entire command line:

# Assign output from sqlplus to variable
# (Notice also proper quoting around variables)
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"    # notice also proper quoting here
echo ==============================================================

I already showed you (in your deleted question) how to solve this. Here's the demo link again; https://ideone.com/xBNSVk

In some more detail, the general syntax is

result=$(some command here
... which spans multiple lines ...
until the end |
maybe a pipeline too;
or just multiple commands
)

where the command we execute is sqlplus with input from a here document.

As a matter of aesthetics, you might want to use a here document instead of the fugly repeated echos too:

cat <<EOF
==============================================================
Printing the results
==============================================================
$result
==============================================================
EOF

(though of course the formatting suggests aesthetics are not a priority here).

Inside your sqlplus here document, you have another command substitution. For consistency, probably use the modern $(...) syntax instead of obsolescent `...` backtick syntax there, too.

The shell doesn't care what you put after << -- as long as you have the same delimiter in both places, you can use anything you like. (I usually prefer ____ or : because they are less noisy than common text delimiters like EOF or HERE.)