Keep it Simple and Smart
How 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 moreA trigger is a procedure that is run automatically by the database when a specified SQL DML INSERT, UPDATE, or DELETE statement is run against a table or view. Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger.
» Read moreSYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifiers consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a non repeating value (sequence of bytes) for that process or thread. Example
» Read moreNVL lets you replace null with a string in the results of a query. Syntax NVL (string1, replace_with) If string1 is null, then NVL returns replace_with. If string1 is not null, then NVL returns string1. Note: The arguments string1and replace_with can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they are cannot be converted implicitly, the database returns an error.
» Read more