Home » SQL & PL/SQL » SQL & PL/SQL » teradata rollback functionality in oracle
teradata rollback functionality in oracle [message #241439] Tue, 29 May 2007 10:32 Go to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
hi all,

can anyone tell me how we can implement this teradata rollback functionality in oracle

replace macro ops_dbs.del_nei_ric (
ric varchar(17)
)
as (
rollback 'Temp Ric does not exists ' where :ric not in
(
select ric
from adb_prod_t.traded_item_on_market
where ric = :ric
and ti_on_market_id > 100000000
);
);

regards,
dinesh
Re: teradata rollback functionality in oracle [message #241442 is a reply to message #241439] Tue, 29 May 2007 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you explain what does it mean?

Regards
Michel
Re: teradata rollback functionality in oracle [message #241463 is a reply to message #241442] Tue, 29 May 2007 13:35 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Are you talking about DCL (rollback,commit,savepoint)
Re: teradata rollback functionality in oracle [message #241550 is a reply to message #241442] Wed, 30 May 2007 00:08 Go to previous messageGo to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
if the select query does not return a record for the value of ric given as argument to the macro, the current transaction would be aborted (any changes done as part of the transaction would be reverted back to their original state). And the message 'Temp Ric does not exists ' would be generated.
Re: teradata rollback functionality in oracle [message #241559 is a reply to message #241550] Wed, 30 May 2007 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
create or replace procedure XXX (p_ric in out varchar(17))
is
begin
  select ric into p_ric
  from adb_prod_t.traded_item_on_market
  where ric = p_ric
    and ti_on_market_id > 100000000;
exception
  when no_data_found then rollback;
end;
/

Syntaxically this is the equivalent but it is a bad practice to put a rollback inside a procedure. This is not the server that has to know if a transaction must be rolled back or commited this is the client application.
You should instead trap the "no_data_found" error inside your program and roll back in it and not in the procedure.

Regards
Michel


Re: teradata rollback functionality in oracle [message #241569 is a reply to message #241559] Wed, 30 May 2007 01:13 Go to previous messageGo to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
Thanks for reply.....
Re: teradata rollback functionality in oracle [message #241577 is a reply to message #241569] Wed, 30 May 2007 01:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry I missed the error message:
create or replace procedure XXX (p_ric in out varchar(17))
is
begin
  select ric into p_ric
  from adb_prod_t.traded_item_on_market
  where ric = p_ric
    and ti_on_market_id > 100000000;
exception
  when no_data_found then 
    rollback;
    raise_application_error (-20000,'Temp Ric does not exists');
end;
/

But I think a default return of "no data found" is not less informative than your custom message.
Once again it should be the application that format the message not the procedure then different applications using the same procedure can display different messages.

Regards
Michel
Re: teradata rollback functionality in oracle [message #241917 is a reply to message #241577] Thu, 31 May 2007 00:59 Go to previous messageGo to next message
lokhande.dinesh29
Messages: 36
Registered: May 2007
Member
Hi,

i created this procedure....

SQL> create or replace procedure proc_31(p_ric in out varchar2)
2 is
3 begin
4 select ric into p_ric from temp_31 where ric=p_ric and amount>500;
5 exception
6 when no_data_found then
7 rollback;
8 raise_application_error(-20000,'Temp Ric does not exists');
9 end;
10 /

Procedure created.

& i have this data in my table

SQL> select * from temp_31;

RIC AMOUNT
----------------- ----------
din 1000
rag 1200
san 1500


when i am trying to execute procedure one error has come:

SQL> exec proc_31('din');
BEGIN proc_31('din'); END;

*
ERROR at line 1:
ORA-06550: line 1, column 15:
PLS-00363: expression 'din' cannot be used as an assignment target
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

plz tell me the reason...

Regards,
dinesh
Re: teradata rollback functionality in oracle [message #241930 is a reply to message #241917] Thu, 31 May 2007 01:56 Go to previous message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It was just a way to do it not a final code.
Just add a local variable in your procedure and use it in the "into" clause instead of the parameter (I was too lazy to write it and I'm still).

Regards
Michel
Previous Topic: subquery factoring
Next Topic: Equivalent of set commands in oracle.....?
Goto Forum:
  


Current Time: Fri Dec 02 12:16:38 CST 2016

Total time taken to generate the page: 0.12151 seconds