Hi all,
I am trying to retrieve the data returned from a stored function in Oracle.
This is the stored function in Oracle:
The Xbasic I use to call this function is:
In my SQL Developer the 'SQL' above works fine with the addition of
before the 'END;' tag and I am able to see what is in 'retval'.
I have never done something like this before but stepping into the code from Alpha the function executes correctly just no rows are returned.
Can anyone advise as to what may need to be modified in order for me to get the value of what is stored in 'retval' via Alpha?
Thanks,
Laura-Kae
I am trying to retrieve the data returned from a stored function in Oracle.
This is the stored function in Oracle:
Code:
create or replace FUNCTION "GET_SERVER_TIME" ( P_LAST_UPDATE_TIME OUT VARCHAR2, P_ERROR_CODE OUT VARCHAR2 ) return VARCHAR2 as BEGIN SELECT TO_CHAR (SYSDATE , 'dd-Mon-yyyy hh24:mi:ss' ) INTO P_LAST_UPDATE_TIME FROM DUAL; RETURN P_LAST_UPDATE_TIME ; EXCEPTION WHEN OTHERS THEN P_ERROR_CODE := HANDLE_ERROR(SQLCODE,'SERVER DATE ERROR'); RETURN P_ERROR_CODE ; END;
Code:
function test as c (E as P) dim cn as SQL::Connection dim flg as l = cn.open("::Name::Oracle_Road-Bay") dim args as SQL::Arguments dim sql as c sql = "DECLARE retval VARCHAR2(20);P_LAST_UPDATE_TIME VARCHAR2(20);P_ERROR_CODE VARCHAR2(20); BEGIN retval:=GET_SERVER_TIME(P_LAST_UPDATE_TIME,P_ERROR_CODE); END;" if (flg .and. cn.Execute(sql)) then ' NOT SURE WHAT TO PUT HERE ' end if end function
Code:
DBMS_OUTPUT.put_line(retval);
I have never done something like this before but stepping into the code from Alpha the function executes correctly just no rows are returned.
Can anyone advise as to what may need to be modified in order for me to get the value of what is stored in 'retval' via Alpha?
Thanks,
Laura-Kae
Comment