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 <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 crea…

Accepted Answer

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).

Issue with execution of PLSQL block in Shell script

Solution

Problem