Keep it Simple and Smart
First I would like to share some background of this post. As I have SQL Server background (you can see in my profile here) and when I started learning Oracle. I have so many basic questions regarding oracle environment. Even I was little lit bit confused about few things. But when I got the answer of my question then I thought I should write here on my blog. Oracle and SQL server are very different products. There is no comparison
» Read moreON DELETE CASCADE: When a referenced parent table row is removed the entire child are removed automatically. Example: — Create a Table as mytable1 having the PRIMARY KEY CREATE TABLE MYTABLE1 ( ID INTEGER PRIMARY KEY, EMP_NAME VARCHAR2(10) ); — Create a Table as mytable2 having the FOREIGN KEY CREATE TABLE MYTABLE2 ( ID INTEGER, ADDRESS VARCHAR2(50), FOREIGN KEY (ID) REFERENCES MYTABLE1 (ID) ON DELETE CASCADE ); — Insert one row in mytable1 INSERT INTO MYTABLE1 (ID, EMP_NAME) VALUES (1,'VARINDER');
» Read moreIf you have user that owns objects and you want to drop the user then you have to use the Cascade keyword with your drop statement/command. The Oracle DROP USER CASCADE command drops a user and all owned objects. Without using DROP USER CASCADE you cannot delete the user that owns objects. Example: First you try to drop the user without CASCADE then below error occurred because my_user user own the my_table object (as shown above) But now use the
» Read moreHow to get the Second highest/ largest Value from the column ? Example: See the table values in the snapshot. As per requirement we need to get second highest/ largest salary form the column sal which is 3000 as per column values. Solution: Script: select ename,sal from ( select ename, sal, row_number()over(order by sal desc) rn from emp ) x where rn = 2;
» Read moreWe can create a trigger with multiple actions (i.e. Insert or Update or Delete) here sharing an example of trigger in which we can handle three actions as insert, update and delete. Example: CREATE OR REPLACE TRIGGER EMPLOYEE_AI_AU_AD AFTER INSERT OR UPDATE OR DELETE ON EMPLOYEE FOR EACH ROW DECLARE V_Msg VARCHAR2(30) := 'Row Level Trigger Fired'; BEGIN IF INSERTING THEN dbms_output.put_line(V_Msg || ' After Inserting'); ELSIF UPDATING THEN dbms_output.put_line(V_Msg || ' After Updating'); ELSIF DELETING THEN dbms_output.put_line(V_Msg || '
» Read moreDatabases, tablespaces, and datafiles are closely related, but they have important differences. It is important to know the differences for Newbie that‘s why sharing here. Databases: An Oracle database consists of one or more logical storage units called tablespaces that collectively store all of the database data. Tablespace: Each tablespace in an Oracle database consists of one or more files called datafiles. Datafiles: A database’s data is collectively stored in the datafiles that constitute each tablespace of the database. For
» Read more— CREATE TABLE CREATE TABLE EMPLOYEE ( ID NUMBER NOT NULL, FIRST_NAME VARCHAR2(10 BYTE), LAST_NAME VARCHAR2(10 BYTE) ); — INSERT SOME VALUES INTO TABLES INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (1,'VARINDER','SANDHU'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (2,'DINESH','SHARMA'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (3,'RANJOYT','SINGH'); INSERT INTO EMPLOYEE (ID,FIRST_NAME,LAST_NAME) VALUES (4,'VIKRAM','SINGH'); — CREATE A COPY OF EMPLOYEE TABLE WITH DATA CREATE TABLE EMPLOYEE_COPY AS SELECT * FROM EMPLOYEE; — CHECK THE TABLE SELECT * FROM EMPLOYEE_COPY; — CREATE A COPY OF EMPLOYEE TABLE
» Read moreWithin a trigger body of a row trigger, two correlation names exist for every column of the table being modified: one for the old column value, and one for the new column value. Depending on the type of triggering statement, certain correlation names might not have any meaning. A trigger fired by an INSERT statement has meaningful access to new column values only. Because the row is being created by the INSERT, the old values are null. A trigger fired
» Read moreError: ORA-04091: table name is mutating, trigger/function may not see it Cause: Mutating trigger error occurs when a trigger references the table that owns the trigger. So the result is we receive the above error message. Solution: We cannot refer the table in the trigger that owns the trigger. If you face the similar condition or scenario then don’t use the trigger for that purpose. Use the alternative approach to handle this situation you can refer the Mutating Table Exceptions
» Read moreA cursor is basically a set of rows that we can access one at a time. When we use the cursor, it means we have the select statement that returns the more than one row; we need to access the one row at a time. Working of Cursors Basically in the cursors, we retrieve or fetch the rows using SELECT statement into the cursors then retrieve or fetch the row from the cursor one by one. (i.e. fetch one row
» Read moreA LOOP statement executes a sequence of statements multiple times. PL/SQL provides these loop statements: Numeric FOR_LOOP loops iterate over a specified range of integers. The range is part of an iteration scheme, which is enclosed by the keywords FOR and LOOP. The range is evaluated when the FOR loop is first entered and is never re-evaluated. The loop body is executed once for each integer in the range defined by lower_boun..upper_bound. After each iteration, the loop index is incremented.
» Read moreThere are four types of triggers: Table-level triggers can initiate activity before or after an INSERT, UPDATE, or DELETE event. View-level triggers define to the view. Database-level triggers can be activated at startup and shutdown of a database. Session-level triggers can be used to store specific information.
» Read more