Truncating table from a shell script

Shell Programming and Scripting
1

I am trying to truncate a table using below script. When I ran the script it runs fine but table was not truncated and the spool is empty. I am not sure what is wrong with pl/sql block.

#!/bin/ksh
# ----------------------------------------------------------------------
#
#  Created by: XXXX
#  Generated on: 03/02/2009 15:00:00
#  Job name : TRUNC-MEMBER
#  Job description: Truncate Queries
# ----------------------------------------------------------------------
#
echo "Now processing step: LOAD_"
DB_CONNECT="USER/PASS@TESTDB"
SPOOLFILE=/home/log.txt

echo "Now processing step:" $DB_CONNECT
sqlplus -s ${DB_CONNECT} <<EOF

spool ${SPOOLFILE}

whenever oserror exit sql.sqlcode
whenever sqlerror exit sql.sqlcode
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED

declare
Begin

DBMS_OUTPUT.PUT_LINE('started;');
EXECUTE IMMEDIATE 'TRUNCATE TABLE MEMBER DROP STORAGE';

End;

spool off;
EXIT;
EOF
echo "Now processing step: LOAD_"
2

Make sure you have permission to truncate the table with the user that you are logged in.

3

Yes, I do have and I verified running

echo "Now processing step: LOAD_"
DB_CONNECT="OPS_STATS/OPS_4STATS@UTIDAL"
SPOOLFILE=/home/ca51336/log.txt

echo "Now processing step:" $DB_CONNECT
sqlplus -s ${DB_CONNECT} <<EOF

spool ${SPOOLFILE}

whenever oserror exit sql.sqlcode
whenever sqlerror exit sql.sqlcode
SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED


TRUNCATE TABLE MEMBER DROP STORAGE;


spool off;
exit;
EOF
echo "Now processing step: LOAD_"

Its not truncating only if the code is enclosed in pl/sql block

---------- Post updated at 11:59 PM ---------- Previous update was at 09:49 PM ----------

After adding / at end of pl/sql block the code worked fine.

SET SERVEROUTPUT ON SIZE 1000000 FORMAT WRAPPED
SET FEED OFF
spool ${SPOOLFILE}

BEGIN

DBMS_OUTPUT.PUT_LINE('started;');
EXECUTE IMMEDIATE 'TRUNCATE TABLE MEMBER DROP STORAGE';
END;
/
spool off;
exit;
EOF