Some useful Oracle SQL-2

1)  To Retrieve Deleted data :

       # select * from recyclebin
       # alter table CSMS_USER_REG enable row movement
       # flashback table CSMS_USER_REG to timestamp (sys timestamp-interval '1' hour)
       # select * from CSMS_USER_REG

2) To add a new column which value cannot be null:

     # ALTER TABLE E_RAJUK.RP_REGUSER
      MODIFY(NATIONAL_ID  NOT NULL);
     Here  E_RAJUK =schema name, RP_REGUSER =Table name, NATIONAL_ID  =Column Name
3) To Delete and Add a Column name from every table of a schema

#   at first altera table colomn.get the sql.by the Sql write the sql "a" ,then see the result below the tode and edit the sql like sql "b" .then "ctrl+Enter",get the bellow result sql and get a new editor and copy and pest the all sql .then "ctrl+Enter".Then you will get all altered table.

a)  select 'ALTER TABLE E_RAJUK.PROJECT_WISE_CATEGORY DROP COLUMN INSERT_BY;' from tab where tabtype='TABLE'

b)  select 'ALTER TABLE E_RAJUK.'||tname||' DROP COLUMN INSERT_BY;' from tab where tabtype='TABLE'

c)  select 'ALTER TABLE E_RAJUK.'||tname||' ADD (INSERT_BY  NUMBER);' from tab where tabtype='TABLE'

N.B.-Here E_RAJUK=Schema name, PROJECT_WISE_CATEGORY=table name(You can use any kind of table name).You must do all those sql query in a Toad editor.

4) To add same column in database whole table :
a) ALTER TABLE E_RAJUK.ALL_LOOKUP ADD (insert_by  VARCHAR2(30)DEFAULT user);

b)  select 'ALTER TABLE E_RAJUK.'||tname||' ADD (insert_date  date DEFAULT sysdate);' from tab where tabtype='TABLE'

c) select 'ALTER TABLE E_RAJUK.'||tname||' ADD (insert_by  VARCHAR2(30)DEFAULT user);' from tab where tabtype='TABLE'

N.B.-Here E_RAJUK=Schema name ALL_LOOKUP =table name(You can use any kind of table name).You must do all those sql query in a Toad editor.

5) To Delete The Duplicate rows in a table:

# Delete table_a where rowid not in (select min(rowid)from table_a group by column1,column2);

In the above data Mnipulation Language statement ,the sub-query should be grouped by all the columns that are part of the primary/unique key in the above case it is column1 and column2

6) To create a View with a package :

a) Step - 1 :

create or replace force view DAP_IMAGES_v (DAP_IMAGE_ID,  THANA_NAME,  UNION_WORD_NAME, MOUZA_NAME, GROUP_OR_LOCATION_NAME, PLOT_NO, THANA_ID, UNION_WORD_ID, MOUZA_ID, GROUP_OR_LOCATION_ID, PLOT_ID, DELETED_FLAG, IMAGE_NAME, DAP_IMAGE_LINK                                        )

As (SELECT d.DAP_IMAGE_ID, d.THANA_IDRAJUK.GET_location_NAME(d.THANA_ID,1) THANA_NAME,  d.UNION_WORD_ID, RAJUK.GET_location_NAME(d.UNION_WORD_ID,2)UNION_WORD_NAME, d.MOUZA_ID, RAJUK.GET_location_NAME(d.MOUZA_ID,3)MOUZA_NAME, d.PLOT_ID, RAJUK.GET_location_NAME(d.PLOT_ID,4) PLOT_NO, d.GROUP_OR_LOCATION_ID, RAJUK.GET_location_NAME(d.GROUP_OR_LOCATION_ID,5) GROUP_OR_LOCATION_NAME d.DELETED_FLAG, d.IMAGE_NAME, d.DAP_IMAGE_LINK from DAP_IMAGES d)

2)Step - 2:             

CREATE OR REPLACE PACKAGE E_RAJUK.RAJUK
AS  
 FUNCTION GET_location_NAME(pLOCATION_ID IN NUMBER, pLOCATION_TYPE_ID in number)
 RETURN VARCHAR2;
END;
/

3) Step – 3:

CREATE OR REPLACE PACKAGE BODY E_RAJUK.RAJUK
AS
 FUNCTION GET_location_NAME(pLOCATION_ID IN NUMBER, pLOCATION_TYPE_ID in number)
 RETURN VARCHAR2
 IS
 V_RET VARCHAR2 (50);
 BEGIN
 SELECT LOCATION_NAME
 INTO V_RET
 FROM RAJUK_LOCATION
 WHERE LOCATION_ID=pLOCATION_ID
 and   LOCATION_TYPE_ID = pLOCATION_TYPE_ID;
 RETURN V_RET;
 EXCEPTION
 WHEN NO_DATA_FOUND
 THEN
 V_RET := 'Not Defined';
 RETURN  V_RET;

 END;

end;