problem in getting Scope_Identity in Oracle????

[Sally.mokhtar]

Hi All,
i'm converting my website from connecting to SQL to Connect to Oracle DB and i'm reconfiguring the DataSets and TableAdapters for that .
i have function in a tableAdapter that insert in a table then get the SCOPE_IDENTITY() of the ID of the last inserted row .
This the Code that refer to SQL DB:

INSERT INTO [JobFunction] ([JobFunctionName]) VALUES (@JobFunctionName); SELECT SCOPE_IDENTITY()

And This the Code that refer to ORACLE DB:

INSERT INTO JOBFUNCTION
(JOBFUNCTIONNAME)
VALUES JOBFUNCTIONNAME);
SELECT JOBFUNCTION_SEQ.nextval
FROM dual

The proplem is the last code generate an error : ORA-00911 invaild character
that's because i use ; to separate the INSERT statment from SELECT statment
and if i remove the ; it generates another error : ORA-00933 sql command not properly ended !!!!!!!!!!
Please Help Meeeeeeeeeeeeeeeeeeeeeeeeee

 

[David R. Longnecker]

What about putting this logic into a stored procedure that accepts the values
you're entering and returns a number?

Another option, though I haven't tested it to see if it'd work, might be
to keep the code inline, but assign your next sequence value to an output
parameter and capture the output parameter via the OracleClient class or
however your data access code is structured.

HTH.

-dl