0

Is there a longer type variable for building lengthy sql statements? how add new line too? I want to store the statement in a very long variable text so I can run it from sql prompt. I think it will exceed limit if I use normal variable. thanks

    export DATABASE_LIST="/location/dblist.cfg"
    export SQL_INSERT_TEXT=""

    while read -r USERID ORACLE_SID2
    do

    ### how do I do this if too long?? and add new line
    SQL_INSERT_TEXT="${SQL_INSERT_TEXT} longgSqlStatements; \newline"

    done < <(tac $DATABASE_LIST)

    echo ${SQL_INSERT_TEXT}


    export sqllog = "/location/sqllog.log"
    $ORACLE_HOME/bin/sqlplus -S accnt_name@db_name << EOF >${sqllog}
    ${SQL_INSERT_TEXT}
    EOF
  • 1
    You don't seem to use USERID nor ORACLE_SID2 in your loop. Are these variables needed at all? Why don't you just pipe the output of tac directly into sqlplus (possibly with an awk preprocessing stage in-between)? – Kusalananda May 9 at 18:16
  • @Kusalananda I'm not sure how I will awk it because I need loop to generate sql, also USERID and ORACLE_SID2 are used within the long sql statement – aymanzone May 9 at 18:25
  • It looks like you are trying to append longgSqlStatements; \n to the end of a here document. Is that correct? – ctrl-alt-delor May 9 at 18:47
1

Linux shells generally don't have any variable types. There are just strings, and the maximum length of a string can be had by running getconf ARG_MAX; on my system the maximum string is about 2M chars long.

Try using bash's builtin printf -v to conveniently load a variable with new lines, like this:

printf -v s "%s\n" hello there, this works "with quotes" too\.
echo "$s"

Output:

hello
there,
this
works
with quotes
too.

So the syntax might be something like:

printf -v SQL_INSERT_TEXT '%s\n' "${SQL_INSERT_TEXT}" "longgSqlStatements;"

Or, depending on what's needed, perhaps:

printf -v SQL_INSERT_TEXT '%s\n' "${SQL_INSERT_TEXT} longgSqlStatements;"
  • thanks @agc that helps a lot. cheers – aymanzone May 10 at 16:30

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.