Home » SQL & PL/SQL » SQL & PL/SQL » Rewrite my MSSQL procedure to Oracle syntax
Rewrite my MSSQL procedure to Oracle syntax [message #317712] Fri, 02 May 2008 13:07 Go to next message
trikard
Messages: 1
Registered: May 2008
Junior Member
Hi, I'm a beginner regarding Oracle. I've done a MSSQL cleanup script that deletes records in tables older than X days.
Now I want use the same procedure in an Oracle database, but I'm not sure about the syntax and functions in Oracle.
The MSSQL script look like this:

CREATE procedure dbo.P_TablesCleanup
@X int
as
set nocount on

declare @err int

begin tran

DELETE FROM dbo.P_AccGLPostingEnvelopeOut WHERE (DATEDIFF(day, ADB_TIMESTAMP, GETDATE()) > @X) AND (ADB_L_DELIVERY_STATUS = 'C')

DELETE FROM dbo.P_ActivityAccEnvelopeOut WHERE (DATEDIFF(day, ADB_TIMESTAMP, GETDATE()) > @X) AND (ADB_L_DELIVERY_STATUS = 'C')

DELETE FROM dbo.P_SalesInvoiceEnvelopeOut WHERE (DATEDIFF(day, ADB_TIMESTAMP, GETDATE()) > @X) AND (ADB_L_DELIVERY_STATUS = 'C')

set @err=@@ERROR
if @err = 0
commit
else
begin
rollback
return (@err)
end

GO
Re: Rewrite my MSSQL procedure to Oracle syntax [message #317718 is a reply to message #317712] Fri, 02 May 2008 15:05 Go to previous message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
This will get you started:

delete from table
where trunc(sysdate) - trunc(datecolumn) >= input_variable;
Previous Topic: Is it Possible?
Next Topic: Spool a report file in the Background
Goto Forum:
  


Current Time: Thu Dec 08 12:33:40 CST 2016

Total time taken to generate the page: 0.10114 seconds