Toad World® Forums

Script Working in Toad Quest Script Runner but not in Toad SQL*Plus

The script is pasted at the bottom of this post.

Here is the Script Runner Output:

start

loop 1

Update 1567471 F90183

loop 1

Update 7165 F91186

loop 1

Update 13112 F90660

loop 1

Update 9762 F91257

292 job records updated in NBRJOBS

PL/SQL procedure successfully completed.

Here is the output from SQL*Plus:

SQL*Plus: Release 12.1.0.1.0 Production on Tue Jul 19 13:24:37 2016

Copyright © 1982, 2013, Oracle. All rights reserved.

Last Successful login time: Tue Jul 19 2016 13:19:54 -06:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions

41

Each time Enter is pressed, SQLPlus displays the next number (ie. 41 42 43…). __A coworker is able to run this script in SQLPlus and I am able to run other scripts in SQL*Plus.__

Any suggestions would be appreciated. Thank you, Dave

Here is the script:

– Faculty Contracts - Base Salary Update
– This script updates NBRJOBS salary amounts based upon spreadsheet from Faculty Contracts.

set serveroutput on;
declare

CURSOR sal_upd_c IS
select * from dpace.base_salary_upd@BANI_BAND.UNM.EDU;

curr_rec nbrjobs%ROWTYPE;
ins_count NUMBER default 0;

begin
dbms_output.put_line(‘start’);
FOR rec IN sal_upd_c LOOP
begin
dbms_output.put_line(‘loop 1’);
select * into curr_rec from nbrjobs a
where nbrjobs_effective_date = (
select max(nbrjobs_effective_date) from nbrjobs
where nbrjobs_pidm = a.nbrjobs_pidm and
nbrjobs_posn = a.nbrjobs_posn and
nbrjobs_suff = a.nbrjobs_suff) and
nbrjobs_pidm = rec.pidm and
nbrjobs_ecls_code = rec.eclass and
nbrjobs_suff = ‘00’ and
nbrjobs_status = ‘A’;

    dbms_output.put_line('Update '||rec.pidm||' '||curr_rec.nbrjobs_posn);
 
    ins_count := ins_count + 1;

  exception
    when no_data_found then
      dbms_output.put_line ('Skipping '||rec.pidm||' no active job record');
    when others then
      dbms_output.put_line ('Skipping '||rec.pidm||' unknown error');
  end;
 
 
END LOOP;

dbms_output.put_line(ins_count||’ job records updated in NBRJOBS’);
end;

Sorry for the delay in getting your question answered. SQLplus is not a Toad product, it is an Oracle product. SQLplus is the editor that comes with your Oracle client. If you are having issues running a query in SQLplus, I would suggest you contact Oracle to find out why this is occurring.