I'd like to know difference between
SELECT * FROM emp WHERE rownum=1
inconsistent datatypes: expected ROWID got NUMBER
SELECT rownum r1, rowid r2 FROM emp
Both, rownum and rowed are pseudo columns.
For each row in the database, the ROWID pseudocolumn returns the address of the row.
An example query would be:
SELECT ROWID, last_name FROM employees WHERE department_id = 20;
More info on rowid here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm
For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.
You can limit the amount of results with rownum like this:
SELECT * FROM employees WHERE ROWNUM < 10;
More info on rownum here: https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm
The actual difference between
rownum is, that rowid is a permanent unique identifier for that row. However, the rownum is temporary. If you change your query, the rownum number will refer to another row, the rowed won't.
So the ROWNUM is a consecutive number which applicable for a specific SQL statement only. In contrary the ROWID, which is a unique ID for a row.