Recompiling objects in oracle

A pl/sql script to recompile all invalid objects in oracle. Simple but powerfull.

DECLARE

   obj_name_    User_Objects.object_name%TYPE;
   obj_type_    User_Objects.object_type%TYPE;
   str_run_     VARCHAR2(200);
   cid_         INTEGER;
   ret_         INTEGER;
   CURSOR Invalid_Objects_ IS
      SELECT object_name, object_type
      FROM user_objects
      WHERE status = 'INVALID'
      ORDER BY object_type ASC;

BEGIN 
   FOR Get_Rec_ IN Invalid_Objects_ LOOP
      BEGIN
         obj_name_ := Get_Rec_.object_name;
         obj_type_ := Get_Rec_.object_type;
         IF (obj_type_ = 'FUNCTION') THEN
            str_run_ := 'ALTER FUNCTION ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'JAVA SOURCE') THEN
            str_run_ := 'ALTER JAVA SOURCE "' || obj_name_ || '"
COMPILE';
         ELSIF (obj_type_ = 'JAVA CLASS') THEN
            str_run_ := 'ALTER JAVA CLASS "' || obj_name_ || '"
RESOLVE';
         ELSIF (obj_type_ = 'PACKAGE') THEN
            str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'PACKAGE BODY') THEN
            str_run_ := 'ALTER PACKAGE ' || obj_name_ || ' COMPILE
BODY';
         ELSIF (obj_type_ = 'PROCEDURE') THEN
            str_run_ := 'ALTER PROCEDURE ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'TRIGGER') THEN
            str_run_ := 'ALTER TRIGGER ' || obj_name_ || ' COMPILE';

         ELSIF (obj_type_ = 'VIEW') THEN
            str_run_ := 'ALTER VIEW ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'MATERIALIZED VIEW') THEN
            str_run_ := 'ALTER MATERIALIZED VIEW ' || obj_name_ || '
COMPILE';
         ELSIF (obj_type_ = 'DIMENSION') THEN
            str_run_ := 'ALTER DIMENSION ' || obj_name_ || ' COMPILE';
         ELSIF (obj_type_ = 'TYPE') THEN
            str_run_ := 'ALTER TYPE ' || obj_name_ || ' COMPILE';
         END IF;
         --
         cid_ := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(cid_, str_run_, DBMS_SQL.NATIVE);
         ret_ := DBMS_SQL.EXECUTE(cid_);
         DBMS_SQL.CLOSE_CURSOR(cid_);
      EXCEPTION
         WHEN OTHERS THEN
            BEGIN
               IF (DBMS_SQL.IS_OPEN(cid_)) THEN
                  DBMS_SQL.CLOSE_CURSOR(cid_);
               END IF;
            END;
      END;

   END LOOP; 
END; 

Source:
http://www.orafaq.com/usenet/comp.databases.oracle.misc/2005/02/15/0382.htmy

How to use LIMIT and ORDER BY in sqlite

To get the first 10 in descending order in sqlite


SELECT * FROM table ORDER BY id DESC LIMIT 10

To get the first 10 in ascending order in sqlite


SELECT * FROM table ORDER BY id ASC LIMIT 10

Note: If there are less than 10 entrys in the sqlite database, you will get less than 10 results.

Oracle reverse engineering

To reverse engineere the oracle database, you can use the Oracle data dictionary like follows.

-- Get table information from oracle DB
select T.* from USER_TABLES T;

-- Get column information from oracle DB
select C.* from USER_TAB_COLUMNS C;

-- Get constraint information from oracle DB
SELECT cons.constraint_type, cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_name = cols.constraint_name
ORDER BY cols.table_name, cols.position;

-- oracle constraint types
--C (check constraint on a table)
--P (primary key)
--U (unique key)
--R (referential integrity)
--V (with check option, on a view)
--O (with read only, on a view)

-- Get all referential constraints from oracle DB including referencing table
select UCC.TABLE_NAME, UCC.CONSTRAINT_NAME, UCC.COLUMN_NAME, UC.CONSTRAINT_TYPE, UC.SEARCH_CONDITION, UC2.TABLE_NAME as REFERENCES_TABLE
from USER_CONS_COLUMNS UCC, USER_CONSTRAINTS UC, USER_CONSTRAINTS UC2
where UCC.CONSTRAINT_NAME = UC.CONSTRAINT_NAME
and UC.R_CONSTRAINT_NAME = UC2.CONSTRAINT_NAME(+) and UC.CONSTRAINT_TYPE = 'R'
order by UCC.CONSTRAINT_NAME;

-- Get ddl of a table from oracle by name
SELECT DBMS_METADATA.GET_DDL('TABLE','MY_TABLE_NAME') FROM DUAL;

-- Get all referential constraints from oracle DB including referencing table and column     
SELECT CONS.CONSTRAINT_NAME, CONS.TABLE_NAME, COLS.COLUMN_NAME,
    CONS.R_CONSTRAINT_NAME, CONS_R.TABLE_NAME R_TABLE_NAME, COLS_R.COLUMN_NAME R_COLUMN_NAME
FROM USER_CONSTRAINTS CONS
    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R' -- AND CONS.TABLE_NAME = 'MY_TABLE_NAME'
ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME;