On 2005-10-13, DFS <nospam_at_dfs_.com> wrote:
> 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
>
> 1. CREATE OR REPLACE PROCEDURE sp_SetListOrder (ProjectID Integer) IS
> 2. 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;
It seems as though you solved the problem with the wrong quoting in the
execute immediate statement. But it remains unclear to me why
you use execute immediate at all. The statement is perfectly fit for
an ordinary update. Likewise, there is no reason why you quote the
select statement.
Rene
--
Rene Nyffenegger
http://www.adp-gmbh.ch/
Received on Thu Oct 13 2005 - 02:05:03 CDT
