OraFAQ Forum: SQL & PL/SQL » Row length of table in Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » Row length of table in Oracle

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Row length of table in Oracle [message #637469]

Mon, 18 May 2015 11:49

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

is there any way to calculate the length of row...for example the emp table has the col empno,ename,sal i want to return length of empno + lenght of ename and the sal....how ?

Report message to a moderator

Re: Row length of table in Oracle [message #637470 is a reply to message #637469]

Mon, 18 May 2015 11:52

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

irfankundi786@yahoo.com wrote on Mon, 18 May 2015 09:49

is there any way to calculate the length of row...for example the emp table has the col empno,ename,sal i want to return length of empno + lenght of ename and the sal....how ?

the specific calculation depends upon the actual datatypes involved.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/

Report message to a moderator

Re: Row length of table in Oracle [message #637471 is a reply to message #637470]

Mon, 18 May 2015 11:54

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

please explain how we can get the length on the datatype..

Report message to a moderator

Re: Row length of table in Oracle [message #637472 is a reply to message #637470]

Mon, 18 May 2015 11:54

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member


  1* select avg_row_len from user_tables where table_name = 'EMP'
SQL> /

AVG_ROW_LEN
-----------
         38

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

[Updated on: Mon, 18 May 2015 11:55]

Report message to a moderator

Re: Row length of table in Oracle [message #637473 is a reply to message #637472]

Mon, 18 May 2015 12:02

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

i want the length of rows like for example first row have the data 123,jahn,3000 lenght of this row is 11, similary each row has its own lenght....

Report message to a moderator

Re: Row length of table in Oracle [message #637474 is a reply to message #637473]

Mon, 18 May 2015 12:08

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

how many bytes does NUMBER(4), VARCHAR2(10), VARCHAR2(9), NUMBER(4), DATE, NUMBER(7,2), NUMBER(7,2), & NUMBER(2) each consume?
then just add up the individual lengths

Report message to a moderator

Re: Row length of table in Oracle [message #637475 is a reply to message #637474]

Mon, 18 May 2015 12:17

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

is there anyother way to calcute like lenght function or using cursor .....one anthoer thing i want to know that this is cursor

declare
--rec emp%rowtype;
cursor c1 is
select * from emp;
rec c1%rowtype;
begin

open c1;
loop
fetch c1 into rec;
dbms_output.put_line(rec.ename);
exit when c1%notfound;
end loop;
end;

in this curosr is there any method that rec.ename is mention but only rec is used to first display the empno,ename ,sal and so on.explicitely col name is not mentioned.

Report message to a moderator

Re: Row length of table in Oracle [message #637476 is a reply to message #637469]

Mon, 18 May 2015 12:31

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You can use VSIZE function:

SQL> select empno, ename, sal, vsize(empno), vsize(ename), vsize(sal) from emp;
     EMPNO ENAME             SAL VSIZE(EMPNO) VSIZE(ENAME) VSIZE(SAL)
---------- ---------- ---------- ------------ ------------ ----------
      7369 SMITH             800            3            5          2
      7499 ALLEN            1600            3            5          2
      7521 WARD             1250            3            4          3
      7566 JONES            2975            3            5          3
      7654 MARTIN           1250            3            6          3
      7698 BLAKE            2850            3            5          3
      7782 CLARK            2450            3            5          3
      7788 SCOTT            3000            3            5          2
      7839 KING             5000            3            4          2
      7844 TURNER           1500            3            6          2
      7876 ADAMS            1100            3            5          2
      7900 JAMES             950            2            5          3
      7902 FORD             3000            3            4          2
      7934 MILLER           1300            3            6          2

Report message to a moderator

Re: Row length of table in Oracle [message #637477 is a reply to message #637476]

Mon, 18 May 2015 12:39

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

Michel.....i want to use some else in way i can use the length function like lenght(empno)+lenght(ename)+lenght(sal) but if i dot'n know about extact col name then what i can solve this....like length(t.*)

Report message to a moderator

Re: Row length of table in Oracle [message #637478 is a reply to message #637477]

Mon, 18 May 2015 12:51

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Query all_tab_columns.
What is your actual purpose?
What about the hidden columns?

Report message to a moderator

Re: Row length of table in Oracle [message #637479 is a reply to message #637478]

Mon, 18 May 2015 13:12

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

Micel..Plz look a this ,,is this posiblle.

is there anyother way to calcute like lenght function or using cursor .....one anthoer thing i want to know that this is cursor

declare
--rec emp%rowtype;
cursor c1 is
select * from emp;
rec c1%rowtype;
begin

open c1;
loop
fetch c1 into rec;
dbms_output.put_line(rec.ename);
exit when c1%notfound;
end loop;
end;

in this curosr is there any method that rec.ename is mention but only rec is used to first display the empno,ename ,sal and so on.explicitely col name is not mentioned.

Report message to a moderator

Re: Row length of table in Oracle [message #637480 is a reply to message #637479]

Mon, 18 May 2015 13:18

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Did you try what I mentioned?
Why don't you answer my questions?
Do you think you will get more help just repeating your question and not having the respect to answer our questions?

Also, read How to use [code] tags and make your code easier to read and format your posts.

And read OraFAQ Forum Guide and provide your Oracle version.

Report message to a moderator

Re: Row length of table in Oracle [message #637481 is a reply to message #637480]

Mon, 18 May 2015 13:21

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

i just want to get the length of row.....thats is asked to me of my boss.....but not all the col listed to get length and then plus.

Report message to a moderator

Re: Row length of table in Oracle [message #637482 is a reply to message #637481]

Mon, 18 May 2015 13:23

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Do you think there is a way but we don't want to mention it?
I gave you a way to do it?

Now answer my question: "Why?".
Let me put it clearly.
You have an issue, you think you find THE way to solve it, you didn't know how to do it so you put your question.
Now, are you sure you have found the proper to solve your issue?
Do you think you're the best man and we can't find a better way to do it?

Report message to a moderator

Re: Row length of table in Oracle [message #637483 is a reply to message #637479]

Mon, 18 May 2015 13:25

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

irfankundi786@yahoo.com wrote on Mon, 18 May 2015 11:12

Micel..Plz look a this ,,is this posiblle.

is there anyother way to calcute like lenght function or using cursor .....one anthoer thing i want to know that this is cursor

declare
--rec emp%rowtype;
cursor c1 is
select * from emp;
rec c1%rowtype;
begin

open c1;
loop
fetch c1 into rec;
dbms_output.put_line(rec.ename);
exit when c1%notfound;
end loop;
end;

in this curosr is there any method that rec.ename is mention but only rec is used to first display the empno,ename ,sal and so on.explicitely col name is not mentioned.

NEVER do in PL/SQL that which can be done in plain SQL.

Report message to a moderator

Re: Row length of table in Oracle [message #637497 is a reply to message #637483]

Tue, 19 May 2015 01:46

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Does a virtual column have a length? Wink

What about an unused one?

[Updated on: Tue, 19 May 2015 01:47]

Report message to a moderator

Re: Row length of table in Oracle [message #637499 is a reply to message #637477]

Tue, 19 May 2015 02:11

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

SQL> select * from emp
  2  /

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1800        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1450        500         30
      7566 JONES      MANAGER         7839 02-APR-81       3175                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1450       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       3050                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2650                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3200                    20
      7839 KING       PRESIDENT            17-NOV-81       5200                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1700                    30
      7876 ADAMS      CLERK           7788 12-JAN-83       1300                    20
      7900 JAMES      CLERK           7698 03-DEC-81       1150                    30

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7902 FORD       ANALYST         7566 03-DEC-81       3200                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1500                    10
      7369 SMITH      CLERK           7902 17-DEC-80       1200                    20

14 rows selected.

SQL> select empno||ename||job||mgr||hiredate||sal||comm||deptno
  2  from emp
  3  /

EMPNO||ENAME||JOB||MGR||HIREDATE||SAL||COMM||DEPTNO
----------------------------------------------------------------
7499ALLENSALESMAN769820-FEB-81180030030
7521WARDSALESMAN769822-FEB-81145050030
7566JONESMANAGER783902-APR-81317520
7654MARTINSALESMAN769828-SEP-811450140030
7698BLAKEMANAGER783901-MAY-81305030
7782CLARKMANAGER783909-JUN-81265010
7788SCOTTANALYST756609-DEC-82320020
7839KINGPRESIDENT17-NOV-81520010
7844TURNERSALESMAN769808-SEP-81170030
7876ADAMSCLERK778812-JAN-83130020
7900JAMESCLERK769803-DEC-81115030

EMPNO||ENAME||JOB||MGR||HIREDATE||SAL||COMM||DEPTNO
----------------------------------------------------------------
7902FORDANALYST756603-DEC-81320020
7934MILLERCLERK778223-JAN-82150010
7369SMITHCLERK790217-DEC-80120020

14 rows selected.

SQL> select length(empno||ename||job||mgr||hiredate||sal||comm||deptno) from emp
  2  /

LENGTH(EMPNO||ENAME||JOB||MGR||HIREDATE||SAL||COMM||DEPTNO)
-----------------------------------------------------------
                                                         39
                                                         38
                                                         35
                                                         41
                                                         35
                                                         35
                                                         35
                                                         32
                                                         37
                                                         33
                                                         33

LENGTH(EMPNO||ENAME||JOB||MGR||HIREDATE||SAL||COMM||DEPTNO)
-----------------------------------------------------------
                                                         34
                                                         34
                                                         33

14 rows selected.

Report message to a moderator

Re: Row length of table in Oracle [message #637501 is a reply to message #637476]

Tue, 19 May 2015 02:13

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

thanks for this reply michel , i came to know about vsize fucntion today.
find it very useful thanks Smile

Report message to a moderator

Re: Row length of table in Oracle [message #637521 is a reply to message #637501]

Tue, 19 May 2015 09:43

irfankundi786@yahoo.com
Messages: 269
Registered: February 2009
Location: pakistan

Senior Member

jgjeetu...your solution is good.what is difference in vsize and length function.

Report message to a moderator

Re: Row length of table in Oracle [message #637522 is a reply to message #637521]

Tue, 19 May 2015 09:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

irfankundi786@yahoo.com wrote on Tue, 19 May 2015 07:43

jgjeetu...your solution is good.what is difference in vsize and length function.

are you incapable or just unwilling to Read The Fine Manual yourself?

Report message to a moderator

Re: Row length of table in Oracle [message #637523 is a reply to message #637521]

Tue, 19 May 2015 10:08

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

irfankundi786@yahoo.com wrote on Tue, 19 May 2015 16:43

jgjeetu...your solution is good.what is difference in vsize and length function.

And what is the difference, in programming, between take the length of concatenation of all columns and summing the vsize of all columns?
How are you able to do one and not the other one?
And why don't you answer to our questions?

Report message to a moderator

Re: Row length of table in Oracle [message #637524 is a reply to message #637499]

Tue, 19 May 2015 10:40

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

jgjeetu wrote on Tue, 19 May 2015 02:11

SQL> select empno||ename||job||mgr||hiredate||sal||comm||deptno
  2  from emp
  3  /

EMPNO||ENAME||JOB||MGR||HIREDATE||SAL||COMM||DEPTNO
----------------------------------------------------------------
7499ALLENSALESMAN769820-FEB-81180030030
7521WARDSALESMAN769822-FEB-81145050030
7566JONESMANAGER783902-APR-81317520
7654MARTINSALESMAN769828-SEP-811450140030
7698BLAKEMANAGER783901-MAY-81305030
7782CLARKMANAGER783909-JUN-81265010
7788SCOTTANALYST756609-DEC-82320020
7839KINGPRESIDENT17-NOV-81520010
7844TURNERSALESMAN769808-SEP-81170030
7876ADAMSCLERK778812-JAN-83130020
7900JAMESCLERK769803-DEC-81115030

EMPNO||ENAME||JOB||MGR||HIREDATE||SAL||COMM||DEPTNO
----------------------------------------------------------------
7902FORDANALYST756603-DEC-81320020
7934MILLERCLERK778223-JAN-82150010
7369SMITHCLERK790217-DEC-80120020

14 rows selected.

Of course the above does NOT reveal the length of the row, but rather the length of the concatenation of the character representation of the data in the rows. Not the same thing at all. The DISPLAYED length of NUMBER and DATE data types is NOT the same as their length as stored in the row.

Report message to a moderator

Re: Row length of table in Oracle [message #637528 is a reply to message #637524]

Tue, 19 May 2015 11:17

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

As we don't know what "length of the row" means for OP and what he wants to do with it as he refuses to tell us, maybe it is what he wants, or maybe he does not even know what he wants.

Report message to a moderator

Re: Row length of table in Oracle [message #637563 is a reply to message #637521]

Wed, 20 May 2015 05:15

jgjeetu
Messages: 373
Registered: July 2013
Location: www.Orafaq.com/Forum

Senior Member

thanks buddy but listen what others say and seniors the most.
now coming to your question.
Length returns the number of characters in a string and Vsize returns number of bytes used to store the string. for ex:-

SQL> select vsize(sysdate) from dual
  2  /

VSIZE(SYSDATE)
--------------
             7

SQL> select length(sysdate) from dual
  2  /

LENGTH(SYSDATE)
---------------
              9

SQL> select sysdate from dual
  2  /

SYSDATE
---------
20-MAY-15

SQL> select vsize(22.7) from dual
  2  /

VSIZE(22.7)
-----------
          3

SQL> select length(22.7) from dual
  2  /

LENGTH(22.7)
------------
           4

SQL> select vsize('ORAFAQ') from dual
  2  /

VSIZE('ORAFAQ')
---------------
              6

SQL> select length('ORAFAQ') from dual
  2  /

LENGTH('ORAFAQ')
----------------
               6

[Updated on: Wed, 20 May 2015 05:17]

Report message to a moderator

Re: Row length of table in Oracle [message #637564 is a reply to message #637563]

Wed, 20 May 2015 05:21

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And these ones:

SQL> select length(systimestamp), vsize(systimestamp) from dual;
LENGTH(SYSTIMESTAMP) VSIZE(SYSTIMESTAMP)
-------------------- -------------------
                  33                  13

SQL> select length(N'ORAFAQ'), vsize(N'ORAFAQ') from dual;
LENGTH(N'ORAFAQ') VSIZE(N'ORAFAQ')
----------------- ----------------
                6               12

Report message to a moderator

Previous Topic:	ERROR-ORA-06533: Subscript beyond count
Next Topic:	Date validation

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Apr 27 00:00:59 CDT 2024