How can I pass a variable to a query inside a shell script?

Question

while IFS=# read -r process_id source destination type
do
        echo "Process id: $process_id"
        echo "Source: $source"
        echo "Destination: $destination"
        case "$type" in
                2)
                echo "Type is outbound: $type"
                        contact=$(sqlplus -s ${SQLPLUS_INFO} <<EOF
                        SET PAGESIZE 0
                        SELECT email FROM table WHERE partner = '${destination}';
                        exit
                        EOF
                        )
                echo
                echo ${contact}
                echo
                ;;

Based in the code above, how can I pass the value from $destination to the query? The example above is not working, even these other ones:

SELECT email FROM table WHERE partner = '"${destination}"';
SELECT email FROM table WHERE partner = '$destination';

take out the contact=$( and ) just to see if that is source of the problem. I would expect this to work as is, but the command-substitition ($( ...)) might be a problem. Good luck. — shellter
– shellter, Commented Jun 28, 2013 at 17:08
As a separate idea, add export destination before calling the contact=$(....). Good luck. — shellter
– shellter, Commented Jun 28, 2013 at 17:09
contact=sqlplus -s ${SQLPLUS_INFO} <<EOF SET PAGESIZE 0 SELECT email FROM table WHERE partner = '${destination}'; exit EOF Did not work as well, it returned: line 73: syntax error: unexpected end of file Just using the ( ) did not work as well, returning: syntax error near unexpected token `<<' . . PS: The export also did not work @shellter, @Emmanuel — Leandro Toshio Takeda
– Leandro Toshio Takeda, Commented Jun 28, 2013 at 17:58
no, I meant take the chars contact=$( AND ) (only).Add an exit statement right after EOF, to see if the sqlplus <<EOS ... EOS stuff is working, without worrying about the assigning to the variable contact. As usual, JonathanL is giving you excellent advice. I would add that you're getting into complicated territory, you'll have to slow down a bit and take the time to understand what each bit of syntax that is added to your script is doing, both in a basic sense as well as what you hope it is doing in your particular case. Good luck.! — shellter
– shellter, Commented Jun 28, 2013 at 18:47

Jonathan Leffler · Accepted Answer · 2013-06-28 17:52:25Z

3

What happens when you run the script with bash -x? I ask because the here-document notation expects to find the end marker at the start of a line. When I run this code:

#!/bin/bash

    contact=$(cat - <<EOF
    input from here document
    second line
    EOF
    )

echo "$contact"

I get errors like:

eof.sh: line 3: unexpected EOF while looking for matching `)'
eof.sh: line 10: syntax error: unexpected end of file

If the lines start with tabs, you can use a dash before the end-of-file marker to indicate that leading tabs should be ignored.

#!/bin/bash

        contact=$(cat - <<-EOF
        input from here document
        second line
        EOF
        )

echo "$contact"

This outputs:

input from here document
second line

Replace those tabs with blanks and you are back into the syntax errors. Although I've couched this in terms of bash, I believe you run into the same issues with Korn and Bourne shells too.

So, my suspicion is that your problem is related to the formatting of the here-document in your code, but you should have been seeing some sort of error, so I'm a bit puzzled. You should be getting the substitutions you wanted made:

#!/bin/bash

description="The Description"

        contact=$(cat - <<-EOF
        input from here document
        second line with '$description' embedded.
        EOF
        )

echo "$contact"

This yields:

input from here document
second line with 'The Description' embedded.

Using bash -x can be helpful to trace the execution of a command.

All of this is only coincidentally related to Oracle and SQL*Plus.

answered Jun 28, 2013 at 17:52

Jonathan Leffler

759k145 gold badges961 silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Leandro Toshio Takeda Over a year ago

I guess I found the problem with bash -x + echo 'Destination: x ' Destination: x + case "$type" in + echo 'Type is outbound: 2' Type is outbound: 2 ++ sqlplus -s user/pass@EDDB + contact=' no rows selected' + echo + echo no rows selected no rows selected + echo The query is returning lot of spaces: + echo 'Destination: x ' <<<<< blank spaces I set the: set linesize 1000 set trimspool on set pagesize 0 set colsep # but looks like it did not work.

Emmanuel · Accepted Answer · 2013-06-28 16:40:49Z

0

Please try to remove the single quotes around your variable: they prevent your variable to be interpreted (contrary to double quotes).

That should be:

SELECT email FROM table WHERE partner = ${destination};

answered Jun 28, 2013 at 16:40

Emmanuel

14.2k12 gold badges53 silver badges73 bronze badges

2 Comments

Leandro Toshio Takeda Over a year ago

Now it returned: SELECT email FROM table WHERE partner = X; script.sh result.csv SELECT.sql ERROR at line 1: ORA-00904: "X": invalid identifier Did not work, but thanks for the suggestion.

shellter Over a year ago

SQL requires a text value to be surrounded by single quotes.

Collectives™ on Stack Overflow

How can I pass a variable to a query inside a shell script?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related