Please help, what am i missing, sql in ksh script.

Hi all


Ok, I know this should be easy and I am amazed that I am not getting it
right.

Client has odd couple of thousand of scripts,

Trying to add some stored procedures/package calls with variables.
Sp_snapsys.snaplog(?) and dbms_application(?)


If I log in via sqlplus manually and paste/execute the anonymous block
(from the declare to the end;) then it works. If I execute it by calling
tst.ksh as below, she not work.

What am I missing.

explanation, what i mean by does not work. when executed by pasting it
into sqlplus the final result currently is 4 records into a output/logging
table. when run via the script, no output as if the stored procedure
sp_snapsys.snaplog is not being called.


--- start of script:   tst.ksh ----
#!/bin/ksh

echo "TST Started At : " `date`
###################################
## Part 1 - Load from input file ##
###################################
##

## insert into snapsys_sysnames (name,active_ind) values('HOBATCH','R');
## commit;
## insert into snapsys_objnames (sysseq_num,name,active_ind) values
(1,'TST.step1.complete','Y') ;
## insert into snapsys_objnames (sysseq_num,name,active_ind) values
(1,'TST.step33.complete','Y');

## insert into snapsys_objnames (sysseq_num,name,active_ind) values
(1,'TST.completed','Y')      ;
## commit;

sqlplus -s snaps/snaps << EOD1
declare
            d_start   timestamp;
            d_start1  timestamp;
            n_loopcnt number;

begin
            d_start  := systimestamp;
            d_start1 := systimestamp;

            dbms_application_info.set_module('tst.sh', 'Starting');
            -- do some prep work
            dbms_application_info.set_module('tst.sh', 'Step 1 Started');

            -- lets waist some time
            FOR n_loopcnt IN 1..1000000
            LOOP
                       null;
            END LOOP;
            sp_snapsys.snaplog('HOBATCH','TST.step1.complete','DAWA',
d_start1, '');


            dbms_application_info.set_module('tst.sh', 'Step 33 Started');
            d_start1 := systimestamp;

            -- waste some more time to show a delta between d_start1 ad
when snaplog is called.
            FOR n_loopcnt IN 1..10000000
            LOOP
                        null;
            END LOOP;
            sp_snapsys.snaplog('HOBATCH','TST.step33.complete','DAWA',
d_start1, '');

            sp_snapsys.snaplog('HOBATCH','TST.completed','DAWA', d_start,
'');

end;
EOD1

echo "TST Ended At : " `date`
--- end of script ---


------------------------------------------------
George

george@xxxxxxxxxxxx

You Have The Obligation to Inform One Honestly of the risk, And As a Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk,
Every Fool Has the Right to Kill or Injure Themselves as They See Fit!



-------------------------------------------
For super low premiums, click here http://www.webmail.co.za/dd.pwm

--
http://www.freelists.org/webpage/oracle-l

Other related posts: