Handling of a stored Oracle function return string value

General discussion forum about all databases other than SqlBase.
Andras

Handling of a stored Oracle function return string value

Post by Andras » 22 Apr 2015, 12:31

I have a problem in TD 6.3 .NET Trial version using with Oracle.

If I make a stored function in Oracle which has a varchar2 type return value, then I must use this in TD as long string, but the length is less than 254. Why? Is there a setting wich I didn't set? Or what?

Example:

create or replace function
tmp_fnc
return varchar2
as
xs_retval varchar2(100);
begin
xs_retval := 'AbcDef';
return xs_retval;
end tmp_fnc;

TD side

variables
String: sVal
Long String: lsVal
Sql Handle: hSql
Number: n

If SqlPrepareAndExecute(hSql,'select tmp_fnc from dual into :sVal)
Call SqlFetchNext(hSql,n)
! The value of sVal is null
If SqlPrepareAndExecute(hSql,'select tmp_fnc from dual into :lsVal)
Call SqlFetchNext(hSql,n)
! The value of lsVal is 'AbcDef'

BUT! If I make a stored procedure in Oracle which has an out varchar2 parameter, it works correctly.

Example:
create or replace
procedure tmp_prc
(
xs_output OUT varchar2
)
as
begin
xs_output := 'AbcDef';
end tmp_prc;

TD Side

variables
String: sVal
Long String: lsVal
Sql Handle: hSql
Number: n
String: lsSqlStmt

Set lsSqlStmt = 'tmp_prc(sVal)'
Call SqlPLSQLCommand( hSql, lsSqlStmt )
! The value of sVal is 'AbcDef' too

Return to “General Discussion”

Who is online

Users browsing this forum: [Ccbot] and 0 guests