Problem:You want to monitor currently executing SQL statistics in Oracle.SolutionIf your database is Oracle Database 11g, you can use the following query to select from the “V$SQL_MONITOR” to monitor the near real time resource consumption of SQL queries.The statistics in “V$SQL_MONITOR” are updated every second. This helps us to view the resource consumption as it updates. These statistics are gathered by default when a SQL statement runs in parallel or consumes more than 5 seconds of CPU or I/O time.The “V$SQL_MONITOR" view includes a subset of statistics contained in the "V$SQL”, “V$SQLAREA", and "V$SQLSTATS” views.The “V$SQL_MONITOR" view displays real-time statistics ... Read More

Problem:You want to limit the amount of resources a user can consume in your database.SolutionTo limit the resources, we can follow below steps.We can use below SQL statement to view the current setting of RESOURCE_LIMIT in our database.select name, value from v$parameter where name='resource_limit';Create a profile to limit the resources and assign it to a user. It won’t limit the CPU utilization though.ExampleCREATE PROFILE test_profile LIMIT    SESSIONS_PER_USER          2    CPU_PER_SESSION            UNLIMITED    CPU_PER_CALL               300000    CONNECT_TIME           ... Read More

Problem:You want to use Primary Key Constraints and Foreign Key Constraints to enforce database integrity in Oracle.SolutionAny DML statement (an INSERT, UPDATE, or DELETE) executed on tables defined with integrity, helps to ensures that the rows in the tables maintain their integrity.Let’s look at some examples that show the enforcement of a primary key constraint. The customers table’s primary key is the customer_id column, which means that every value stored in the customer_id column must be unique. If you try to insert a row with a duplicate value for a primary key, the database returns the error ORA-00001, as in ... Read More

Problem:You want to store the results of a SQL temporarily.SolutionWe can use the CREATE GLOBAL TEMPORARY TABLE statement to create a table that stores data temporarily for a session. Further, you can specify whether to retain temporary table data for a session or until a transaction commits. We can further Use ON COMMIT PRESERVE ROWS clause to specify the data be deleted at the end of the user’s session.ExampleCREATE GLOBAL TEMPORARY TABLE tmp_players ON COMMIT PRESERVE ROWS AS SELECT * FROM players WHERE coach_id IS NOT NULL;Global temporary tables store session private data that exists only for the duration of ... Read More

Problem:You want to perform case-insensitive search in Oracle.SolutionOne way to deal with case issues is to use the built in UPPER and LOWER functions. These functions let you force case conversion on a string for a single operationExampleDECLARE    full_name1  VARCHAR2(30) := 'roger federer';    full_name2   VARCHAR2(30) := 'ROGER FEDERER'; BEGIN    IF LOWER(full_name1) = LOWER(full_name2) THEN        DBMS_OUTPUT.PUT_LINE( full_name1 || ' and ' || full_name2 || ' are the same.');    END IF; END;In the above example the full_name1 and full_name2 are first converted into LOWER CASE then compared with each other resulting the outputroger federer ... Read More

Problem:You want to define a function in Oracle using WITH clause.SolutionStarting with Oracle Database 12.1, you can define functions as well as procedures within the same SQL statement in which the SELECT statement appears. This allows the context switch between the PL/SQL and SQL engines by allowing both steps to take place in the SQL engine and, in turn, provides for a performance gain.The function or procedure needs to be defined using the WITH clause. Remember, In previous versions of the Oracle platform, only subqueries could be defined in the WITH clause.ExampleWITH FUNCTION func_amount(p_emailid IN VARCHAR2) RETURN NUMBER IS l_amt ... Read More

Problem:You want to capture Oracle errors in PL/SQL.SolutionAll Oracle errors can be trapped with the help of the OTHERS exception handler. Let us take an example. We will be using students table to demonstrate the usage.Let us first look at the table structure.ExampleDESC students;OutputName          Null     Type         ------------- -------- ------------ STUDENT_ID             NUMBER(6)   FIRST_NAME             VARCHAR2(20) LAST_NAME     NOT NULL VARCHAR2(25) EMAIL         NOT NULL VARCHAR2(40) PHONE_NUMBER           VARCHAR2(20) JOIN_DATE   ... Read More

Problem:You want to generate JSON data and perform Schema Validation in Oracle.SolutionFunctions like JSON_OBJECT, JSON_ARRAYAGG, JSON_QUERY can be used to generate a complex json data using mutiple columns and tables.JSON_OBJECT:- Its an SQL/JSON function. JSON_OBJECT takes as its input one or more property key-value pairs. It returns a JSON object that contains an object member for each of those key-value pairs.JSON_ARRAYAGG:-The SQL/JSON function JSON_ARRAYAGG is an aggregate function. It takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values.JSON_QUERY:-JSON_QUERY finds one or more ... Read More

Problem:You want to use datetime functions to extract or process datetimes.SolutionADD_MONTHS()ADD_MONTHS(x, y) returns the result of adding y months to x. If y is negative, then y months are subtracted from xExample-- add 12 months SELECT ADD_MONTHS('01-JAN-2020', 12) FROM dual;Output-- 01/JAN/21Example-- subtract 12 months SELECT ADD_MONTHS('01-JAN-2020', -12) FROM dual;Output-- 01/JAN/21We can provide a time and date to the ADD_MONTHS() function.ExampleSELECT ADD_MONTHS(TO_DATE('01-JAN-2020 01:01:01', 'DD-MON-YYYY HH24:MI:SS'), 2) FROM dual;Output-- 01/MAR/20LAST_DAY()LAST_DAY(x) returns the date of the last day of the month part of x.ExampleSELECT LAST_DAY('01-JAN-2020') FROM dual;Output-- 31/JAN/20MONTHS_BETWEEN()MONTHS_BETWEEN(x, y) returns the number of months between x and y. If x occurs before y ... Read More

Problem:You want to insert dates into a table and retrieve them.SolutionOracle, by default the uses the format DD-MON-YYYY to represent a date, whereDD represents a two-digit day.MON represents first three letters of the month, e.g., FEB.YYYY is a four-digit year, e.g., 1968Let us take a look at an example of adding a row to the customers table, which contains a DATE column named dob.The following INSERT adds a row to the customers table, setting the dob column to 05-FEB-1968:ExampleINSERT INTO customers ( customer_id, first_name, last_name, dob, phone) VALUES (7, 'James', 'Bond', '31-DEC-2000', '007-007-0007');You can also use the DATE keyword to ... Read More