Learn Database Concepts

declare         cursor c_job is select * from job;         r_job c_job%ROWTYPE;         cursor c_empInjob (cin_jobNo NUMBER) is select * from employee where empno = cin_jobNo;         r_emp c_empInjob%ROWTYPE;     begin         open c_job;         loop            fetch c_job into r_job;            exit when c_job%NOTFOUND;            open c_empInjob (r_job.empno);            loop                fetch c_empInjob into r_emp;                exit when c_empInjob%NOTFOUND;            end loop;            close c_empInjob;        end loop;        clo...

Cross table update (also known as correlated update, or multiple table update) in Oracle uses non-standard SQL syntax format (non ANSI standard) to update rows in another table. The differences in syntax are quite dramatic compared to other database systems like MS SQL Server or MySQL. In this article, we are going to look at four scenarios for Oracle cross table update. Suppose we have two tables Categories and Categories_Test. See screenshots below. lets take two tables TABA & TABB: Records in TABA: Records in TABB: 1. Update data in a column LNAME in table A to be upadted with values from common column LNAME in table B. The update query below shows that the PICTURE column LNAME is updated by looking up the same ID value in ID column in table TABA and TABB.  update TABA A set (a.LNAME) = (select B.LNAME FROM TABB B where A.ID=B.ID); 2. Update data in two columns in table A based on a common column in table B. If you need to update multiple columns simultaneously, use comma to...

Querying directory permissions granted to a user SELECT grantee, table_name directory_name, LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY grantee)   FROM dba_tab_privs  WHERE table_name =' DPUMP' group by GRANTEE,TABLE_NAME; SAMPLE output: GRANTEE              DIRECTORY_NAME                 GRANTS             -------------------- ------------------------------ -------------------- SCOTT                  DPUMP                       READ,WRITE          TIGER              ...

SET VERIFY OFF ACCEPT par1 prompt "ENTER PARAMETER #1: " ACCEPT par2 prompt "ENTER PARAMETER #2: " execute pkg_TEST_VARIABLES.TEST_PASS_VARIABLES ( &&par1, &&par2);

select grants on tables to users or roles SELECT   OWNER,   GRANTEE,   GRANTOR,   TABLE_NAME,   PRIVILEGE FROM   DBA_TAB_PRIVS WHERE   table_name   = 'TABLENAME' AND PRIVILEGE IN ('DELETE','INSERT','SELECT','UPDATE') AND GRANTEE   IN ('ROLE1','ROLE2') ORDER BY   1,2; select grants on table to a user thru role or direct priv select Grantee,'Granted Through Role' as Grant_Type, role, table_name from role_tab_privs rtp, dba_role_privs drp where rtp.role = drp.granted_role and table_name = 'TABLENAME' union select Grantee,'Direct Grant' as Grant_type, null as role, table_name from dba_tab_privs where table_name = 'TABLENAME' ;

Method 1: set ORACLE_HOME=D:\app\oracle\product\11.2.0\db_1 set ORACLE_SID=ORCL sqlplus -s "USERNAME/PASSWORD" @C:\Shell\Drop_objs.sql sqlplus -s "USERNAME/PASSWORD" @C:\Shell\Change_pass.sql Method 2: set ORACLE_HOME=D:\app\oracle\product\11.2.0\db_1 set ORACLE_SID=ORCL (ECHO select username from dba_users where username in ('SCOTT'); ECHO exit;) | sqlplus -s "USER/PASS" > C:\Shell\test_out.txt

********************************************************************** You can use this simple plsql block to drop all objects in your current schema. ********************************************************************** declare VSQL varchar2(4000); OBJ_NAME varchar2(100); OBJ_TYPE varchar2(100); OBJ_OWNER varchar2(100); cursor c1 is select object_type,object_name from user_objects where object_type in ('TABLE','VIEW'); begin open c1; loop fetch c1 into OBJ_TYPE,OBJ_NAME; exit when c1%NOTFOUND; IF OBJ_TYPE='TABLE'  THEN VSQL:=' drop '||OBJ_TYPE||' '||OBJ_NAME||' cascade constraints'; DBMS_OUTPUT.PUT_LINE(VSQL); execute IMMEDIATE VSQL; ELSE   VSQL:=' drop '||OBJ_TYPE||' '||OBJ_NAME; DBMS_OUTPUT.PUT_LINE(VSQL); execute IMMEDIATE VSQL; END IF; end loop; close c1; end; / Note : Remove "where object_type in ('TABLE','VIEW')" to drop all objects from current user. Or you can edit to include only type o...

Create table sql :  CREATE TABLE ABC (  ID NUMBER , NAME VARCHAR2(20 BYTE) ) ; Lets insert duplicate values into it: INSERT INTO ABC (ID, NAME) VALUES ('15', 'f'); INSERT INTO ABC (ID, NAME) VALUES ('15', 'f'); INSERT INTO ABC (ID, NAME) VALUES ('15', 'f'); INSERT INTO ABC (ID, NAME) VALUES ('11', 'B'); INSERT INTO ABC (ID, NAME) VALUES ('11', 'B'); INSERT INTO ABC (ID, NAME) VALUES ('13', 'G'); INSERT INTO ABC (ID, NAME) VALUES ('13', 'G'); Now table looks like this:                     Now we have a duplicate combination of 2 columns. sql to find duplicate values combination in two columns:    select ID,NAME, count(ID) from ABC group by ID,NAME having count(ID) > 1; sample output:   Find the duplicate ID's in a column :  select ID, count(ID) from ABC group by ID having count(ID) > 1; Sample output:  Retrieving dup...