Topic: SQL*Plus and PL/SQL >> pseudo column
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| jayesh |
Posted: Feb 11, 2008 04:09:03 AM |
Total Post: 60
Joined: Jan, 2008
|
| can anybody pls tell me what is pseudocolumn |
|
|
 Murtuja |
| Posted: Feb 11, 2008 09:26:57 AM |  |
|
Total Post: 857
Joined: Jan, 2006
|
Jayesh,
Make habit of finding answer from google.It will help you.
http://www.google.com/search?hl=en&q=pseudo+column+oracle&meta=
|
|
|
|
|
| bhmadhukar |
| Posted: Feb 16, 2008 08:17:14 AM | |
|
Total Post: 14
Joined: Jan, 2008
|
Pseudocolumns are in-generally, they are not columns of a table (object). They are hidden things. But, it returns a value(s), when it is referenced with other columns of a table(s). Four Pseudocolumns are ROWID,ROWNUM,CURRVAL,NEXTVAL.
ex: select rowid,ename from emp;
|
|
|
|
|
| jayesh |
| Posted: Feb 16, 2008 08:37:53 AM | |
|
Total Post: 60
Joined: Jan, 2008
|
thanx the only thing i wanted to know u make me understand it in very simple way thanx madhukar.
|
|
|
|
|
| bhmadhukar |
| Posted: Feb 17, 2008 08:27:22 AM | |
|
Total Post: 14
Joined: Jan, 2008
|
Know About Pseudocolumns
In generally, Pseudocolumns are not columns of a table. But, they have some characterstics. Pseudocolumns will return values when referenced with columns of a table. Pseudocolumns are CURRVAL,NEXTVAL,ROWID and ROWNUM.
Let us know the meaning of each Pseudocolumn;
ROWID: It represents binary address of a row in a table.
CURRVAL: It returns the current value of a referenced sequence.
NEXTVAL: It returns the next value of a referenced sequence.
ROWNUM: It refers the order in which the data was retrieved from the table.
Now, let us discuss about Pseudocolumns & Sequence based on the given below example.
Login-in to any USER, say SCOTT/TIGER,
SYS> conn scott/tiger
connected.
Now, create a table named 'fname' having two columns,
scott>>create table fname (id number(5), name varchar2(25));
table created.
Now, create a sequence,
scott>> create sequence id start with 1 increment by 1 maxvalue 10 minvalue 1 cycle order cache 2;
sequence created.
Now, issue the below query;
scott>select id.currval from dual;
ORACLE immediatly throws an error like below
select id.currval from dual;
*
ERROR at line 1:
ORA-08002:Sequence ID.CURRVAL is not yet defined in this session.
This is because, it is just created and have no CURRVAL value.
Now, give below query;
scott>select id.nextval from dual;
NEXTVAL
----------
1
Now, give below query;
scott>select id.currval from dual;
CURRVAL
----------
1
Based on this Sequence (created above with Sequence name id) and Pseudocolumns, we can insert rows in a table(s).
scott>insert into fname values(id.currval,'bhmk');
1 row created.
scott>select *from fname;
ID NAME
------ ----------------------------
1 bhmk
Again, issue insert query;
scott>insert into fname values(id,nextval,'sachin');
1 row created.
scott>select *from fname;
ID NAME
------ ----------------------------
1 bhmk
2 sachin
Now,
scott>select id.currval from dual;
CURRVAL
----------
2
See the description of the table named fname (table);
scott>desc fname;
NAME NULL? TYPE
--------------- ------ ------------
ID NUMBER(5)
NAME VARCHAR2(25)
Finally, issue below query;
scott>> select rowid,rownum,id, name from fname;
ROWID ROWNUM ID NAME
------------------ ------ ----- --------
AAACAMAABAAAFO5AAA 1 1 bhmk
AAACAMAABAAAFO5AAB 2 2 sachin
this is how Pseudocolumns can be referenced with columns of a table(s).
|
|
|
|
|
| jayesh |
| Posted: Feb 19, 2008 11:29:23 AM | |
|
Total Post: 60
Joined: Jan, 2008
|
thanx now its cleared to me very well
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 335 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
