I have a script that I created that works fine in SQL Server, but now I need to make it work in Oracle and I am having issues getting the script converted.
Here is my SQL Server Script:
-- run the commented out statement in another query analyzer window to stop the script
-- update ##stopsign set val = 1
set nocount on
--declare variables
declare @morework int
declare @archivecount int
declare @stopsign int
--if working tables exists clear them, if not create and initialize them
if (object_id('tempdb..##stopsign') is null)
create table ##stopsign (val int)
else
delete from ##stopsign
insert into ##stopsign values (0)
if (object_id('tempdb..#tempdins') is null)
create table #tempdins (tempdin varchar(255) not null, processed int null)
else
delete from #tempdins
--initialize #tempdins working table with all the records eligible to be unarchived
--edit the select statement if needed to change the records to be unarchived
insert into #tempdins(tempdin)
select tempdin
from document
where archivestatus = 'C'
and status = 'U'
option (MAXDOP 1)
--inialize variables with current values
select @archivecount = (select count(*) from unarchs)
select @stopsign = (select val from ##stopsign)
select @morework = 1
--while there is more to do, unarchs table has less then 1000 records, and the stopsign value is 0 loop
while (@morework >= 1 and @stopsign = 0)
begin
if @archivecount <1000
begin
-- number to be processed at once
-- change this value if you would like to dump more in to the unarchs table at once
set rowcount 100
update #tempdins
set processed = 0
where processed is null
--reset rowcount
set rowcount 0
--populate the unarchs table with valid values
--this will unarchive at the page (lowest) level
insert into unarchs (drawer,foldernumber,packageid,docid,pagenumber,unarchtype,unarchdate,unarchtime,userid,unarchdays)
select distinct drawer,foldernumber,packageid,docid,pagenumber,'Page','20061128','12:00:00','ADMIN',360
from document
where tempdin in (select tempdin
from #tempdins
where processed = 0)
--update with rowcount to see if finished
select @morework = @@rowcount
--set the tempdins to processed in working table
update #tempdins
set processed = 1
where processed = 0
--get new counts for variables for evaulation
select @archivecount = (select count(*) from unarchs)
select @stopsign = (select val from ##stopsign)
--wait a second so the CPU doesn't spin
waitfor delay '00:00:01'
end
else
begin
--get new counts for variables for evaulation
select @archivecount = (select count(*) from unarchs)
select @stopsign = (select val from ##stopsign)
--wait a second so the CPU doesn't spin
waitfor delay '00:00:01'
end
end
set nocount off
Here is what I have for ORACLE so far (writing in PL/SQL):
-- run the commented out statement in another query analyzer window to stop the script
-- update ##stopsign set val = 1
--if working tables exists clear them, if not create and initialize them
declare
v_sql LONG;
begin
v_sql:='CREATE GLOBAL TEMPORARY TABLE STOPSIGN;
(
VAL int
)';
execute immediate v_sql;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL; -- suppresses ORA-00955 exception
ELSE
delete from STOPSIGN;
END IF;
END;
/
insert into STOPSIGN values (0);
--if working tables exists clear them, if not create and initialize them
declare
v_sql LONG;
begin
v_sql:='CREATE GLOBAL TEMPORARY TABLE TEMPDINS;
(
tempdin varch(255),
processed int null
)';
execute immediate v_sql;
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -955 THEN
NULL; -- suppresses ORA-00955 exception
ELSE
delete from TEMPDINS;
END IF;
END;
/
--initialize #tempdins working table with all the records eligible to be unarchived
--edit the select statement if needed to change the records to be unarchived
insert into TEMPDINS(tempdin)
select * from (select tempdin
from document join packtype on packtype.packagetype=document.packagetype
and archivestatus = 'C' and ADRIVE is not null
and ADRIVE <> DRIVE ) where ROWNUM < 10;
--inialize variables with current values
Declare
archivecount int;
stopsign int;
morework int;
Begin
Select count(*) INTO archivecount from UNARCHS;
Select VAL into stopsign from STOPSIGN;
morework := 1;
END
--while there is more to do, unarchs table has less then 1000 records, and the stopsign value is 0 loop
WHILE morework > 0 and stopsign = 0
LOOP{
begin
if archivecount <1000
begin
-- number to be processed at once
-- change this value if you would like to dump more in to the unarchs table at once
set rowcount 100
update TEMPDINS
set processed = 0
where processed is null
}
--reset rowcount
set rowcount 0
--populate the unarchs table with valid values
--this will unarchive at the page (lowest) level
insert into UNARCHS (drawer,foldernumber,packageid,docid,pagenumber,unarchtype,unarchdate,unarchtime,userid,unarchdays)
select distinct drawer,foldernumber,packageid,docid,pagenumber,'Page','20061128','12:00:00','ADMIN',360
from DOCUMENT
where tempdin in (select tempdin
from TEMPDINS
where processed = 0)
--update with rowcount to see if finished
select morework = select NUM_ROWS into morework from user_tables where table_name = 'UNARCHS'
--set the tempdins to processed in working table
update TEMPDINS
set processed = 1
where processed = 0
--get new counts for variables for evaulation
select archivecount = (select count(*) from unarchs)
select stopsign = (select val from STOPSIGN)
--wait a second so the CPU doesn't spin
waitfor delay '00:00:01'
end
else
begin
--get new counts for variables for evaulation
select archivecount = (select count(*) from unarchs)
select stopsign = (select val from STOPSIGN)
--wait a second so the CPU doesn't spin
waitfor delay '00:00:01'
end
end
End
END IF
END LOOP
Any help would be appreciated. My company has no Oracle resources for me to go to and Google is getting tired of me.
