PL-SQL

Procedure with cursor:
---------------------------

create or replace PROCEDURE               "PlaPROC" AS

CURSOR cr_update IS
select su.subscriberuser_id,
       su.subscriberuser_pid secondary_user_subuser_pid,
       su3.subscriberuser_pid default_subuser_pid,
       su3.subscriberuser_pid
       || substr(su.subscriberuser_pid, instr(su.subscriberuser_pid,'_'), 4) revised_subuser_pid
from subscriberuser su
join subscriberuser su3 on su.subscriber_id = su3.subscriber_id
where su.usertype_id = 2
and exists (select * from subscriberuser su2
            where su.subscriber_id = su2.subscriber_id
            and su2.subscriberuser_id between 10714703 and 10983638)
and su3.usertype_id = 1
and su.subscriberuser_id between 8617661 and 10714702
and not exists (select * from subscriberuser su4
                where su3.subscriberuser_pid
               || substr(su.subscriberuser_pid, instr(su.subscriberuser_pid,'_'), 4) = su4.subscriberuser_pid)
and su.subscriber_id not in (
1234,
5678);

counter NUMBER(9) := 0;
counter2 NUMBER(9) := 0;

BEGIN
//log
  DBMS_OUTPUT.PUT_LINE(to_char(sysdate,   'DD-MON-YYYY HH24:MI:SS') || ' Starting update to subscriber_pid in SUBSCRIBERUSER.');

  FOR cr_rec IN cr_update
  LOOP
    BEGIN
      counter := counter + 1;
      counter2 := counter2 + 1;

      UPDATE subscriberuser
      SET subscriberuser_pid = cr_rec.revised_subuser_pid
      WHERE subscriberuser_id = cr_rec.subscriberuser_id;

      IF counter2 = 10000 THEN
        COMMIT;
        counter2 := 0;
      END IF;

    END;
  END LOOP;

  COMMIT;

  DBMS_OUTPUT.PUT_LINE(to_char(sysdate,   'DD-MON-YYYY HH24:MI:SS') || ' - ' || counter || ' SUBSCRIBERUSER records updated');
  NULL;

END PlaPROC;

How to call a stored procedure from shell program?

sqlplus "userid/pwd" <<!
set serveroutput on size 1000000
exec my_storedProc;
!