Re: using pl/sql to update a table

A copy of this was sent to ianr_ekha_at_my-dejanews.com (if that email address didn't require changing) On Mon, 26 Apr 1999 09:59:58 GMT, you wrote:

>I am trying to update one field in a table using the contents of a field in
>another table, using this script;
>
>declare
> CURSOR curs_temp_cds_id is select user_49, user_50 from
>cds_inpatients_load_temp;
> var_cds_id cds_inpatients_load_temp.user_49%TYPE;
> var_loc_spec cds_inpatients_load_temp.user_50%TYPE;
>begin
> open curs_temp_cds_id;
> loop
> fetch curs_temp_cds_id into var_cds_id, var_loc_spec;
> exit when curs_temp_cds_id%NOTFOUND;
> update cds_inpatients_load
> set user_50 = var_loc_spec
> where user_44 = var_cds_id;
> end loop;
> close curs_temp_cds_id;
>end;
>/
>

You could look into putting a call or two to dbms_application_info.set_client_info into your plsql block. this lets you populate a column in v$session (you can populate 3 of these columns actually using other functions in the dbms_application_info package). For example:

SQL> exec dbms_application_info.set_client_info( 'Hello' );

PL/SQL procedure successfully completed.

SQL> select client_info from v$session where username = user;

CLIENT_INFO

That way, your plsql routine could put something like "processing row 55" into the client_info column and you could watch it in the other session to see how far its gotten and how fast its moving...

Do you have an index on cds_inpatients_load(user_44) ?? If not, this will be very slow as it full scans cds_inpatients_load for each row in cds_inpatients_load_temp.

You could instead do a simple update:

update cds_inpatients_load

   set user_50 = ( select user_50

                     from cds_inpatients_load_temp 
                    where cds_inpatients_load_temp.user_49 =
                                    cds_inpatients_load.user_44  )
 where exists ( select user_50 
                     from cds_inpatients_load_temp 
                    where cds_inpatients_load_temp.user_49 =
                                    cds_inpatients_load.user_44  )
                    

that update would like to have the cds_inpatients_load_temp table indexed on the user_49 column tho.

>The common field in the two tables is an ID located in user_44 of the
>cds_inpatients_load table and user_49 of the cds_inpatients_load_temp table,
>and the field which I wish to update is user_50 in the cds_inpatients_load,
>with the corresponding data in user_50 of the cds_inpatients_load_temp table.
>
>The above script ran without any feedback for a long period, does anyone have
>any suggestions what I am doing wrong? I am fairly happy with SQL, but new to
>PL/SQL.
>
>
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation