No question at this time
The DBA-Village forum
as RSS feed
Site StatisticsEver registered users | 48726 | Total active users | 1323 | Act. users last 24h | 0 | Act. users last hour | 0 | Registered user hits last week | 35 | Registered user hits last month | 263 |
|
Go up
How to get SELECT columns size with Perl DBI+DBD::Oracle?
Message |
Score |
Author |
Date |
After you prepare and execute a SELECT the state...... |
|
Michel Cadot |
Aug 11, 2015, 10:11 |
Hi Michel,
May be this can help you !
<pre>
P...... |
     |
Joël Péran |
Aug 12, 2015, 09:05 |
Hi,
you can query those tables/views
dba_tab_c...... |
     |
Richard Tesar |
Aug 12, 2015, 09:33 |
Hi Joël,
Thanks for your answer; I know this ...... |
|
Michel Cadot |
Aug 12, 2015, 09:45 |
Thanks Richard but this will not give the answer...... |
     |
Michel Cadot |
Aug 12, 2015, 09:57 |
Subject: |
How to get SELECT columns size with Perl DBI+DBD::Oracle? |
Author: |
Michel Cadot, France |
Date: |
Aug 11, 2015, 10:11, 2783 days ago |
Os info: |
Perl: 5.20.2, DBI: 1.633, DBD::Oracle: 1.74 |
Oracle info: |
Oracle: 10.2-11.2 |
Message: |
After you prepare and execute a SELECT the statement handle has some attributes set for each column: name, type, precision, scale...
I didn't find any attribute giving the fields size or their maximum size.
Is there a way to get this (without first fetching all the rows)?
Michel
cross-ref: http://www.orafaq.com/forum/t/198232/ |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: How to get SELECT columns size with Perl DBI+DBD::Oracle? |
Author: |
Joël Péran, France |
Date: |
Aug 12, 2015, 09:05, 2782 days ago |
Score: |
       |
Message: |
Hi Michel,
May be this can help you !
PRECISION
The PRECISION attribute contains a reference to an array of integer values that represent the defined length or size of the columns in the SQL statement.
There are two general ways in which the precision of a column is calculated. String datatypes, such as CHAR and VARCHAR, return the maximum length of the column. For example, a column defined within a table as:
location VARCHAR2(1000)
would return a precision value of 1000.
Numeric datatypes are treated slightly differently in that the number of significant digits is returned. This may have no direct relationship with the space used to store the number. Oracle, for example, stores numbers with 38 digits of precision but uses a variable length internal format of between 1 and 21 bytes.
For floating-point types such as REAL, FLOAT, and DOUBLE, the maximum ``display size'' can be up to seven characters greater than the precision due to concatenated sign, decimal point, the letter ``E,'' a sign, and two or three exponent digits.
I found that on this site : http://docstore.mik.ua/orelly/linux/dbi/ch06_01.htm
There are plenty of detailed examples in this site.
I hope it has helped you ...
Regards,
Joel |
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: How to get SELECT columns size with Perl DBI+DBD::Oracle? |
Author: |
Richard Tesar, Czech Republic |
Date: |
Aug 12, 2015, 09:33, 2782 days ago |
Score: |
       |
Message: |
Hi,
you can query those tables/views
dba_tab_columns
dba_tab_cols
also you can check this http://docs.oracle.com/cd/B19306_01/server.102/b14237/limits001.htm#i287903
R. |
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: How to get SELECT columns size with Perl DBI+DBD::Oracle? |
Author: |
Michel Cadot, France |
Date: |
Aug 12, 2015, 09:45, 2782 days ago |
Message: |
Hi Joël,
Thanks for your answer; I know this site and this is one of my top 3 ones
about DBI (but DBD:Oracle is sightly different).
Unfortunately, this will not answer my question. As you saw, "precision"
is accurate only for string and raw datatypes. For example, for number ones
(of any kind), if the internal representation is up to 21 bytes this does
not tell me the size it will take to display, theoretically from the Oracle
documentation it contains at most 38 digits but in fact the max display size
is 41 characters:
SQL> set numwidth 50
SQL> select 1/3 from dual;
1/3
--------------------------------------------------
,3333333333333333333333333333333333333333
then you have many other standard types (date, timestamps, rowids...) without
speaking about custom ones. Some sizes are fixed (rowid), some depends on
session nls parameters (date, timestamp)...
Even SQL*Plus is not able to correctly compute all these cases:
SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
12/08/2015 09:38:51.937 +02:00
It takes 75 characters for the width of this column (although it correctly
handles dates depending on the nls settings!).
I'd like DBD::Oracle will do the job for me and return the max size depending
on the type but, for what I see, I have to do the job myself.
Regards
Michel
|
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
Subject: |
Re: How to get SELECT columns size with Perl DBI+DBD::Oracle? |
Author: |
Michel Cadot, France |
Date: |
Aug 12, 2015, 09:57, 2782 days ago |
Score: |
       |
Message: |
Thanks Richard but this will not give the answer for the general case where SELECT
does not return simple columns but expressions based on these ones.
Even with single column field it does not completely work. For instance, for DATE,
data_length is always 7 but display width depends on the NLS settings so this can
be computed only in DBD layer (which knows these settings).
Regards
Michel
|
Your rating?: |
This reply is Good Excellent |
Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
|