Oracle Database Discussions

Hello guys, I have a pl/sql script which is updating a table and i put it in a .sql file and am running it in the sqlplus. since that pl/sql script has 13,000 lines and toad gets hanged when executed with that many lines, hence I am using sqlplus.

The problem is the sqlplus output shows all the lines of the pl/sql being executed and when the script finishes, the cursor doesn't comeback to the sql prompt and just stays in the script.

I tried many things such as executing only the pl/sql code and adding forward slash at the end of the pl/sql block. I am still unable to figure out if the problem is in the script or if the script executed successfully and is waiting for some other event.

#############################################################################################################

tbl_insert.sql::

spool /u02/output/plsql.log;

select substr(user || '@' || upper(sys_context('userenv','instance_name')), 1, 30) AS "USER" from dual;

show user;

set echo on;

set define off;

set serveroutput on size 100000;

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') Start_Time from dual;

set verify off

set define off

DECLARE

v_modified_by user_Account.user_Acct_Sid%type:=75412895;---change this before running script

  v_tot_rows_affected number(15):=0;

CURSOR CUR_PA_WITHOUT_SPCLTY

IS

  SELECT DISTINCT Tracking_No.IDNTFR, PRS.PA_rQST_SRVC_SID, Tracking_No.spclty_to_copy

FROM

(

  SELECT 1 AS SNO,'010002320' AS IDNTFR,'35' AS spclty_to_copy FROM DUAL UNION ALL

  SELECT 2 AS SNO,'010022144' AS IDNTFR,'EN' AS spclty_to_copy FROM DUAL UNION ALL

  SELECT 3 AS SNO,'010040899' AS IDNTFR,'AL' AS spclty_to_copy FROM DUAL UNION ALL

       ---

       ---

       ---

       ---

    (13,000 select lines)

)

BEGIN

FOR i in cur_pa_without_spclty

loop

  UPDATE PA_REQUEST_SERVICE PRS

  SET PRS.SPCLTY_LKPCD=i.spclty_to_copy,

      modified_by=v_modified_by,

where prs.pa_rqst_srvc_sid=i.pa_rqst_srvc_sid;

   v_tot_rows_affected :=v_tot_rows_affected +(sql%rowcount);

END LOOP;

dbms_output.put_line('v_tot_rows_affected -->'||v_tot_rows_affected);

dbms_output.put_line('Success');

EXCEPTION

WHEN OTHERS

THEN

dbms_output.put_line('Error Code-->'||sqlcode);

dbms_output.put_line('Error Message-->'||sqlerrm);

END ;

/

select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') End_Time from dual;

pause;

commit;

spool off;

#############################################################################################################