Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Programming Articles
Page 2264 of 2547
How to capture Oracle errors in PL/SQL?
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 MoreHow to generate JSON data and perform Schema Validation in Oracle?
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 MoreHow to use DATETIME functions in Oracle?
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 MoreHow to insert and retrieve dates in Oracle ?
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 MoreHow to UNPIVOT results in Oracle?
Problem:You want to UNPIVOT results in Oracle.SolutionThe UNPIVOT clause is new for Oracle Database 11g and enables you to flip the columns into rows in the output from a query, and, at the same time, allow you to run an aggregation function on the data.Consider a table called customer which has below data stored inside.ExampleSELECT * FROM customers;Output1 tammy.bryant@internalmail Tammy Bryant 2 roy.white@internalmail Roy White 3 gary.jenkins@internalmail Gary Jenkins 4 victor.morris@internalmail Victor Morris 5 beverly.hughes@internalmail Beverly HughesIn the customer table we can see that full name has First name and last ...
Read MoreHow to PIVOT results in Oracle?
Problem:You want to PIVOT results in Oracle.SolutionThe PIVOT clause is new for Oracle Database 11g and enables you to flip the rows into columns in the output from a query, and, at the same time, allow you to run an aggregation function on the data.PIVOT is especially useful to look at overall trends in large amounts of data.We will be using sales data to demonstrate the usage.ExampleSELECT * FROM sales;Output12008 12 1998-02-28 6 37 552 1898.88 12008 18 1998-02-28 6 37 463 1592.72 12008 20 1998-02-28 6 37 2430 8359.2 12008 25 ...
Read MoreHow to monitor temporary tablespace usage in Oracle?
Problem:You want to monitor the usage of the temporary tablespace in Oracle.Solution:We can use the following query to find out the used and free space in a temporary tablespace.We will begin with identifying temporary table space names.ExampleSELECT tablespace_name FROM dba_tablespaces WHERE contents = 'TEMPORARY';OutputTEMPNext up we will use below SQL to identify the used and free space in temporary tablespace.ExampleSELECT * FROM (SELECT a.tablespace_name, SUM(a.bytes/1024/1024) allocated_mb FROM dba_temp_files a WHERE a.tablespace_name = 'TEMP' GROUP BY a.tablespace_name ) x, (SELECT SUM(b.bytes_used/1024/1024) used_mb, SUM(b.bytes_free /1024/1024) free_mb FROM v$temp_space_header b ...
Read MoreHow to identify recent WAIT events in a Oracle database ?
Problem:You want to find out the recent important waits in your database, as well as the users, SQL statements, and objects that are responsible for most of those waits.SolutionOracle provides us with V$ACTIVE_SESSION_HISTORY to get information about the most common wait events, and the SQL statements, database objects, and users responsible for those waits.SQL to find the wait eventsExampleSELECT event, SUM(wait_time + time_waited) total_wait_time FROM v$active_session_history GROUP BY event ORDER BY total_wait_time DESC;To find out the users who experienced the most waits we can use below SQL.ExampleSELECT s.sid, s.username, SUM(a.wait_time + a.time_waited) total_wait_time FROM v$active_session_history a, ...
Read MoreHow to identify blocked and blocking sessions in Oracle ?
Problem:You’d like to identify the blocking and the blocked sessions in your database.SolutionWhen we see an enqueue wait event in an Oracle database, the chances are that there is some thing locking or holding up some sessions from executing their SQL statements. When a session waits on an “enqueue” wait event, that session is waiting for a lock that is held by a different session. We can issue the following command to view information about the blocked and the blocking sessions in ORacle.ExampleSELECT DECODE(request, 0, 'Holder: ', 'Waiter: ') || sid sess, id1, id2, lmode, request, ...
Read MoreHow to use Grouping clause to handle NULLS in Oracle?
Problem:You want to use GROUPING() with ROLLUP, CUBE and SETS functions.SolutionThe GROUPING() function accepts a column and returns 0 or 1. This function returns 1 when the column value is null and returns 0 when the column value is non null. However, GROUPING() is used only in queries that use ROLLUP or CUBE. GROUPING() is very useful when you want to display a value when a null would otherwise be returned.GROUPING() with a Single Column in a ROLLUPSQL to pass a column to ROLLUPThe last row in the below example will result total/sum of fees. This total is represented as ...
Read More