Return sequence number from Oracle

[Steven David]

People,
someone help me please.

I have a webapplication, and i store the statements in xml
file. Then, when i need then i took at the file.
In one method of mine i have to add a record at the table
and return to my application the number of sequence that
was created. The database is in oracle. I know that if i
do this (in Sql Server) it works:
insert into ....
select @@identity
and execute the command with cmd.executesacalar.
But in oracle don't work.
Any idea? I can't use stored procedures at databse.

Steven Alexander
Brazil

 

[David Browne]

People,
someone help me please.

I have a webapplication, and i store the statements in xml
file. Then, when i need then i took at the file.
In one method of mine i have to add a record at the table
and return to my application the number of sequence that
was created. The database is in oracle. I know that if i
do this (in Sql Server) it works:
insert into ....
select @@identity
and execute the command with cmd.executesacalar.
But in oracle don't work.
Any idea?

select some_sequence.curval from dual

Does this in Oracle.
The only difference is that you need to know the name of the sequence.

David

 

[David Browne]

Correct, i treid this. But the error
ORA-06550: line 10, column 1: PLS-00428: an INTO clause
is expected in this SELECT statement
appears

Here is my block that i execute
declare id number;
BEGIN
select SEQ_PERFIL.nextval into id from DUAL;
insert into per_perfil
(per_codigo, per_nome, per_descricao,
per_dt_criacao, per_emaillicenca, sft_codigo)
values
(id,ER_NOME,ER_DESCRICAO,sysdate,ER_EMAILLICENCA,:SFT
_CODIGO);
select SEQ_PERFIL.currval from DUAL;
END;

That's another problem entirely.

A select without INTO is illegal in PL\SQL. (OK in SQL, not OK in PL\SQL).

Change your batch to select it into a variable and retrieve it with a bind
variable, or open a ref cursor on it, or issue
select SEQ_PERFIL.currval from DUAL
in a seperate command.



BEGIN
select SEQ_PERFIL.nextval into id from DUAL;
insert into per_perfil
(per_codigo, per_nome, per_descricao,
per_dt_criacao, per_emaillicenca, sft_codigo)
values
(id,ER_NOME,ER_DESCRICAO,sysdate,ER_EMAILLICENCA,:SFT
_CODIGO);
select :new_seq := SEQ_PERFIL.currval from DUAL;
END;

or

BEGIN
select SEQ_PERFIL.nextval into id from DUAL;
insert into per_perfil
(per_codigo, per_nome, per_descricao,
per_dt_criacao, per_emaillicenca, sft_codigo)
values
(id,ER_NOME,ER_DESCRICAO,sysdate,ER_EMAILLICENCA,:SFT
_CODIGO);
open :rc for select SEQ_PERFIL.currval from DUAL;
END;

or

BEGIN
select SEQ_PERFIL.nextval into id from DUAL;
insert into per_perfil
(per_codigo, per_nome, per_descricao,
per_dt_criacao, per_emaillicenca, sft_codigo)
values
(id,ER_NOME,ER_DESCRICAO,sysdate,ER_EMAILLICENCA,:SFT
_CODIGO);
END;

followed by

select SEQ_PERFIL.currval from DUAL;

David

 

[David Browne]

oops

BEGIN
select SEQ_PERFIL.nextval into id from DUAL;
insert into per_perfil
(per_codigo, per_nome, per_descricao,
per_dt_criacao, per_emaillicenca, sft_codigo)
values
(id,ER_NOME,ER_DESCRICAO,sysdate,ER_EMAILLICENCA,:SFT
_CODIGO);
select :new_seq := SEQ_PERFIL.currval from DUAL;
END;

Should be

BEGIN
select SEQ_PERFIL.nextval into id from DUAL;
insert into per_perfil
(per_codigo, per_nome, per_descricao,
per_dt_criacao, per_emaillicenca, sft_codigo)
values
(id,ER_NOME,ER_DESCRICAO,sysdate,ER_EMAILLICENCA,:SFT
_CODIGO);
select SEQ_PERFIL.currval into :new_seq from DUAL;
END;


David