CODEWITHSUNDEEP
oracle-databaseshellsqlplus
Leandro Toshio Takeda
Leandro Toshio Takeda

Reputation: 1639

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

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';

Upvotes: 0

Views: 3896

Answers (2)

Jonathan Leffler
Jonathan Leffler

Reputation: 753725

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.

Upvotes: 3

Emmanuel
Emmanuel

Reputation: 14209

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};

Upvotes: 0

Related Questions