Problem:
Trying to code an embedded OpenESQL call to a function in an Oracle database which returns a VARCHAR2 result.
Using Oracle ODBC driver 11.2.
They get sqlcode -10000 on the call but the same call seems to work for SQL Server database.
ORACLE stored procedure function call:
create or replace
function function1 return varchar2 as
begin
return 'some value';
end function1;
COBOL:
identification division.
program-id. Program1.
environment division.
configuration section.
data division.
working-storage section.
EXEC SQL INCLUDE SQLCA END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 WS-PARAMS.
05 PARAM5.
49 PARAM5-LEN PIC S9(4) COMP-5.
49 PARAM5-ARR PIC X(20).
EXEC SQL END DECLARE SECTION END-EXEC.
procedure division.
a.
EXEC SQL CONNECT TO "TESTORA" END-EXEC.
MOVE 20 TO PARAM5-LEN.
MOVE SPACES TO PARAM5-ARR.
EXEC SQL
:PARAM5 = CALL FUNCTION1
END-EXEC.
DISPLAY "SQLCODE IS " SQLCODE.
EXEC SQL DISCONNECT END-EXEC.
goback.
end program Program1.
Resolution:
Proposed workarounds:
Convert the function call to a procedure with an output parameter as this works.
create or replace function function2(x output) as beginx = 'some value';
end function2;
EXEC SQL CALL PROC2(:PARAM5 out) END-EXEC.
Another alternative that doesn't require changing the server code is:
EXEC SQL SELECT FUNCTION1() INTO : PARAM5 FROM DUAL END-EXEC


