Table Name from Rowid

In this section we will learn about .

How we can get the Table Name from Rowid?

Before going to get the answer of the above question lets have a quick review on Rowid.

ROWID:

• Rowid is a pseudocolumn that returns the address of the row.
• A rowid value uniquely identifies a row in the database.
• The rowid is intended to be immutable (that is, unchangeable) and the user should not try to change it once it is assigned.

In the above image the rowid return by the dual table is :

AAAABzAABAAAAMiAAA .

Six positions for the data object number .  
Three positions for the relative file number.   
Six positions for the block number .   
Three positions for the row number.

Now , Let back track and get the name of given rowid for example .

There are so many ways to get the Table name from Rowid . 

So, from the above discussion we get the conclusion that  rowid return the address of row and rows are stored in table and table is database object .

So if we get the all table name and scan for rowid then we will get the answer.

Let gets start .

Line 3: created cursor for collecting the table name .

Line 8: Started the loop to access the table one by one for searching the rowid.

Line 16: Condition checking for exists the rowid in table or not .

The other way is to use oracle in built function 

• Get the object id from rowid using DBMS_ROWID.rowid_object() function.
• Then collect the object id information from dba_objects view.
  

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Alias name

     A column alias name is an alternative name given to the column in a select statement.It is used to rename a column heading in select statement and it allows the user to understand the output easily.

Syntax:
    Select column_name AS "Alias Name" from table_name ;

   The keyword AS used between the column name and alias name is optional.we can give the blank space instead of AS keyword.Requires double quotation marks if it contains spaces or special characters or if it is case sensitive.

Example :
SELECT last_name as name ,commission_pct as comm  FROM     employees;

SELECT last_name as name ,salary*12 "Annual Salary"  as comm  FROM     employees;















Default column heading :

• Character and date column headings are left aligned.
• Number column heading are right align .
• Default heading display in UPPERCASE .

      I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends:

Merge statement

          In oracle 9i Release 2, Merge statement is introduced to select rows from one or more source for update or insertion into one or more tables .Merge statement have the ability to delete rows,as well as update and insert them So it is also known as "UPSERT"

• Useful in data warehousing .

• Increases performance and ease of use

Syntax:

MERGE INTO table_name table_alias
USING (table|view|sub_query) alias
ON (join condition)
WHEN MATCHED THEN
UPDATE SET
col1 = col_val1,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list)
VALUES (column_values);

Example :

MERGE INTO emp_new c
USING employees e
ON (c.employee_id = e.employee_id)
WHEN MATCHED THEN
UPDATE SET
c.first_name = e.first_name,
c.last_name = e.last_name,
c.department_id = e.department_id
WHEN NOT MATCHED THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,
e.email, e.phone_number, e.hire_date, e.job_id,
e.salary, e.commission_pct, e.manager_id,
e.department_id);

NOTE:
Deletion were always done separately via the DELETE statement.

 I am interested in hearing your feedback, so that I can improve my articles and learning resources for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: