>I know how to call oracle procedures and packages, but how would I call an oracle function. SQLexec(nHandle,'{call xyz_func(
)}') returns an error that xyz_func is not a procedure or is undefined. Do I have to created a wrapper SP to return the results of the function?
What you can do is pass a var by reference and have the SP stuff the value into this var. Something like this:CREATE OR REPLACE PROCEDURE CustomerInsert
(NewPKVal OUT Customer.KeyID%Type,
cFName IN Customer.FName%Type,
cLName IN Customer.LName%Type,
cUserName IN Customer.UserName%Type,
cPassword IN Customer.Password%Type) IS
BEGIN
SELECT S_Schema_CustomerID.NextVal INTO NewPKVal FROM DUAL;
INSERT INTO Customer
(KeyID, FName, LName, UserName, Password)
VALUES
(NewPKVal, cFName, cLName, cUserName, cPassword);
COMMIT;
END;
/In VFP, the code looks like:PRIVATE NewPKVal, pcFName, pcLName, pcUserName, pcPassword
NewPKVal = 0
pcFName = trim(crsCustomer.cFName)
pcLName = trim(crsCustomer.cLName)
pcUserName = trim(crsCustomer.cUserName)
pcPassword = trim(crsCustomer.cPassword)
lcSQL = "begin Schema.CustomerInsert(" ;
+ "NewPKVal=>?@NewPKVal, " ;
+ "cFName=>?pcFName, " ;
+ "cLName=>?pcLName, " ;
+ "cPassword=>?pcPassword, " ;
+ "cUserName=>?pcUserName); end;"
lnRetVal = SQLEXEC(lnHandle, lcSQL)
IF lnRetVal < 1
DIMENSION laErrorInfo[1]
AERROR(laErrorInfo)
DISP MEMO LIKE laErrorInfo*
ENDIF
RETURN NewPKValWhen you do it this way, the order of the values passed to the SP no longer matters.
Mark McCasland
Midlothian, TX USA
