The PL/SQL code at the bottom of this message compiles with no errors in Oracle. However when I issue the following from VFP, I get an error:

lcSQL = "{call FnAgentInsert('Mark','McCasland','','mark_at_nowhere.net','markm','mypwd')}"

lnNewID = SQLExec(lnHandle, lcSQL)

The error when I execute that directly in SQL Plus is:

BEGIN FnAgentInsert('Mark','McCasland','','mark_at_nowhere.net',
'markm','mypwd'); END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00221: 'FNAGENTINSERT' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

A query of the USER_OBJECTS table shows this object_name does exist and has an object_type of function. Anyone know how this should be modified so I can use a SP to insert a new record and return the PK ID that is generated by the INSERT?

If I convert this to CREATE PROCEDURE syntax, it works beautifully, but then I can not get the new PK value returned.

I know I could issue 2 SQL from VFP, the first to get the new PK, then pass that to the insert SP. I was just wondering if this could be done with a function like the one below. TIA!

CREATE OR REPLACE FUNCTION FnAgentInsert
   (cFName    Agent.FName%Type,
    cLName    Agent.LName%Type,
    cTitle    Agent.Title%Type,
    cEMail    Agent.EMail%Type,
    cUserName Agent.UserName%Type,
    cPassword Agent.Password%Type)
    RETURN numeric IS
    nNewPKVal Agent.ID%Type;
BEGIN
  SELECT S_CIS_AGENT.nextval into nNewPKVal from dual;
  INSERT INTO agent
    (id, fname, lname, title, email, username, password)
      VALUES 
    (nNewPKVal, cFName, cLName, cTitle, cEMail, cUserName, cPassword);
  COMMIT;
  RETURN nNewPKVal;
END;
/
show errors