problems with variable conversion

[jaburke]

Hi all. I am writing a ksh script that runs a bunch of SQL. I have a sql command that does something like this:

v_seqno=$(sqlplus -S databaseinfo <<EOF
select seq_no from table;
EOF
)

I can echo $v_seqno and I see the result (i.e. 1916). Later on, I want to use that value in another query. So I do this:

select * from table2 where seq_no = $v_seqno;

I get this error:
1916)
*
ERROR at line 69:
ORA-00920: invalid relational operator

I have tried converting v_seqno to an integer using typeset -i and I get errors on that. I'm not sure what I'm doing wrong here. I'd appreciate any input.

 

[PHV]

What is the output of this command ?

echo "seq_no='$seq_no'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[feherke]

Hi

I would say the error is somewhere else around the second SQL [tt]select[/tt] you posted. Show us the shell commands used to execute that [tt]select[/tt].

Feherke.

http://rootshell.be/~feherke/

 

[jaburke]

Agh. I see the problem, I think. The $v_seqno variable contains this:

SEQ_NO
----------
1916

For some reason, I was seeing that but in my head it was just the 1916. Here's how I am capturing the variable:

v_seqno=$(sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT <<EOF
set linesize 150
set tab off
select seq_no from table1;
EOF
)

sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT <<EOF
set linesize 150
set tab off
select * from table2 where seq_no = $v_seqno;
EOF

So I guess the real question should be, how do I get just the value (1916) into my v_seqno variable instead of the column name, etc? Thanks for the replies on this!!! I am so rusty at ksh.

 

[PHV]

I'd try this:

v_seqno=$(echo "set linesize 150
set tab off
select seq_no from table1;" | sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT | tail -1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[feherke]

Hi

Maybe this ?

v_seqno=$(sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT <<EOF
set linesize 150
set tab off
[red]set heading off[/red]
select seq_no from table1;
EOF
)

Feherke.

http://rootshell.be/~feherke/

 

[jaburke]

I tried PHV's syntax and it came back with a blank for the value of v_seqno.

I tried this:
v_seqno=$(sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT <<EOF
set linesize 150
set feed off
set echo off
set head off
set tab off
select seq_no from table1;
EOF
)

Now the echo comes back with:
*
1916 *

So, we're getting closer.

 

[PHV]

And this ?

v_seqno=$(echo "set linesize 150
set tab off
select seq_no from table1;" |
sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT |
awk 'NF{i=$1}END{print i}')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jaburke]

I still get a blank when I do the echo after the command.

 

[PHV]

And this ?

v_seqno=$(echo "set linesize 150
set tab off
select seq_no from table1;" |
sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT |
awk '/[0-9]+/')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jaburke]

Still a blank. Maybe I am doing something wrong, but I am copying/pasting your command and then modifying to have the correct SQL statement. Then I do an echo '*' $v_seqno '*' and I get:
* *

 

[PHV]

the correct SQL statement
???

 

[feherke]

Hi

Then maybe an alternative, however not sure if Unix's [tt]tr[/tt] supports those swithces :

v_seqno=$(echo "set linesize 150
set tab off
select seq_no from table1;" |
sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT | [red]tr -dc '[:digit:]'[/red]
)

If not works, please post an output with some clear delimiters around the output :

v_seqno="$(echo "set linesize 150
set tab off
select seq_no from table1;" |
sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT)"

echo "|>$v_seqno<|"

And post it between [tt][ignore]

[/ignore][/tt] and [tt][ignore]

[/ignore][/tt] or [tt][ignore][tt][/ignore][/tt] and [tt][ignore][/tt][/ignore][/tt] tags to make more readable. ( See TGML for more. )

Feherke.

http://rootshell.be/~feherke/

 

[jaburke]

My example just showed select * from table1. I inserted the actual statement with my database table, etc.

I got it to work. Here's what I did:

v_seqno=$(sqlplus -S $DBSCHEMA/$DBPASS@$DBCONNECT <<EOF
set linesize 150
set feed off
set echo off
set head off
set tab off
set pages 0
select seq_no from table1;
EOF
)

I didn't have 'set pages 0' in there previously. That seems to have gotten rid of the newline. Now the echo returns:

* 1916 *

But, it does work. Thank you so much for all of your help.

 

[SamBones]

Here's how I've done it in the past and seems to work on my systems...

$!/bin/ksh

sqlplus -s /nolog <<-EOF | read V_SEQNO
   connect $DBSCHEMA/$DBPASS@$DBCONNECT

   set serverout on heading off linesize 200 autoprint on
   set tab off trimspool on echo off

   select seq_no from tabel1;

   exit;
EOF

print "V_SEQNO = ${V_SEQNO}"

 

[SamBones]

Here's some code I have from a running script that checks the row count of a table and performs some action based on whether it over a certain number of rows or not...

sqlplus -S /nolog <<-SQLCMDS | read ROW_COUNT
        connect ${USERID}
        set head        off
        set echo        off
        set feedback    off
        set verify      off
        set timing      off
        set time        off
        set trimspool   on
        set pages       0
        set linesize    20
        column ROW_COUNT format 999999990
        select count(*) ROW_COUNT from SOME_BIG_TABLE;
        exit 0
        SQLCMDS

(( $? )) && print "An Oracle error has occurred" && exit

print "There were ${ROW_COUNT} rows in SOME_BIG_TABLE."
print

if (( ROW_COUNT > 100000 ))
then
        print "That's over 100,000. Running too_many.sh"
        print
        too_many.sh
else
        print "That's under 100,000. Running normal_load.sh"
        print
        normal_load.sh
fi