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 Go to next message
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 Smile
Thanks in advance!
Re: procedure inside transaction [message #438887 is a reply to message #438886] Tue, 12 January 2010 20:13 Go to previous messageGo to next message
BlackSwan
Messages: 24905
Registered: January 2009
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 #438888 is a reply to message #438887] Tue, 12 January 2010 20:53 Go to previous messageGo to next message
said
Messages: 4
Registered: January 2010
Location: Portugal
Junior Member
Thanks!
It works just fine.
Sorry for not following the Guidelines.
Re: procedure inside transaction [message #438925 is a reply to message #438886] Wed, 13 January 2010 01:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
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 #438928 is a reply to message #438887] Wed, 13 January 2010 01:32 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
COMMIT;

at the beginning is not required I believe


Regards,
Ved
Re: procedure inside transaction [message #438941 is a reply to message #438886] Wed, 13 January 2010 03:05 Go to previous messageGo to next message
flyboy
Messages: 1831
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 Go to previous messageGo to next message
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 #439073 is a reply to message #439059] Thu, 14 January 2010 01:10 Go to previous messageGo to next message
Littlefoot
Messages: 20825
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
said wrote on Thu, 14 January 2010 00:39
the truth is that I get the number generated in the sequence.

If you read Avoid breakup in the sequence recent thread, perhaps you'll change your mind (i.e. learn that truth isn't always true).
Re: procedure inside transaction [message #439074 is a reply to message #439059] Thu, 14 January 2010 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
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 #439076 is a reply to message #439059] Thu, 14 January 2010 01:39 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
said wrote on Thu, 14 January 2010 00:39
Flyboy, I didn´t quite get your opinion. You're saying that we can get a return value from an insert?

Yes (if by "return value" you mean the value present in inserted row), that is exactly what SQL I posted does.
It is documented in SQL Reference book, available e.g. online on http://tahiti.oracle.com/
INSERT statement (with examples) for 10gR2 is described here: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9014.htm#i2163698

Your approach will get the greatest value of COD_TURMA, which may not be the one you inserted (just imagine the procedure called twice from different clients). Anyway, it is at least strange to get value the code generated by querying all data afterwards.

[Edit: Precising the first sentence]

[Updated on: Thu, 14 January 2010 01:41]

Report message to a moderator

Re: procedure inside transaction [message #439087 is a reply to message #439059] Thu, 14 January 2010 02:58 Go to previous messageGo to next message
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 #439088 is a reply to message #439087] Thu, 14 January 2010 03:17 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the last way to be exhaustive:
SELECT s_turmas.nextval
  INTO   ct
  FROM   dual;

INSERT INTO turmas
  VALUES     (ct,
              'Segunda',
              '14:30',
              'A1.2',
              'Teórica',
              'T3');

But the INSERT returning clause is the best option.

Regards
Michel
Re: procedure inside transaction [message #439200 is a reply to message #439088] Thu, 14 January 2010 17:30 Go to previous message
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
Previous Topic: Zero fill an OUT parameter
Next Topic: internal error code
Goto Forum:
  


Current Time: Mon Sep 26 06:01:30 CDT 2016

Total time taken to generate the page: 0.08284 seconds