Sqlplus in shell script

Shell Programming and Scripting
1

Hi All,

Please let me know what i am missing in the following code (part of my script)

Schemas=(AWQM WFCONTROLLER PROVCO PRISM)

for s in "${Schemas[@]}"
do 
    sch="${s}_$tol"
        
    if [ $tol == AD ] || [ $tol == TD ];then

        echo "This is AD or TD region"

        sqlplus -s $sch/$tpwd@$ttns <<EOF
        set feedback off;
        set pagesize 0;
        set linesize 500;
        set trimspool on;

        spool run_$sch_drops.sql

        select 'set echo on'||chr(59) from dual;
        select 'spool run_'${sch}'_drops.log'||chr(59) from dual;
        select 'show user'||chr(59) from dual;
        select '' from dual;
        select 'drop table "'||table_name||'" cascade constraints purge'||chr(59) from all_tables where owner = '${sch}';
        select '' from dual;
        select 'drop synonym "'||synonym_name||'"'||chr(59) from all_synonyms where owner = '${sch}';
        select '' from dual;
        select 'Drop TYPE "'||OBJECT_NAME||'" Force'||chr(59) from all_objects where object_type='TYPE' and owner='${sch}';
        select 'quit'||chr(59) from dual;

        quit;
        EOF
        

    else
        echo "This is Other Env"
        
    fi
done

I am getting error syntax error: unexpected end of file when i run my script.

Please suggest

Thanks,
Mani

2

This:

        EOF

You cannot indent this, it has to be at the very beginning of the line. Otherwise it will not be considered the end of the here-document.

1 Like
3

I think EOF started at beginning of sql and end after doing the query. Please correct me if i am wrong.

4

It did, but that's not what Corona688 is talking about. The final "END" in your script should be at the beginning of the line, but is not.

5

You might get away with indenting the EOF line with tabs, but only tabs. Copy and paste will normally replace it with spaces, so you have to be very careful.

For safety, you might need to end up with something that looks a bit odd like this:-

:
:
:
        select 'drop synonym "'||synonym_name||'"'||chr(59) from all_synonyms where owner = '${sch}';
        select '' from dual;
        select 'Drop TYPE "'||OBJECT_NAME||'" Force'||chr(59) from all_objects where object_type='TYPE' and owner='${sch}';
        select 'quit'||chr(59) from dual;

        quit;
EOF
        

    else
        echo "This is Other Env"
:
:
:

Robin

6

You are correct....it was space issue... i put a tab and it got worked..thanks a lot

sqlplus -s $sch/$tpwd@$ttns	<<EOF
7

I would still suggest you move the $sch/$tpwd@$ttns to another line so that it does not appear to anyone else running ps -ef | grep sql

You might need to code it as:-

sqlplus -s <<EOF
$sch/$tpwd@$ttns
set feedback off;
:
:
:

I don't know how sensitive your databases are, but I would like to keep my credentials secret no matter what they can see or do.

Robin

2 Likes
8

Yes I will do....I am writing the script for env refresh and doing the testing parallel....It will not go to PROD...but anyway i will do that once i am done with the clean up module... Thanks alot