All I'm doing is taking a list and renumbering a column to make sure the
values are sequential from 1 to N.
It compiles fine, but when I run it I get:
ORA-00936: missing expression
ORA-06512: at "DFS.SP_SETLISTORDER", line 14
- CREATE OR REPLACE PROCEDURE sp_SetListOrder (ProjectID Integer) IS
- TYPE projCursor IS REF CURSOR;
3 pCur projCursor;
4 i number := 1;
5 ProjCPID number;
6 ProjItemOrder number;
7 cSQL VARCHAR2(1000);
8 BEGIN
9 CSQL := 'SELECT ProjCPID, ItemOrder FROM Table_Project WHERE ProjID =
:ProjectID ORDER BY ItemOrder';
10 OPEN pCur FOR cSQL USING ProjectID;
11 LOOP
12 FETCH pCur INTO ProjCPID, ProjItemOrder;
13 EXIT WHEN pCur%NOTFOUND;
14 EXECUTE IMMEDIATE 'UPDATE Table_Project SET ItemOrder = ||i||
WHERE ProjCPID = ||ProjCPID ||';
15 i := i + 1;
16 END LOOP;
17 CLOSE pCur;
18 END;
Thanks
Received on Wed Oct 12 2005 - 22:56:18 CDT
