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

From: George <george_at_mighty.co.za>
Date: Thu, 23 Nov 2006 06:26:59 +0200 (SAST)
Message-ID: <41550.193.32.3.83.1164256019.squirrel@mail.mighty.co.za>

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_at_mighty.co.za

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

Received on Wed Nov 22 2006 - 22:26:59 CST