DBMS_SQL and cursor scope

From: <mvalek_at_ema-inc.com>
Date: 2000/04/12
Message-ID: <8d2m67$cgr$1@nnrp1.deja.com>#1/1

I am using DBMS_SQL to select a row from a table based on rowid. I use EXECUTE_AND_FETCH to fetch the row. I next do an update on the fetched row, also using DBMS_SQL, except using 'UPDATE .. where current of c1', where c1 is the cursor handle of the previously DBMS_SQL fetched row. Is that cursor out of scope with the call to DBMS_SQL.PARSE of the update statement? I receive an "ORA-00936: missing expression". Thanks for any help.

Matt

---

cursor_handle_update := DBMS_SQL.OPEN_CURSOR;

DBMS_SQL.PARSE

	(cursor_handle_update,
	 'SELECT * FROM '||p_table||' WHERE rowid = '''||p_row_id||'''',
	 DBMS_SQL.NATIVE);

l_update_status := DBMS_SQL.EXECUTE_AND_FETCH(cursor_handle_update, FALSE); IF l_update_status = 0 THEN

        RAISE UPDATE_FAILED;
END IF; cursor_handle := DBMS_SQL.OPEN_CURSOR;

---

• update current record as pointed to by cursor_handle_update

  ---

  FOR k IN 1..p_arg_names.count LOOP IF p_arg_values(k) IS NOT NULL THEN strQuery := 'UPDATE '||p_table||' SET '||p_arg_names(k) ||' = '||p_arg_values(k)||' WHERE CURRENT OF cursor_handle_update';

		DBMS_SQL.PARSE
				(cursor_handle,
				 strQuery,
				 DBMS_SQL.NATIVE);

		l_status := DBMS_SQL.EXECUTE(cursor_handle);

		IF l_status = 0 THEN
			rollback;
			RAISE UPDATE_FAILED;
		END IF;
	END IF;

END LOOP; Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Apr 12 2000 - 00:00:00 CDT