I have a table defined as follows:
SQL> desc sb1
Name Null? Type
----------------------------- -------- --------------------
C1 VARCHAR2(30 CHAR)
C2 VARCHAR2(30)
Note the difference is the character semantics, c1 is CHAR, c2 is BYTE.
I am trying to distinguish between them in my perl code which looks like
this:
my $name = 'sb1';
$dbh->{'ora_verbose'} = 6;
my $sth = $dbh->prepare( 'select * from ' . $tname . ' where 1=0' );
my $p = $sth->{PRECISION};
$dbh->{'ora_verbose'} = 0;
log_it(Data::Dumper->Dump([$p], ['$p']));
Here's the output:
OCIHandleAlloc(2548710,25eb278,OCI_HTYPE_STMT,0,0)=SUCCESS
OCIStmtPrepare(25e94b0,256eda8,'select * from SB1 where 1=0',27,1,0)=SUCCESS
OCIAttrGet(25e94b0,OCI_HTYPE_STMT,25eb28c,0,OCI_ATTR_STMT_TYPE,256eda8)=SUCCESS
dbd_st_prepare'd sql SELECT ( auto_lob1, check_sql1)
dbd_describe SELECT (EXPLICIT, lb 80)...
OCIStmtExecute(25763c0,25e94b0,256eda8,0,0,0,0,mode=DESCRIBE_ONLY,16)=SUCCESS
OCIAttrGet(25e94b0,OCI_HTYPE_STMT,7ffcea08f4fc,0,OCI_ATTR_PARAM_COUNT,256eda8)=SUCCESS
OCIParamGet(25e94b0,4,256eda8,25eca10,1,OCI_HTYPE_STMT)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca40,0,OCI_ATTR_DATA_TYPE,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca42,0,OCI_ATTR_DATA_SIZE,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca58,0,OCI_ATTR_CHAR_USED,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca5a,0,OCI_ATTR_CHAR_SIZE,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca5c,0,OCI_ATTR_CHARSET_ID,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca5e,0,OCI_ATTR_CHARSET_FORM,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca44,0,OCI_ATTR_PRECISION,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca46,0,OCI_ATTR_SCALE,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca47,0,OCI_ATTR_IS_NULL,256eda8)=SUCCESS
OCIAttrGet(25ec8f8,OCI_DTYPE_PARAM,25eca48,7ffcea08f4f8,OCI_ATTR_NAME,256eda8)=SUCCESS
Describe col #1 type=1(VARCHAR)
Described col 1: dbtype 1(VARCHAR), scale 0, prec 120, nullok 1, name C1
: *dbsize 120, char_used 1, char_size 30*, csid 873, csform
1(SQLCS_IMPLICIT), disize 120
fbh 1: 'C1' NULLable, otype 1-> 5, dbsize 120/121, p120.s0
OCIParamGet(25e94b0,4,256eda8,25ecab8,2,OCI_HTYPE_STMT)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecae8,0,OCI_ATTR_DATA_TYPE,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecaea,0,OCI_ATTR_DATA_SIZE,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecb00,0,OCI_ATTR_CHAR_USED,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecb02,0,OCI_ATTR_CHAR_SIZE,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecb04,0,OCI_ATTR_CHARSET_ID,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecb06,0,OCI_ATTR_CHARSET_FORM,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecaec,0,OCI_ATTR_PRECISION,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecaee,0,OCI_ATTR_SCALE,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecaef,0,OCI_ATTR_IS_NULL,256eda8)=SUCCESS
OCIAttrGet(25ec8a8,OCI_DTYPE_PARAM,25ecaf0,7ffcea08f4f8,OCI_ATTR_NAME,256eda8)=SUCCESS
Describe col #2 type=1(VARCHAR)
Described col 2: dbtype 1(VARCHAR), scale 0, prec 30, nullok 1, name C2
: *dbsize 30, char_used 0, char_size 30*, csid 873, csform
1(SQLCS_IMPLICIT), disize 30
fbh 2: 'C2' NULLable, otype 1-> 5, dbsize 30/31, p30.s0
OCIAttrSet(25e94b0,OCI_HTYPE_STMT,
7ffcea08f4f8,4,Attr=OCI_ATTR_PREFETCH_MEMORY,256eda8)=SUCCESS
OCIAttrSet(25e94b0,OCI_HTYPE_STMT,
7ffcea08f4f4,4,Attr=OCI_ATTR_PREFETCH_ROWS,256eda8)=SUCCESS
cache settings DB Handle RowCacheSize=0,Statement Handle RowCacheSize=0,
OCI_ATTR_PREFETCH_ROWS=156, OCI_ATTR_PREFETCH_MEMORY=0, Rows per Fetch=156,
Multiple Row Fetch=On
OCIDefineByPos(25e94b0,25eca18,256eda8,1,25edc90,121,5,25ecb60,25ecca0,25ecde0,mode=DEFAULT,0)=SUCCESS
calling OCIAttrSet OCI_ATTR_CHARSET_FORM with csform=1 (SQLCS_IMPLICIT)
OCIAttrSet(25ec700,OCI_HTYPE_DEFINE,
25eca5e,0,Attr=OCI_ATTR_CHARSET_FORM,256eda8)=SUCCESS
OCIDefineByPos(25e94b0,25ecac0,256eda8,2,25f32b0,31,5,25f45a0,25f46e0,25f4820,mode=DEFAULT,0)=SUCCESS
calling OCIAttrSet OCI_ATTR_CHARSET_FORM with csform=1 (SQLCS_IMPLICIT)
OCIAttrSet(25ec4b0,OCI_HTYPE_DEFINE,
25ecb06,0,Attr=OCI_ATTR_CHARSET_FORM,256eda8)=SUCCESS
dbd_describe'd 2 columns (row bytes: 150 max, 75 est avg, cache: 0)
$p = [
120,
30
];
As you can see, DBD::Oracle knows the difference but the array returned by
PRECISION is in bytes rather than characters. I want to access the number
of characters.
Is this possible?
Thanks,
Steve
--
------------------------------------------------------------------
This email is intended solely for the use of the addressee and may
contain information that is confidential, proprietary, or both.
If you receive this email in error please immediately notify the
sender and delete the email..
------------------------------------------------------------------
