Hello Friends ,

Lots of My dear friends and viewers have requested to know about ROWID and its Its details .

This Post I try to give more details about Rowid and How best can we use it to locate the unique Record

Rowid is also called as Pseudo Column .


For each row in the database, the ROWID pseudo ccolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:

•     The data object number of the object

•     The data block in the datafile in which the row resides

•     The position of the row in the data block (first row is 0)

•     The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Values of the ROWID pseudocolumn have the datatype ROWID or UROWID. Please refer to "ROWID Datatype" and "UROWID Datatype" for more information.

Rowid values have several important uses:

•     They are the fastest way to access a single row.

•     They can show you how the rows in a table are stored.

•     They are unique identifiers for rows in a table.

You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

Although you can use the ROWID pseudo column in the SELECT and WHERE clause of a query, these pseudo column values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudo column.

Example This statement selects the address of all rows that contain data for employees in department 20:

SELECT ROWID, last_name 

   FROM XXGHAN

   WHERE department_id = 50;


With Help of ROWID , We can delete the duplicate records as well as an example

DELETE FROM XXGH WHERE ROWID NOT IN (SELECT MAX(ROWID) FROM XXGH GROUP BY EMPNO);



Comments

Post a Comment

Popular posts from this blog

How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM)

Image
Dear Viewers ,   Lets Understand with different case studies How to Customize AP Invoice Approval in Oracle Fusion cloud Application using Business Process Management(BPM) .   Case Study 1 #Configure an approval rule to route a notification to a requestor  It covers the most common business use cases of invoice approval in a Payables Department.   Each business use case provides how to configure the invoice approval rules, using various participants, invoice attributes, and list builders such as:   • Single Approver   • Approval Group   • Supervisor Hierarchy   • Job level Hierarchy   • Position Hierarchy   The information presented in this document is intended to help various users (Payables business users, Approval workflow administrators and Consultants) who are involved in the configuration of invoice approval policy to meet the various business use cases of invoice approvals in a Payables Department.  Note: 1) Refer to the Appendix section for navigation details for configuring in
Read more
Image
Sometime , We need to know specific relationship between some important tables . Lets come to know one such important SQL Query to know the relationship between Transfer Order, Pick Slip Number, and Supply Orchestration SQL Query can be used to show the link between a Transfer Order, Pick Slip Number, and Supply Orchestration Belo is the screenshot for instance    The Required output We need from the report is below  The Query used for above requirement is below  SELECT  a.pick_slip_number ,   b.source_header_number transfer_order_number,   c.supply_order_number ,   c.supply_order_reference_number FROM   INV_PICK_SLIP_NUMBERS a ,   WSH_DELIVERY_DETAILS b ,   DOS_SUPPLY_HEADERS c ,   INV_TRANSFER_ORDER_HEADERS d,   INV_TRANSFER_ORDER_LINES e WHERE 1 =1   AND a.PICK_SLIP_NUMBER = 11022   AND a.pick_slip_batch_id = b.batch_id   AND b.source_header_number = d.header_number   AND d.header_id = e.header_id   AND e.source_header_id = c.header_id Thank you , Please do comment and share here .
Read more
Dear Friends ,  Below Query can be useful to find the Unit cost of Item at costing Module . We can have  FOR Unit cost OF Material under specified Val Unit code AND Cost Book SELECT TO_CHAR ( CICT . total_cost , 'fm99999999d00' ) UNIT_COST FROM CST_ITEM_COSTS_V CICT , CST_TRANSACTIONS CT WHERE 1 = 1 AND CICT . ITEM_NUMBER = :P_Item_Number AND CICT . TRANSACTION_ID = CT . TRANSACTION_ID AND TRUNC ( CICT . PREVIOUS_COST , 6 ) = ( SELECT TRUNC ( UNIT_COST_AVERAGE , 6 ) FROM cst_perpavg_cost WHERE PERPAVG_COST_ID = ( SELECT MIN ( PERPAVG_COST_ID ) FROM cst_perpavg_cost WHERE TRANSACTION_ID = CICT . TRANSACTION_ID ) ) AND CICT . VAL_UNIT_CODE LIKE 'XXXXXX' AND TO_CHAR ( CT . TRANSACTION_DATE , 'DD-MM-YYYY' ) BETWEEN TO_CHAR ( :P_START_DATE , 'DD-MM-YYYY' ) AND TO_CHAR ( :P_END_DATE , 'DD-MM-YYYY' ) And If you want to have the Query to fetch Current Item cost
Read more