# Sqlplus in shell script

**URL:** <https://community.unix.com/t/sqlplus-in-shell-script/334707>\
**Category:** Shell Programming and Scripting\
**Created:** [September 25, 2013, 5:12pm UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707 "2013-09-25T17:12:09Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![pvmanikandan](https://community.unix.com/letter_avatar/pvmanikandan/32/6_449c533f586939b6e5f246632a46421f.png) [@pvmanikandan](https://community.unix.com/u/pvmanikandan)\
**Post date:** [September 25, 2013, 5:12pm UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707/1 "2013-09-25T17:12:09Z")

</div>

Hi All,

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

```nohighlight
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

---

<div class="post-metadata">

**Author:** ![Corona688](https://community.unix.com/letter_avatar/corona688/32/6_449c533f586939b6e5f246632a46421f.png) [@Corona688](https://community.unix.com/u/Corona688)\
**Post date:** [September 25, 2013, 5:29pm UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707/2 "2013-09-25T17:29:29Z")

</div>

This:

```nohighlight
        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.

---

<div class="post-metadata">

**Author:** ![pvmanikandan](https://community.unix.com/letter_avatar/pvmanikandan/32/6_449c533f586939b6e5f246632a46421f.png) [@pvmanikandan](https://community.unix.com/u/pvmanikandan)\
**Post date:** [September 25, 2013, 5:43pm UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707/3 "2013-09-25T17:43:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![durden\_tyler](https://community.unix.com/user_avatar/community.unix.com/durden_tyler/32/1450_2.png) [@durden\_tyler](https://community.unix.com/u/durden_tyler)\
**Post date:** [September 26, 2013, 3:11am UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707/4 "2013-09-26T03:11:23Z")

</div>

> [@pvmanikandan](#):
>
> I think EOF started at beginning of sql and end after doing the query. Please correct me if i am wrong.

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.

---

<div class="post-metadata">

**Author:** ![rbatte1](https://community.unix.com/letter_avatar/rbatte1/32/6_449c533f586939b6e5f246632a46421f.png) [@rbatte1](https://community.unix.com/u/rbatte1)\
**Post date:** [September 26, 2013, 5:58am UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707/5 "2013-09-26T05:58:48Z")

</div>

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

```nohighlight
:
:
:
        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

---

<div class="post-metadata">

**Author:** ![pvmanikandan](https://community.unix.com/letter_avatar/pvmanikandan/32/6_449c533f586939b6e5f246632a46421f.png) [@pvmanikandan](https://community.unix.com/u/pvmanikandan)\
**Post date:** [September 26, 2013, 12:00pm UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707/6 "2013-09-26T12:00:16Z")

</div>

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

```nohighlight
sqlplus -s $sch/$tpwd@$ttns	<<EOF

```

---

<div class="post-metadata">

**Author:** ![rbatte1](https://community.unix.com/letter_avatar/rbatte1/32/6_449c533f586939b6e5f246632a46421f.png) [@rbatte1](https://community.unix.com/u/rbatte1)\
**Post date:** [September 26, 2013, 12:15pm UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707/7 "2013-09-26T12:15:44Z")

</div>

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

```nohighlight
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

---

<div class="post-metadata">

**Author:** ![pvmanikandan](https://community.unix.com/letter_avatar/pvmanikandan/32/6_449c533f586939b6e5f246632a46421f.png) [@pvmanikandan](https://community.unix.com/u/pvmanikandan)\
**Post date:** [September 26, 2013, 12:36pm UTC](https://community.unix.com/t/sqlplus-in-shell-script/334707/8 "2013-09-26T12:36:36Z")

</div>

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
