A problem with a cursor

[theworm]

We are having a problem getting a cursor to increment it's self. Can anybody please tell me what we might be doing wrong.

Thanks



SELECT DISTINCT max(LogCounter) AS LogCounter, LogNumber
FROM AppServerLog
GROUP BY LogNumber
HAVING count(LogNumber) > 1;

SELECT DISTINCT max(LogNumber) AS LogNumber, LogCounter
FROM AppServerLog
GROUP BY LogCounter
HAVING count(LogCounter) > 1;

BEGIN TRANSACTION DEDUPLICATE1
CREATE TABLE TempAppServerLog(
ID NUMBER(38),
ClassType VARCHAR(150),
Message VARCHAR(4000),
UserName VARCHAR(150),
AppServerName VARCHAR(150),
LogType VARCHAR(150),
ExceptionClassName VARCHAR(150),
LogNumber NUMBER(38),
LogCounter NUMBER(38) DEFAULT '0',
Time NUMBER(38))
PCTFREE 10
PCTUSED 60
INITRANS 8
STORAGE(
INITIAL 100M
NEXT 100M
MAXEXTENTS UNLIMITED
FREELISTS 8
FREELIST GROUPS 4
PCTINCREASE 0);


INSERT INTO TempAppServerLog(
ClassType,
Message,
UserName,
AppServerName,
LogType,
ExceptionClassName,
LogNumber,
LogCounter,
Time)
SELECT DISTINCT
ClassType,
Message,
UserName,
AppServerName,
LogType,
ExceptionClassName,
LogNumber,
LogCounter,
Time
FROM AppServerLog;

TRUNCATE TABLE AppServerLog;

CURSOR LogNumbersCursor
IS
SELECT LogNumber
FROM TempAppServerLog;

DECLARE v_logCounter NUMBER(38) := NULL;
DECLARE v_logNumber NUMBER(38) := NULL;
DECLARE v_newPK NUMBER(38) := NULL;

SELECT 0 INTO v_logCounter FROM Dual;

BEGIN
FOR LogNumberRec IN LogNumbersCursor LOOP
v_newPK := WorkflowUtil.New_PK();
SELECT v_logCounter + 1 INTO v_logCounter FROM Dual;
INSERT INTO AppServerLog (
ID,
ClassType,
UserName,
AppServerName,
LogType,
ExceptionClassName,
LogNumber,
LogCounter,
Time,
Message)
SELECT
v_newPK,
ClassType,
UserName,
AppServerName,
LogType,
ExceptionClassName,
LogNumber,
v_logCounter,
Time,
Message
FROM TempAppServerLog
WHERE LogNumber = LogNumberRec;
EXCEPTION
WHEN OTHERS THEN
g_SQLErrCd := SQLCODE;
g_SQLErrMsg := SQLERRM(g_SQLErrCd);
RAISE_APPLICATION_ERROR(-20000, g_SQLErrMsg);
RETURN;
END LOOP;
END ;


CLOSE LogNumbersCursor;

DROP TABLE TempAppServerLog;

COMMIT TRANSACTION DEDUPLICATE1;

SELECT DISTINCT max(LogCounter) AS LogCounter, LogNumber
FROM AppServerLog
HAVING count(LogNumber) > 1
GROUP BY LogNumber;

SELECT DISTINCT max(LogNumber) AS LogNumber, LogCounter
FROM AppServerLog
HAVING count(LogCounter) > 1
GROUP BY LogCounter;

[LKBrwn_DBA]

Your PL/SQL block construct is incorrect, should look like this:


DECLARE

CURSOR LogNumbersCursor
IS
SELECT LogNumber
FROM TempAppServerLog;

v_logCounter NUMBER(38) := 0;
v_logNumber NUMBER(38) := 0;
v_newPK NUMBER(38) := 0;

BEGIN
FOR LogNumberRec IN LogNumbersCursor LOOP
...etc...
END LOOP;
...etc...
END;
:D