Tuesday, 10 April 2018

WHERE CURRENT OF clause in Oracle PL/SQL

What is WHERE CURRENT OF clause?
The WHERE CURRENT OF clause is used in conjunction with the FOR UPDATE clause to refer to the current row in an explicit cursor. The WHERE CURRENT OF clause is used in the UPDATE or DELETE statement, whereas the FOR UPDATE clause is specified in the cursor declaration.

We can use the combination for updating and deleting the current row from the corresponding database table. This enables us to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the row ID. We must include the FOR UPDATE clause in the cursor query so that the rows are locked on OPEN.

In the syntax:

cursor Is the name of a declared cursor (The cursor must have been declared with the FOR UPDATE clause.)

Requirement: Let us consider the EMP table as shown below.
Now the requirement is to increment the EMP_SALARY of each row by 1000. Thus the EMP_SALARY after incrementing it by 1000 is as below.
Solution: For the solution of the above requirement follow the steps as shown below.

Step 1: Create the EMP table.

CREATE TABLE emp (
    emp_name     VARCHAR2(20),
    emp_salary   NUMBER(10)
);

Step 2: Insert data into the EMP table using the below INSERT statements.

INSERT INTO emp VALUES('Susanto',3000);
INSERT INTO emp VALUES('Paul',4000);
INSERT INTO emp VALUES('Susanto',3000);
INSERT INTO emp VALUES('Paul',6000);
INSERT INTO emp VALUES('Anjali',4000);

After executing the above statement COMMIT the changes.

Thus the data in the EMP table is shown below:
Step 3: Let us create a PROCEDURE as shown below to increment the records by 1000.

DECLARE
    CURSOR c1 IS SELECT * FROM emp;
BEGIN
    FOR r IN c1 LOOP
        UPDATE emp SET emp_salary = emp_salary + 1000 WHERE emp_name = r.emp_name;
    END LOOP;
    COMMIT;
END;
/

Now when we execute the above PL/SQL  procedure, the records get incremented as shown below.
From the above analysis, we can see that the output for EMP 'Susanto' and 'Paul' is wrong as the EMP_SALARY of EMP 'Susanto' and 'Paul' got incremented by 2000 instead of 1000.
To avoid such issue we can use the WHERE CURRENT OF clause as shown below:

DECLARE
    CURSOR c1 IS SELECT * FROM emp FOR UPDATE OF emp_salary NOWAIT;
BEGIN
    FOR r IN c1 LOOP
        UPDATE emp SET emp_salary = emp_salary + 1000 WHERE CURRENT OF c1;
    END LOOP;
    COMMIT;
END;
/

Thus the output is shown below:
To use CURRENT OF clause our SELECT statement should have FOR UPDATE OF <update column_name> nowait;

The main advantage of using CURRENT OF clause is to update a table that does not contain the primary key or unique key.

Hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.

Saturday, 24 March 2018

ORA-01950: no privileges on tablespace 'USERS'

Issue: ORA-01950: no privileges on tablespace 'USERS'

Scenario: I have created a new Schema GSMUSER. In that, I have created a table location_details using the below statement.

CREATE TABLE location_details (
    id             NUMBER(10),
    country        VARCHAR2(20),
    name           VARCHAR2(20),
    abbreviation   VARCHAR2(20),
    area           VARCHAR2(20),
    largest_city   VARCHAR2(20),
    capital        VARCHAR2(20),
    PRIMARY KEY ( id )
);

Now when I tried insert records into location_details I am getting the below error.

Error starting at line : 1 in command -
INSERT INTO location_details (
    id,
    country,
    name,
    abbreviation,
    area,
    largest_city,
    capital
) VALUES (
    '1',
    'India',
    'Meghalaya',
    'IN',
    '12345789SFT',
    'Shillong',
    'Shillong'
)
Error report -
ORA-01950: no privileges on tablespace 'USERS'

Cause of the issue: This is because we cannot insert data because we have a quota of 0 on the tablespace

Solution: Thus, we need to login to the ADMIN account and execute the below command:

First, check the TABLESPACE_NAME for USERS using the below query:
SELECT tablespace_name,status, contents FROM dba_tablespaces;
Now as a fix to ORA-01950: no privileges on tablespace 'USERS', execute the below statement.
ALTER USER gsmuser QUOTA UNLIMITED ON users;
Now try to insert the record in location_details table, and we can see that it can inserted successfully.
Hence the solution to the requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.

Friday, 23 March 2018

ORA-01031: insufficient privileges

Issue: ORA-01031: insufficient privileges

Scenario: Whenever I tried to create the below package  as shown below:
CREATE OR REPLACE PACKAGE test_services AS
    TYPE ref_cursor IS REF CURSOR;
    PROCEDURE get_service_data (
        textval    IN VARCHAR2,
        mycursor   OUT ref_cursor
    );

END test_services;

I was getting the below error:
Error starting at line : 1 in command -
CREATE OR REPLACE PACKAGE test_services AS
    TYPE ref_cursor IS REF CURSOR;
    PROCEDURE get_service_data (
        textval    IN VARCHAR2,
        mycursor   OUT ref_cursor
    );

END test_services;

Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

Cause of the Issue: There is no GRANT privilege given to the USER to create a package.

Solution: Thus we need to login to the ADMIN account and GRANT package creation privilege using the below command

GRANT CREATE PROCEDURE TO gsmuser;
Now when we try to create the package it gets created successfully as shown below:
Hence the solution to the requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.

Friday, 16 March 2018

ORA-01157: cannot identify/lock data file

Issue: ORA-01157: cannot identify/lock data file
Cause of the above error: The datafile may have been renamed at the operating system level, moved to a different directory or disk drive either intentionally or unintentionally. Or  the datafile does not exist or is unusable by Oracle. The datafile has been physically removed or damaged to an extent that Oracle cannot recognize it anymore. 

Solution: For solution of the above error follow the steps as shown below:
  • If the database is down, mount it. 
    STARTUP MOUNT; 
  • Offline drop the datafile. 
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV01.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV02.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV03.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV04.DF' OFFLINE DROP;
  • If the database is at mount, open it. 
    ALTER DATABASE OPEN; 
  • Drop the user tablespace. 
    DROP TABLESPACE HR_DEV INCLUDING CONTENTS; 

Thus, the complete scripts executed in DOS command prompt is shown below:
Now we can connect to the database again and hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.

ORA-01033: ORACLE initialization or shutdown in progress

Issue: ORA-01033: ORACLE initialization or shutdown in progress.

Steps to replicate the issue: I have created a tablespace and then deleted the folder where the tablespace was created. After deletion of the folder, I tried to open the SCHEMA, but it started giving the "ORA-01033: ORACLE initialization or shutdown in progress" error as shown below.
Solution: For the solution of  the above error follow the steps as shown below:
  • Open DOS command prompt
  • sqlplus /nolog
  • connect sys/manager as sysdba
  • shutdown immediate
  • startup
Thus, we will get the below error message:

ORA-01157: cannot identify/lock data file 11 - see DBWR trace file
ORA-01110: data file 11: 'C:\DATABASE\HR_DEV\HRDEV01.DF'
The above error is the real cause of ORA-01033. Once we solve the above error, then ORA-01033 will also get solved. To solve ORA-01157 follow the steps as shown below:
  • If the database is down, mount it. 
        STARTUP MOUNT; 
  • Offline drop the datafile. 
        ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV01.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV02.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV03.DF' OFFLINE DROP;
    ALTER DATABASE DATAFILE 'C:\DATABASE\HR_DEV\HRDEV04.DF' OFFLINE DROP;
  • If the database is at mount, open it. 
         ALTER DATABASE OPEN; 
  • Drop the user tablespace. 
         DROP TABLESPACE HR_DEV INCLUDING CONTENTS; 

Thus, the complete scripts executed in DOS command prompt is shown below:
Now we can connect to the database again and hence the solution to our requirement.

If you like the post, please comment, share the post and do like me on Facebook

Thanks & Regards,
Susanto Paul.

Wednesday, 14 March 2018

Get the version of the installed Oracle Database

To get the version of the installed Oracle Database execute "SELECT * FROM v$version WHERE banner LIKE 'Oracle%';" as shown below:
Thanks & Regards,
Susanto Paul

Grant CREATE Table privileges to USER in Oracle - ORA-01031: insufficient privileges

When I tried to create the below table, I was getting an exception ORA-01031: insufficient privileges.

Table Name:
CREATE TABLE student (
    student_id      NUMBER PRIMARY KEY,
    student_name    VARCHAR2(30) NOT NULL,
    date_of_birth   DATE NOT NULL,
    address         VARCHAR2(200)

);

Exception:
Error starting at line : 1 in command -
CREATE TABLE student (
    student_id      NUMBER PRIMARY KEY,
    student_name    VARCHAR2(30) NOT NULL,
    date_of_birth   DATE NOT NULL,
    address         VARCHAR2(200)
)
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security

           administrator to grant you the necessary privileges

Solution:
For the solution of the above Exception we need to Login to the Admin account, and Grant permission to create a table using GRANT CREATE ANY TABLE TO gsmuser; as shown below:
Thus after granting the privileges, we were able to create table as shown below:
Hence the solution to the requirement.

If you like the post, please comment, share the post and do like me on Facebook.

Thanks & Regards,
Susanto Paul