Runs in PL/SQL not in Shell Script 1

[cwsstins]

I have a SQL script (largely revised by Mufasa) that runs fine when I run it in a blank program window in PL/SQL. It is designed to create multiple txt files and it does.

But when I run it from my KSH shell script, nothing happens. No files are created and the shell script just hangs indefinitely. The command in the shell script to run the SQL script is: sqlplus -s user/pwd @$SQL_DIR/test.sql

What is it about the code that KSH doesn't like running it?

--set feedback off;
--set long 99000;
--set serveroutput on size 1000000;
--set escape '\'
Declare
    row_cnt number;
    max_row number;
    file_cnt number;

    l_file_hdl utl_file.file_type;

    procedure prt (str_in varchar2) is
      begin
          utl_file.put_line(l_file_hdl,str_in);
      end;

    procedure fmt (label_in varchar2,content varchar2) is
      begin
          prt('    <'||label_in||'>'||content||'</'||label_in||'>');
      end;

BEGIN

  file_cnt :=1;
  max_row :=100;
  row_cnt := 0;

  l_file_hdl:= utl_file.fopen('/apps/heatstg/htcs/data/test','ccs45_'||file_cnt||'.txt','W',32767);
  prt('<?xml version="1.0" encoding="UTF-8"?>');
  prt('<test_import xsi:noNamespaceSchemaLocation="[URL unfurl="true"]http://xml.test.com/test_import_v1.2.xsd"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">');[/URL]


  for t in (select * from tlb1 
               where fld1='2006-11-07'
                 and fld2 = 'test')
  LOOP
    if row_cnt = max_row then
     prt( '</test_import>');
     utl_file.fclose(l_file_hdl);
     file_cnt := file_cnt + 1;
     l_file_hdl:= utl_file.fopen('/apps/heatstg/htcs/data/test','ccs45_'||file_cnt||'.txt','W',32767);
     prt('<?xml version="1.0" encoding="UTF-8"?>');
     prt('<test_import xsi:noNamespaceSchemaLocation="[URL unfurl="true"]http://xml.test.com/test_import_v1.2.xsd"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">');[/URL]
     prt( '</test_import>');
     row_cnt := 0;
    end if;


    row_cnt:=row_cnt+1;

    prt('<test>');

	...more data...
	
    prt('</test>');


END LOOP;

if row_cnt <> 0 then
   prt( '</test_import>');
   utl_file.fclose(l_file_hdl);
end if;

end;

--exit;

 

[Dagon]

You've got the "exit" commented out, so the script will not terminate. It will simply sit in SQL*Plus waiting for more commands. Also you need to put a "/" after your PL/SQL block to get it to execute.

 

[cwsstins]

ARGH! I can't believe that. I had commented that out, and removed the "/" to get it to run in the test PL/SQL window. Forgot to add them back once I got it working there. Thanks a lot!