How to use Oracle Nextval function from Visual FoxPro

Article translations Article translations
Article ID: 186103 - View products that this article applies to.
This article was previously published under Q186103
This article has been archived. It is offered "as is" and will no longer be updated.
Expand all | Collapse all

On This Page

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")

Properties

Article ID: 186103 - Last Review: February 23, 2014 - Revision: 3.1
APPLIES TO
  • Microsoft Visual FoxPro 3.0 Standard Edition
  • Microsoft Visual FoxPro 3.0b Standard Edition
  • Microsoft Visual FoxPro 5.0 Standard Edition
  • Microsoft Visual FoxPro 5.0a
Keywords: 
kbnosurvey kbarchive kbhowto KB186103

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com