| Returned vArray problem [message #39612] |
Fri, 26 July 2002 05:27  |
Dave Dodson
Messages: 3
Registered: July 2002
|
Junior Member |
|
|
Have a function that takes a single concatenated name arguement and returns a varray of 5 elements - Prefix, First, etc. My utils package has a "TYPE prsdName IS VARRAY(5) OF varChar2(100)". Its parseName function declares "aAllParts prsdName;", and begins by initializing it as "aAllParts := prsdName('', '', '', '', '');". The proc (in another package) that calls it first declares "aName utils_PKG.prsdName;" and gets its return value as "aName := utils_PKG.FN_parseName(exec.chief_executive_officer_name);" (where exec is a cursor). Where a name component is missing (i.e. prefix) the function returns '' (which PL/SQL seems to equate with NULL).
On successive calls, iterating through the cursor, if some elements of the returned varray are NULL (''), the elements in the calling procedure's array retain their previous values, instead of being set to NULL. Thus, when an exec with a 'Dr.' prefix is followed by serveral with none, the 'Dr.' prefix is maintained in the calling proc's varray (aName), until it's replaced with another non-NULL prefix.
Tried re-initalizing the varray between calls. In test code, with no cursor or loop - just a series of successive calls with complete and incomplete names, reinitialization works. In my cursor loop, though, it doesn't.
Could Kludge this, by returning 'XYZ' instead of NULL, for missing components, and decoding that, but surely there's a right way.
Anyone know how to fix this?
|
|
|
|
| Re: Returned vArray problem [message #39617 is a reply to message #39612] |
Fri, 26 July 2002 09:18  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
In your parseName function, change your initialization and subsequent code to something like:
function parseName(p_name in varchar2)
return prsdName
is
aAllParts prsdName := prsdName();
begin
aAllParts.extend(5);
-- parsing code here
return(aAllParts);
end; |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
