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]

"Steven David" <st****@amazoncorp.com.br> wrote in message
news:00****************************@phx.gbl...

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]

"Steven David" <st****@amazoncorp.com.br> wrote in message
news:00****************************@phx.gbl...

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]

"Steven Alexander" <st****@amazoncorp.com.br> wrote in message
news:02****************************@phx.gbl...

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_EMAILLIC ENCA,: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,:PER_NOME,:PER_DESCRICAO,sysdate,:PER_EMAILLIC ENCA,: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_EMAILLIC ENCA,: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_EMAILLIC ENCA,:SFT
_CODIGO);
END;

followed by

select SEQ_PERFIL.currval from DUAL;

David

[David Browne]

"Steven Alexander" <st****@amazoncorp.com.br> wrote in message
news:02****************************@phx.gbl...

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