Re: The sql in Bourne shell takes long time
From: lovecreatesbea..._at_gmail.com <lovecreatesbeauty_at_gmail.com>
Date: 16 Nov 2006 21:53:22 -0800
Message-ID: <1163742802.860539.29380_at_m7g2000cwm.googlegroups.com>
> 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?
> 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.
> 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].
> But neither works. How can I execute a block of plsql program in Korn
> shell?
> #...
> 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"
> #...
> [2] - plsql in a1.sql
> # Korn shell invokes a1.sql
> #...
> UMASK=`umask`
> umask 277
> 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;
> --a1.sql ends--
Received on Fri Nov 17 2006 - 06:53:22 CET
Date: 16 Nov 2006 21:53:22 -0800
Message-ID: <1163742802.860539.29380_at_m7g2000cwm.googlegroups.com>
"lovecreatesbea..._at_gmail.com дµÀ£º"
> 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?
>
> 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.
>
> 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].
>
> But neither works. How can I execute a block of plsql program in Korn
> shell?
The plsql script may be parsed properly, both run without errors. What I mean is how to make the UPDATE statement within the plsql block execute and modify the field value in database table. Thank you.
> [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"
>
> #...
>
> [2] - plsql in a1.sql
>
> # Korn shell invokes a1.sql
> #...
>
> UMASK=`umask`
> umask 277
> 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;
> --a1.sql ends--
Received on Fri Nov 17 2006 - 06:53:22 CET