No question at this time
DBA Top 10
1 M. Cadot 7400
2 B. Vroman 1700
3 P. Wisse 1300
4 P. Tsongayinwe 1200
5 J. Péran 800
6 J. Schnackenberg 400
7 J. Alcroft 200
7 D. Walgude 200
7 B. M 200
10 A. Hudspith 100
10 L. Ywema 100
10 D. Johnson 100
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48726
Total active users1323
Act. users last 24h0
Act. users last hour0
Registered user hits last week35
Registered user hits last month263
Go up

How to get SELECT columns size with Perl DBI+DBD::Oracle?
Next thread: Lag in GoldenGate Process
Prev thread: finding checkpoint# in controlfile - vs autobackup checkpoint_change#

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...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Joël Péran Aug 12, 2015, 09:05
Hi, you can query those tables/views dba_tab_c...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts 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...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Michel Cadot Aug 12, 2015, 09:57

Follow up by mail Click here


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:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
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:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
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:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
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