Returning a sequence from ORACLE Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi, i'm developing a smal program to insert records in a ORACLE Database, but my client has a sequence fort the PK of the table, how do i get the value generated by the sequence? Without this value i can't insert child records..

Thanks
 
Hi
We do it like this:
Before you execute the insert statement, get the pk-value by calling the
following proc:

public static int GetSeqVal(string shortTName)
{ using(OracleConnection co=GetCon())
{ OracleCommand cmd=new OracleCommand("select "+shortTName+"_seq.nextval
from dual",co);
co.Open();
String rslt = cmd.ExecuteScalar().ToString();
co.Close();
return Convert.ToInt32(rslt);
}
}

So..don't use a DB-Trigger to fill the pk-value
Regards

Valter said:
Hi, i'm developing a smal program to insert records in a ORACLE Database,
Click to expand...
but my client has a sequence fort the PK of the table, how do i get the
value generated by the sequence? Without this value i can't insert child
records...
 
ivo said:
Hi
We do it like this:
Before you execute the insert statement, get the pk-value by calling the
following proc:

public static int GetSeqVal(string shortTName)
{ using(OracleConnection co=GetCon())
{ OracleCommand cmd=new OracleCommand("select "+shortTName+"_seq.nextval
from dual",co);
co.Open();
String rslt = cmd.ExecuteScalar().ToString();
co.Close();
return Convert.ToInt32(rslt);
}
}

So..don't use a DB-Trigger to fill the pk-value
Regards

Database,
but my client has a sequence fort the PK of the table, how do i get the
value generated by the sequence? Without this value i can't insert child
records...
Click to expand...

If you are after the PK value for the master record just inserted to use as
the FK for the child records, change the '.nextval' to '.currval.'

My personal recommendation would be to do the insert into the master table
using a Stored Procedure and return the PK from the SP. You now have it for
all of the child records.
 
Back
Top