Re: A little help debugging this procedure, please

On Sun, 28 Aug 2005 04:50:41 -0400, "DFS" <nospam_at_dfs_.com> wrote:

>I'm not quite getting it. It compiles with errors, and won't run.
>
>It contains nested cursors, with the inner one (cursor2) using values from
>the outer one (cursor1). Probably the cursor2 should be declared outside
>the first FOR...LOOP?
>
>===================================================
>CREATE OR REPLACE PROCEDURE SetEventNbr (FacID INTEGER) IS
>BEGIN
>
>DECLARE
>i NUMBER := 1;
>j NUMBER := 1;
>execObject VARCHAR2(200);
>objectCursor INTEGER;
>
>CURSOR cursor1 IS
> SELECT FIELD1
> FROM TABLE
> WHERE FIELD2 = || FacID ||
>
> FOR cObject IN cursor1 LOOP
> execObject := 'UPDATE TABLE SET FIELD4 = ' || i || ' WHERE FIELD1 = ' ||
>cObject.FIELD1 || ';
> DBMS_SQL.PARSE(DBMS_SQL.OPEN_CURSOR, execObject, DBMS_SQL.NATIVE);
>
> CURSOR cursor2 IS
> SELECT FIELD1
> FROM TABLE WHERE FIELD2 = ' || FacID || ' AND FIELD3 = ' ||
>cObject.FIELD1 || ';
> j := 1;
> FOR cObject2 in cursor2 LOOP
> execObject := 'UPDATE TABLE SET FIELD4 = || (i + (j / 100)) || WHERE
>FIELD1 = ' || cObject2.FIELD1 || ';
> DBMS_SQL.PARSE(DBMS_SQL.OPEN_CURSOR, execObject, DBMS_SQL.NATIVE);
> j := j + 1;
> END LOOP;
>
> i := i + 1;
> END LOOP;
> END SetEventNbr;
>===================================================
>
>Thanks for your help
>
>

There is a true plethora of errors here, which raises doubt as to whether you rather shouldn't review basic pl/sql syntax.

As to the first cursor definition
TABLE is a reserved word.
The || symbol is used for *string* concatenations. Assuming field2 is a number, the || are unnecessary. If field2 is a character string the statement should have been
field2 = to_char(id)
Cursor definitions should be closed with a semicolon ; You can define a cursor inside a for loop only it is has it's own declare
begin
end
structure

The last || in the first execobject statement is incorrect, and needs to be deleted with the last ' on that line.

The definition of cursor2 is incorrect. It has the problems of cursor1 and the first execobject definition combined.

The second execobject definition is incorrect.

If you use dbms_sql.open_cursor in every call to dbms_sql.parse, you don't understand the dbms_sql package. Your code will open a cursor handle for every parse. Those cursor handles won't close, so you will soon run out of cursor.

--
Sybrand Bakker, Senior Oracle DBA