| Concepts on pro*c [message #38867] |
Thu, 23 May 2002 02:38  |
Amit
Messages: 166
Registered: February 1999
|
Senior Member |
|
|
Hi
First of all i'm sorry for placing this question in this section. I placed my question in the 'PRECOMPILER,OCI' but couldn't get the response.
My questions are
1) While retrieving rows from a table in a host variable, is it necessary that a host variable (which is varchar) size is greater by 1 from table field size?
eg
table-field varchar2(4);
host-variable varchar2(4) OR varchar2(5)(as it has to be 1 length greater as that in C )
2)Assuming 1 is correct, I've found that if a table contains records
say
field(varchar2(4))
-------
abcd
abx
az
asdf
then while retrieving the rows thru cursor in the host_variable,it came out to be
abcd
abxd
azxd
asdf
ie beside the new value it is displaying the remains of the previous record in the remaining size of current value.
However, if i use
select fieldname|| chr(0)(ie end of line) in the cursor defn i get the the correct records.
My problem is that i'm retrieving the records (fieldname|| chr(0) whose size is 4 + 1 ) in a host variable of size 4, it is not displaying any error and working fine.
Can anybody tell me what is the concept behind it (ie how a record of size 5 is being fetched in a hostvariable of size varchar 4????
|
|
|
|
| Re: Concepts on pro*c [message #38869 is a reply to message #38867] |
Thu, 23 May 2002 04:13  |
Rick Cale
Messages: 111
Registered: February 2002
|
Senior Member |
|
|
Hi,
If a oracle column is defined as VARCHAR2(4) your
pro*c host varchar variable only needs to be 4.
If you are going to use that host variable in
something like strcmp then you would have define
it as 4+1 and null terminate it. If you are just
using it to insert,update,etc size of 4 is fine.
You can load a field greater than the defined width
of a variable but truncation will take place. Your
indicator variable will be a non-zero value ( I think
> 0). You think in this case it is working because it
is just truncating end of line. ALWAYS use indicator
variables when fetching so you can check data was retrieved correctly.
|
|
|
|
| Re: Concepts on pro*c [message #38886 is a reply to message #38867] |
Thu, 23 May 2002 20:13 |
Amit
Messages: 166
Registered: February 1999
|
Senior Member |
|
|
Thanks Rick, for the information.
But still i'm not able to get that how my host variable
(size varchar(4) is showing the CORRECT and UNTRUNCATED data while being fetched from table-field(varchar2(4))|| chr(0) [[ie size 4 + 1]] in a cursor declaration.
Does it means that, it will truncate the chr(0) part if it gets 4 character without giving any ERROR and take the whole string + chr(0) if the the nos of characters in string <4.
If i'm processing this host variable(size varchar 4) to write records in a file and not undergoing for any string operations then should i assume it that there will be no impact or failure in the functionality.
Thanx in advance
Amit
|
|
|
|
| Re: Concepts on pro*c [message #38948 is a reply to message #38867] |
Thu, 30 May 2002 06:19 |
Rick Cale
Messages: 111
Registered: February 2002
|
Senior Member |
|
|
Sorry for the delay, just got back. If your oracle
field is defined as varchar2(4) and your host variable
is defined as varchar2(4) then you fetch variable &
chr(0) into your host variable you will get your oracle
variable without the chr(0). This will not produce an
oracle error but if you have an indicator variable it will be non-zero. You will get correct results. But if
you purposely fetching 5 characters then change your host variable to 5 or do not fetch the chr(0).
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
