1

I have a script which is working fine in TOAD:

SELECT max (ID)+1 from apps_details;

When I'm trying to run it in the shell script, I'm getting an error:

DB_CONNECT="XXXXX/XXXXX@XXXXXX"

echo "Inserting the DataBase"
/oravl01/oracle/12.1.0.1/bin/sqlplus -s $DB_CONNECT <<END
set head off
set feedback off
set pagesize 2400
set linesize 2048

ID_VAL=`SELECT max (ID)+1 from apps_details;`
exit
END
/pciuser/tools/jenkins/jenkins/scripts/Jenkins_Internal/Create_Jenkins_Container/InsertToDB.ksh[31]: syntax error at line 1: `(' unexpected

Line 31 refers to oravl01 line, but I know the issue is not there, as once I'm removing the ID_VAL assignment, it works.

what is the correct syntax to write it?

  • Looks like you need something along the lines of unix.stackexchange.com/q/299545/117549 – Jeff Schaller Aug 14 at 14:28
  • 1
    What exactly are you trying to do? Is the ID_VAL a shell variable or something in the SQL? And the set commands? Are those the shell builtin set or some sort of SQL thing? – terdon Aug 14 at 14:37
  • @terdon ID_VAL is a shell variable, which I later want to use for an INSERT SQL statement. the set command also belongs to the SQL. – arielma Aug 14 at 16:16
  • I'm not sure what you're asking about, arielma. Do you mean indenting the lines between the <<END and END? EOF is "End of File", so it doesn't make sense in the context of alignment. If it helps clarify your question, please edit your question using this "edit" link and while you're at it, put your "ID_VAL is a shell variable..." update into the question, too. Thank you! – Jeff Schaller Aug 14 at 16:27
1

I think it is much cleaner (and simpler) to first define your command and then run it to save its output:

DB_CONNECT="XXXXX/XXXXX@XXXXXX"

read -r -d '' command <<END
set head off
set feedback off
set pagesize 2400
set linesize 2048
SELECT max (ID)+1 from apps_details;
exit
END

echo "Inserting the DataBase"
ID_VAL=$(/oravl01/oracle/12.1.0.1/bin/sqlplus -s "$DB_CONNECT" <<<"$command")

That way, you can easily store the output in a variable and you have the command separate from the flow of the script which will make it easier both to understand and to maintain in future.

  • It was close, but eventually, other command worked for me. posting the answer – arielma Aug 14 at 17:12
  • @arielma does a simple command like var=$(/oravl01/oracle/12.1.0.1/bin/sqlplus -s "$DB_CONNECT" "select 1 from apps_details") work? I don't know sqlplus at all. The mysql command needs an -e when you run a command from the terminal, are you sure the format is right? And do you need the set stuff? Why, if you're not looking at an actual table but only want the results? Does removing those help? – terdon Aug 14 at 17:19
  • @terdon At least in my environment sqlplus ... "$command" fails, while sqlplus ... <<<"$command" works. I.e. it won't accept a script passed to it as an argument (string), but it will be happy to read it from standard input. – fra-san Aug 14 at 17:31
  • @fra-san ah! That makes sense, I should have realized from the command int he question! Thanks. – terdon Aug 14 at 17:32
  • 1
    @arielma if you have a new question, please post it separately. It's very hard to do this in the comments. However, I'm willing to bet your problem is that you aren't quoting your variables. – terdon Aug 14 at 17:44
1

These commands worked for me:

DB_CONNECT="XXXXX/XXXXX@XXXXX"

ID_VAL=$(sqlplus -s $DB_CONNECT <<END
set head off
set feedback off
set pagesize 2400
set linesize 2048
SELECT max (ID)+1 from apps_details;
exit;
END
)

echo $ID_VAL
  • Great! Glad you solved it and thank you for taking the time to post your solution. – terdon Aug 14 at 17:33
0

Here are some alternatives, along the lines of terdon's answer, that try to make it easier to handle SQL*Plus' output given that you are trying to fetch it into the shell variable ID_VAL.

By default, SQL statements executed in SQL*Plus produce formatted reports, made of columns whose width will depend on the respective data types. This will lead to some possibly unwanted effects: for instance, the left-padded numbers you pointed out in a comment.

Demonstration:

$ DB_CONNECT="user/password@db"
$ ID_VAL="$(sqlplus -s $DB_CONNECT <<'END'
set feedback off
set head off
set pagesize 0
SELECT 123 FROM DUAL
/
END
)"
$ printf '"%s"\n' "$ID_VAL"
"       123"
$ printf '%s' "$ID_VAL" | od -An -tx1
 20 20 20 20 20 20 20 31 32 33

Some alternatives:

  1. If you are selecting a number, convert it to a string and trim it in your SQL statement. With everything else in the above script unchanged (I'll keep using my dummy SELECT statement, for ease of testing):

    SELECT TRIM(TO_CHAR(123)) FROM DUAL;
    
  2. Use dbms_output.put_line in a PL/SQL block:

    ID_VAL="$(sqlplus -s $DB_CONNECT <<'END'
    set serveroutput on
    set feedback off
    DECLARE
    myvar NUMBER;
    BEGIN
    SELECT 123 INTO myvar FROM DUAL;
    dbms_output.put_line(myvar);
    END;
    /
    END
    )"
    

    Note that set serveroutput on is required, no output is shown otherwise.

With both (1) and (2) you will get:

$ printf '"%s"\n' "$ID_VAL"
"123"
$ printf '%s' "$ID_VAL" | od -An -tx1
 31 32 33

Note, however, that these approaches will fail if you are selecting a string with flanking blank characters you want to preserve (e.g. foo ). You may enclose that string in quotes and remove them afterwards, or:

  1. Let SQL*Plus format its output as CSV:

    ID_VAL="$(sqlplus -s -M "CSV ON" $DB_CONNECT <<'END'
    set feedback off
    set head off
    SELECT '  foo  ' FROM DUAL;
    END
    )"
    printf '"%s"\n' "$ID_VAL"
    ""  foo  ""
    

    Unlike numbers, strings will be double-quoted, requiring a bit of post-processing. Assuming that the returned value does not contain <newline> characters, one way could be to use sed to remove the starting and the ending " characters (everything else unchanged):

    ID_VAL="$(sqlplus -s -M "CSV ON" $DB_CONNECT <<'END' | sed 's/^"//; s/"$//;'
    

The best option will depend on your actual use case.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.