| Sequence ? - Invalid reference to variable [message #374285] |
Mon, 04 June 2001 10:39  |
iwilliam
Messages: 2
Registered: June 2001
|
Junior Member |
|
|
I hv sequence ?
when i run this script, i am getting the error
ERROR at line 27:
ORA-06550: line 27, column 27:
PLS-00487: Invalid reference to variable 'LOAD_SEQ'
ORA-06550: line 24, column 5:
PL/SQL: SQL Statement ignored
----- here is the script
DECLARE
load_seq NUMBER(2);
stupidm NUMBER(8);
entry_term VARCHAR(6);
sbgi_code VARCHAR2(6);
hold_pidm NUMBER(8) := 0;
CURSOR c1 IS SELECT
STUDENT.PIDM,STUDENT.TERM_CODE_ENTRY,STUAVTY.SBGI_CODE
FROM STUDENT,STUAVTY
WHERE STUDENT.PIDM=STUAVTY.PIDM
AND STUDENT.PIDM IS NOT NULL
AND NOT EXISTS (SELECT 'X' FROM SRRRSRC
WHERE SRRRSRC_PIDM=STUDENT.PIDM
AND SRRRSRC_TERM_CODE=STUDENT.TERM_CODE_ENTRY)
ORDER BY STUDENT.PIDM;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO stupidm,entry_term,sbgi_code;
EXIT WHEN c1%NOTFOUND;
IF STUPIDM<>hold_pidm AND hold_pidm<>0 THEN
load_seq:=0;
END IF;
INSERT INTO SRRRSRC_TEMP
(SRRRSRC_ADMIN_SEQNO,SRRRSRC_PIDM,SRRRSRC_TERM_CODE,SRRRSRC_SBGI_CODE,
SRRRSRC_ACTIVITY_DATE)
VALUES (stupidm,load_seq.nextval,entry_term,sbgi_code,SYSDATE);
COMMIT;
hold_pidm:=stupidm;
END LOOP;
END;
/
---
please help me solve the above problem.
thanks in advance..
Ivory W
|
|
|
|
| Re: Sequence ? - Invalid reference to variable [message #374289 is a reply to message #374285] |
Mon, 04 June 2001 12:24  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
It seems that your sequence number generator and your local variable have the same name.
something like this colud help...
INSERT INTO SRRRSRC_TEMP
(SRRRSRC_ADMIN_SEQNO,SRRRSRC_PIDM,SRRRSRC_TERM_CODE,SRRRSRC_SBGI_CODE,
SRRRSRC_ACTIVITY_DATE)
VALUES (stupidm,
NVL(my_load_seq_var, load_seq.nextval)
,entry_term,sbgi_code,SYSDATE);
i.e. whenever my_load_seq_var is null, then load_seq.nextval is used.
Also - be careful of embedding commits in your code. For re-usability the calling application should cedide when to commit or rollback. You generally don't wan't them in the middle of a transaction.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
