| ROWID [message #10248] |
Sat, 10 January 2004 22:32  |
clap
Messages: 3
Registered: January 2004
|
Junior Member |
|
|
Hi All,
I created the following table.
SQL> create table aa(a ROWID, name VARCHAR2(11));
Table created.
SQL> insert into aa(name) values('hi');
1 row created.
SQL> select * from aa;
A NAME
------------------ -----------
hi
Now when I try to insert some value into the ROWID variable, I face the following error messages :
SQL> insert into aa(a,name) values(weo,' ewoi');
insert into aa(a,name) values(weo,' ewoi')
*
ERROR at line 1:
ORA-00984: column not allowed here
SQL> ed
Wrote file afiedt.buf
1* insert into aa(a,name) values('weo',' ewoi')
SQL> /
insert into aa(a,name) values('weo',' ewoi')
*
ERROR at line 1:
ORA-01410: invalid ROWID
SQL> ed
Wrote file afiedt.buf
1* insert into aa(a,name) values(323,' ewoi')
SQL> /
insert into aa(a,name) values(323,' ewoi')
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected ROWID got NUMBER
SQL>
Please help me understand why we are not able to enter any value into the ROWID variable.
|
|
|
|
| Re: ROWID [message #10250 is a reply to message #10248] |
Sun, 11 January 2004 01:17  |
resy
Messages: 86
Registered: December 2003
|
Member |
|
|
You cant assign values to a ROWID field like that.
Check the following code.
SQL> INSERT INTO AA(A,NAME) VALUES((SELECT ROWID FROM AA),'NAME2');
1 row created.
SQL> SELECT * FROM AA;
A NAME
------------------ -----------
hi
AAAQ7AAABAAAMWKAAC NAME2
Cheers
|
|
|
|
| Re: ROWID [message #10251 is a reply to message #10250] |
Sun, 11 January 2004 01:50 |
clap
Messages: 3
Registered: January 2004
|
Junior Member |
|
|
Hi,
Thanks for the reply.
But after inserting two entries with that inner query
SELECT ROWID FROM AAA, while entering the 3rd insert statement i get an error message.
please see the following :
SQL> create table aaa (rid ROWID, name VARCHAR2(11));
Table created.
SQL> INSERT INTO aaa values ((SELECT ROWID FROM AA),'NAME2')
2 .
SQL> ed
Wrote file afiedt.buf
1* INSERT INTO aaa values ((SELECT ROWID FROM aaa),'NAME2')
SQL> /
1 row created.
SQL> ed
Wrote file afiedt.buf
1* INSERT INTO aaa values ((SELECT ROWID FROM aaa),'NAME3')
SQL> /
1 row created.
SQL> SELECT ROWID from aaa;
ROWID
------------------
AABWQDAAdAAANsBAAA
AABWQDAAdAAANsBAAB
SQL> ed
Wrote file afiedt.buf
1* INSERT INTO aaa values ((SELECT ROWID FROM aaa),'NAME4')
SQL> /
INSERT INTO aaa values ((SELECT ROWID FROM aaa),'NAME4')
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
SQL>
Hoping for your response.
|
|
|
|
| Re: ROWID [message #10253 is a reply to message #10251] |
Sun, 11 January 2004 05:51 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your sub-query (SELECT ROWID FROM aaa) is selecting every existing row from the aaa table, so once you have inserted more than one row, it is selecting more than one row. Then you are attempting to insert these multiple rows into the one row that you are entering. That is why you get the error message. It expects any sub-query used in a single-row insert to be a single-row sub-query, so it is telling you that this expected single-row sub-query is returning more than one row.
A ROWID is automatically assigned to every row by Oracle. This value can be retrieved using the ROWID pseudo-column, in addition to being usable as a column datatype. If you want to populate your rid column with the ROWID that is automatically assigned by Oracle, you can do so with an update statement, as demonstrated in the example below.
Since you can always retrieve the ROWID using the ROWID pseudo-column, why do you want to create a column in the table with that value?
scott@ORA92> -- create table:
scott@ORA92> CREATE TABLE aaa (rid ROWID, name VARCHAR2 (11))
2 /
Table created.
scott@ORA92> -- insert three rows and commit them
scott@ORA92> -- only providing values for name and leaving rid null
scott@ORA92> -- (Oracle automatically assigns a ROWID):
scott@ORA92> INSERT INTO aaa (name) VALUES ('NAME1')
2 /
1 row created.
scott@ORA92> INSERT INTO aaa (name) VALUES ('NAME2')
2 /
1 row created.
scott@ORA92> INSERT INTO aaa (name) VALUES ('NAME3')
2 /
1 row created.
scott@ORA92> COMMIT
2 /
Commit complete.
scott@ORA92> -- view the name values you entered
scott@ORA92> -- and ROWID's that Oracle automatically assigned:
scott@ORA92> SELECT ROWID, rid, name FROM aaa
2 /
ROWID RID NAME
------------------ ------------------ -----------
AAAILeAABAAAMY6AAA NAME1
AAAILeAABAAAMY6AAB NAME2
AAAILeAABAAAMY6AAC NAME3
scott@ORA92> -- update the rid column with the ROWID values
scott@ORA92> -- automatically assigned by Oracle and commit:
scott@ORA92> UPDATE aaa
2 SET rid = ROWID
3 /
3 rows updated.
scott@ORA92> COMMIT
2 /
Commit complete.
scott@ORA92> -- view all values, observing that the rid
scott@ORA92> -- values are now the same as the ROWID values:
scott@ORA92> SELECT ROWID, rid, name FROM aaa
2 /
ROWID RID NAME
------------------ ------------------ -----------
AAAILeAABAAAMY6AAA AAAILeAABAAAMY6AAA NAME1
AAAILeAABAAAMY6AAB AAAILeAABAAAMY6AAB NAME2
AAAILeAABAAAMY6AAC AAAILeAABAAAMY6AAC NAME3
|
|
|
|
| Re: ROWID [message #10254 is a reply to message #10253] |
Sun, 11 January 2004 18:39 |
clap
Messages: 3
Registered: January 2004
|
Junior Member |
|
|
hi
Thanks for your response.
If ROWID as a peudo-column is always present in any record in a table, then why should there be a datatype ROWID?
and after checking out the values of ROWID, which involve some 18 characters. ROWID is said to posses BINARY VALUES, but
SQL> select ROWID from dual;
ROWID
------------------
AAAADeAADAAAAZSAAA
this value of ROWID doesnt look like binary value.
Please help me understand
i. why we have ROWID datatype when ROWID is something we have for any record without our declaring it?
ii. why the value "AAAADeAADAAAAZSAAA" is considered binary when they dont look-like one?
Thanks in anticipation
|
|
|
|
| Re: ROWID [message #10259 is a reply to message #10254] |
Sun, 11 January 2004 20:51 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Here is a link to a section of Oracle on-line documentation, that explains it better than I could. Just click on the link. If you don't already have an OTN (Oracle Technology Network) account, it may require you to register, but it is free.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
