Home » SQL & PL/SQL » SQL & PL/SQL » procedure inside transaction (iSQL*Plus 10.2.0.1 - Windows 7)
procedure inside transaction [message #438886] |
Tue, 12 January 2010 19:59 |
said
Messages: 4 Registered: January 2010 Location: Portugal
|
Junior Member |
|
|
This is my first post, I hope the information I'll give will be enough.
I'm trying to do a transaction that calls a procedure inside, but I'm getting an error.
Here's the code:
declare
ct NUMBER;
begin
commit;
set transaction read write name 'turmas aulas';
insert into turmas values(s_turmas.NEXTVAL, 'Segunda', '14:30', 'A1.2', 'Teórica', 'T3');
select max(cod_turma) into ct
from turmas;
insert into tem values(1, ct, 'T3');
exec cria_aulas(ct);
commit;
exception
when DUP_VAL_ON_INDEX then
rollback;
DBMS_OUTPUT.PUT_LINE('A transacção fez roll back!');
end;
/
And here's the error:
ERROR at line 10:
ORA-06550: line 10, column 6:
PLS-00103: Encountered the symbol "CRIA_AULAS" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "CRIA_AULAS" to continue.
I hope the table names in portuguese won´t be a problem
Thanks in advance!
|
|
|
Re: procedure inside transaction [message #438887 is a reply to message #438886] |
Tue, 12 January 2010 20:13 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
DECLARE
ct NUMBER;
BEGIN
COMMIT;
set TRANSACTION READ WRITE NAME 'turmas aulas';
INSERT INTO turmas
VALUES (s_turmas.nextval,
'Segunda',
'14:30',
'A1.2',
'Teórica',
'T3');
SELECT Max(cod_turma)
INTO ct
FROM turmas;
INSERT INTO tem
VALUES (1,
ct,
'T3');
Cria_aulas(ct);
COMMIT;
EXCEPTION
WHEN dup_val_on_index THEN
ROLLBACK;
dbms_output.Put_line('A transacção fez roll back!');
END;
/
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
EXEC is used to invoke PL/SQL procedure from SQLPLUS.
Within PL/SQL itself, just use the procedure name.
[Updated on: Tue, 12 January 2010 20:14] Report message to a moderator
|
|
|
|
Re: procedure inside transaction [message #438925 is a reply to message #438886] |
Wed, 13 January 2010 01:26 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Oracle is not Sybase, you must not manage your transactions inside stored procedures.
The caller is the one that knows if a transaction must begin or end.
In addition, I don't know if s_turmas.NEXTVAL is recorded inside cod_turma column but if this is the case between the first insert and the folowing select you might don't get the number generated by the sequence in the first statement.
Regards
Michel
[Updated on: Thu, 14 January 2010 01:15] Report message to a moderator
|
|
|
|
Re: procedure inside transaction [message #438941 is a reply to message #438886] |
Wed, 13 January 2010 03:05 |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
It seems to me that you are (incorrectly) trying to mimic RETURNING clause of INSERT statement. INSERT INTO turmas
VALUES (s_turmas.nextval,
'Segunda',
'14:30',
'A1.2',
'Teórica',
'T3')
RETURNING cod_turma INTO ct;
Incorrectly, because in Oracle, READ WRITE transaction is consistent on statement level, as documented e.g. here. And, as it is default behaviour, you do not need to specify it.
|
|
|
Re: procedure inside transaction [message #439059 is a reply to message #438886] |
Wed, 13 January 2010 17:39 |
said
Messages: 4 Registered: January 2010 Location: Portugal
|
Junior Member |
|
|
Thanks for all the answers.
Michel, I understand your opinion. s_turmas.NEXTVAL is saved inside cod_turma. And although thinking like you, the truth is that I get the number generated in the sequence.
Ved, I read somewhere that we should put the commit, to end any other transaction still running on the system.
Flyboy, I didn´t quite get your opinion. You're saying that we can get a return value from an insert?
Vítor
|
|
|
|
Re: procedure inside transaction [message #439074 is a reply to message #439059] |
Thu, 14 January 2010 01:31 |
|
Michel Cadot
Messages: 68704 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Quote:And although thinking like you, the truth is that I get the number generated in the sequence.
Because till now you have been lucky and maybe you were alone on your sytem to make test of this procedure.
Quote:You're saying that we can get a return value from an insert?
Yes, you can do it.
Quote:I read somewhere that we should put the commit, to end any other transaction still running on the system.
Wrong, commit ends YOUR transaction not others one, hopefully.
But once again, you do not put a commit inside your procedure, only the caller (the application) knows if it wants to commit or not.
Regards
Michel
|
|
|
|
Re: procedure inside transaction [message #439087 is a reply to message #439059] |
Thu, 14 January 2010 02:58 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote:Michel, I understand your opinion. s_turmas.NEXTVAL is saved inside cod_turma. And although thinking like you, the truth is that I get the number generated in the sequence. that's most likely because you've not tried running the code under heavy load in a multi user environment.
There is another way to get the sequence number used back, other than the RETURNING clause. You could do:
INSERT INTO turmas
VALUES (s_turmas.nextval,
'Segunda',
'14:30',
'A1.2',
'Teórica',
'T3');
SELECT s_turmas.currval
INTO ct
FROM dual;
If I were you, I'd use the RETURNING clause.
|
|
|
|
Re: procedure inside transaction [message #439200 is a reply to message #439088] |
Thu, 14 January 2010 17:30 |
said
Messages: 4 Registered: January 2010 Location: Portugal
|
Junior Member |
|
|
Thanks for all the answers.
I've followed your opinions. I've removed the first commit.
And got the sequence number with the returning clause, which I didn´t know about.
You were right, I'm testing this all alone in my system.
The trasanction has done everything as I wanted.
Vítor
|
|
|
Goto Forum:
Current Time: Mon Nov 04 05:28:28 CST 2024
|