Other Oracle database topics - A little help debugging this procedure, please

Author

A little help debugging this procedure, please

DFS

2005-08-28, 7:23 am

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

Sybrand Bakker

2005-08-28, 7:23 am

On Sun, 28 Aug 2005 04:50:41 -0400, "DFS" <nospam@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

Dave

2005-08-28, 7:23 am

"DFS" <nospam@dfs_.com> wrote in message
news:BYeQe.6248$Yh6.4077@fe04.lga...
> 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?
>

]

how about posting the error you get and the version you use
>

Dave

2005-08-28, 7:23 am

"DFS" <nospam@dfs_.com> wrote in message
news:BYeQe.6248$Yh6.4077@fe04.lga...

CREATE OR REPLACE PROCEDURE SetEventNbr (FacID IN 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
UPDATE TABLE SET FIELD4 = i WHERE FIELD1 = cObject.FIELD1;

CURSOR cursor2 IS
SELECT FIELD1
FROM TABLE WHERE FIELD2 = FacID AND FIELD3 = cObject.FIELD1;

FOR cObject2 in cursor2 LOOP
UPDATE TABLE SET FIELD4 = (i + (j / 100)) WHERE FIELD1 =
cObject2.FIELD1;
j := j + 1;
END LOOP;
i := i + 1;
END LOOP;
END SetEventNbr;

DA Morgan

2005-08-28, 1:23 pm

Dave wrote:
> "DFS" <nospam@dfs_.com> wrote in message
> news:BYeQe.6248$Yh6.4077@fe04.lga...
>
>
> CREATE OR REPLACE PROCEDURE SetEventNbr (FacID IN 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
> UPDATE TABLE SET FIELD4 = i WHERE FIELD1 = cObject.FIELD1;
>
> CURSOR cursor2 IS
> SELECT FIELD1
> FROM TABLE WHERE FIELD2 = FacID AND FIELD3 = cObject.FIELD1;
>
> FOR cObject2 in cursor2 LOOP
> UPDATE TABLE SET FIELD4 = (i + (j / 100)) WHERE FIELD1 =
> cObject2.FIELD1;
> j := j + 1;
> END LOOP;
> i := i + 1;
> END LOOP;
> END SetEventNbr;

This is an inappropriate use of a CURSOR. Review the syntax for
bulk collection.

www.psoug.org
click on Morgan's Library
click on Bulk Collection
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)

Sponsored Links