| ORA-01036 illegal variable name/number Error in Oracle 9i [message #10841] |
Thu, 19 February 2004 22:54 |
K. Sreekumar
Messages: 6
Registered: May 2003
|
Junior Member |
|
|
We have our database in Oracle 7 workgroup server and in the front end we are using delphi 4 with BDE.
Recently we have migrated our db to Oracle 9i and in the front end we are now using delphi 7 with DB Express.
While executing one stored procedure with the new setup we have received the error
"ORA-01036 illegal variable name/number"
But the procedure is working fine in TOAD.
The procedure code is as given below.
CREATE OR REPLACE PROCEDURE Spcreplicateservicedistricts (
iREGISTRATIONID IN NUMBER,
iSERVICEID IN NUMBER,
iSERVICEVERSION IN NUMBER,
iNEWSERVICEVERSION IN NUMBER
) AS BEGIN
/* Purge any old entries just in case there are any */
DELETE SERVICEDISTRICT
WHERE REGISTRATIONID = iREGISTRATIONID
AND SERVICEID = iSERVICEID
AND SERVICEVERSION = iNEWSERVICEVERSION;
INSERT INTO SERVICEDISTRICT (REGISTRATIONID, SERVICEID, SERVICEVERSION,
DISTRICTID, UPDCOUNT)
SELECT REGISTRATIONID, SERVICEID, iNEWSERVICEVERSION,
DISTRICTID, UPDCOUNT
FROM SERVICEDISTRICT
WHERE REGISTRATIONID = iREGISTRATIONID
AND SERVICEID = iSERVICEID
AND SERVICEVERSION = iSERVICEVERSION;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20000,'No data found to delete');
WHEN DUP_VAL_ON_INDEX THEN
RAISE_APPLICATION_ERROR(-20000,'Can not insert duplicate rows');
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20000, 'Spcreplicateservicedistricts: '||SQLERRM);
END;
/
Can anyone suggest the solution ?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
