Issue with execution of PLSQL block in Shell script

Question

I am trying to drop tables, which have a particular suffix(passed as argument $1), using shell script.

If a parent table is selected without its child tables being dropped, I am bypassing the parent table and increasing the counter in the exception block.

When I run this script in sql developer with $1 replaced with the proper value, it works. But when I run this shell script, it is getting stuck.

Could you please have a look and let me know, what am I missing in the shell script?

Code:

#!/bin/bash

cat <<ENDDROPNEWTABS >dropnewtabs.sql
set pagesize 100

DECLARE

t_cnt NUMBER;
CURSOR C001
IS
SELECT table_name FROM user_tables WHERE table_name LIKE '%$1%';

BEGIN
BEGIN SELECT COUNT(*) INTO t_cnt FROM user_tables WHERE table_name LIKE '%$1%'; 
END;

WHILE(t_cnt > 0) LOOP
FOR i IN C001 LOOP
BEGIN EXECUTE IMMEDIATE 'DROP TABLE '||i.table_name;
EXCEPTION
WHEN OTHERS THEN
t_cnt := t_cnt+1;
NULL;
END;      
t_cnt := t_cnt-1;
END LOOP;
END LOOP;

END;

exit
ENDDROPNEWTABS

echo "Dropping the tables created for this task..."

sqlplus -s usn/pwd@sid @dropnewtabs.sql >tablesDropped.txt

#END

Alex Poole · Accepted Answer · 2012-05-08 09:47:57Z

9

You are missing a / after the END; of your anonymous block, so it will never execute it, and the exit will be seen as part of the previous command. The / is very roughly analogous to 'run' in SQL Developer.

...
END LOOP;

END;
/

exit
ENDDROPNEWTABS

(You don't need the BEGIN/END around the SELECT, or the NULL in the exception handler, but those won't break anything; it's also not a good idea to squash all possible exceptions silently, just look for the one you're expecting to see. And personally I find it easier to follow with some indentation).

answered May 8, 2012 at 9:47

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Savitha Over a year ago

Thanks Alex for the solution. :-) Regarding the indentation, I had deliberately removed it thinking that would be the cause of the issue. :)

Chandra Bhushan · Accepted Answer · 2012-05-08 10:17:44Z

1

#!/bin/ksh 

PSQL1=dropnewtabs.sql
TABNAME=$1

echo $1

>tablesDropped.txt
>$PSQL1              #this command will create a new empty file

echo "set echo off feed off head off pages 0  " >> $PSQL1
echo "set serveroutput on "          >> $PSQL1
echo "set define off " >> $PSQL1
echo "DECLARE  " >> $PSQL1
echo "CURSOR C001 " >> $PSQL1
echo "IS " >> $PSQL1
echo "SELECT table_name FROM user_tables WHERE table_name = '$TABNAME'; " >> $PSQL1
echo "BEGIN " >> $PSQL1
echo "FOR i IN C001  " >> $PSQL1
echo "LOOP " >> $PSQL1
echo "EXECUTE IMMEDIATE 'DROP TABLE '|| i.table_name ; " >> $PSQL1
echo "dbms_output.put_line('TEST-------->'); " >> $PSQL1
echo "END LOOP; " >> $PSQL1
echo "END; " >> $PSQL1
echo "/ "       >> $PSQL1
echo "exit;"       >> $PSQL1


echo "Dropping the tables created for this task..."

sqlplus -s user/pwd@sid @$PSQL1 >>tablesDropped.txt 2>&1
echo "Complete"

answered May 8, 2012 at 10:17

Chandra Bhushan

3752 gold badges5 silver badges16 bronze badges

1 Comment

Alex Poole Over a year ago

There's nothing really wrong with the cat method the OP used; it's easer to read than a load of echo, in my opinion. And you've lost the exception handling which, while flawed, was necessary for the methodology being used to avoid ref constraint issues. The only fatal problem in the original code is the missing /.

Collectives™ on Stack Overflow

Issue with execution of PLSQL block in Shell script

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related