why wont it run - sql script

[bookouri]

I have a simple script

declare
x, y, z etc

begin
do some select count(*)s into the x, y, z variables

insert x, y and z into a table

this script runs find if I execute it within TOAD, but if I execute it from plus33, plus33w, plus80, plus80w etc. the scripts claims to run, but no values are ever inserted into the table.

why wont this script run?

any suggestions would be appreciated..

 

[sem]

Could you explain how you run this script? Do you run it from file or from command line? Maybe you've ommited end; or
/ to really run it. And it'll be useful to provide more complete code, no matter it uses unknown table/column names, for the construct like "insert x, y and z into a table" is of course invalid.

 

[bookouri]

thanks

here is the bat file
C:\ORANT\BIN\PLUS33.EXE -silent bubba/bubbapass@live @counts.sql



here is a part of the sql

DECLARE

currentdate DATE;
totalcount NUMBER;
MaleCount NUMBER;
femalecount NUMBER;

begin
-- CurrentDate
SELECT SYSDATE INTO currentdate FROM dual;

-- TotalCount
SELECT COUNT(ID_DISPLAY) INTO totalcount
FROM names
WHERE
ACTIVE_FLAG='Y';

--MaleCount
SELECT COUNT(ID_DISPLAY) INTO malecount
FROM names
WHERE
ACTIVE_FLAG='Y'
AND
sex_code='M';

-- FemaleCount
SELECT COUNT(ID_DISPLAY) INTO femalecount
FROM names
WHERE
ACTIVE_FLAG='Y'
AND
sex_code='F';


-- insert statement to insert all variables into table
INSERT INTO COUNTS
VALUES(currentdate,totalcount,malecount,femalecount);
END;
/

I cut out 30 or 40 other counts being done.. the script takes about 6 minutes to run the whole thing...

I have discovered one thing... I ran the script using plus33w.exe. The script executes immediately from the command line, however.. when the script finishes I get a message stating the procedure successfully completed.. and it dumps me back to a sQL> prompt.. if I type commit and hit enter from here the table is updated. How to I get the script to commit and exit from sql> ??? I understood the / was supposed to commit the sql???

thanks again..

 

[bookouri]

progress on the script, I havnt fully tested it yet, but it appears that in the sql script I have to set autocommit on. I set it on in sqlplus from the menu, but it looks like it doesnt save the settings...

im not sure this will fix the problem, but it seems to be the right route. I thought the / was supposed to handle the commit and everything but that doesnt seem to be the case.

thanks again for any suggestions, input, etc...

 

[sem]

/ doesn't handle commit, but rather run the commands from a buffer.
To save autocommit as well as other settings you have to store them into login.sql script called every time when logging in.
If you add 'exit' to the end of your script, this commits your changes.

 

[bookouri]

I finally stumbled through all those pieces and have it working now... of course now my NT server is driving me nuts. Trying to use the AT command (like cron in unix) is almost impossible... cant seem to get the right combination to make it schedule properly.

thanks again