Posts

Oracle: Script to drop ALL tables

*** Be aware what you are running!

Copy this script to SQL tool and run.

BEGIN
  FOR cur_rec IN (SELECT object_name, object_type
                    FROM user_objects
                   WHERE object_type IN
                            ('TABLE',
                             'VIEW',
                             'PACKAGE',
                             'PROCEDURE',
                             'FUNCTION',
                             'SEQUENCE'
                            ))
  LOOP
     BEGIN
        IF cur_rec.object_type = 'TABLE'
        THEN
           EXECUTE IMMEDIATE    'DROP '
                             || cur_rec.object_type
                             || ' "'
                             || cur_rec.object_name
                             || '" CASCADE CONSTRAINTS';
        ELSE
           EXECUTE IMMEDIATE    'DROP '
                             || cur_rec.object_type
           …

Shell Script: Passing parameter with Comma and Single Quote to SQL script

I developed Shell script to call SQL command in Oracle. The parameter that I need to pass is part of this type of query:

DELETE FROM <table_name>
WHERE <column_name> IN ('XX','YY')

Which value 'XX','YY' need to parameter from Shell script.

I need to call SQL in another Shell script file.

The problem is when you passing parameter contains of both of Single Quote (') and Comma (,). If you use wrong syntax in parameter, SQL will not be proceeded.

Here is the solution that I use:

Environment:

Shell: Korn ShellDatabase: Oracle
Shell script: file.sh

parameter_1=XX"','"YY
#expansion is X X " ' , ' " Y Y
`sqlplus -s <orauser>/<orapwd>@<sid> @sqlfile.sql $parameter_1`

SQL script: sqlfile.sql

DELETE FROM <table_name>
WHERE <column_name> IN ('&1')

Now, you can add more value to match your criteria.

Good luck!