update rows from multiple tables (correlated update)

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 separate each column after the SET keyword.

update TABA A
set (a.LNAME, a.SAL) = (select B.LNAME, B.SAL FROM TABB B where A.ID=B.ID);

Directory permissions granted to a user in Oracle database

Querying directory permissions granted to a user

To query directory permissions granted to users in Oracle Database, you can use the DBA_TAB_PRIVS view. This view contains information about table and directory object privileges granted to users. Specifically, you want to retrieve information about directory privileges granted to users for a particular directory object.

Here’s a refined SQL query to achieve this, including sample output:

SQL Query:

sql:


SELECT 
    grantee AS "GRANTEE",
    table_name AS "DIRECTORY_NAME",
    LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY privilege) AS "GRANTS"
FROM 
    dba_tab_privs
WHERE 
    table_name = 'DPUMP' 
GROUP BY 
    grantee, table_name;

Columns Explanation:

• grantee: The user or role to whom the privilege is granted.
• table_name: The name of the directory object. In Oracle, directories are also managed as table-like objects in the DBA_TAB_PRIVS view.
• LISTAGG(privilege, ',') WITHIN GROUP (ORDER BY privilege): Aggregates the list of privileges granted to each user, separating them by commas.

Sample Output:

GRANTEE              DIRECTORY_NAME                 GRANTS            
-------------------- ------------------------------ --------------------
SCOTT                DPUMP                           READ,WRITE         
TIGER                DPUMP                           READ,WRITE         
TOM                  DPUMP                           READ,WRITE         
CAM                  DPUMP                           READ,WRITE         
SAM                  DPUMP                           READ,WRITE         

Usage Notes:

1. Adjust Table Name: Make sure to replace 'DPUMP' in the WHERE clause with the actual directory name you want to query.
2. Privileges: The privilege column typically includes access types such as READ and WRITE.
3. Oracle Versions: The DBA_TAB_PRIVS view is standard across Oracle versions, but always refer to your specific Oracle documentation for any version-specific details.

This query helps database administrators quickly review and manage directory access permissions granted to different users, ensuring appropriate access control and security.

passing variables in sqlplus scripts

The script snippet you provided is an SQL*Plus script used to interactively accept parameters and execute a stored procedure with those parameters. Here’s a detailed breakdown of each component and how it works:

Explanation of the Script:

1. SET VERIFY OFF

   • This command disables the verification of substitution variables in SQLPlus. When VERIFY is set to OFF, SQLPlus does not display the text of substituted variables, which can make the output cleaner.

2. ACCEPT par1 prompt "ENTER PARAMETER #1: "

   • The ACCEPT command is used to prompt the user for input. In this case, it asks the user to enter a value  par1 and displays the prompt message "ENTER PARAMETER #1: ". The value entered by the user is stored in the variable par1.

3. ACCEPT par2 prompt "ENTER PARAMETER #2: "

   • Similarly, this command prompts the user to enter a value for par2, with the prompt message "ENTER PARAMETER #2: ". The value entered by the user is stored in the variable par2.

4. execute pkg_TEST_VARIABLES.TEST_PASS_VARIABLES ( &&par1, &&par2);

   • This line executes a stored procedure TEST_PASS_VARIABLES from the package pkg_TEST_VARIABLES. The && notation is used to reference the variables par1 and par2 which were previously set by the ACCEPT commands. The double & notation ensures that the variables are resolved at runtime.

Putting It All Together:

When you run this SQL*Plus script, the following sequence of actions occurs:

1. Prompting for Input:

   • The script first prompts you to enter two parameters: par1 and par2.

2. Executing the Procedure:

   • After you enter the parameters, the script executes the procedure TEST_PASS_VARIABLES from the package pkg_TEST_VARIABLES, passing the entered parameters to the procedure.

Example Execution:

Assuming you have a stored procedure defined as follows:

sql

CREATE OR REPLACE PACKAGE pkg_TEST_VARIABLES AS
  PROCEDURE TEST_PASS_VARIABLES(p1 IN VARCHAR2, p2 IN VARCHAR2);
END pkg_TEST_VARIABLES;
/

CREATE OR REPLACE PACKAGE BODY pkg_TEST_VARIABLES AS
  PROCEDURE TEST_PASS_VARIABLES(p1 IN VARCHAR2, p2 IN VARCHAR2) IS
  BEGIN
    -- Procedure logic here
    DBMS_OUTPUT.PUT_LINE('Parameter 1: ' || p1);
    DBMS_OUTPUT.PUT_LINE('Parameter 2: ' || p2);
  END TEST_PASS_VARIABLES;
END pkg_TEST_VARIABLES;
/

Running the script will prompt for input:

ENTER PARAMETER #1: value1
ENTER PARAMETER #2: value2

After providing the values, the procedure will be executed, and you will see the output:

Parameter 1: value1
Parameter 2: value2

Summary:

• SET VERIFY OFF: Clean output by turning off variable verification.
• ACCEPT: Prompts for user input and stores it in variables.
• execute: Executes a stored procedure with the provided parameters.

This script is useful for testing stored procedures interactively or running scripts that require user inputs.

select grants on tables to users or roles

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' ;

run sql from windows cmd prompt CLI

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

Drop all tables in a schema

**********************************************************************
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 of objects you want to be dropped.

You can Alter OBJECT TYPE any of below:

'TABLE','VIEW','SYNONYM','SEQUENCE','PROCEDURE','TRIGGER'