Re: The sql in Bourne shell takes long time

frank.van.bortel_at_gmail.com wrote:
> lovecreaTEsbea..._at_gmail.com schreef:
>
> > Lars Tetzlaff wrote:
> > > lovecreaTEsbea..._at_gmail.com wrote:
> > > > I have a written following sql statements in the Bourne shell script.
> > > > But when I run the shell script, it takes a long time and doesn't give
> > > > out any result. I have to interrupt it by CTRl-C or kill -9. the code
> > > > has no syntax errors and the variables like $user, $pass and $sid have
> > > > been assigned before invoking sqlplus.
> > > >
> > > > #...
> > > >
> > > > UMASK=`umask`
> > > > umask 277
> > > > sqlplus -s "$user"/"$pass"_at_"$sid" <<-EOF > /dev/null 2>&1
> > > ^^^^^^^^^^^^^^^^
> > >
> > > why do you expeCT some output?
> >
> > thank you.
> >
> > You're right. I am sorry to post it incorrectly. I commented this part:
> > > /dev/null 2>&1 in first my code for TEsting purpose. It runs
> > correctly later, perhaps the table locked by anoher application.
>
> Nope... your own.
>
> You updaTE and use a seleCT - did you TEst this staTEment in a plain
> session?

[Quoted] Thank you. You may be right. I ran toad software at that time doing some things on the same table. My sql was completed and ran properly later without changing. Maybe the action in toad locked the table, or it was locked in my script itself. I have another problem now. Could you please help me out of it? Thank you.

[Quoted] Suppose I want to run a block of plsql script against the Oracle database through Korn shell. I mean it's not just a single SQL statement. I write it as following [1]; and I write the plsql block in a file, a1.sql and run it like in [2].

[Quoted] [Quoted] But neither works. How can I execute a block of plsql program in Korn shell?

[1] - inline plsql

#...

UMASK=`umask`
umask 277
sqlplus -s "$user"/"$pass"_at_"$sid" <<-EOF > /dev/null 2>&1

DECLARE i conf_sequence.seq_val%TYPE; /* plsql extension */

BEGIN SELECT seq_val
INTO i
FROM conf_sequence;

i := i + 1;

UPDATE conf_sequence
SET seq_val = i
WHERE sequence_id = '111';

END; EOF
umask "$UMASK"

#...

[Quoted] [2] - plsql in a1.sql

[Quoted] # Korn shell invokes a1.sql
#...

UMASK=`umask`
umask 277
[Quoted] cat "./a1.sql" | sqlplus -s "$user"/"$pass"_at_"$sid" umask "$UMASK"

#...

--a1.sql --
DECLARE i conf_sequence.seq_val%TYPE; /* plsql extension */

BEGIN SELECT seq_val
INTO i
FROM conf_sequence;

i := i + 1;

UPDATE conf_sequence
SET seq_val = i
WHERE sequence_id = '111';

END;
[Quoted] --a1.sql ends-- Received on Fri Nov 17 2006 - 06:31:47 CET