How to use Oracle Nextval function from Visual FoxPro (186103)

SUMMARY

The sample code below demonstrates how to use the Nextval function of an Oracle database from Visual FoxPro. The Nextval function retrieves the next available number from a sequence.

Oracle Sequence is similar to Identity Columns in Microsoft SQL Server version 6.0, which contains system-generated sequential values that uniquely identify each row within a table.

MORE INFORMATION

SQL Passthrough sample code

   Sqlhandle=sqlconnect("Oracle_Server")
   =Sqlexec(Sqlhandle,"Insert into person(P_id,lastname,firstname) ;
   values (PersonSN.nextval,'Lastname','Firstname')")

Remote view

The Remote View designer parses 'PersonSN.nextval' as a field named "nextval" in a table named "PersonSN". Since a table named "PersonSN" does not exist, the following error occurs at run-time:

Alias not found.

In order to retrieve the sequence from Oracle, you must call an Oracle User Defined 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;
   /

   Visual FoxPro
   -------------

   =SQLEXEC(Sqlhandle,"Select FNext from dual")