Tweaked a bit with the error treatment on the clean schema script; creates a simple proc that generate a random password with minimal security; minor changes on the findObjects and getUserGrants scripts

David FRANCO · David FRANCO · commit 8d781682b82d · 2018-02-05T11:42:39.000-02:00
diff --git a/cleanSchema.sql b/cleanSchema.sql
@@ -66,7 +66,7 @@ begin
             execute immediate 'drop table '||tbl.owner||'.'||tbl.table_name||' cascade constraints purge';
             --dbms_output.put_line('drop table '||tbl.owner||'.'||tbl.table_name||' cascade constraints purge');
         exception
-            when no_data_found then
+            when others then
                 dbms_output.put_line('ERROR: Drop failed on Table: '||tbl.owner||'.'||tbl.table_name||' --> '|| sqlerrm);
         end;
     end loop;
@@ -78,7 +78,7 @@ begin
             execute immediate 'drop '||obj.object_type||' '||obj.owner||'.'||obj.object_name;
             --dbms_output.put_line('drop '||obj.object_type||' '||obj.owner||'.'||obj.object_name);
         exception
-            when no_data_found then
+            when others then
                 dbms_output.put_line('ERROR: Drop failed on '||obj.object_type||': '||obj.owner||'.'||obj.object_name||' --> '|| sqlerrm);
         end;
     end loop;
@@ -90,7 +90,7 @@ begin
             execute immediate 'drop '||pkg.object_type||' '||pkg.owner||'.'||pkg.object_name;
             --dbms_output.put_line('drop '||pkg.object_type||' '||pkg.owner||'.'||pkg.object_name);
         exception
-            when no_data_found then
+            when others then
                 dbms_output.put_line('ERROR: Drop failed on '||pkg.object_type||': '||pkg.owner||'.'||pkg.object_name||' --> '|| sqlerrm);
         end;
     end loop;
@@ -102,7 +102,7 @@ begin
             dbms_scheduler.drop_job(job.owner||'.'||job.object_name);
             --dbms_output.put_line('dbms_scheduler.drop_job('||job.owner||'.'||job.object_name||')');
         exception
-            when no_data_found then
+            when others then
                 dbms_output.put_line('ERROR: Drop failed on JOB: '||job.owner||'.'||job.object_name||' --> '|| sqlerrm);
         end;
     end loop;
@@ -114,7 +114,7 @@ begin
             dbms_scheduler.drop_schedule(schedule.owner||'.'||schedule.object_name);
             --dbms_output.put_line('dbms_scheduler.drop_schedule('||schedule.owner||'.'||schedule.object_name||')');
         exception
-            when no_data_found then
+            when others then
                 dbms_output.put_line('ERROR: Drop failed on SCHEDULE: '||schedule.owner||'.'||schedule.object_name||' --> '|| sqlerrm);
         end;
     end loop;
diff --git a/createRandPass.sql b/createRandPass.sql
@@ -0,0 +1,21 @@
+set lines 190 pages 50000 long 10000
+set serveroutput on size unlimited
+
+DECLARE
+    vPasswd varchar2(50);
+BEGIN   
+    vPasswd := DBMS_RANDOM.STRING('a', 1);
+    FOR i IN 1 .. 11
+    LOOP
+        CASE ROUND(DBMS_RANDOM.VALUE(1,5))
+            WHEN 1 THEN
+                vPasswd := vPasswd || ROUND(DBMS_RANDOM.VALUE(0, 10));
+            WHEN 2 THEN
+                vPasswd := vPasswd || '#';
+            ELSE
+                vPasswd := vPasswd || DBMS_RANDOM.STRING('a', 1);
+        END CASE;
+    END LOOP;
+    DBMS_OUTPUT.PUT_LINE(vPasswd);
+END;
+/
diff --git a/findObject.sql b/findObject.sql
@@ -16,5 +16,5 @@ SELECT DISTINCT OWNER,
        STATUS
   FROM DBA_OBJECTS 
  WHERE OBJECT_NAME like upper('%' || :v_owner || '%')
- ORDER BY 2, 3
+ ORDER BY 1, 2, 3
 ;
diff --git a/getCompilationErrors.sql b/getCompilationErrors.sql
@@ -0,0 +1,30 @@
+set lines 230 pages 5000 long 10000
+col owner for a20
+col name for a30
+col "LIN/POS" for a8
+col text for a168
+
+select OWNER, 
+    NAME, 
+    LINE || '/' || POSITION as "LIN/POS",
+    TEXT
+from dba_errors
+where owner = 'REPORTWEB'
+order by owner, name, SEQUENCE
+;
+
+
+/*
+SQL> desc dba_errors
+ Name                                      Null?    Type
+ ----------------------------------------- -------- ----------------------------
+ OWNER                                     NOT NULL VARCHAR2(30)
+ NAME                                      NOT NULL VARCHAR2(30)
+ TYPE                                               VARCHAR2(12)
+ SEQUENCE                                  NOT NULL NUMBER
+ LINE                                      NOT NULL NUMBER
+ POSITION                                  NOT NULL NUMBER
+ TEXT                                      NOT NULL VARCHAR2(4000)
+ ATTRIBUTE                                          VARCHAR2(9)
+ MESSAGE_NUMBER                                     NUMBER
+*/
diff --git a/getUserGrants.sql b/getUserGrants.sql
@@ -15,4 +15,5 @@ where grantee in (:v_owner)
 union all
 select 'grant '||privilege||' on '||owner||'.'||table_name||' to '||grantee||';' 
   from dba_tab_privs 
- where grantee in (:v_owner);
+ where grantee in (:v_owner)
+;

Original file line number	Diff line number	Diff line change
`@@ -16,5 +16,5 @@ SELECT DISTINCT OWNER,`
`16`	`16`	`STATUS`
`17`	`17`	`FROM DBA_OBJECTS`
`18`	`18`	`WHERE OBJECT_NAME like upper('%' \|\| :v_owner \|\| '%')`
`19`		`- ORDER BY 2, 3`
	`19`	`+ ORDER BY 1, 2, 3`
`20`	`20`	`;`