CODEWITHSUNDEEP
shellunixscriptingnewlinesqlplus
pradipti
pradipti

Reputation: 85

Need to pass string with newline character from Unix shell script to sql query

I am using unix shell KSH scripting to do some table cleanup. I have a file "partner.txt" with 5000 line like this

>cat partner.txt

aaa0000
aaa0001
aaa0002
...
...
aaa5000

Using this file, I am supposed to clean few tables with matching, say agreements of the partners. So i am constructing a partner list string in the format that i can use in the sql statement with 'IN' clause (select * from tab where partner IN partner_list)

('aaa0000',
'aaa0001','aaa0002',...,'aaa0010',
'aaa0011','aaa0012',...,'aaa0020',
...
'aaa4990','aaa4991',...,'aaa5000')

I am assigning the string to partner_list variable like this.

export BO="("
export BC=")"
export BQ="('"
export QC="','"
export QB="')"
export  C=","
export CE=","'\n'
export QCE="',"'\n'"'"

partnerListLine=${BO}
while read partnerline;
do
    if [ `expr ${counter} % 10` -eq 0 ]
    then
        partnerListLine=${partnerListLine}${partnerline}${CE}

    elif [ ${counter} -lt ${numOfObsoletePartner} ]
    then
        partnerListLine=${partnerListLine}${partnerline}${C}
    fi 
    counter=`expr ${counter} + 1`
done < partner.txt
partnerListLine=${partnerListLine}${partnerline}${BC}

Then I am using this partner list to fetch my agreement list like

SQL_agreement='select distinct a.agreement from partner_agreement_map a where a.partner in ${partner_list} order by agreement asc;'

I needed the newline character in my partner list since i was using sqlplus and was encountering SP2-0027: Input is too long (> 2499 characters) I am adding the newline character by appending the below to my partner list string after N partners

CE=","'\n'

This worked fine when i was using sqlplus directly in the script.

But when i try to pass this partner_list string as parameter to a sql script, it shows '\n' in the query.

This is how i call my sql script and pass the parameter

sqlplus -s ${REFERENCE_DB_USER}/${REFERENCE_DB_PASS}@${DATABASE_INSTANCE} << !!
set serveroutput on size 10000;
set feedback off;
set verify off;
set echo off;
set term off;
set pagesize 0;
SET linesize 1000;
SET TRIMSPOOL ON;

spool  1_del_agreement_spool_$$.lst;

@1_del_agreement.sql ${partner_list};

spool off;

exit;
/
!!

this is my spooled file

>cat 1_del_agreement_spool_18165.lst                                   <

select distinct a.agreement from partner_agreement_map a where a.partner in ('aaa0000',\n'aaa0001','aaa0002','aaa0003',...'aaa0010',\n'aaa0011'...) order by agreement asc
                                                                                    *
ERROR at line 1:
ORA-00907: missing right parenthesis

How can i maintain the newline character when i pass the parameter to the sql script and not have it replaced to '\n'? I have tried ANSI-C quoting but failed.

Please let me know if you would need more details of the shell or sql script

Upvotes: 0

Views: 1991

Answers (3)

pradipti
pradipti

Reputation: 85

UPDATED MY ENTIRE SOLUTION DESIGN

After trying all night, i have given up. Thanks Aaron and mplf for your inputs.

I have decided to change my solution from file based to table based. I will be reading the partner.txt file and inserting the partners in a dummy temporary table. Then I can formulate queries with ease on other tables.

In fact, i think this should have been my first design :) There may be something very minor that i was missing in the previous design. But anyways, this will be much easier I wish my team lead ever reviews design rather than code formatting issues :P

Upvotes: 1

Aaron Digulla
Aaron Digulla

Reputation: 328724

I don't have a working solution but a hint: '\n' means "insert the literal backslash followed by n". So you tell the shell to leave this string alone.

Try NL=$(echo -e '\n') or similar to get a string variable which actually contains a newline. Then you can define CE=",$NL"

The shell might preserve this new line character as it processes the string.

Or use a tool like awk to create a string value with newlines which you assign to partner_list with partner_list=$(awk ...) to prevent the shell from doing any kind of processing of the value.

If that doesn't work, you may have to write the data to a file (with new lines).

Upvotes: 0

mproffitt
mproffitt

Reputation: 2527

If you are using bash you can use $'\n' to print a newline character which would make your example

if [ `expr ${counter} % 10` -eq 0 ]
then
    partnerListLine=${partnerListLine}${partnerline}"',"$'\n'"'"
else
    ...

Example:

$ echo hello"',"$'\n'"'"
hello',
'

Upvotes: 0

Related Questions