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 '                              || cu
Post a Comment
Read more

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 Shell Database: 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!
Post a Comment
Read more