What is Pseudocolumn in Oracle SQL? Learn with Examples

Earlier, we discussed Duplicate Records in SQL. Today, we will see Pseudocolumns in Oracle SQL. Also, we will understand the 4 different types of Pseudocolumn with their examples. 

So, let us start SQL Pseudocolumn tutorial.

What is Pseudocolumn in Oracle SQL? Learn with Examples

What is Pseudocolumn in Oracle SQL? Learn with Examples

Keeping you updated with latest technology trends, Join DataFlair on Telegram

1. What is a Pseudocolumn in SQL?

A Pseudocolumn which may behave like a table column but actually it is not a part of a table or stored in a table. The values of a Pseudocolumn cannot be inserted, updated or deleted but you can select from Pseudocolumns. A function and Pseudocolumns are same but without the argument.

Have a look at Dynamic SQL 

The examples of Pseudocolumns are –

  • rowid
  • versions_xid
  • versions_operation
  • versions_startscn
  • versions_endscn
  • sysdate
  • systimestamp
  • rownum
  • ora_rowscn
  • object_value
  • level
  • user

We will study the following pseudocolumns-

  • CURRVAL and NEXTVAL
  • LEVEL
  • ROWID
  • ROWNUM

2. Types of Pseudocolumn

i. CURRVAL and NEXTVAL Pseudocolumn in Oracle

This is a sequence with schema objects that can generate values which are unique and sequential. We often use these values as unique and primary keys.

The Pseudocolumns can refer in sequence values as –

CURRVAL – This returns the current value

NEXTVAL- This Pseudocolumn increment the sequence and thus returns the next value

  • To qualify the sequence –

sequence.CURRVAL

sequence.NEXTVAL

  • For referring to the current or next value of a sequence in the schema of another user we must be granted with SELECT or SELECT ANY SEQUENCE system privilege.

schema.sequence.CURRVAL

schema.sequence.NEXTVAL

  • For referring to the value of a sequence on a remote database

schema.sequence.CURRVAL@dblink

schema.sequence.NEXTVAL@dblink

You must read SQL Statements

ii. LEVEL Pseudocolumn in Oracle

This pseudocolumn returns the level pseudocolumn for each student row by the hierarchical query. For example, it will return 1 for a root node and 2 for a child of a root and so on. In this type in an inverted tree, a root row is the highest row whereas a child row can be any nonroot row.

A row with children is known as parent row, while which doesn’t have is considered as a leaf.

Pseudocolumn

Level Pseudocolumn in SQL

iii. ROWID Pseudocolumn in Oracle

This will return rows’ address for each Row in the database the ROWID pseudo name contains 3 information about every row address

Fileno- It means the table number

Datablockno – It means the space assigned by the engine to save the record

Recordno- It maintains a record number for every record

Do you know about SQL Subquery?

  • ADVANTAGES

They are the fastest way to access a single row, and also they can show you how the rows in a table are stored.

They also act as unique identifiers for rows in a table.

  • Example:
SELECT ROWID, ename FROM emp WHERE deptno = 10;

iv. ROWNUM Pseudocolumn in Oracle

Analytical engine dips record of every number of Record inserted by the user in the table and with the help of this SQL clause we can access the data according to the records inserted.

Example:

SELECT * FROM EMP WHERE ROWNUM <= 3;

If in any case an ORDER BY clause follows ROWNUM in the same query, then the rows will be reordered by the ORDER BY clause. And also the results can vary depending on the type the rows are accessed.

SELECT * FROM employees WHERE ROWNUM < 11 ORDER BY last_name;

If ORDER BY clause is embed in a subquery and place the ROWNUM condition in the top-level query, then you can force the ROWNUM condition to be applied after the ordering of the rows. This is sometimes referred to as top-N reporting:

SELECT * FROM
  (SELECT * FROM employees ORDER BY employee_id)
  WHERE ROWNUM < 11;

Recommended Reading – SQL Stored Procedure

So, this was all in Pseudocolumns in SQL. Hope you liked our explanation.

3. Conclusion

Hence, in this Pseudocolumn in SQL tutorial, we studied about the different pseudo columns in SQL. Moreover, we discussed 4 Pseudocolumns and that are CURRVAL and NEXTVAL, LEVEL, ROWID, and ROWNUM with their examples and syntax. 

Still, if you have any query, ask in the comment tab.

You must check –

SQL Database Tuning

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.