Cursor -- For Loop question 2

[CIMTEET]

There is an explicit cursor that has data passed to it from a for loop. This looks at table A. In cursor for loop i have an update statement that uses data retrieved from the cursor in the where statement before populating table B. The problem is the update statement itself has to have a column name in table B in the whwere clause::
WHERE column_name LIKE UPPER(%RV%);
Unfortunately I need it to do this
WHERE (data retreived from cursor using table A) LIKE UPPER(%RV%);

Is there anyway to attach the update statement to a retreived field from the cursor?

WHERE tableA.Field_data LIKE UPPER(%RV%)

The reason I thought this might be possible is the update statement for TABLE B is inside the Cursor for_loop for table A. hope this makes sense.

Greg

 

[SantaMufasa]

Greg,

I'm sure we can help you, but to be of utmost help, our seeing your actual code would be very useful. Would you be willing to post it here?

There is a great feature that will probably be useful/applicable to great advantage in your case...Oracle PL/SQL's "...WHERE CURRENT OF <cursor name>", which updates the table based upon the rowid of the current record in the CURSOR. But to be sure, please post your code (if it's not too propriety or lengthy. <smile>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

The Cursor tables are tbl_session_message and tbl_session_data. Update table is temp_id_map

I am having to type this manually because my online computer has nothing to do with Oracle, convenient eh?
Declare

Cursor get_source_track_map (p_ses_id NUMBER,p_msg_id NUMBER, p_pos_low NUMBER, p_pos_high NUMBER) IS
SELECT sm.unique_msg_id, sm.recorded_time, sm.sent_time, sd.position, sd.field_name, sd.field_data
FROM tbl_session_data sd, tbl_session_message sm
WHERE (sd.position BETWEEN p_pos_low AND p_pos_high) AND sd.unique_msg_id ……blah blah blah


Begin

For rec_source IN get_source_track_map_desc(n_ses_id,n_msg,n_pos_low,n_pos_high)
IF rec_source.position = n_pos_low THEN
n_object_id : = To_NUMBER(rec_source.field_data
n_wait := 0;
ELSIF rec_source.position = n_pos_high THEN
v_class := rec_source.field_data
n_wait := 1;
END IF
UPDATE temp_id_map tim SET tim.m_id = n_undetected
WHERE tim.object_id = n_object_id AND (UPPER(v_class) LIKE UPPER(‘%RV%’)) AND … blah blah

 

[LKBrwnDBA]

Your logic seems lacking:

1) TO_NUMBER() function is missing at least the closing parentesis and ending semicolon.
2) v_class has no default value when 'rec_source.position = n_pos' condition is true.
3) 'n_object_id' has no default value when 'rec_source.position = n_pos' condition is false.
4) 'v_class:=' is missing ending semicolon.
5) UPPER(‘%RV%’) is unnecesary (already upper case).

If these are typos, please post correct code.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Greg,

My suggestion about "...WHERE CURRENT OF..." does not apply here since you are updating a table that is not the focus of the CURSOR.

Nonetheless, the logic in the code you posted looks solid to me. Have you tried it yet? If so, did it not give you the results that you wanted?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[CIMTEET]

The last line of code has the UPPER(v_class), where I would need a column name. How would the WHERE CURRENT OF work out.

 

[CIMTEET]

The UPPER(V_class) returns a value of NONE. I think hte update statement is looking for a column name linked to temp_id_map. I keep getting an invalid identifier for NONE.

 

[LKBrwnDBA]

Check point #2 in my post.

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[CIMTEET]

the default value is ''