This article was previously published under Q186103
This article has been archived. It is offered "as is" and will no longer be updated.
The sample code below demonstrates how to use the Nextval function of anOracle database from Visual FoxPro. The Nextval function retrieves the nextavailable number from a sequence.
Oracle Sequence is similar to Identity Columns in Microsoft SQL Serverversion 6.0, which contains system-generated sequential values thatuniquely identify each row within a table.
SQL Passthrough sample code
Sqlhandle=sqlconnect("Oracle_Server") =Sqlexec(Sqlhandle,"Insert into person(P_id,lastname,firstname) ; values (PersonSN.nextval,'Lastname','Firstname')")
The Remote View designer parses 'PersonSN.nextval' as a field named"nextval" in a table named "PersonSN". Since a table named "PersonSN" doesnot exist, the following error occurs at run-time:
Alias not found.
In order to retrieve the sequence from Oracle, you must call an Oracle UserDefined Function (UDF) before appending a record.
Oracle Server -------------
** Create a Sequence name "PersonSN" CREATE SEQUENCE PersonSN INCREMENT BY 1 START WITH 100001 NOCACHE; ** Create a Server Function "FNext" to return the next sequence from ** Person. CREATE OR REPLACE FUNCTION FNext RETURN NUMBER IS Count1 NUMBER(10,2); BEGIN SELECT PersonSN.nextval INTO Count1 FROM dual; RETURN(Count1); END; /