Posts Tagged ‘database queries’

Oracle Skip Locked

Oracle Skip Locked
Oracle 11g introduced SKIP LOCKED clause to query the records from the table which are not locked in any other active session of the database.This looks quite similar to exclusive mode of locking.The select for update statement has always been problematic for large updates because it the default is to wait for locks and using select for ...

Pagination in Oracle using ROWNUM and Limiting Result Set

Pagination in Oracle using ROWNUM and Limiting Result Set
ROWNUM is a magic column in Oracle Database that gets many people into trouble. When you learn what it is and how it works, however, it can be very useful. I use it for two main things:To perform top- N processing. This is similar to using the LIMIT clause, available in some other databases. To paginate through a ...

How To Convert Number into Words using Oracle SQL Query

How To Convert Number into Words using Oracle SQL Query
How can you convert a number into words using Oracle Sql Query? What I mean by Number to Word is:Here's a classy query which will convert number into words.Please see the query below:select to_char(to_date(:number,'j'),'jsp') from dual;If I pass 234 in number, then the output will : two hundred thirty-fourSELECT TO_CHAR (TO_DATE (234, 'j'), 'jsp') ...

Batch Insert In Java – JDBC

Let's see how we can perform batch insert in Java using JDBC APIs. Although you might already knew this, I will try to explain the basic to a bit complex scenarios.In this note, we will see how we can use JDBC APIs like Statement and PreparedStatement to insert data in any database in batches. Also we ...

Generating Random Data in Oracle

Most of the times, production data is not available in development environments. Here, I would like to share a single sql command which can generate random data. But before that, let's address another issue faced by a lot of new oracle users. We need to generate a sequence of numbers using a sql statement. This will generate a number sequence. ...

Deleting Duplicate Rows in Oracle

Deleting Duplicate Rows in Oracle
Deleting duplicate rows from tables is one of the common task oracle developers come across. The data might get duplicated because of missing primary/unique key on the table or batch file getting loaded multiple times. Here I have tried to summarize different ways of deleting this duplicated data. Please note that this is not an extensive list ...

Fetch Random rows from Database (MySQL, Oracle, MS SQL, PostgreSQL)

Fetch Random rows from Database (MySQL, Oracle, MS SQL, PostgreSQL)
Ever thought why would one need to fetch random rows from a database table? You may want to display some random information (like articles/pages/links) to your user. I had a requirement wherein I had to fetch 10 random rows from MySQL database and display it on screen. I used following simple query for retrieving random records ...