Re: Return value of PL/SQL-functions

From: Stephan Hüttner <stephan.huettner_at_hackenberg.de>
Date: 1997/01/14
Message-ID: <32DC2AB0.3A8C@hackenberg.de>#1/1

Thanks Mike!

The way you described isn't working. There will allways show up a message like 'ORA - 06509 .. missing ICD vector'.
Thanks anyway.
The reason why I tried to use pessub() is that we are migrating some applications (with about 3000 SQL-Statements) from Gupta SQLBase to Oracle. The applications make use of several SQLBase functions which do not have equivalents in Oracle. So we built up the missing functions with PL/SQL.
Everything worked out really fine except all the functions which return strings. Every string-function will return something like a 'long string' regardless of the length of the return value. The application won't take a long string into a string variable and for shure we won't change all string variables into long strings. Studying standard.sql we found the definition of SUBSTR(). SUBSTR() will give back a string unless the return value is longer than 254. SUBSTR() uses pessub() and so we tried to make use of it ourself.

Another way could be to define our functions whith constrained subtypes .. which is impossible.

So we are still trying to define functions that return strings instead of long strings.

A statement like

	SELECT 	my_substr( column, 1, 2 )
	FROM   	table
	INTO	:str_1;

should work without changing it.

We tried very hard on this so far. We also got a lot of very good answers but none of them hit the point.

Thanks Stephan Received on Tue Jan 14 1997 - 00:00:00 CST