| Multiple Function definitions and Null datatype [message #289447] |
Fri, 21 December 2007 14:30  |
annagel
Messages: 220
Registered: April 2006
|
Senior Member |
|
|
I have been working on some code that basically takes in a string of select SQL and processes it and pumps out a XLS file. My first version of this just made a tab separated file very simple. The newest version is intended to make XML formated XLS files which allow us to do something like preserver the string '000456' instead of turning it into 456 as would happen with a tab separated value because excel without context assumes a number. To make the value type determination easy on myself I just created a function with three declarations like this:
FUNCTION cell (dat IN VARCHAR2)
RETURN VARCHAR2;
FUNCTION cell (dat IN DATE)
RETURN VARCHAR2;
FUNCTION cell (dat IN NUMBER)
RETURN VARCHAR2;
I call this function on each column of the SQL statement and depending on the columns data-type I get one of three formatting options. Except for the rare case when the column has no data-type, when the column is simply a placeholder column and has been declared in the SQL as simply NULL.
In this case I get this error message:
| Quote: |
ORA-06553: PLS-307: too many declarations of 'CELL' match this call
|
I think the meaning is obvious, the Null has either no type or matches all types so the process does not know what to do with it. For now I have simply been looking for NULL returns and will wrap it in a TO_CHAR to just invoke the text version of the function, but I was curious if there was another way to handle this situation. This works for now but I could see the potential exists for it to be fooled if something turns up that my code does not recognize as an un-typed null.
Any ideas?
Andrew
|
|
|
|
|
|
| Re: Multiple Function definitions and Null datatype [message #289452 is a reply to message #289451] |
Fri, 21 December 2007 15:00  |
annagel
Messages: 220
Registered: April 2006
|
Senior Member |
|
|
I think it would be more a case of never having seen this functionality before...I am still not completely convinced this will do what I want and of course this would turn this project into a significantly more time consuming one (the conversion TSV to my own XML Spreadsheet only took 3 hours) as I obviously have to learn how this works a bit better, but I think it would certainly be more robust and learning to put it into place could be fun.
On my original question though, if you have multiple function declarations is there any way to declare one of them as the hey if you are a null data-type come to me?
Andrew
|
|
|
|
| Re: Multiple Function definitions and Null datatype [message #289951 is a reply to message #289447] |
Wed, 26 December 2007 19:57 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
in order for NULL to be sent to one of the procedures, it must be cast to one of the appropriate datatypes.
Which one should Oracle implicitly cast the null to, number, date, varchar2?
The question has no good answer as all sucessfully cast so all are valid, and thus Oracle is faced with an ambiguous sitation. Your workaround is I believe the oracle recommended solution. To my knowledge, there is no other workaround.
Good luck, Kevin
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
