Return sequence number from Oracle

  • Thread starter Thread starter Steven David
  • Start date Start date
S

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
 
Steven David said:
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?
Click to expand...


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
 
Steven Alexander said:
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,:PER_NOME,:PER_DESCRICAO,sysdate,:PER_EMAILLICENCA,:SFT
_CODIGO);
select SEQ_PERFIL.currval from DUAL;
END;
Click to expand...

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,:PER_NOME,:PER_DESCRICAO,sysdate,:PER_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,:PER_NOME,:PER_DESCRICAO,sysdate,:PER_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,:PER_NOME,:PER_DESCRICAO,sysdate,:PER_EMAILLICENCA,:SFT
_CODIGO);
END;

followed by

select SEQ_PERFIL.currval from DUAL;

David
 
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,:PER_NOME,:PER_DESCRICAO,sysdate,:PER_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,:PER_NOME,:PER_DESCRICAO,sysdate,:PER_EMAILLICENCA,:SFT
_CODIGO);
select SEQ_PERFIL.currval into :new_seq from DUAL;
END;


David
 
Back
Top