SQLServer procedure from Oracle procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQLServer procedure from Oracle procedure

Author

Topic

rahulm_32003
Starting Member

7 Posts

Posted - 2012-09-03 : 13:35:55

I am new to DB Programming.Can some one guide to modify it as per SQLServer sytnax

CREATE OR REPLACE procedure TRANS_DELETE ( result OUT types.ref_cursor
, startDate1 IN TIMESTAMP
, endDate1 IN TIMESTAMP
, boolean1 IN numeric
)
AS

TYPE lt_trans_id IS TABLE OF trans.trans_id%TYPE ;
la_trans_id lt_trans_id; -- Array on trans_id's

TYPE lt_trans_auth_id IS TABLE OF trans_auth.trans_auth_id%TYPE;
la_trans_auth_id lt_trans_auth_id;

-- Driving Cursor for purgable trans_id's
CURSOR c_trans_id IS
SELECT trans_id
FROM TRANS
WHERE SCHEDULED=1
AND ALLOWED=1
AND TRAINING=boolean1
-- AND ROWNUM<20000
;

CURSOR c_trans_auth_id IS
SELECT t.trans_auth_id
FROM TRANS_AUTH t
WHERE t.SCHEDULED=1
AND t.ALLOWED=1
AND trans_auth_id NOT IN
( SELECT trans_auth_id FROM TRANS_LINE_AUTH_XREF WHERE trans_auth_id=t.trans_auth_id
UNION ALL
SELECT trans_auth_id FROM TRANS_AUTH_XREF WHERE trans_auth_id=t.trans_auth_id
UNION ALL
SELECT trans_auth_id FROM TRANS_TENDER_AUTH_XREF WHERE trans_auth_id=t.trans_auth_id
);

array_size NUMBER:=10000; -- process array size
-- careful uses pga .. session memory not database sga memory
-- number of rows to process each time

expected_trans NUMBER:=0;
processed NUMBER:=0;
expected_trans_auth NUMBER:=0;
processed_trans_auth NUMBER:=0;
long_ops_rindex PLS_INTEGER;
long_ops_slno PLS_INTEGER;
active_table VARCHAR2(30);

BEGIN

DBMS_APPLICATION_INFO.set_module(module_name => 'SP_PURGETRANS_DELETE', action_name => 'Expected Calculation');
--DBMS_OUTPUT.PUT_LINE('Start EXPECTED Calculation '||to_char(sysdate,'MM/DD HH24:MI:SS'));
-- Retrieve the count of records to be deleted
SELECT COUNT(TRANS_ID)
INTO expected_trans
FROM TRANS
WHERE SCHEDULED = 1
AND ALLOWED=1
AND TRAINING=boolean1;
--DBMS_OUTPUT.PUT_LINE('... expected result count ('||TO_CHAR(expected_trans,'9999999999')||') '||to_char(sysdate,'MM/DD HH24:MI:SS'));

OPEN c_trans_id;
LOOP

-- loop though in array_size increments
FETCH c_trans_id BULK COLLECT INTO la_trans_id LIMIT array_size;

-- Updated for VI 1298 purge training transaction process
-- Deleting records from Signature table
-- *** Has to occur before TRANS_TENDER_SIGNATURE ... which currently is not in this routine.
-- ** Thus no dependancies

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM SIGNATURE s WHERE SIGNATURE_ID IN
( SELECT SIGNATURE_ID FROM TRANS_TENDER_SIGNATURE
WHERE signature_id= s.signature_id
AND trans_id = la_trans_id(i) );

-- Added as art of re-write
FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_TENDER_SIGNATURE WHERE trans_id = la_trans_id(i);

-- - Order was determined using a home grown depth first directed graph sort routine
-- based on the constraint partent child dependancy tree
--
FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LINE_ASSOCIATE WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_CUSTOMER WHERE trans_id = la_trans_id(i);

-- Added as art of re-write
FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_AUTH_XREF WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LINE_AUTH_XREF WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_TENDER_AUTH_XREF WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_ASSOCIATE WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_DISCOUNT_SPREAD WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_DISCOUNT WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_COUPON WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LOCATION WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_TAX WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LOST_AUTH WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LN_PRMT_DTL WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LINE_PROMPT WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LINE_DISC WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LINE_TAX WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LINE_PRICE WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_LINE WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_PROMPT_DTL WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_PROMPT WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_TENDER WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_MULTI_CHANNEL WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_REBATE_XREF WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_REBATE WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_BOUNCEBACK WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS_CASH_OUT WHERE trans_id = la_trans_id(i);

FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM SECURITY_OVERRIDE_LOG WHERE TRANSACTION_NUMBER IN
( SELECT x.TRANSACTION_NUMBER FROM TRANS x
WHERE x.trans_id = la_trans_id(i) );

-- FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
-- DELETE FROM TRANSACTION_NUMBER WHERE trans_id = la_trans_id(i);

-- Driving table ... last to be purged!
FORALL i IN la_trans_id.FIRST..la_trans_id.LAST
DELETE FROM TRANS WHERE trans_id = la_trans_id(i);

COMMIT;
processed:=processed+la_trans_id.COUNT;
--DBMS_OUTPUT.PUT_LINE('... PROCESSED: '||PROCESSED||' '||to_char(sysdate,'MM/DD HH24:MI:SS'));
DBMS_APPLICATION_INFO.SET_ACTION('Trans_id:'||processed||'/'||expected_trans);

EXIT WHEN c_trans_id%NOTFOUND;

END LOOP;
close c_trans_id;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-03 : 14:56:07

looks like Oracle code. This is MS SQL Server forum. please post in some Oracle forums like www.dbforums.com for getting Oracle specfific help

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources